Mysql主从同步故障处理：cannot be converted from type ‘longblob‘ to type ‘text‘

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;

然后按照上面要求，在从库上开启同步进程