Mysql主从同步故障处理：cannot be converted from type ‘longblob’ to type ‘text’
一、从库报错信息
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.12.7.135
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 508622884
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1677
Last_Error: Column 8 of table 'lxpm.sys_oper_log' cannot be converted from type 'longblob' to type 'text'
Skip_Counter: 0
Exec_Master_Log_Pos: 496957738
Relay_Log_Space: 11665673
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1677
Last_SQL_Error: Column 8 of table 'lxpm.sys_oper_log' cannot be converted from type 'longblob' to type 'text'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 56037a73-bc90-11ec-b7f7-0050568ff83c
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 220513 10:04:35
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
二、结合报错信息，对主库和从库的lxpm用户下的sys_oper_log表结构进行比较
主库：
mysql> desc lxpm.sys_oper_log;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| module | varchar(50) | YES | | | |
| content | longtext | YES | | NULL | |
| description | varchar(10) | YES | | | |
| operator_type | int(1) | YES | | 0 | |
| username | varchar(50) | YES | | | |
| user_id | varchar(255) | YES | | | |
| ip | varchar(50) | YES | | | |
| returns | longtext | YES | | NULL | |
| create_time | datetime | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
从库：
mysql> desc lxpm.sys_oper_log;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| module | varchar(50) | YES | | | |
| content | longtext | YES | | NULL | |
| description | varchar(10) | YES | | | |
| operator_type | int(1) | YES | | 0 | |
| username | varchar(50) | YES | | | |
| user_id | varchar(255) | YES | | | |
| ip | varchar(50) | YES | | | |
| returns | text | YES | | NULL | |
| create_time | datetime | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
对比结果发现returns字段的类型不一致；
通过查看主从库的部署时间来看字段的修改时间
从库：
[root@pmsbak mysql]# ll
total 369176
-rw-r-----. 1 mysql mysql 56 May 11 10:15 auto.cnf
-rw-------. 1 root root 1679 May 11 10:15 ca-key.pem
-rw-r--r--. 1 root root 1107 May 11 10:15 ca.pem
-rw-r--r--. 1 root root 1107 May 11 10:15 client-cert.pem
-rw-------. 1 root root 1679 May 11 10:15 client-key.pem
-rw-r-----. 1 mysql mysql 362 May 11 10:26 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 May 11 15:53 ibdata1
-rw-r-----. 1 mysql mysql 50331648 May 11 15:53 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 May 11 15:53 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 May 13 10:34 ibtmp1
drwxr-x---. 2 mysql mysql 4096 May 11 11:25 lxpm
-rw-r-----. 1 mysql mysql 132 May 13 10:34 master.info
drwxr-x---. 2 mysql mysql 4096 May 11 10:15 mysql
-rw-r-----. 1 mysql mysql 170987103 May 11 10:46 mysql-bin.000001
-rw-r-----. 1 mysql mysql 19 May 11 10:26 mysql-bin.index
-rw-r-----. 1 mysql mysql 207 May 13 10:03 mysql-relay-bin.000001
-rw-r-----. 1 mysql mysql 13972881 May 13 10:33 mysql-relay-bin.000002
-rw-r-----. 1 mysql mysql 50 May 13 10:03 mysql-relay-bin.index
srwxrwxrwx. 1 mysql mysql 0 May 11 10:26 mysql.sock
-rw-------. 1 mysql mysql 5 May 11 10:26 mysql.sock.lock
drwxr-x---. 2 mysql mysql 8192 May 11 10:15 performance_schema
-rw-r-----. 1 mysql mysql 29433 May 13 10:04 pmsbak.err
-rw-r-----. 1 mysql mysql 5 May 11 10:26 pmsbak.pid
-rw-------. 1 root root 1675 May 11 10:15 private_key.pem
-rw-r--r--. 1 root root 451 May 11 10:15 public_key.pem
-rw-r-----. 1 mysql mysql 65 May 13 10:03 relay-log.info
-rw-r--r--. 1 root root 1107 May 11 10:15 server-cert.pem
-rw-------. 1 root root 1679 May 11 10:15 server-key.pem
drwxr-x---. 2 mysql mysql 8192 May 11 10:15 sys
通过查看从库部署时间为5月11日，在主库上查看字段的修改时间.
主库：
mysql> SELECT * FROM information_schema.tables WHERE table_schema='lxpm' AND table_name='sys_oper_log'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: lxpm
TABLE_NAME: sys_oper_log
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 66558
AVG_ROW_LENGTH: 4627
DATA_LENGTH: 307970048
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 4194304
AUTO_INCREMENT: 70489
CREATE_TIME: **2022-05-12 16:34:31**
UPDATE_TIME: 2022-05-13 10:21:35
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: row_format=DYNAMIC
TABLE_COMMENT: 操作日志记录
1 row in set (0.00 sec)
创建时间为：2022-05-12 16:34:31
说明在同步后修改过字段的类型，导致的同步异常；
三、解决方法
1、如果数据量不大，可以重新将数据库备份、然后在从库恢复后，开启同步进程
备份数据库：
备份整个数据库
$> mysqldump -uroot -p lxpm >lxpm.sql
备份数据库中的某个表
$> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql
备份多个数据库
$> mysqldump -u root -h host -p --databases dbname1, dbname2 > backdb.sql
备份系统中所有数据库
$> mysqldump -u root -h host -p --all-databases > backdb.sql
导入数据库：
先创建数据库，然后登陆到数据库，进入数据库，通过source 备份.sql 导入数据
mysql>create database lxpm;
mysql>use lxpm;
mysql>source lxpm.sql
查看主库的日志文件，然后在从库上开启同步进程
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 512601244
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
从库上执行以下语句，开启同步进程：
mysql> CHANGE MASTER TO MASTER_HOST='172.12.7.135',MASTER_USER='slave',MASTER_PASSWORD='mysqld', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=512601244;
在从库上执行“start slave;”并查看slave库状态show slave status\G;
2、通过直接修改备库的对应表的字段类型
mysql> alter table lxpm.sys_oper_log modify returns longtext null;
然后按照上面要求，在从库上开启同步进程
