1.mysqldump或者xtrabackup导出数据库scp到需要需要的备库上，然后倒入到数据库中，本例子使用mysqldump，xtrabacup请参考另外博客 http://blog.51cto.com/1937519/2283779
mysqldump -uroot -p --single_transaction --master-data=2 --databases app app1 data > all_db.sql source all_db.sql
2.添加备库：
查看dump文件有关复制的信息： -- GTID state at the beginning of the backup
SET @@GLOBAL.GTID_PURGED='08a7bcf7-a8e4-11e8-8b61-0800278f64de:1-438374';
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000064', MASTER_LOG_POS=715;
如果使用xtrabackup备份，可以查看xtrabackup_info或者xtrabackup_binlog_info文件获得相应的gtid信息。
第一种方法，传统的方法： CHANGE MASTER TO MASTER_HOST='192.168.56.101',MASTER_PORT=3306,MASTER_USER='repluser',MASTER_PASSWORD='MYsql#123',MASTER_LOG_FILE='binlog.000064',MASTER_LOG_POS=715;
第二种方法，使用gtid： 步骤： a.reset master; b.SET @@GLOBAL.GTID_PURGED='08a7bcf7-a8e4-11e8-8b61-0800278f64de:1-438374'; c.CHANGE MASTER TO MASTER_HOST='192.168.56.101', MASTER_PORT=3306,MASTER_USER='repluser',MASTER_PASSWORD='MYsql#123',MASTER_AUTO_POSITION = 1;
以下为reset master和GTID_PURGED前后的gtid变化情况： 查看现在数据库中gtid信息： mysql> show variables like '%gtid%'; +----------------------------------+-----------------------------------------------+ | Variable_name | Value | +----------------------------------+-----------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | 08a7bcf7-a8e4-11e8-8b61-0800278f64de:1-436675 | | session_track_gtids | OFF | +----------------------------------+-----------------------------------------------+
mysql> reset master; Query OK, 0 rows affected (0.02 sec)
mysql> show variables like '%gtid%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+
mysql> SET @@GLOBAL.GTID_PURGED='08a7bcf7-a8e4-11e8-8b61-0800278f64de:1-438374'; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%gtid%'; +----------------------------------+-----------------------------------------------+ | Variable_name | Value | +----------------------------------+-----------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | 08a7bcf7-a8e4-11e8-8b61-0800278f64de:1-438374 | | session_track_gtids | OFF | +----------------------------------+-----------------------------------------------+ 8 rows in set (0.01 sec)
CHANGE MASTER TO MASTER_HOST='192.168.56.101', MASTER_PORT=3306,MASTER_USER='repluser',MASTER_PASSWORD='MYsql#123',MASTER_AUTO_POSITION = 1;
start slave;
3.参考跳过复制错误： 传统方法跳过事务： stop slave; set global sql_slave_skip_counter=1; start slave; show slave status\G;
使用gtid跳过事务： stop slave; set gtid_next='xxxxxxxxxxxxx:n'; begin; commit; set gtid_next='AUTOMATIC'; start slave;
