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

ERROR3546 (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

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: Waitingfor master tosend event

Master_Host:192.168.142.145Master_User: repuser

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000013Read_Master_Log_Pos:1837Relay_Log_File: db143-relay-bin.000002Relay_Log_Pos:1104Relay_Master_Log_File: mysql-bin.000013Slave_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:1008Last_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:0Exec_Master_Log_Pos:1664Relay_Log_Space:1477Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:1008Last_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:1453306Master_UUID: b2f0bfdc-87db-11e9-b83d-000c298bc91b

Master_Info_File: mysql.slave_master_info

SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:191230 14:31:12Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: b2f0bfdc-87db-11e9-b83d-000c298bc91b:9-12Executed_Gtid_Set: 58f6e65e-9309-11e9-9d88-525400184a0a:1-81,81887515-6ded-11e9-a307-000c29508dcb:1-10,

b2f0bfdc-87db-11e9-b83d-000c298bc91b:1-11Auto_Position:1Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key:0Network_Namespace:1 row in set (0.00sec)

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-11Auto_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;