mysql gtid mysqldump_MySQL5.7主从-GTID-mysqldump,xtrabackup搭建

1、两个空库，都是row+gtid，版本为MySQL5.7.22

mydb1执行：

(dba_user@localhost) [(none)]> show master status;

+------------------+----------+--------------+------------------+------------------------------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |

+------------------+----------+--------------+------------------+------------------------------------------+

| mysql-bin.000005 |      154 |              |                  | 9831a804-8c25-11e8-9a69-000c2983201e:1-3 |

+------------------+----------+--------------+------------------+------------------------------------------+

1 row in set (0.01 sec)

mydb2执行：

(dba_user@localhost) [(none)]> reset master;

(dba_user@localhost) [(none)]> set global GTID_PURGED='9831a804-8c25-11e8-9a69-000c2983201e:1-3';

(dba_user@localhost) [(none)]> change master to master_host='192.168.1.101',master_port=3306,master_user='dba_user',master_password='msds007',master_auto_position=1;

(dba_user@localhost) [(none)]> start slave;

(dba_user@localhost) [(none)]> show slave status\G

(dba_user@localhost) [(none)]> set global read_only=on;

(dba_user@localhost) [(none)]> set global super_read_only=on;

2、主库为已有数据库，使用mysqldump方式新搭建从库，都是row+gtid，版本为MySQL5.7.22

mydb1执行：

[root@mydb1 ~]# mysqldump -h192.168.1.101 -p3306 -udba_user -pmsds007 --single-transaction --master-data=2 test> test.sql

出现warning不用理会

mydb2执行：

(dba_user@localhost) [test]> source /root/test.sql

(dba_user@localhost) [test]> reset master;

(dba_user@localhost) [test]> SET @@GLOBAL.GTID_PURGED='9831a804-8c25-11e8-9a69-000c2983201e:1-433';

(dba_user@localhost) [(none)]> change master to master_host='192.168.1.101',master_port=3306,master_user='dba_user',master_password='msds007',master_auto_position=1;

(dba_user@localhost) [(none)]> start slave;

(dba_user@localhost) [(none)]> show slave status\G

(dba_user@localhost) [(none)]> set global read_only=on;

(dba_user@localhost) [(none)]> set global super_read_only=on;

3、主库为已有数据库，使用xtrabackup方式新搭建从库，都是row+gtid，版本为MySQL5.7.22

使用 my_full_backup.sh 进行备份

使用 my_full_recover.sh 进行恢复

mydb2执行：

(dba_user@localhost) [test]> reset master;

(dba_user@localhost) [test]> SET @@GLOBAL.GTID_PURGED='9831a804-8c25-11e8-9a69-000c2983201e:1-433';

(dba_user@localhost) [(none)]> change master to master_host='192.168.1.101',master_port=3306,master_user='dba_user',master_password='msds007',master_auto_position=1;

(dba_user@localhost) [(none)]> start slave;

(dba_user@localhost) [(none)]> show slave status\G

(dba_user@localhost) [(none)]> set global read_only=on;

(dba_user@localhost) [(none)]> set global super_read_only=on;