MySQL資料庫1236錯誤模擬和解決
環境說明：
DB:MySQL 雙主
OS:Redhat 7.9
172.16.6.137 cjc-db-01
172.16.6.138 cjc-db-02
相關引數
[mysql@cjc-db-02 scripts]$ cat /etc/my.cnf|grep sync
sync_binlog=10
[mysql@cjc-db-02 scripts]$ cat /etc/my.cnf|grep shutdown
innodb_fast_shutdown=1
引數說明
1.sync_binlog
sync_binlog=0，當事務提交之後，MySQL不做fsync之類的磁碟同步指令重新整理binlog_cache中的資訊到磁碟，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁碟。
sync_binlog=n，當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟。
在MySQL中系統預設的設定是sync_binlog=0，也就是不做任何強制性的磁碟重新整理指令，這時候的效能是最好的，但是風險也是最大的。因為一旦系統Crash，在binlog_cache中的所有binlog資訊都會被丟失。而當設定為“1”的時候，是最安全但是效能損耗最大的設定。因為當設定為1的時候，即使系統Crash，也最多丟失binlog_cache中未完成的一個事務，對實際資料沒有任何實質性影響。
1236相關的結果錯誤資訊
---1 Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size' ---2 Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' ---3 Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been'
檢視版本
MySQL [(none)]> select version(); +------------+ | version() | +------------+ | 5.7.36-log | +------------+ 1 row in set (0.13 sec)
檢視主從狀態
172.16.6.137 cjc-db-01
MySQL [(none)]> MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.6.138 Master_User: repl Master_Port: 13309 Connect_Retry: 10 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 234 Relay_Log_File: mysql-relay-bin.000005 Relay_Log_Pos: 407 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 234 Relay_Log_Space: 820 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 6138 Master_UUID: 17733276-6720-11ed-862d-0800275cac40 Master_Info_File: /mysqldata/13309/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-3 Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-3, 4a8a6572-6650-11ed-b79c-0800272944a2:1-9 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
檢視gtid相關資訊
MySQL [(none)]> show global variables like '%gtid%'; +----------------------------------+------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------+------------------------------------------------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 17733276-6720-11ed-862d-0800275cac40:1-3, 4a8a6572-6650-11ed-b79c-0800272944a2:1-9 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+------------------------------------------------------------------------------------+ 8 rows in set (0.02 sec)
檢視主從狀態
172.16.6.138 cjc-db-02
MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.6.137 Master_User: repl Master_Port: 13309 Connect_Retry: 10 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 234 Relay_Log_File: mysql-relay-bin.000005 Relay_Log_Pos: 407 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 234 Relay_Log_Space: 820 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 6137 Master_UUID: 4a8a6572-6650-11ed-b79c-0800272944a2 Master_Info_File: /mysqldata/13309/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4a8a6572-6650-11ed-b79c-0800272944a2:8-9 Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-3, 4a8a6572-6650-11ed-b79c-0800272944a2:8-9 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
檢視GTID相關資訊
MySQL [(none)]> show global variables like '%gtid%'; +----------------------------------+------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------+------------------------------------------------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 17733276-6720-11ed-862d-0800275cac40:1-3, 4a8a6572-6650-11ed-b79c-0800272944a2:8-9 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+------------------------------------------------------------------------------------+ 8 rows in set (0.01 sec)
場景一：
1.模擬主庫執行大量小事務時，意外中斷主庫後。
2.新主庫繼續執行大量小事務。
3.啟動原主庫，兩邊主從關係正常，沒有模擬出1236錯誤。
實驗如下：
137 執行插入指令碼
MySQL [(none)]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 11953 | +----------+ 1 row in set (0.02 sec)
插入指令碼
[mysql@cjc-db-02 scripts]$ more 01_insert.sql use cjc; insert into t1 values(5,'a',now()); insert into t1 values(5,'a',now()); ...... [mysql@cjc-db-02 scripts]$ cat 01_insert.sql |wc -l 1266483
插入
[mysql@cjc-db-01 scripts]$ mysql -uroot -p -vvv < 01_insert.sql > 01_insert.log
意外中斷資料庫，模擬故障
[mysql@cjc-db-01 ~]$ ps -ef|grep mysql|grep -v grep mysql 3750 3679 0 10:16 pts/0 00:00:04 mysqld --defaults-file=/etc/my.cnf --user=mysql [mysql@cjc-db-01 ~]$ kill -9 3750
137先不啟動
檢視138資料量
MySQL [(none)]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 29953 | +----------+ 1 row in set (0.04 sec)
繼續向138插入新資料，模擬VIP切換
[mysql@cjc-db-02 scripts]$ mysql -uroot -p -vvv < 01_insert.sql > 01_insert.log
再次檢視資料量
MySQL [(none)]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 65635 | +----------+ 1 row in set (0.07 sec)
啟動137資料庫
[mysql@cjc-db-01 scripts]$ mysqld --defaults-file=/etc/my.cnf --user=mysql&
檢查137資料量
MySQL [(none)]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 65635 | +----------+ 1 row in set (0.13 sec)
檢查137從庫狀態
MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.6.138 Master_User: repl Master_Port: 13309 Connect_Retry: 10 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 17650028 Relay_Log_File: mysql-relay-bin.000007 Relay_Log_Pos: 11923471 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 17650028 Relay_Log_Space: 11923718 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 6138 Master_UUID: 17733276-6720-11ed-862d-0800275cac40 Master_Info_File: /mysqldata/13309/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-35685 Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-35685, 4a8a6572-6650-11ed-b79c-0800272944a2:1-18009 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
檢查138主從狀態
MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.6.137 Master_User: repl Master_Port: 13309 Connect_Retry: 10 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 11352433 Relay_Log_File: mysql-relay-bin.000007 Relay_Log_Pos: 407 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 11352433 Relay_Log_Space: 820 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 6137 Master_UUID: 4a8a6572-6650-11ed-b79c-0800272944a2 Master_Info_File: /mysqldata/13309/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4a8a6572-6650-11ed-b79c-0800272944a2:8-18009 Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-35685, 4a8a6572-6650-11ed-b79c-0800272944a2:8-18009 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
場景二：大事務
1.模擬主庫執行一個大事務時，還沒執行完成，意外中斷主庫後。
2.新主庫繼續執行事務。
3.啟動原主庫，兩邊主從關係正常，沒有模擬出1236錯誤。
實驗如下：
137：
MySQL [(none)]> use cjc MySQL [cjc]> create table t2 like t1; MySQL [cjc]> set global autocommit=0;
執行2秒後中斷資料庫
MySQL [cjc]> insert into t2 select * from t1; ERROR 2013 (HY000): Lost connection to MySQL server during query [mysql@cjc-db-01 scripts]$ ps -ef|grep mysql|grep -v grep mysql 5074 3679 6 10:36 pts/0 00:00:21 mysqld --defaults-file=/etc/my.cnf --user=mysql
模擬主庫故障
[mysql@cjc-db-01 scripts]$ kill -9 5074
138庫
MySQL [(none)]> select count(*) from cjc.t2; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
138插入新資料
MySQL [(none)]> insert into cjc.t2 values(1,'a',now()); MySQL [(none)]> insert into cjc.t2 values(1,'a',now()); MySQL [(none)]> select count(*) from cjc.t2; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec)
啟動137資料庫
[mysql@cjc-db-01 scripts]$ mysqld --defaults-file=/etc/my.cnf --user=mysql&
檢查資料，雙向同步沒問題
MySQL [(none)]> select count(*) from cjc.t2; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec)
場景三：破壞當前binlog
1.模擬主庫執行一個大事務或多個小事務，還沒執行完成，意外中斷主庫後。
2.破壞原主庫當前binlog檔案。
3.新主庫繼續執行事務。
3.啟動原主庫，138到137主從中斷，報錯1236。
實驗如下：
137 執行插入指令碼
MySQL [(none)]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 71325 | +----------+ 1 row in set (0.13 sec)
插入指令碼
[mysql@cjc-db-01 scripts]$ mysql -uroot -p -vvv < 01_insert.sql > 01_insert.log
中斷資料庫，模擬故障
[mysql@cjc-db-01 ~]$ ps -ef|grep mysql|grep -v grep mysql 3750 3679 0 10:16 pts/0 00:00:04 mysqld --defaults-file=/etc/my.cnf --user=mysql [mysql@cjc-db-01 ~]$ kill -9 3750
137先不啟動
檢視138資料量
MySQL [(none)]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 80933 | +----------+ 1 row in set (0.08 sec)
繼續向138插入新資料，模擬VIP切換
[mysql@cjc-db-02 scripts]$ mysql -uroot -p -vvv < 01_insert.sql > 01_insert.log
再次檢視資料量
MySQL [(none)]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 93054 | +----------+ 1 row in set (0.09 sec)
破壞137最新binlog
[mysql@cjc-db-01 binlog]$ mv mysql-bin.000005 mysql-bin.000005.bak [mysql@cjc-db-01 binlog]$ cp mysql-bin.000001 mysql-bin.000005
啟動137資料庫
[mysql@cjc-db-01 scripts]$ mysqld --defaults-file=/etc/my.cnf --user=mysql&
檢查137資料量,比138少了1條資料
MySQL [(none)]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 93053 | +----------+ 1 row in set (0.21 sec)
檢查138同步狀態，報錯1236
Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'
MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.16.6.137 Master_User: repl Master_Port: 13309 Connect_Retry: 10 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 3211353 Relay_Log_File: mysql-relay-bin.000009 Relay_Log_Pos: 3210882 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3211353 Relay_Log_Space: 3211295 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: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 6137 Master_UUID: 4a8a6572-6650-11ed-b79c-0800272944a2 Master_Info_File: /mysqldata/13309/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 221121 10:54:24 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4a8a6572-6650-11ed-b79c-0800272944a2:8-27620 Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-53496, 4a8a6572-6650-11ed-b79c-0800272944a2:8-27620 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
其中Read_Master_Log_Pos=3211353，而137當前Position: 3856433
MySQL [(none)]> show master status\G; *************************** 1. row *************************** File: mysql-bin.000006 Position: 3856433 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-41373:41376-53496, 4a8a6572-6650-11ed-b79c-0800272944a2:1-18012 1 row in set (0.00 sec) ERROR: No query specified
檢查137同步狀態正常
MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.6.138 Master_User: repl Master_Port: 13309 Connect_Retry: 10 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 26659074 Relay_Log_File: mysql-relay-bin.000011 Relay_Log_Pos: 4050575 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 26659074 Relay_Log_Space: 4050822 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 6138 Master_UUID: 17733276-6720-11ed-862d-0800275cac40 Master_Info_File: /mysqldata/13309/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-53496 Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-41373:41376-53496, 4a8a6572-6650-11ed-b79c-0800272944a2:1-18012 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
檢視138 GTID
MySQL [(none)]> show global variables like '%gtid%'; +----------------------------------+--------------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------------------------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 17733276-6720-11ed-862d-0800275cac40:1-53496, 4a8a6572-6650-11ed-b79c-0800272944a2:8-27620 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+--------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec)
檢視137 GTID
MySQL [(none)]> show global variables like '%gtid%'; +----------------------------------+--------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------------------------------------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 17733276-6720-11ed-862d-0800275cac40:1-41373:41376-53496, 4a8a6572-6650-11ed-b79c-0800272944a2:1-18012 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | 17733276-6720-11ed-862d-0800275cac40:1-41373, 4a8a6572-6650-11ed-b79c-0800272944a2:1-18012 | | session_track_gtids | OFF | +----------------------------------+--------------------------------------------------------------------------------------------------------+ 8 rows in set (0.02 sec)
解決1238錯誤：
一.嘗試跳過事務
由於是雙主架構，無法在從庫清空GTID_EXECUTED
138
stop slave; set global gtid_purged='17733276-6720-11ed-862d-0800275cac40:1-53496,4a8a6572-6650-11ed-b79c-0800272944a2:8-27620,17733276-6720-11ed-862d-0800275cac40:1-41373:41376-53496,4a8a6572-6650-11ed-b79c-0800272944a2:1-18012,17733276-6720-11ed-862d-0800275cac40:1-41373,4a8a6572-6650-11ed-b79c-0800272944a2:1-18012'; ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. reset slave; set global gtid_purged='17733276-6720-11ed-862d-0800275cac40:1-53496,4a8a6572-6650-11ed-b79c-0800272944a2:8-27620,17733276-6720-11ed-862d-0800275cac40:1-41373:41376-53496,4a8a6572-6650-11ed-b79c-0800272944a2:1-18012,17733276-6720-11ed-862d-0800275cac40:1-41373,4a8a6572-6650-11ed-b79c-0800272944a2:1-18012'; ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
雙主架構下還需要執行reset master，執行後137到138的同步也會失敗。
二：重建主從庫關係
137和138 停止主從程式，清理複製配置
stop slave; reset slave all; reset master;
刪除137業務庫cjc，以138資料為準，重新同步資料
MySQL [(none)]> drop database cjc; Query OK, 2 rows affected (0.10 sec) MySQL [(none)]> create database cjc; Query OK, 1 row affected (0.00 sec)
備份138 cjc資料庫
mysqldump -uroot -p cjc --hex-blob --master-data=2 --single-transaction --routines --triggers --events --flush-logs > /home/mysql/bak/mysql_cjc_202221.sql [mysql@cjc-db-02 bak]$ ls -lrth mysql_cjc_202221.sql -rw-rw-r-- 1 mysql mysql 2.7M Nov 21 11:14 mysql_cjc_202221.sql
傳到137資料庫上
137還原資料
MySQL [(none)]> use cjc MySQL [(none)]> source mysql_cjc_202221.sql MySQL [cjc]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 93054 | +----------+ 1 row in set (0.05 sec)
138新增資料，模擬新業務
MySQL [(none)]> use cjc Database changed MySQL [cjc]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 93054 | +----------+ 1 row in set (0.07 sec) MySQL [cjc]> insert into t1 values(1,'a',now()); Query OK, 1 row affected (0.00 sec) MySQL [cjc]> insert into t1 values(1,'a',now()); Query OK, 1 row affected (0.00 sec) MySQL [cjc]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 93056 | +----------+ 1 row in set (0.08 sec)
137建立同步關係
[mysql@cjc-db-01 bak]$ grep "CHANGE MASTER TO MASTER_LOG_FILE" mysql_cjc_202221.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
執行
CHANGE MASTER TO MASTER_HOST='172.16.6.138', MASTER_USER='repl', MASTER_PASSWORD='1', MASTER_PORT=13309, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154, MASTER_CONNECT_RETRY=10;
啟動
MySQL [(none)]> start slave; Query OK, 0 rows affected (0.02 sec)
檢查同步狀態恢復正常
MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.6.138 Master_User: repl Master_Port: 13309 Connect_Retry: 10 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 822 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 988 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 822 Relay_Log_Space: 1195 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 6138 Master_UUID: 17733276-6720-11ed-862d-0800275cac40 Master_Info_File: /mysqldata/13309/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-2 Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-2, 4a8a6572-6650-11ed-b79c-0800272944a2:1-14 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
檢查資料，已經同步最新資料
MySQL [(none)]> select count(*) from cjc.t1; +----------+ | count(*) | +----------+ | 93056 | +----------+ 1 row in set (0.07 sec)
最後恢復138上的同步關係
檢查137位置資訊
MySQL [(none)]> show master status\G; *************************** 1. row *************************** File: mysql-bin.000001 Position: 3930806 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-2, 4a8a6572-6650-11ed-b79c-0800272944a2:1-14 1 row in set (0.00 sec) ERROR: No query specified
138新增資料
MySQL [cjc]> insert into t1 values(1,'a',now());
137位置資訊也會發生變化
MySQL [(none)]> show master status\G; *************************** 1. row *************************** File: mysql-bin.000001 Position: 3931442 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-4, 4a8a6572-6650-11ed-b79c-0800272944a2:1-14 1 row in set (0.00 sec) ERROR: No query specified
先對137上建立全域性鎖
FLUSH TABLES WITH READ LOCK;
138繼續新增資料
137位置資訊沒有變
*************************** 1. row *************************** File: mysql-bin.000001 Position: 3931442 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-4, 4a8a6572-6650-11ed-b79c-0800272944a2:1-14 1 row in set (0.00 sec) ERROR: No query specified
137執行
CHANGE MASTER TO MASTER_HOST='172.16.6.137', MASTER_USER='repl', MASTER_PASSWORD='1', MASTER_PORT=13309, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=3931442, MASTER_CONNECT_RETRY=10;
啟動
MySQL [cjc]> start slave; Query OK, 0 rows affected (0.01 sec)
主從恢復正常
MySQL [cjc]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.6.137 Master_User: repl Master_Port: 13309 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 3931442 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3931442 Relay_Log_Space: 527 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 6137 Master_UUID: 4a8a6572-6650-11ed-b79c-0800272944a2 Master_Info_File: /mysqldata/13309/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 17733276-6720-11ed-862d-0800275cac40:1-6 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
137上取消全域性鎖
UNLOCK TABLES;
檢查兩邊主從狀態恢復正常
MySQL [cjc]> show slave status\G;
結論：
1.MySQL雙主架構，正在執行事務的主庫異常中斷後，如果當前binlog也損壞了，可能會出現1236錯誤。
2.如果只是主從架構，不是雙主架構，透過跳過事務理論上可以恢復主從關係。
其他：
如果複製使用者密碼忘記怎麼辦？
1.可以重建複製使用者，當然也有檔案明文記錄複製使用者密碼：
具體在資料目錄下master.info檔案裡。
[mysql@cjc-db-01 data]$ cat master.info
2.如果業務庫太多，也可以透過直接初始化資料庫恢復主
(1)主從執行reset操作
stop slave; reset slave all; reset master;
(2)主庫全庫備份，並將備份檔案上傳到從庫
mysqldump -uroot -p --all-database --hex-blob --master-data=2 --single-transaction --routines --triggers --events --flush-logs > /home/mysql/bak/mysql_cjc_20221118.sql
(3)停從庫，刪除資料檔案
[mysql@cjc-db-02 bak]$ mysqladmin -uroot -p shutdown
刪除資料
rm -rf binlog/* rm -rf data/* rm -rf log/* rm -rf pid/* rm -rf redo/* rm -rf relaylog/* rm -rf socket/* rm -rf undo/*
(4)從庫資料庫初始化
###mysqld --defaults-file=/etc/my.cnf --initialize-insecure
啟動資料庫
[mysql@cjc-db-02 13309]$ mysqld --defaults-file=/etc/my.cnf --user=mysql &
恢復資料庫
grep "CHANGE MASTER TO MASTER_LOG_FILE"
(5)重建主從關係
例如：
CHANGE MASTER TO MASTER_HOST='172.16.6.138', MASTER_USER='repl', MASTER_PASSWORD='1', MASTER_PORT=13309, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154, MASTER_CONNECT_RETRY=10;
啟動
start slave;
(6)待單向主從正常後，建立另一個主從關係
也需要先申請全域性鎖
CHANGE MASTER TO MASTER_HOST='172.16.6.137', MASTER_USER='repl', MASTER_PASSWORD='1', MASTER_PORT=13309, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=505716, MASTER_CONNECT_RETRY=10;
啟動
start slave;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2924326/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mysql：1236常見錯誤MySql
- 【MySQL】Got fatal error 1236原因和解決方法MySqlGoError
- 如何使用ChatGPT模擬MySQL資料庫ChatGPTMySql資料庫
- MySQL資料庫常見錯誤及解決方案MySql資料庫
- MySQL插入資料1366錯誤解決方案MySql
- MySQL資料庫錯誤server_errno=2013的解決MySql資料庫Server
- ORA-04031錯誤分析和解決
- Oracle 資料庫連線錯誤解決方法Oracle資料庫
- MySQL 資料庫崩潰（crash）的常見原因和解決辦法MySql資料庫
- Oracle資料庫配置錯誤資訊解決方法(轉)Oracle資料庫
- mysql還原資料庫遇到Unknown command 錯誤MySql資料庫
- mysql主備庫資料不一致的原因和解決方案MySql
- sql server資料庫附加錯誤的解決過程SQLServer資料庫
- php7 使用 phpunit 部分錯誤和解決方案PHP
- BlueHost SSH連線常見錯誤和解決方法
- ora-371錯誤的原因和解決方法
- oracle資料庫常見故障和解決難度Oracle資料庫
- 模擬資料庫之部門員工資料庫
- JavaBean連結MySQL資料庫錯誤aq.executeQuery.Can not issue data manipulation statements with executeQuery()解決JavaBeanMySql資料庫
- 解決遷移資料庫錯誤，索引長度過長資料庫索引
- dbua後資料庫無法啟動錯誤的解決資料庫
- 資料庫日誌中Immediate Kill Session錯誤解決方法資料庫Session
- ORA-01502錯誤成因和解決方法
- Nginx502錯誤原因和解決方法總結Nginx
- 5個常見可用性錯誤和解決方案
- 恢復MySQL資料庫建立儲存過程是遇到錯誤MySql資料庫儲存過程
- MySQL 資料庫“十宗罪”（十大經典錯誤案例）MySql資料庫
- MySQL資料庫中的日誌檔案---(1)錯誤日誌MySql資料庫
- 【MySQL】複製1236錯誤（不同版本間binlog_checksum配置問題）MySql
- mysql與php錯誤解決MySqlPHP
- mysql錯誤解決總結MySql
- 【MySQL】解決mysql的 1594 錯誤MySql
- 解決android studio 模擬器取法啟動聲音的錯誤Android
- mysql資料庫連線過多的錯誤，可能的原因分析及解決辦法(轉)MySql資料庫
- 【EM】資料表誤刪除故障模擬及恢復
- SQL Server DACPAC資料庫部署錯誤SQLServer資料庫
- 資料庫ORA-0060錯誤資料庫
- 基於資料的決策：模擬與庫存管理（附PPT下載）