首页 > 代码库 > MySQL多源复制搭建

MySQL多源复制搭建

1.1     实验概要

1.1.1  实验假设

本实验假设已经完成操作系统和MySQL安装部署。

1.1.2  实验目的

MySQL5.7的多源复制技术搭建部署,然后简单测试。

1.1.3  环境信息

操作系统

MySQL版本

服务器地址

服务器角色

Centos7

5.7.18

192.168.102.23

source 1

Centos7

5.7.18

192.168.102.24

source 2

Centos7

5.7.18

192.168.102.25

target

1.1.4  实验规划

服务器地址

服务器角色

MySQL库

账户

192.168.102.23

source 1

emily

repl23

192.168.102.24

source 2

evelyn

repl24

192.168.102.25

target

 

 

 

source 1.emilyàtarget

source 2.evelynàtarget

username:

source 1:repl23

source 2:repl24

1.2     实验操作

1.2.1  源端备份数据库

分别从source1和source2分别备份出emily和evelyn库,然后分别copy到target中。其中操作步骤如下:

source 1:

##备份

[root@dsm-db-102023 11:26:50 /root]

#mysqldump -uroot –pmysql --single-transaction --master-data=http://www.mamicode.com/2 --databases emily > /root/dump/emily.sql

##传输

[root@dsm-db-102023 11:34:38 /root/dump]

#scp /root/dump/emily.sql root@192.168.102.25:/root/dump/

source 2:

##备份

[root@test-mysql-10224 11:19:47 /root]

#mysqldump -uroot -pmysql --single-transaction --master-data=http://www.mamicode.com/2 --databases evelyn > /root/dump/evelyn.sql

##传输

[root@dsm-db-102023 11:34:38 /root/dump]

#scp /root/dump/emily.sql root@192.168.102.25:/root/dump/

1.2.2  创建同步账户

分别在source1和source2中创建同步账户repl23、repl24

source1

root@192.168.102.23:3306 [emily]>create user repl23 identified by "repl";

root@192.168.102.23:3306 [(none)]>grant replication slave on *.* to ‘repl23‘@‘%‘;

source2

root@192.168.102.24:3306 [evelyn]>create user repl24 identified by "repl";

root@192.168.102.24:3306 [evelyn]>grant replication slave on *.* to ‘repl24‘@‘%‘;

1.2.3  目标端恢复数据库

## 恢复evelyn库

root@192.168.102.25:3306 [(none)]>reset master

[root@dsm-db-102025 14:01:24 /root/dump]

#mysql -uroot -pmysql < evelyn.sql

## 查看gtid_purged

root@192.168.102.25:3306 [(none)]>show global variables like ‘%gtid_purged%‘;

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

| Variable_name | Value                                    |

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

| gtid_purged   | 921a9068-24d2-11e7-99b5-005056b59593:1-287,

bd783f44-258f-11e7-914b-005056b5d312:1-28071 |

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

 

## 恢复emily库

root@192.168.102.25:3306 [(none)]>reset master

[root@dsm-db-102025 14:21:22 /root/dump]

#mysql -uroot -pmysql < /root/dump/emily.sql;

##设置source1和source2的gtid_purged

root@192.168.102.25:3306 [(none)]>show variables like ‘%gtid_purged%‘;

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

| Variable_name | Value                                    |

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

| gtid_purged   | 7937ac78-3c39-11e7-b59e-005056b5d25f:1-4 |

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

## set global gtid_purged=‘921a9068-24d2-11e7-99b5-005056b59593:1-287,bd783f44-258f-11e7-914b-005056b5d312:1-28071,7937ac78-3c39-11e7-b59e-005056b5d25f:1-4‘;

 

root@192.168.102.25:3306 [(none)]>reset master;

Query OK, 0 rows affected (0.01 sec)

 

root@192.168.102.25:3306 [(none)]>set global gtid_purged=‘921a9068-24d2-11e7-99b5-005056b59593:1-287,bd783f44-258f-11e7-914b-005056b5d312:1-28071,7937ac78-3c39-11e7-b59e-005056b5d25f:1-4‘;

Query OK, 0 rows affected (0.00 sec)

 

root@192.168.102.25:3306 [(none)]>show variables like ‘%gtid_purged%‘;

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

| Variable_name | Value                                                                                                                              |

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

| gtid_purged   | 7937ac78-3c39-11e7-b59e-005056b5d25f:1-4,

921a9068-24d2-11e7-99b5-005056b59593:1-287,

bd783f44-258f-11e7-914b-005056b5d312:1-28071 |

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

1 row in set (0.01 sec)

 

root@192.168.102.25:3306 [(none)]>

1.2.4  修改MySQL存储方式

修改MySQL存储master-info和relay-info的方式,即从文件存储改为表存储

## 在线修改

STOP SLAVE;

SET GLOBAL master_info_repository = ‘TABLE‘;

SET GLOBAL relay_log_info_repository = ‘TABLE‘;

