mysql5.7多实例logbin_Mysql 5.7 基于Binlog的多源复制

MySQL多源复制使复制从服务器可以同时接收来自多个来源的事务。多源复制可用于将多台服务器备份到单台服务器，合并表分片并将多台服务器的数据合并到单台服务器。多源复制在应用事务时不会执行任何冲突检测或解决方案，并且如果需要，这些任务将留给应用程序。在多源复制拓扑中，从服务器为每个主服务器创建一个复制通道，以便从中接收事务。 https://www.cndba.cn/leo1990/article/2741https://www.cndba.cn/leo1990/article/2741https://www.cndba.cn/leo1990/article/2741https://www.cndba.cn/leo1990/article/2741https://www.cndba.cn/leo1990/article/2741https://www.cndba.cn/leo1990/article/2741

https://www.cndba.cn/leo1990/article/2741https://www.cndba.cn/leo1990/article/2741https://www.cndba.cn/leo1990/article/2741https://www.cndba.cn/leo1990/article/2741

环境说明：

maseter1：192.168.1.32:3306 redhat6.7

maseter2：192.168.1.33:3306 redhat6.7

slave1：192.168.1.32:3406 redhat6.7

1 关闭主从服务器防火墙

[root@www.cndba.cn ~]# systemctl stop firewalld

[root@www.cndba.cn ~]# systemctl disable firewalld

2 修改主从库/etc/my.cnf配置文件

Master1 参数文件修改

server_id = 32

log-bin = master1-bin

Master2 参数文件修改

server_id = 33

log-bin = master2-bin

Slave 参数文件修改

server_id = 34

relay-log = relay-bin

master-info-repository = TABLE

relay-log-info-repository = TABLE

3 创建测试数据

master1 创建数据库

mysql> create database test32;

Query OK, 1 row affected (0.10 sec)

mysql> use test32

Database changed

mysql> create table t1(id int(5));

Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 values(1);

Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values(2);

Query OK, 1 row affected (0.03 sec)

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)

Master2 创建数据库

mysql> create database test33;

Query OK, 1 row affected (0.01 sec)

mysql> use test33

Database changed

mysql> create table t2(id int(5));

Query OK, 0 rows affected (0.10 sec)

mysql> insert into t2 values(1);

Query OK, 1 row affected (0.06 sec)

mysql> insert into t2 values(2);

Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)

4 导出需要同步的数据库

分别在master1和master2上导出需要同步的数据库

在 master1 上备份要同步的数据库

mysqldump -uroot -p --master-data=2 --single-transaction --add-drop-database --databases test32 >/backup/test32.sql

在master2 上备份要同步的数据库

mysqldump -uroot -p --master-data=2 --single-transaction --add-drop-database --databases test33 >/backup/test33.sql

分别把备份scp到slave上

[root@www.cndba.cn backup]# scp test32.sql 192.168.1.34:/backup/

[root@www.cndba.cn ~]# scp /backup/test33.sql 192.168.1.34:/backup/

5 在master1和master2上创建复制账号

这个操作跟MySQL 5.7之前版本一样：

在master1

mysql> grant replication slave on *.* to 'rep1'@'192.168.1.34' identified by 'rep1';

Query OK, 0 rows affected, 1 warning (0.30 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

在master2

mysql> grant replication slave on *.* to 'rep2'@'192.168.1.34' identified by 'rep2';

Query OK, 0 rows affected, 1 warning (0.30 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

6 初始化Slave服务器

分别Slave上把master1和master2的数据导入Slave服务器

在导入前先确认是否修改MySQL存储master-info和relay-info的方式即从文件存储改为表存储，在my.cnf里添加以下选择：

master_info_repository=TABLE

relay_log_info_repository=TABLE

也可以在线修改，灰常方便：

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET GLOBAL master_info_repository = 'TABLE';

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL relay_log_info_repository = 'TABLE';

Query OK, 0 rows affected (0.00 sec)

在slave 上恢复两个主库要同步的数据库

[root@www.cndba.cn backup]# mysql -uroot -p < /backup/test32.sql

[root@www.cndba.cn backup]# mysql -uroot -p < /backup/test33.sql

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test32 |

| test33 |

+--------------------+

7 查看主库binlog位置和pos位置

分别找出master1和master2的binlog位置和pos位置：

[root@www.cndba.cn backup]# cat /backup/test32.sql |grep " CHANGE MASTER"

-- CHANGE MASTER TO MASTER_LOG_FILE='master1-bin.000002', MASTER_LOG_POS=154;

[root@www.cndba.cn backup]# cat /backup/test33.sql |grep " CHANGE MASTER"

-- CHANGE MASTER TO MASTER_LOG_FILE='master2-bin.000002', MASTER_LOG_POS=154;

8 登录Slave进行同步操作

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.32',MASTER_USER='rep1', MASTER_PASSWORD='rep1',MASTER_LOG_FILE='master1-bin.000002',MASTER_LOG_POS=154 FOR CHANNEL 'Master_1';

Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.33',MASTER_USER='rep2', MASTER_PASSWORD='rep2',MASTER_LOG_FILE='master2-bin.000002',MASTER_LOG_POS=154 FOR CHANNEL 'Master_2';

Query OK, 0 rows affected, 2 warnings (0.07 sec)

9 启动slave 复制

默认开启所有的复制通道

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

也可以指定复制通道开启

mysql> start slave for CHANNEL 'Master_1';

Query OK, 0 rows affected (0.01 sec)

mysql> start slave for CHANNEL 'Master_2';

Query OK, 0 rows affected (0.04 sec)

10 查看从库复制状态

注意：结果中Slave_IO_Running和Slave_SQL_Running必须为Yes，如果不是，需要根据提示的错误修改。

show slave status/G;

或者单独查看每个通道的复制状态

mysql> SHOW SLAVE STATUS FOR CHANNEL 'Master_1'/G;

mysql> SHOW SLAVE STATUS FOR CHANNEL 'Master_1'/G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.32

Master_User: rep1

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master1-bin.000004

Read_Master_Log_Pos: 154

Relay_Log_File: relay-bin-master_1.000007

Relay_Log_Pos: 371

Relay_Master_Log_File: master1-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 154

Relay_Log_Space: 749

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 32

Master_UUID: 6715b696-3d3c-11e8-b66c-08002703bb35

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name: master_1

Master_TLS_Version:

1 row in set (0.00 sec)

mysql> SHOW SLAVE STATUS FOR CHANNEL 'Master_2'/G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.33

Master_User: rep2

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master2-bin.000004

Read_Master_Log_Pos: 154

Relay_Log_File: relay-bin-master_2.000007

Relay_Log_Pos: 371

Relay_Master_Log_File: master2-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 154

Relay_Log_Space: 749

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 33

Master_UUID: 73acf59b-3d3c-11e8-b976-080027686323

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name: master_2

Master_TLS_Version:

1 row in set (0.00 sec)

11 测试数据库同步

--master1 插入数据

mysql> use test32

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

mysql> insert into t1 values(3);

Query OK, 1 row affected (0.06 sec)

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

--master2 插入数据

mysql> use test33

mysql> select * from t2;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.01 sec)

mysql> insert into t2 values(3);

Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

slave 查看数据被同步过来

mysql> select * from test32.t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.00 sec)

mysql> select * from test33.t2;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.00 sec)

版权声明：本文为博主原创文章，未经博主允许不得转载。