国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

Mysql使用GTID跳過一個或多個失敗事務

IT那活兒 / 996人閱讀
Mysql使用GTID跳過一個或多個失敗事務
點擊上方“IT那活兒”公眾號，關注后了解更多內容，不管IT什么活兒，干就完了！！！
1

前 言

在Mysql運維過程中，偶爾會遇到這樣的情況，從庫發現同步出現異常，通過分析發現從庫表被認為刪除導致，或者大批量更新，通過確認這些表都是臨時表或者這部分數據可以丟棄，那么可以直接通過GTID跳過失敗事務即可。

使用gtid跳過事務有兩種方法：

  • set gtid_next,可以跳過單個事務;
  • set GTID_PURGED，可以跳過多個事務.

故障模擬場景環境：

  • LINUX 7.5

  • MYSQL 5.7.24主從配置

2

GTID介紹

GTID (Global Transaction ID)是全局事務ID,由主庫上生成的與事務綁定的唯一標識，這個標識不僅在主庫上是唯一的，在MySQL集群內也是唯一的。
GTID實際上是由UUID+TID組成的。其中UUID是一個MySQL實例的唯一標識。TID代表了該實例上已經提交的事務數量，并且隨著事務提交單調遞增。
server_uuid 一般是發起事務的uuid, 標識了該事務執行的源節點，存儲在數據目錄中的auto.cnf文件中，transaction_id 是在該主庫上生成的事務序列號，從1開始，1-2代表第二個事務；第1-n代表n個事務。
GTID = server_uuid:transaction_id
3

場景一處理過程

3.1 查看從庫復制狀態發現從庫復制到主庫binlog:mysql-bin.000009，pos：1377位置失敗。
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.57.22
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 1941
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 1550
Relay_Master_Log_File: mysql-bin.000009
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: 1051
Last_Error: Error Unknown table dbtest1.tab2 on query. Default database: dbtest1. Query: DROP TABLE `tab2` /* generated by server */
Skip_Counter: 0
Exec_Master_Log_Pos: 1377
Relay_Log_Space: 2404
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: 1051
Last_SQL_Error: Error Unknown table dbtest1.tab2 on query. Default database: dbtest1. Query: DROP TABLE `tab2` /* generated by server */
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
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: 200122 10:29:41
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-34
Executed_Gtid_Set: 66e8e5c1-3b2a-11ea-966e-000c29965524:1-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-31:34
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified
3.2 查看主從庫表信息發現從庫少了tab2，tab3,tab4。
  • 主庫：
mysql> show tables from dbtest1;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| tab1 |
| tab2 |
| tab3 |
| tab4 |
+-------------------+
4 rows in set (0.00 sec)
  • 從庫：
mysql> show tables from dbtest1;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| tab1 |
+-------------------+
1 row in set (0.00 sec)
3.3 分析主庫binlog:mysql-bin.000009，pos：1377開始往后的binlog日志。
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000009 --start-position=1377 > mysql-bin.000009.log
示例：
[mysql@gaussdb12 log]$ mysqlbinlog --base64-output=decode-rows -v mysql-bin.000009 --start-position=1377 > mysql-bin.000009.log
[mysql@gaussdb12 log]$
[mysql@gaussdb12 log]$ more mysql-bin.000009.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 1377
#200122 10:10:43 server id 330602  end_log_pos 1442 CRC32 0x9bde31e6    GTID last_committed=3        sequence_number=4       rbr_only=no
SET @@SESSION.GTID_NEXT= 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32/*!*/;
# at 1442
#200122 10:10:43 server id 330602  end_log_pos 1565 CRC32 0x07275b09    Query thread_id=6     exec_time=0     error_code=0
use `dbtest1`/*!*/;
SET TIMESTAMP=1579659043/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `tab2` /* generated by server */
/*!*/;
# at 1565
#200122 10:10:47 server id 330602  end_log_pos 1630 CRC32 0x5b73e3dd    GTID last_committed=4        sequence_number=5       rbr_only=no
SET @@SESSION.GTID_NEXT= 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33/*!*/;
# at 1630
#200122 10:10:47 server id 330602  end_log_pos 1753 CRC32 0xadd86b23    Query thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1579659047/*!*/;
DROP TABLE `tab3` /* generated by server */
/*!*/;
# at 1753
#200122 10:10:52 server id 330602  end_log_pos 1818 CRC32 0x639aca9b    GTID last_committed=5        sequence_number=6       rbr_only=no
SET @@SESSION.GTID_NEXT= 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:34/*!*/;
# at 1818
#200122 10:10:52 server id 330602  end_log_pos 1941 CRC32 0x41816cc3    Query thread_id=6     exec_time=0     error_code=0
--More--(0%)
[1]+ Stopped more mysql-bin.000009.log
[mysql@gaussdb12 log]$ more mysql-bin.000009.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 1377
#200122 10:10:43 server id 330602  end_log_pos 1442 CRC32 0x9bde31e6    GTID last_committed=3        sequence_number=4       rbr_only=no
SET @@SESSION.GTID_NEXT= 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32/*!*/;
# at 1442
#200122 10:10:43 server id 330602  end_log_pos 1565 CRC32 0x07275b09    Query thread_id=6     exec_time=0     error_code=0
use `dbtest1`/*!*/;
SET TIMESTAMP=1579659043/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `tab2` /* generated by server */
/*!*/;
# at 1565
#200122 10:10:47 server id 330602  end_log_pos 1630 CRC32 0x5b73e3dd    GTID last_committed=4        sequence_number=5       rbr_only=no
SET @@SESSION.GTID_NEXT= 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33/*!*/;
# at 1630
#200122 10:10:47 server id 330602  end_log_pos 1753 CRC32 0xadd86b23    Query thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1579659047/*!*/;
DROP TABLE `tab3` /* generated by server */
/*!*/;
# at 1753
#200122 10:10:52 server id 330602  end_log_pos 1818 CRC32 0x639aca9b    GTID last_committed=5        sequence_number=6       rbr_only=no
SET @@SESSION.GTID_NEXT= 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:34/*!*/;
# at 1818
#200122 10:10:52 server id 330602  end_log_pos 1941 CRC32 0x41816cc3    Query thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1579659052/*!*/;
DROP TABLE `tab4` /* generated by server */
/*!*/;
# at 1941
#200122 10:41:51 server id 330602  end_log_pos 2006 CRC32 0x4892fb28    GTID last_committed=6        sequence_number=7       rbr_only=no
SET @@SESSION.GTID_NEXT= 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:35/*!*/;
# at 2006
#200122 10:41:51 server id 330602  end_log_pos 3158 CRC32 0x9bde3958    Query thread_id=6     exec_time=1     error_code=0
use `dbtest2`/*!*/;
SET TIMESTAMP=1579660911/*!*/;
CREATE TABLE `tab3` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT ,
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT ,
`TABLE_NAME` varchar(64) NOT NULL DEFAULT ,
`TABLE_TYPE` varchar(64) NOT NULL DEFAULT ,
發現從pos 1377到pos 1941的操作為drop tab1,tab2,tab3。
3.4 這時候我們可以選擇GTID_NEXT進行一個事務一個事務跳，也可以使用gtid_purged跳過多個事務。
第一種方式使用GTID_NEXT進行單個事務跳躍：
  • 從庫：
stop slave sql_thread;
SET @@SESSION.GTID_NEXT= 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32; <<===GTID對應第三步查出來的失敗事務的GTID
show global variables like %gtid%; <<===查看全局GTID信息
begin;commit; <<===執行空事務
SET @@SESSION.GTID_NEXT=automatic;                          <<===GTID自動執行
start slave sql_thread; <<===啟動SQL線程
show slave statusG; <<===確認事務已跳過
示例：
mysql> SET @@SESSION.GTID_NEXT= 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like %gtid%;
+----------------------------------+-----------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-----------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 66e8e5c1-3b2a-11ea-966e-000c29965524:1-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-31:34 |
| gtid_executed_compression_period | 1000                                                                                    |
| gtid_mode | ON |
| gtid_owned | 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32#6 |
| gtid_purged |                                                                                         |
| session_track_gtids | OFF |
+----------------------------------+-----------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)

mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SET @@SESSION.GTID_NEXT=automatic;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
檢查同步狀態：
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.57.22
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 1941
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 1738
Relay_Master_Log_File: mysql-bin.000009
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: 1051
Last_Error: Error Unknown table dbtest1.tab3 on query. Default database: dbtest1. Query: DROP TABLE `tab3` /* generated by server */
Skip_Counter: 0
Exec_Master_Log_Pos: 1565
Relay_Log_Space: 2404
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: 1051
Last_SQL_Error: Error Unknown table dbtest1.tab3 on query. Default database: dbtest1. Query: DROP TABLE `tab3` /* generated by server */
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
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: 200122 10:40:18
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-34
Executed_Gtid_Set: 66e8e5c1-3b2a-11ea-966e-000c29965524:1-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-32:34
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified
從同步信息來看已跳過GTID:9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32事務了，現在報dbtest1.tab3不存在，如果繼續進行單個事務跳躍重復上面步驟即可。
3.5 接下來使用gtid_purged跳過多個事務。
stop slave;
reset master;
set global gtid_purged=9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33-34;
start slave;
示例：
mysql> set global gtid_purged=9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33-34;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
檢查同步狀態：
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.XX.XX
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 76825
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 76998
Relay_Master_Log_File: mysql-bin.000009
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: 76825
Relay_Log_Space: 77288
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: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
Master_Info_File: mysql.slave_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: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-38
Executed_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33-38
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

確認已和主庫同步。

4

場景二處理過程

4.1 從庫復制卡在一個表上的批量大事務或者是從庫數據跟主庫不一致導致同步復制失敗，通過GTID跳過大事務，重新初始化表。
查看slave狀態報錯：
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.XX.XX
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 82041
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 79665
Relay_Master_Log_File: mysql-bin.000009
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: 1032
Last_Error: Could not execute Update_rows event on table dbtest1.tab4; Cant find record in tab4, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the events master log mysql-bin.000009, end_log_pos 79825
Skip_Counter: 0
Exec_Master_Log_Pos: 79492
Relay_Log_Space: 82504
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: 1032
Last_SQL_Error: Could not execute Update_rows event on table dbtest1.tab4; Cant find record in tab4, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the events master log mysql-bin.000009, end_log_pos 79825
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
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: 200122 14:51:20
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-51
Executed_Gtid_Set: 66e8e5c1-3b2a-11ea-966e-000c29965524:1,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33-44
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified
4.2 發現update行在從庫中不存在，開始主庫備份表。
  • 主庫：
mysqldump -uroot -proot -hlocalhost --
socket=/mysqlhome/mysql_data/db/mysql.sock --single-
transaction --set-gtid-purged=on  --triggers --routines --
events dbtest1 tab4 >/tmp/tab4.sql
cat /tmp/tab4.sql |egrep SET |egrep -v "^/"
示例：
[mysql@gaussdb12 log]$ mysqldump -uroot -proot -hlocalhost --socket=/mysqlhome/mysql_data/db/mysql.sock --single-transaction --set-gtid-purged=on --triggers --routines --events dbtest1 tab4 >/tmp/tab4.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you dont want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[mysql@gaussdb12 log]$
[mysql@gaussdb12 log]$ cat /tmp/tab4.sql |egrep SET |egrep -v "^/"
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=66e8e5c1-3b2a-11ea-966e-000c29965524:13-37,
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
  • 從庫恢復：
reset master; //清空GTID_EXECUTED
cat /tmp/tab4.sql |mysql -uroot -proot --socket=/home/mysql_data/db/mysql.sock dbtest1
會執行備份文件中的SET @@GLOBAL.GTID_PURGED。@@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
> show slave status G
Retrieved_Gtid_Set: 59fe7a3e-9dd6-11e7-9d6c-000c29e57c69:1-22
Executed_Gtid_Set: 59fe7a3e-9dd6-11e7-9d6c-000c29e57c69:1-22
> start slave;
示例：
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like %gtid%;
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 1000  |
| gtid_mode | ON |
| gtid_owned |       |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------+
8 rows in set (0.01 sec)

[mysql@mysql11 db]$ cat /tmp/tab4.sql |mysql -uroot -proot --socket=/home/mysql_data/db/mysql.sock dbtest1
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show global variables like %gtid%;
+----------------------------------+---------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+---------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 66e8e5c1-3b2a-11ea-966e-000c29965524:13-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-61 |
| gtid_executed_compression_period | 1000                                                                                  |
| gtid_mode | ON |
| gtid_owned |                                                                                       |
| gtid_purged | 66e8e5c1-3b2a-11ea-966e-000c29965524:13-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-61 |
| session_track_gtids | OFF |
+----------------------------------+---------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.XX.XX
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 86630
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 79665
Relay_Master_Log_File: mysql-bin.000009
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: 1032
Last_Error: Could not execute Update_rows event on table dbtest1.tab4; Cant find record in tab4, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the events master log mysql-bin.000009, end_log_pos 79825
Skip_Counter: 0
Exec_Master_Log_Pos: 79492
Relay_Log_Space: 87093
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: 1032
Last_SQL_Error: Could not execute Update_rows event on table dbtest1.tab4; Cant find record in tab4, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the events master log mysql-bin.000009, end_log_pos 79825
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
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: 200122 14:51:20
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-63
Executed_Gtid_Set: 66e8e5c1-3b2a-11ea-966e-000c29965524:13-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-61
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> start slave sql_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.XX.XX
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 86630
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 86803
Relay_Master_Log_File: mysql-bin.000009
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: 86630
Relay_Log_Space: 87093
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: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
Master_Info_File: mysql.slave_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: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-63
Executed_Gtid_Set: 66e8e5c1-3b2a-11ea-966e-000c29965524:13-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-63
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified
問題處理
  • set global gtid_purged報ERROR 1840
mysql> set global gtid_purged=9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33-34;
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysql> show global GTID_EXECUTED;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near GTID_EXECUTED at line 1
mysql>
mysql> show global variables like %gtid%;
+----------------------------------+-----------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-----------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 66e8e5c1-3b2a-11ea-966e-000c29965524:1-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-32:34 |
| gtid_executed_compression_period | 1000                                                                                    |
| gtid_mode | ON |
| gtid_owned |                                                                                         |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

原因：

gtid_executed不為空導致。

解決方法：

  • 方法一：reset mater

    這個操作可以將當前庫的GTID_EXECUTED值置空。

  • 方法二：--set-gtid-purged=off
    在dump導出時，添加--set-gtid-purged=off參數，避免將gtid信息導出。
    mysqldump -uroot -p --set-gtid-purged=off -d sso > sso1.sql


本文作者：湯 杰（上海新炬王翦團隊）

本文來源：“IT那活兒”公眾號

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129327.html

相關文章

  • 深度分析 | MGR相同GTID產生不同transaction故障分析

    摘要：對于該故障的分析，我們要從主從實例相同，但是事務不同的原因入手，該問題猜測與相關，我們針對同步事務的時序做如下分析。接受者被動接收提議者的提議，并記錄和反饋，或學習達成共識的提議。節點將的提案信息發送至組內，仍收到了大多數成員返回。 本文是由愛可生運維團隊出品的「MySQL專欄」系列文章，內容來自于運維團隊一線實戰經驗，涵蓋MySQL各種特性的實踐，優化案例，數據庫架構，HA，監控等...

    wuaiqiu 評論0 收藏0

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<