mysql8基于gtid导出导入搭建主从
mysql8基于gtid导出导入搭建主从
开启GTID的情况下导出导入库的注意事项
在开启了 GTID 功能的 MySQL 数据库中, 不论是否使用了 GTID 的方式做了主从同步, 导出导入时都需要特别注意数据库中的 GTID 信息.
# 导出
# 在主库上全备，--set-gtid-purged决定是否带gtid，这里建议明确指出是ON还是OFF
[root@db145 bak]# mysqldump --login-path=instance_3306 --set-gtid-purged=ON --all-databases --single-transaction --master-data=2 --triggers --routines --events >/home/bak/all_database.dump.sql
# 在主库上全备，不带gtid
[root@db145 bak]# mysqldump --login-path=instance_3306 --set-gtid-purged=OFF --all-databases --single-transaction --master-data=2 --triggers --routines --events >/home/bak/all_database.dump.sql
# 导入
# 导入的时候也分两种, 一种是导入带有 GTID 的信息的库, 一种是导入不带有 GTID 信息的库
不带有 GTID 信息的 dump 文件, 不管目标数据库实例是否开启了 GTID 功能, 且不管数据库实例是否已有其他 GTID 信息, 都可以顺利导入
带有 GTID 信息的 dump 文件, 要求目标数据库实例必须开启 GTID 功能, 且当前数据库中无其他 GTID 信息.
如果目标数据库中已经记录了一条或一条以上的 GTID 信息, 那么在导入数据库时会报出类似如下的错误
[root@db143 bak]# mysql --login-path=instance_3306_root <all_database.dump.sql ERROR 3546 (HY000) at line 26: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED
这个时候你有两个选择:
重新 dump 数据库, 使用--set-gtid-purged=OFF的参数禁止导出 GTID 信息,再 导入到目标数据库
在目标数据库中执行
MySQL>reset slave all; reset master;
清空所有 GTID 信息之后就可以导入了
[root@db143 bak]# mysql --login-path=instance_3306_root <all_database.dump.sql
You have new mail in /var/spool/mail/root
[root@db143 bak]#
# 搭建主从
-- drop user 'repuser'@'%';
create user 'repuser'@'%' identified WITH 'mysql_native_password' by 'repuserpwd';
grant replication slave on *.* to 'repuser'@'%';
CHANGE MASTER TO
MASTER_HOST='192.168.142.145',
MASTER_USER='repuser',
MASTER_PASSWORD='repuserpwd',
MASTER_PORT=3306,
MASTER_AUTO_POSITION = 1;
start slave ;
show slave status\G;
# gtid报错处理
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.142.145 Master_User: repuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000013 Read_Master_Log_Pos: 1837 Relay_Log_File: db143-relay-bin.000002 Relay_Log_Pos: 1104 Relay_Master_Log_File: mysql-bin.000013 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: 1008 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'b2f0bfdc-87db-11e9-b83d-000c298bc91b:12' at master log mysql-bin.000013, end_log_pos 1837. 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: 1664 Relay_Log_Space: 1477 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: 1008 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'b2f0bfdc-87db-11e9-b83d-000c298bc91b:12' at master log mysql-bin.000013, end_log_pos 1837. 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: 1453306 Master_UUID: b2f0bfdc-87db-11e9-b83d-000c298bc91b 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: 191230 14:31:12 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: b2f0bfdc-87db-11e9-b83d-000c298bc91b:9-12 Executed_Gtid_Set: 58f6e65e-9309-11e9-9d88-525400184a0a:1-81, 81887515-6ded-11e9-a307-000c29508dcb:1-10, b2f0bfdc-87db-11e9-b83d-000c298bc91b:1-11 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
Retrieved_Gtid_Set: b2f0bfdc-87db-11e9-b83d-000c298bc91b:9-12 # 这一行很重要 Executed_Gtid_Set: 58f6e65e-9309-11e9-9d88-525400184a0a:1-81, # 这一行很重要 81887515-6ded-11e9-a307-000c29508dcb:1-10, b2f0bfdc-87db-11e9-b83d-000c298bc91b:1-11 Auto_Position: 1
# 处理办法：
# 准备工作：在主库中解析对应的binlog日志，查找pos点位；(可选)
mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000013 >13_binlog
vim 13_binlog
# at 1664
#191230 14:31:12 server id 1453306 end_log_pos 1737 GTID last_committed=6 sequence_number=7 rbr_only=no original_committed_timestamp=1577687472295487 immediate_commit_timestamp=1577687472295487 transaction_length=173
# original_commit_timestamp=1577687472295487 (2019-12-30 14:31:12.295487 CST)
# immediate_commit_timestamp=1577687472295487 (2019-12-30 14:31:12.295487 CST)
/*!80001 SET @@session.original_commit_timestamp=1577687472295487*//*!*/;
/*!80014 SET @@session.original_server_version=80018*//*!*/;
/*!80014 SET @@session.immediate_server_version=80018*//*!*/;
SET @@SESSION.GTID_NEXT= 'b2f0bfdc-87db-11e9-b83d-000c298bc91b:12'/*!*/; # 这一行很重要(实际上和推想的保持一致)
# at 1737
#191230 14:31:12 server id 1453306 end_log_pos 1837 Query thread_id=30 exec_time=0 error_code=0 Xid = 47
# 具体处理：
STOP SLAVE;
SET SESSION GTID_NEXT='b2f0bfdc-87db-11e9-b83d-000c298bc91b:12';
# 这个就是根据上面得出的，简单的可以认为就是：Retrieved_Gtid_Set的值，截取冒号取横杠后面的值。
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;
START SLAVE;
SHOW SLAVE STATUS\G;
