mysqldump部署主从(gtid模式)

环境:
OS:Centos 7
mysql版本:5.7.29

 

1.备份主库
采用mysqldump的方式导出
/home/middle/mysql57/bin/mysqldump -h localhost -uroot -pmysql -P13306 -S /tmp/mysql.sock --databases db_test db_test01 --default-character-set=utf8 --set-gtid-purged=on --skip-tz-utc --skip-disable-keys --skip-add-locks --single-transaction>/tmp/mydata.sql

这里要开启--set-gtid-purged=on,导出的时候会有这么一句

SET @@GLOBAL.GTID_PURGED='2839d3fc-9960-11ec-bd39-080027e0fbb1:1-36';

导入的时候我们要将该句注释掉,开启set-gtid-purged的目的是在做从库的时候告诉从库从这个gtid后面的执行

 

2.将导出的文件scp到从库机器
[root@rac01 tmp]# scp mydata.sql root@192.168.56.192:/tmp/

 

3.修改文件
vi /tmp/mydata.sql
注释掉如下语句
##SET @@GLOBAL.GTID_PURGED='2839d3fc-9960-11ec-bd39-080027e0fbb1:1-36';

 

4.这个时候可以模拟主库建表写数据
mysql> create table tb_test03 (id int,name varchar(10));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into tb_test03 values(1,'name1'),(2,'name2'),(3,'name3');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from tb_test03;
+------+-------+
| id | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
+------+-------+
3 rows in set (0.00 sec)

 

5.从库导入
mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)

我这里之前有部署过从库的,先删除掉
mysql> drop database db_test;
Query OK, 10 rows affected (0.08 sec)

mysql> drop database db_test01;
Query OK, 3 rows affected (0.03 sec)

导入:
/home/middle/mysql57/bin/mysql -h localhost -uroot -P13306 -p --default-character-set=utf8</tmp/mydata.sql

6.部署从库
mysql> reset slave;
mysql> reset master;
mysql> set global gtid_purged='2839d3fc-9960-11ec-bd39-080027e0fbb1:1-36'; ##这里就是导出dump文件记录的gtid
mysql> change master to master_host='192.168.56.191',master_user='ureplsync',master_password='mysql',master_port=13306,master_auto_position=1;
mysql> start slave;

上面的方法也支持从mysql 5.7同步到mysql 8.0

部署到到mysql 8.0
导入到mysql 8.0
/home/middle/mysql8/bin/mysql -h localhost -uroot -P23306 -p -S /home/middle/mysql8/mysql.sock --default-character-set=utf8</tmp/mydata.sql

从库部署(mysql 8.0):
mysql> reset slave;
mysql> reset master;
mysql> set global gtid_purged='2839d3fc-9960-11ec-bd39-080027e0fbb1:1-36';
mysql> change master to master_host='192.168.56.191',master_user='ureplsync',master_password='mysql',master_port=13306,master_auto_position=1;
mysql> start slave;