MySQL5.7 新特性 多源复制(多主一从) 配置全解

 MySQL 5.7.18已经开始支持了多源复制,相信小伙们都很激动,MySQL 5.7.18之前只能实现一主一从、一主多从或者多主多从的复制,如果想实现多主一从的复制,只好使用MariaDB,但是MariaDB又与官方的MySQL版本不兼容的,在MySQL 5.7.18版本已经可以实现多主一从的复制了。MySQL 5.7.18版本相比之前的版本,无论在功能还是性能、安全等方面都已经提升了不少,值得大家去研究和使用。 

MySQL 5.7.18版本之前的最常见的复制方式,一主一从或者一主多从的架构:


MySQL 5.7.18之后就可以实现多主一从的复制:


多主一从架构带来的好处(个人总结):
   一、在从服务器进行数据汇总,如果我们的主服务器进行了分库分表的操作,为了实现后期的一些数据统计功能,往往需要把数据汇总在一起再统计。
   二、如果我们想在从服务器时时对主服务器的数据进行备份,在MySQL 5.7.18之前每一个主服务器都需要一个从服务器,这样很容易造成资源浪费,同时也加大了DBA的维护成本,但MySQL 5.7.18引入多源复制,可以把多个主服务器的数据同步到一个从服务器进行备份。
 

下面演示一下在MySQL 5.7.18下搭建多主一从的过程:

实验环境:
操作系统环境:
[root@node86 dump]# uname -a
Linux node86 3.10.0-514.el7.x86_64 #1 SMP Tue Nov 22 16:42:41 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
[root@node86 dump]# cat /etc/redhat-release 
CentOS Linux release 7.3.1611 (Core) 

数据库拓扑环境:
Master_1: 192.168.66.111
Master_2: 192.168.66.112
Slave_3:  192.168.66.113

MySQL 5.7.18的搭建过程省略 
 
一、分别在Master_1和Master_2上导出需要同步的数据库:
在Master_1:

[root@Master_1 mysql]# mysqldump -uroot -p123456 --master-data=2 --single-transaction --databases --add-drop-database Order  >Order.sql
在Master_2:

[root@Master_2 mysql]# mysqldump -uroot -p123456 --master-data=2 --single-transaction --databases  --add-drop-database  Order_2 >Order_2.sql
把分别把备份scp到Slave上:

[root@Master_1 mysql]# scp -P22 Order.sql 192.168.66.113:/data/service/mysql/
[root@Master_2 mysql]# scp -P22 Order_2.sql 192.168.66.113:/data/service/mysql/


二、在Master_1和Master_2上创建复制账号,这个操作跟MySQL 5.7.18之前版本一样:
在Master_1:

<Master_1>[(none)]> grant replication slave on *.* to 'repl'@'192.168.66.113' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
在Master_2:

<Master_2> [(none)]> grant replication slave on *.* to 'repl'@'192.168.66.113' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)
 

三、分别Slave上把Master_1和Master_2的数据导入Slave服务器,在导入前先修改MySQL存储master-info和relay-info的方式,即从文件存储改为表存储,在my.cnf里添加以下选择:
master_info_repository=TABLE
relay_log_info_repository=TABLE
也可以在线修改,非常方便:

<Slave> [(none)]> stop slave;
Query OK, 0 rows affected (0.02 sec)
 
<Slave> [(none)]> SET GLOBAL master_info_repository = 'TABLE';
Query OK, 0 rows affected (0.00 sec)
 
<Slave> [(none)]> SET GLOBAL relay_log_info_repository = 'TABLE';
Query OK, 0 rows affected (0.00 sec)
 
<Slave> [(none)]>


下面进行数据导入:
[root@Slave mysql]# mysql -uroot -p  <./Order.sql
[root@Slave mysql]# mysql -uroot -p123456  <./Order_2.sql
分别找出Master_1和Master_2的binlog位置和Pos位置:


[root@Slave mysql]# cat Order.sql |grep " CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='Master_1-bin.000001', MASTER_LOG_POS=1539;
[root@Slave mysql]# cat Order_2.sql |grep " CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='Master_2-bin.000003', MASTER_LOG_POS=630;
[root@Slave mysql]#
 

