MySQL資料庫1236錯誤模擬和解決

chenoracle發表於2022-11-21

環境說明:

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章