Stories

Detail Return Return

mysql主從複製error 1236 - Stories Detail

主庫
主庫查看gtid_purged
show master status;

從庫
關閉複製
stop slave;
重置slave和master的gtid
reset master;
reset slave;
重新設置gtid
set @@global.gtid_purged='主庫gtid';
開啓複製
start slave;

操作步驟如下：

--主庫
mha@testmydb02 14:32:  [(none)]> show master status;
+-----------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |
+-----------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| mybinlog.000025 |      519 |              |                  | 2dcd640c-0f05-11ee-b4f9-005056bac149:1-19,
8273e8f6-0e50-11ee-a2d1-005056baf9ae:1-78 |
+-----------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
--從庫

mha@testmydb01 14:55:35 [(none)]> show slave status\G
 *************************** 1. row ***************************
 Slave_IO_State:
 Master_Host: 10.98.20.122
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File:
 Read_Master_Log_Pos: 4
 Relay_Log_File: testmydb01-relay-bin.000001
 Relay_Log_Pos: 4
 Relay_Master_Log_File:
 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: 0
 Relay_Log_Space: 157
 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: 13114
 Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. The GTID set sent by the replica is '2dcd640c-0f05-11ee-b4f9-005056bac149:1-10:12-18,
 8273e8f6-0e50-11ee-a2d1-005056baf9ae:1-78', and the missing transactions are '2dcd640c-0f05-11ee-b4f9-005056bac149:11''
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 122
 Master_UUID: 2dcd640c-0f05-11ee-b4f9-005056bac149
 Master_Info_File: mysql.slave_master_info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
 Master_Retry_Count: 86400
 Master_Bind:
 Last_IO_Error_Timestamp: 250928 14:55:35
 Last_SQL_Error_Timestamp:
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Retrieved_Gtid_Set:
 Executed_Gtid_Set: 2dcd640c-0f05-11ee-b4f9-005056bac149:1-10:12-18,
 8273e8f6-0e50-11ee-a2d1-005056baf9ae:1-78
 Auto_Position: 1
 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)

mha@testmydb01 14:55:39 [(none)]> stop slave;
 Query OK, 0 rows affected, 1 warning (0.00 sec)

mha@testmydb01 14:56:46 [(none)]> reset slave;
 Query OK, 0 rows affected, 1 warning (0.01 sec)

mha@testmydb01 14:57:53 [(none)]> show master status;
 +-----------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------+
 | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                          |
 +-----------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------+
 | mybinlog.000014 |     5426 |              |                  | 2dcd640c-0f05-11ee-b4f9-005056bac149:1-10:12-18,
 8273e8f6-0e50-11ee-a2d1-005056baf9ae:1-78 |
 +-----------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

mha@testmydb01 14:58:23 [(none)]> reset master;
 Query OK, 0 rows affected (0.01 sec)

mha@testmydb01 14:58:32 [(none)]> show master status;
 +-----------------+----------+--------------+------------------+-------------------+
 | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +-----------------+----------+--------------+------------------+-------------------+
 | mybinlog.000005 |      157 |              |                  |                   |
 +-----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

mha@testmydb01 14:58:35 [(none)]> set @@global.gtid_purged='2dcd640c-0f05-11ee-b4f9-005056bac149:1-17,8273e8f6-0e50-11ee-a2d1-005056baf9ae:1-48';
 Query OK, 0 rows affected (0.00 sec)

mha@testmydb01 14:59:47 [(none)]> start slave;
 Query OK, 0 rows affected, 1 warning (0.01 sec)

mha@testmydb01 15:00:34 [(none)]> show master status;
 +-----------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
 | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |
 +-----------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
 | mybinlog.000005 |     1205 |              |                  | 2dcd640c-0f05-11ee-b4f9-005056bac149:1-19,
 8273e8f6-0e50-11ee-a2d1-005056baf9ae:1-48 |
 +-----------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

mha@testmydb01 15:00:38 [(none)]> show slave status\G
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for source to send event
 Master_Host: 10.98.20.122
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mybinlog.000025
 Read_Master_Log_Pos: 519
 Relay_Log_File: testmydb01-relay-bin.000006
 Relay_Log_Pos: 693
 Relay_Master_Log_File: mybinlog.000025
 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: 519
 Relay_Log_Space: 1162
 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: 122
 Master_UUID: 2dcd640c-0f05-11ee-b4f9-005056bac149
 Master_Info_File: mysql.slave_master_info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Replica 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: 2dcd640c-0f05-11ee-b4f9-005056bac149:18-19
 Executed_Gtid_Set: 2dcd640c-0f05-11ee-b4f9-005056bac149:1-19,
 8273e8f6-0e50-11ee-a2d1-005056baf9ae:1-48
 Auto_Position: 1
 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)
MySQL
Favorites

Add a new Comments

Some HTML is okay.