##修改配置文件

[mysqld]

master_info_repository=TABLE

relay_log_info_repository=TABLE

1.2.5  同步操作

  • change master

登录slave进行同步操作,分别change master到两台master主机,多源复制需要标注

FOR CHANNEL ‘CHANNEL_NAME’区分

##source 1

root@192.168.102.25:3306 [(none)]>CHANGE MASTER TO MASTER_HOST=‘192.168.102.23‘,MASTER_USER=‘repl23‘, MASTER_PASSWORD=‘repl‘,master_auto_position=1 FOR CHANNEL ‘repl23‘;

##source 2

root@192.168.102.25:3306 [(none)]>CHANGE MASTER TO MASTER_HOST=‘192.168.102.24‘,MASTER_USER=‘repl24‘, MASTER_PASSWORD=‘repl‘,master_auto_position=1 FOR CHANNEL ‘repl24‘;

  • 启动slave

启动所有同步: start slave;

启动单个同步: start slave for channel ‘channel_name’;

##启动source 1

root@192.168.102.25:3306 [(none)]>start slave for channel ‘repl23‘;

##启动source 2

root@192.168.102.25:3306 [(none)]>start slave for channel ‘repl24‘;

  • 检查slave状态

检查所有slave: show slave status\G;

检查单个slave: show slave status for chennel ‘channel_name’\G;

 

## source 1

root@192.168.102.25:3306 [(none)]>show slave status for channel ‘repl23‘\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.102.23
Master_User: repl23
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2849
Relay_Log_File: relay-bin-repl23.000008
Relay_Log_Pos: 454
Relay_Master_Log_File: mysql-bin.000002
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: 2849
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: 233306
Master_UUID: 7937ac78-3c39-11e7-b59e-005056b5d25f
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: 7937ac78-3c39-11e7-b59e-005056b5d25f:5-13
Executed_Gtid_Set: 7937ac78-3c39-11e7-b59e-005056b5d25f:1-13,
921a9068-24d2-11e7-99b5-005056b59593:1-287,
bd783f44-258f-11e7-914b-005056b5d312:1-28090
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: repl23
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

 

##source 2

root@192.168.102.25:3306 [(none)]>show slave status for channel ‘repl24‘\G;

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

               Slave_IO_State:

                  Master_Host: 192.168.102.24

                  Master_User: repl24

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000040

          Read_Master_Log_Pos: 185363

               Relay_Log_File: relay-bin-repl24.000003

                Relay_Log_Pos: 454

        Relay_Master_Log_File: mysql-bin.000040

             Slave_IO_Running: No

            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: 185363

              Relay_Log_Space: 4318

              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: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 1595

                Last_IO_Error: Relay log write failure: could not queue event from master

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 243306

                  Master_UUID: bd783f44-258f-11e7-914b-005056b5d312

             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: 170519 16:12:33

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: bd783f44-258f-11e7-914b-005056b5d312:28072-28088

            Executed_Gtid_Set: 7937ac78-3c39-11e7-b59e-005056b5d25f:1-11,

921a9068-24d2-11e7-99b5-005056b59593:1-287,

bd783f44-258f-11e7-914b-005056b5d312:1-28088

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name: repl24

           Master_TLS_Version:

1 row in set (0.00 sec)

 

ERROR:

No query specified

1.2.6  验证同步

##source 1

root@192.168.102.23:3306 [emily]>insert into emily(id,name)values(2,‘evelyn‘);

Query OK, 1 row affected (0.00 sec)

 

root@192.168.102.23:3306 [emily]>select * from emily;

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

| id   | name   |

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

|    1 | emily  |

|    2 | evelyn |

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

2 rows in set (0.00 sec)

 

##target验证

 

root@192.168.102.25:3306 [emily]>select * from emily;

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

| id   | name   |

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

|    1 | emily  |

|    2 | evelyn |

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

2 rows in set (0.00 sec)

 

 

## source 2

root@192.168.102.24:3306 [evelyn]>insert into evelyn(id,name)values(2,‘emily‘);

Query OK, 1 row affected (1.00 sec)

 

root@192.168.102.24:3306 [evelyn]>select * from evelyn;

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

| id   | name   |

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

|    1 | evelyn |

|    2 | emily  |

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

2 rows in set (0.00 sec)

## target端

root@192.168.102.25:3306 [evelyn]>select * from evelyn;

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

| id   | name   |

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

|    1 | evelyn |

|    2 | emily  |

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

2 rows in set (0.00 sec)

1.2.7  监控

root@192.168.102.25:3306 [evelyn]>

select * from performance_schema.replication_connection_status\G;

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

             CHANNEL_NAME: repl23

               GROUP_NAME:

              SOURCE_UUID: 7937ac78-3c39-11e7-b59e-005056b5d25f

                THREAD_ID: 71

            SERVICE_STATE: ON

