[MYSQL] 从库 Last_SQL_Errno: 1197 问题处理过程

MySQL error code MY-001197 (ER_TRANS_CACHE_FULL): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

set global max_binlog_cache_size=10*1024*1024*1024; -- 实际值根据自己业务情况来, 基本上这个值就是限制事务大小

(root@127.0.0.1) [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.101.21
                  Master_User: repl
                  Master_Port: 3314
                Connect_Retry: 60
              Master_Log_File: m3314.000001
          Read_Master_Log_Pos: 128860
               Relay_Log_File: relay.000003
                Relay_Log_Pos: 362
        Relay_Master_Log_File: m3314.000001
             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: 1197
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'b68e2434-cd30-11ec-b536-000c2980c11e:7' at source log m3314.000001, end_log_pos 128829. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 87616
              Relay_Log_Space: 129473
              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: 1197
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'b68e2434-cd30-11ec-b536-000c2980c11e:7' at source log m3314.000001, end_log_pos 128829. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 866003314
                  Master_UUID: b68e2434-cd30-11ec-b536-000c2980c11e
             Master_Info_File: mysql.slave_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: 250102 16:37:21
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b68e2434-cd30-11ec-b536-000c2980c11e:1-7
            Executed_Gtid_Set: b68e2434-cd30-11ec-b536-000c2980c11e:1-6
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

-- 主库初始化参数
set global max_binlog_cache_size=4096;
exit;

-- 从库初始化参数
set global max_binlog_cache_size=4096;
exit;

-- 主库测试报错
create database db2025;
create table db2025.t1(id int, name text);
insert into db2025.t1 values(1,repeat('x',4096)); -- 会看到报错 ERROR 1197 (HY000)

-- 主库修改参数后再次测试
set global max_binlog_cache_size=4096000;
show variables like 'max_binlog_cache_size';
insert into db2025.t1 values(1,repeat('x',4096));  -- 还会报错,因为没有重新连接,虽然能查询到新的值了
exit;

-- 主库重连后插入数据
show variables like 'max_binlog_cache_size';
insert into db2025.t1 values(1,repeat('x',4096));  -- 这次应该就成功了

-- 从库修改参数(生产环境的时候,数据还没有同步过来, 所以从库看到的应该是正常的,但也得修改参数, 由于本次测试数据量小, 此时已经能看到参数报错了)
set global max_binlog_cache_size=4096000;
exit;

-- 查看主从状态
show slave status\G -- 能看到从库报错Last_Errno: 1197

-- 从库启动主从
start slave; -- 直接启动就行. 从库回放应该比主库快好几倍(一样配置的情况下.)