1、使用mysqldump搭建基于GTID的从库
A库--3306端口
B库--3307端口
C库--3308端口
现有mysql架构:基于GTID的A-B
mysqldump 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_info
mysql-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