mysql 组复制和传统复制_MySQL传统复制和GTID复制参考

1.mysqldump或者xtrabackup导出数据库scp到需要需要的备库上，然后倒入到数据库中，本例子使用mysqldump，xtrabacup请参考另外博客

https://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;