四、登录Slave进行同步操作,分别change master到两台Master服务器,后面以FOR CHANNEL 'CHANNEL_NAME'区分
<Slave> [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.66.111',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='Master_1-bin.000001',MASTER_LOG_POS=1539 FOR CHANNEL 'Master_1';               
Query OK, 0 rows affected, 2 warnings (0.05 sec)
 
<Slave> [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.66.112',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='Master_2-bin.000003',MASTER_LOG_POS=630 FOR CHANNEL 'Master_2';                       
Query OK, 0 rows affected, 2 warnings (0.04 sec)
进行启动slave操作,可以通过start slave的方式去启动所有的复制,也可以通过启动单个复制源的方式,下面进行单个复制源的启动进行演示(停止也是一样):
<Slave> [(none)]> start slave for CHANNEL  'Master_1';
Query OK, 0 rows affected (0.01 sec)
 
<Slave> [(none)]> start slave for CHANNEL  'Master_2';
Query OK, 0 rows affected (0.02 sec)
正常启动后,可以查看同步的状态:执行SHOW SLAVE STATUS FOR CHANNEL 'channel_name'\G


查看复制源Master_1的同步状态:

<Slave> [(none)]> SHOW SLAVE STATUS FOR CHANNEL 'Master_1'\G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.66.111
                 Master_User: repl
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: Master_1-bin.000001
         Read_Master_Log_Pos: 1987
              Relay_Log_File: localhost-relay-bin-master_1.000002
               Relay_Log_Pos: 771
       Relay_Master_Log_File: Master_1-bin.000001
            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: 1987
             Relay_Log_Space: 991
             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: 100128
                 Master_UUID: 44b653d4-8843-11e5-b97e-000c29dfaaf7
            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)


查看复制源Master_2的同步状态:

<Slave> [(none)]> SHOW SLAVE STATUS FOR CHANNEL 'Master_2'\G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.66.112
                 Master_User: repl
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: Master_2-bin.000003
         Read_Master_Log_Pos: 1078
              Relay_Log_File: localhost-relay-bin-master_2.000002
               Relay_Log_Pos: 771
       Relay_Master_Log_File: Master_2-bin.000003
            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: 1078
             Relay_Log_Space: 991
             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: 100129
                 Master_UUID: 583f5433-43ef-11e5-8958-000c29d5bdfa
            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)


也可以通过查看performance_schema相关的表查看同步状态,执行命令:SELECT * FROM performance_schema.replication_connection_status; 监控复制状态。


+--------------+------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+--------------------------+-------------------+--------------------+----------------------+
| CHANNEL_NAME | GROUP_NAME | SOURCE_UUID                         | THREAD_ID | SERVICE_STATE |COUNT_RECEIVED_HEARTBEATS | LAST_HEARTBEAT_TIMESTAMP |RECEIVED_TRANSACTION_SET | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE |LAST_ERROR_TIMESTAMP |
+--------------+------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+--------------------------+-------------------+--------------------+----------------------+
| master_1     |           | 44b653d4-8843-11e5-b97e-000c29dfaaf7|        34 | ON           |                      184 | 2015-08-14 08:06:10      |                         |                0 |                   | 0000-00-00 00:00:00 |
| master_2     |           | 583f5433-43ef-11e5-8958-000c29d5bdfa|        36 | ON           |                      183 | 2015-08-14 08:06:24      |                         |                0 |                   | 0000-00-00 00:00:00 |
+--------------+------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+--------------------------+-------------------+--------------------+----------------------+
2 rows in set (0.00 sec)
 
<Slave> [(none)]>


五、验证数据是否同步
在Master_1上插入两条数据:


<Master_1>[Order]> insert into tb1(name)  values ('user1'),('user2');
Query OK, 2 rows affected (0.01 sec)
在Master_2上插入两条数据:


<Master_2> [Order_2]> insert into tb2(name) values ('user3'),('user4');
Query OK, 2 rows affected (0.04 sec)
回到Slave上查看数据是否正常把数据同步过来了:


<Slave> [(none)]> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql             |
| performance_schema |
| sys               |
| Order           |
| Order_2         |
+--------------------+
6 rows in set (0.03 sec)
 
<Slave> [(none)]> select * from Order.tb1;
+----+-------+
| id | name  |
+----+-------+
| 1 | user1 |
| 2 | user2 |
+----+-------+
2 rows in set (0.00 sec)
 
<Slave> [(none)]> select * from Order_2.tb2;
+----+-------+
| id | name  |
+----+-------+
| 1 | user3 |
| 2 | user4 |
+----+-------+
2 rows in set (0.00 sec)
成功的实现了多主一从的环境搭建,*。*


总结:
     一、MySQL 5.7.18的多源复制,能有效的解决分库分表的数据统计问题,同时也可以实现在一台从服务器对多台主服务器的数据备份。
     二、MySQL 5.7.18的多源复制的出现,我们就不需要使用MariaDB 的多主一从的架构了,让很多小伙伴又看到了新的希望。

  
分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS