mysql8.0.17复制搭建及其gtid的1062和1032异常
参考资料：
https://blog.csdn.net/wzy0623/article/details/91982743
https://blog.51cto.com/20131104/2397443
# 附录
附录：mysql参数sql_log_bin配置 如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。 比如说,模拟主从同步复制异常。 -- 在从库上执行 mysql> set sql_log_bin=0;#设为0后，在Master数据库上执行的语句都不记录binlog mysql> delete from t1 where id = 3; mysql> set sql_log_bin=1; -- 要慎重使用global修饰符（set global sql_log_bin=0)，这样会导致所有在Master数据库上执行的语句都不记录到binlog，这肯定不是你想要的结果
# 环境：
/*
主库：10.192.30.53 从库：10.192.30.60 用户名：admin_m 密码：rA75MQy*R*y@KO4z%LZe
*/
# 创建同步复制账号
CREATE USER 'repl'@'10.192.30.%' IDENTIFIED WITH mysql_native_password BY 'DHTg9nRZ4AoRRV6BDQup'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.192.30.%';
# 一、基于GTID搭建复制
# 一、基于GTID搭建复制 -- 1、备份主库 yum -y install lz4-devel yum -y install lz4 mysqlpump -h 10.192.30.53 -P 3306 -uadmin_m -p"rA75MQy*R*y@KO4z%LZe" --default-character-set=utf8mb4 -A --add-drop-database --add-drop-table --add-drop-user --add-locks -C --compress-output=LZ4 --default-parallelism=2 --events --hex-blob --log-error-file=/tmp/dump.err --max-allowed-packet=33554432 --routines --set-gtid-purged=ON --single-transaction --triggers >/data/bak/all_dump_10.192.30.53.sql.lz4
# 解压
lz4 -d all_dump_10.192.30.53.sql.lz4 > all_dump_10.192.30.53.sql
# 传输至从库
scp all_dump_10.192.30.53.sql 10.192.30.60:/opt/
# 导入到从库
mysql> reset slave all; reset master; mysql -h 10.192.30.60 -P 3306 -uadmin_m -p"rA75MQy*R*y@KO4z%LZe" --default-character-set=utf8mb4 < all_dump_10.192.30.53.sql change master to master_host = '10.192.30.53', master_port = 3306, master_user = 'repl', master_password = 'DHTg9nRZ4AoRRV6BDQup', master_auto_position = 1; start slave; show slave statusG; 至此主从复制搭建完毕。
# 二、GTID运维
传统基于二进制坐标的复制中，从库由于某些错误导致复制中断时，一个可能的解决方案是设置sql_slave_skip_counter全局系统变量，
跳过导致错误的事件，然后重启复制。但启用GTID后，执行的单位由事件变为事务，因此该方法不再有效。
mysql> set global sql_slave_skip_counter=1; ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction mysql>
从错误消息可以看到，GTID跳过事务的方法是注入一个空事务，具体步骤为：
（1）定位出错事务的GTID。
从库报错我们需要获得从库执行的最后一个事务，方法有：
show slave status G 中的 Executed_Gtid_Set; show global variables like '%gtid%'; # gtid_executed show master status; # 中的Executed_Gtid_Set。
（2）将会话级系统变量gtid_next设置为上一步的GTID，如。
stop slave; set gtid_next='46760eb0-bcb3-11e9-a352-24a9c4e7e87e:73'; # Retrieved_Gtid_Set: 46760eb0-bcb3-11e9-a352-24a9c4e7e87e:66-73 begin; commit; set GTID_NEXT='AUTOMATIC'; start slave;
# 模拟1062故障
-- 模拟 1062 故障 -- 主库 CREATE TABLE `info_area` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '地区ID', `name` varchar(20) NOT NULL DEFAULT '' COMMENT '名称', `rel_id` varchar(50) NOT NULL DEFAULT '' COMMENT '关系ID', `pid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '父ID', `level` int(11) NOT NULL DEFAULT '0' COMMENT '类别，1、省份 2、市 3、区 4、县', PRIMARY KEY (`id`), UNIQUE KEY `UNQ_RID` (`rel_id`) USING BTREE, KEY `IDX_PID` (`rel_id`) ) ENGINE=InnoDB AUTO_INCREMENT=55185 DEFAULT CHARSET=utf8mb4 COMMENT='地区表'; INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10000', '北京', '10000', '10000', '1'); INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10002', '三环以内', '100001000110002', '10001', '3'); INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10003', '三环到四环之间', '100001000110003', '10001', '3'); mysql> select * from info_area; +-------+-----------------------+-----------------+-------+-------+ | id | name | rel_id | pid | level | +-------+-----------------------+-----------------+-------+-------+ | 10000 | 北京 | 10000 | 10000 | 1 | | 10002 | 三环以内 | 100001000110002 | 10001 | 3 | | 10003 | 三环到四环之间 | 100001000110003 | 10001 | 3 | +-------+-----------------------+-----------------+-------+-------+ 3 rows in set (0.00 sec) -- 从库上执行 set sql_log_bin=0; INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10001', '朝阳区', '1000010001', '10000', '2'); set sql_log_bin=1; -- 主库 INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10001', '朝阳区', '1000010001', '10000', '2'); -- 从库 show slave statusG; mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.192.30.53 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 705517 Relay_Log_File: fudao_db_cluster_003-relay-bin.000003 Relay_Log_Pos: 2478 Relay_Master_Log_File: mysql-bin.000002 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: test.%,information_schema.% Last_Errno: 1062 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '46760eb0-bcb3-11e9-a352-24a9c4e7e87e:79' at master log mysql-bin.000002, end_log_pos 705486. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0 Exec_Master_Log_Pos: 705195 Relay_Log_Space: 5837 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: 1062 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '46760eb0-bcb3-11e9-a352-24a9c4e7e87e:79' at master log mysql-bin.000002, end_log_pos 705486. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Replicate_Ignore_Server_Ids: Master_Server_Id: 3306 Master_UUID: 46760eb0-bcb3-11e9-a352-24a9c4e7e87e 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: 190813 13:47:05 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 46760eb0-bcb3-11e9-a352-24a9c4e7e87e:66-79 Executed_Gtid_Set: 46760eb0-bcb3-11e9-a352-24a9c4e7e87e: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 (0.00 sec) ERROR: No query specified mysql>
# 故障解决
gtid方式 mysql> stop slave; mysql> set GTID_NEXT='46760eb0-bcb3-11e9-a352-24a9c4e7e87e:79'; # Retrieved_Gtid_Set: 46760eb0-bcb3-11e9-a352-24a9c4e7e87e:66-79 mysql> begin; mysql> commit; mysql> set GTID_NEXT='AUTOMATIC'; mysql> start slave; mysql> show slave statusG; 注：传统方式 mysql> stop slave; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n; mysql> start slave; mysql> show slave statusG;
注意：
重启复制前需要将gtid_next设置为缺省值'automatic'。下面是个跳过多个事务的例子。 stop slave; set gtid_next='8eed0f5b-6f9b-11e9-94a9-005056a57a4e:980055'; begin;commit; set gtid_next='8eed0f5b-6f9b-11e9-94a9-005056a57a4e:980056'; begin;commit; set gtid_next='8eed0f5b-6f9b-11e9-94a9-005056a57a4e:980057'; begin;commit; set gtid_next='automatic'; start slave;
# 模拟 1032 故障
REATE TABLE `t1` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `xname` VARCHAR(20) NOT NULL DEFAULT '', `address` CHAR(20) NOT NULL DEFAULT '', `sex` TINYINT(1) NOT NULL DEFAULT '1', `hobby` VARCHAR(30) NOT NULL DEFAULT '', `age` TINYINT(2) DEFAULT '18', PRIMARY KEY (`id`), KEY `idx_name` (`xname`) ) ENGINE=INNODB; INSERT INTO `t1` (`xname`, `address`, `hobby`) VALUES ('edusoho_e', '上海', '开发'); INSERT INTO `t1` (`xname`, `address`, `hobby`) VALUES ('eduali_e', '南京', '开发'); INSERT INTO `t1` (`xname`, `address`, `hobby`) VALUES ('edutx_e', '广州', '开发'); INSERT INTO `t1` (`xname`, `address`, `hobby`) VALUES ('eduwy_e', '北京', '开发'); INSERT INTO `t1` (`xname`, `address`, `hobby`) VALUES ('eduajd_e', '天津', '开发'); mysql> select * from t1; +----+-----------+---------+-----+--------+------+ | id | xname | address | sex | hobby | age | +----+-----------+---------+-----+--------+------+ | 1 | edusoho_e | 上海 | 1 | 开发 | 18 | | 2 | eduali_e | 南京 | 1 | 开发 | 18 | | 3 | edutx_e | 广州 | 1 | 开发 | 18 | | 4 | eduwy_e | 北京 | 1 | 开发 | 18 | | 5 | eduajd_e | 天津 | 1 | 开发 | 18 | +----+-----------+---------+-----+--------+------+ 5 rows in set (0.00 sec) mysqlbinlog -v --base64-output=decode mysql-bin.000002 --start-position=702860 >2.log [root@fudao_db_cluster_001 logs]# cat 2.log /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 155 #190812 11:46:21 server id 3306 end_log_pos 124 CRC32 0x100f57e1 Start: binlog v 4, server v 8.0.17 created 190812 11:46:21 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; # at 702860 #190813 11:22:24 server id 3306 end_log_pos 702939 CRC32 0x4ffade6b GTID last_committed=72 sequence_number=73 rbr_only=yes original_committed_timestamp=1565666544911622 immediate_commit_timestamp=1565666544911622 transaction_length=313 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1565666544911622 (2019-08-13 11:22:24.911622 CST) # immediate_commit_timestamp=1565666544911622 (2019-08-13 11:22:24.911622 CST) /*!80001 SET @@session.original_commit_timestamp=1565666544911622*//*!*/; /*!80014 SET @@session.original_server_version=80017*//*!*/; /*!80014 SET @@session.immediate_server_version=80017*//*!*/; SET @@SESSION.GTID_NEXT= '46760eb0-bcb3-11e9-a352-24a9c4e7e87e:73'/*!*/; # at 702939 #190813 11:22:24 server id 3306 end_log_pos 703014 CRC32 0xd95cd7a1 Query thread_id=62 exec_time=0 error_code=0 SET TIMESTAMP=1565666544/*!*/; SET @@session.pseudo_thread_id=62/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!C utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; BEGIN /*!*/; # at 703014 #190813 11:22:24 server id 3306 end_log_pos 703078 CRC32 0x02d5cf92 Table_map: `db53`.`t1` mapped to number 2012 # at 703078 #190813 11:22:24 server id 3306 end_log_pos 703142 CRC32 0xa13132ed Delete_rows: table id 2012 flags: STMT_END_F ### DELETE FROM `db53`.`t1` ### WHERE ### @1=3 ### @2='edutx_e' ### @3='广州' ### @4=1 ### @5='开发' ### @6=18 # at 703142 #190813 11:22:24 server id 3306 end_log_pos 703173 CRC32 0xdd03b72d Xid = 20609 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@fudao_db_cluster_001 logs]#
-- 在从库上执行 mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.192.30.53 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 703173 Relay_Log_File: fudao_db_cluster_003-relay-bin.000002 Relay_Log_Pos: 2656 Relay_Master_Log_File: mysql-bin.000002 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: test.%,information_schema.% Last_Errno: 1032 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '46760eb0-bcb3-11e9-a352-24a9c4e7e87e:73' at master log mysql-bin.000002, end_log_pos 703142. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0 Exec_Master_Log_Pos: 702860 Relay_Log_Space: 3192 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: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '46760eb0-bcb3-11e9-a352-24a9c4e7e87e:73' at master log mysql-bin.000002, end_log_pos 703142. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Replicate_Ignore_Server_Ids: Master_Server_Id: 3306 Master_UUID: 46760eb0-bcb3-11e9-a352-24a9c4e7e87e 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: 190813 11:22:24 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 46760eb0-bcb3-11e9-a352-24a9c4e7e87e:66-73 Executed_Gtid_Set: 46760eb0-bcb3-11e9-a352-24a9c4e7e87e:1-72 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 (0.00 sec) ERROR: No query specified mysql>
mysql> stop slave; mysql> set GTID_NEXT='46760eb0-bcb3-11e9-a352-24a9c4e7e87e:73'; # Retrieved_Gtid_Set: 46760eb0-bcb3-11e9-a352-24a9c4e7e87e:66-73
mysql> begin;
mysql> commit;
mysql> set GTID_NEXT='AUTOMATIC';
mysql> start slave;
mysql> show slave statusG;