GTID：基于GTID搭建从库

来源：互联网发布：淘宝卖家网址编辑：程序博客网时间：2024/05/04 16:37

1、使用mysqldump搭建基于GTID的从库

A库--3306端口

B库--3307端口

C库--3308端口

现有mysql架构：基于GTID的A-Bmysqldump B库/usr/local/mysql/bin/mysqldump  --master-data=2 --single-transaction -A -h127.0.0.1 -uroot -proot -P3307 > 3307.sql 3307.sql中有如下信息：SET @@GLOBAL.GTID_PURGED='65dfb885-05ff-11e6-b9f7-080027275cb7:1,  --B的GTID信息babb81f3-e8c4-11e5-bb5e-080027d6e4e2:1-3047';   --A的GTID信息C库导入[root@192.168.56.159 db_bak]mysql -uroot -proot -h127.0.0.1 -P3308 < 3307.sql ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.此时C库执行：reset master;然后导入C库执行CHANGE MASTER TO MASTER_HOST='192.168.56.159', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl', master_auto_position=1;OK

2、使用innobackup搭建基于GTID的从库

现有mysql架构：基于GTID的A-B使用innobackup备份B库/usr/bin/innobackupex-1.5.1 --defaults-file=/data/mysql/mysql3307/my3307.cnf --user=root --password=root --host=127.0.0.1 --port=3307  --slave-info  --no-timestamp  /data/mysqlbak/db_bak/db3307恢复还原到C库/usr/bin/innobackupex-1.5.1 --user=root --host=127.0.0.1 --port=3308 --defaults-file=/data/mysql/mysql3308/my3308.cnf --apply-log /data/mysqlbak/db_bak/db3307/usr/bin/innobackupex-1.5.1 --user=root --host=127.0.0.1 --port=3308 --defaults-file=/data/mysql/mysql3308/my3308.cnf --copy-back /data/mysqlbak/db_bak/db3307在备份目录/data/mysqlbak/db_bak/db3307查看主的GTID位置[root@192.168.56.159 db3307]more xtrabackup_binlog_infomysql-bin.000019        24211           65dfb885-05ff-11e6-b9f7-080027275cb7:1,babb81f3-e8c4-11e5-bb5e-080027d6e4e2:1-3049在恢复的库上执行set global gtid_purged='65dfb885-05ff-11e6-b9f7-080027275cb7:1,babb81f3-e8c4-11e5-bb5e-080027d6e4e2:1-3049';CHANGE MASTER TO MASTER_HOST='192.168.56.159', MASTER_PORT=3307, MASTER_USER='repl', MASTER_PASSWORD='repl', master_auto_position=1;start slave；

查看binlog文件内容

/usr/local/mysql/bin/mysqlbinlog -v --base64-output=decode-rows mysql-bin.000019 > 1.log

1 0