COUNT_RECEIVED_HEARTBEATS: 78

 LAST_HEARTBEAT_TIMESTAMP: 2017-05-19 17:37:05

 RECEIVED_TRANSACTION_SET: 7937ac78-3c39-11e7-b59e-005056b5d25f:5-13

        LAST_ERROR_NUMBER: 0

       LAST_ERROR_MESSAGE:

     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

*************************** 2. row ***************************

             CHANNEL_NAME: repl24

               GROUP_NAME:

              SOURCE_UUID: bd783f44-258f-11e7-914b-005056b5d312

                THREAD_ID: 73

            SERVICE_STATE: ON

COUNT_RECEIVED_HEARTBEATS: 13

 LAST_HEARTBEAT_TIMESTAMP: 2017-05-19 17:37:09

 RECEIVED_TRANSACTION_SET: bd783f44-258f-11e7-914b-005056b5d312:28072-28090

        LAST_ERROR_NUMBER: 0

       LAST_ERROR_MESSAGE:

     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

2 rows in set (0.00 sec)

 

ERROR:

No query specified

 

root@192.168.102.25:3306 [emily]>

select * from performance_schema.replication_connection_configuration\G;

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

                 CHANNEL_NAME: repl23

                         HOST: 192.168.102.23

                         PORT: 3306

                         USER: repl23

            NETWORK_INTERFACE:

                AUTO_POSITION: 1

                  SSL_ALLOWED: NO

                  SSL_CA_FILE:

                  SSL_CA_PATH:

              SSL_CERTIFICATE:

                   SSL_CIPHER:

                      SSL_KEY:

SSL_VERIFY_SERVER_CERTIFICATE: NO

                 SSL_CRL_FILE:

                 SSL_CRL_PATH:

    CONNECTION_RETRY_INTERVAL: 60

       CONNECTION_RETRY_COUNT: 86400

           HEARTBEAT_INTERVAL: 30.000

                  TLS_VERSION:

*************************** 2. row ***************************

                 CHANNEL_NAME: repl24

                         HOST: 192.168.102.24

                         PORT: 3306

                         USER: repl24

            NETWORK_INTERFACE:

                AUTO_POSITION: 1

                  SSL_ALLOWED: NO

                  SSL_CA_FILE:

                  SSL_CA_PATH:

              SSL_CERTIFICATE:

                   SSL_CIPHER:

                      SSL_KEY:

SSL_VERIFY_SERVER_CERTIFICATE: NO

                 SSL_CRL_FILE:

                 SSL_CRL_PATH:

    CONNECTION_RETRY_INTERVAL: 60

       CONNECTION_RETRY_COUNT: 86400

           HEARTBEAT_INTERVAL: 30.000

                  TLS_VERSION:

2 rows in set (0.00 sec)

 

ERROR:

No query specified

 

1.2.8  跳过事务

##GTID

STOP SLAVE FOR CHANNEL ‘CHANNEL_NAME’;

SET SESSION GTID_NEXT=’’;

BEGIN;COMMIT;

SET SESSION GTID_NEXT=’AUTOMATIC’;

START SLAVE FOR CHANNEL ‘CHANNEL_NAME’;

## binlog+position

stop slave sql_thread FOR CHANNEL ‘CHANNEL_NAME’;;

set global sql_slave_skip_counter=1;

start slave sql_thread FOR CHANNEL ‘CHANNEL_NAME’;;

 

 

##

root@192.168.102.25:3306 [(none)]>set session gtid_next=‘bd783f44-258f-11e7-914b-005056b5d312:28083‘;

Query OK, 0 rows affected (0.00 sec)

 

root@192.168.102.25:3306 [(none)]>begin;commit;

Query OK, 0 rows affected (0.00 sec)

 

root@192.168.102.25:3306 [(none)]>set session gtid_next=automatic;

Query OK, 0 rows affected (0.00 sec)

 

root@192.168.102.25:3306 [(none)]>start slave for channel ‘repl24‘;

Query OK, 0 rows affected (0.00 sec)

 

1.2.9  遇见错误

1、  Last_IO_Error: Relay log write failure: could not queue event from master  ##repl24

Last_IO_Error: Fatal error: Failed to run ‘after_read_event‘ hook          ##repl23

stop slave

start slave

最后发现是开启一个源开启了半同步复制,一个源没有开题半同步复制。

2、  清除slave信息

reset slave

## 创建多源复制过程中,发现有一个slave没有channel_name,使用如下语句清除slave信息

reset slave all for channel ‘‘;

3、  root@192.168.102.24:3306 [evelyn]>uninstall plugin rpl_semi_sync_master;

Query OK, 0 rows affected (0.01 sec)

root@192.168.102.24:3306 [evelyn]>uninstall plugin rpl_semi_sync_slave;

Query OK, 0 rows affected (0.00 sec)

4、   

 

参考资料:

http://www.cnblogs.com/xuanzhi201111/p/5151666.html

http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

http://www.longlong.asia/2015/10/21/mysql57-new-features.html

MySQL多源复制搭建