基于GTID搭建MySQL主从使用xtrabackup工具
第一步:主库备份 PRI 备份 xtrabackup --defaults-file=/usr/local/mysql/etc/my.cnf --backup --user=root --host=127.0.0.1 --password --port=3306 \ --socket=/usr/local/mysql/data/mysql.sock --parallel=2 --target-dir=/dbbackup 第二步：拷贝备份文件到备库 第三步:备库恢复数据 stop mysql 修改原来的数据文件目录 xtrabackup --prepare --apply-log-only --target-dir=/dbbackup xtrabackup --prepare --target-dir=/dbbackup xtrabackup -uroot -p -P3306 --datadir=/usr/local/mysql/data/ --copy-back --target-dir=/dbbackup chown mysql.mysql data -R chmod 775 data -R /etc/init.d/mysqld start /etc/init.d/mysqld status mysql -uroot -pBai_yun123 第四步：从库设置GTID 从备份文件获取GTID cat xtrabackup_info stop slave; reset slave all; reset master; SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; SET @@GLOBAL.GTID_PURGED='27270078-0cd4-11f1-9b0c-0800274c0fb9:1-3'; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN; change master to master_host='192.168.56.100',master_port=3306,master_user='root',master_password='Bai_yun123',master_auto_position=1; start slave; show slave status\G 第五步：主库指向从库配置双主： stop slave; reset slave all; change master to master_host='192.168.56.101',master_port=3306,master_user='root',master_password='Bai_yun123',master_auto_position=1; start slave; show slave status\G
