MySQL 5.7搭建多源复制

叁歲伎倆 2024-04-18 00:38 43阅读 0赞

MySQL 5.7版本支持多源复制，MySQL 5.5和5.6不支持。   
 多源复制可以让多个主节点同时并行进行复制到一个从节点上。一个slave为每个master创建一个复制通道。  
至少需要两台主库和一台从库。  
多源复制中的主库，可以配置成基于全局事务标准(GTID)的复制，或者基于二进制日志的复制。  
  
IP规划  
  
主库01 192.168.174.201  
主库02 192.168.174.202  
从库 192.168.174.203  
   
 1.  在多源复制的从库中，需要基于表的repositories，和基于文件的repositories不兼容。  
在从库上面操作  
可以将下面参数添加到参数文件中  
master-info-repository=TABLE  
 relay-log-info-repository=TABLE  
  
master\_info\_repository  
决定包含master状态和连接信息的slave日志，是以文件格式(master.info)，还是以表格式(mysql.slave\_master\_info)存在。  
当没有复制线程执行的时候，可以改变这个参数的值。  
这个参数还会对sync\_master\_info系统参数有直接的影响。  
  
relay\_log\_info\_repository  
这个参数决定写到文件（relay-log.info）或表（mysql.slave\_relay\_log\_info）中的中继日志slave节点的位置。只有当没有复制线程执行时，才可修改这个参数的值。  
这个参数用于存放中继日志的信息。默认是文件格式，文件的默认名是relay-log.info。  
如果是TABLE格式，日志信息会写到mysql.slave\_relay\_log\_info。  
  
动态修改，使用下面命令  
STOP SLAVE;  
SET GLOBAL master\_info\_repository = 'TABLE';  
 STOP SLAVE;   
SET GLOBAL relay\_log\_info\_repository = 'TABLE';  
  
 mysql> show global variables like '%repositor%';  
\+---------------------------+-------+  
| Variable\_name             | Value |  
\+---------------------------+-------+  
| master\_info\_repository    | FILE  |  
| relay\_log\_info\_repository | FILE  |  
\+---------------------------+-------+  
2 rows in set (0.03 sec)

mysql> SET GLOBAL master\_info\_repository = 'TABLE';  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> SET GLOBAL relay\_log\_info\_repository = 'TABLE';  
ERROR 1766 (HY000): Unknown error 1766

mysql> stop slave;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

编辑从库的其他配置文件  
\[root@localhost 5505\]\# vim /mysql\_data/cnf/my.cnf  
\# Log  
server-id = 300  
log-bin = /mysql\_log/binlog/mysql-bin  
relay-log = /mysql\_log/binlog/product-relay-bin  
relay-log-index = /mysql\_log/binlog/product-relay-index  
  
之后重启数据库，使得参数生效

2. 修改主库1和主库2参数文件，创建复制用户，创建测试数据

编辑主库的配置文件，注意，每个库的server-id不能相同  
\[root@localhost install\]\# vim /etc/my.cnf  
\# Log  
server-id = 100  
log-bin = /log/binlog/mysql-bin  
  
之后重启数据库，使得参数生效    
  
创建复制用户  
 mysql> grant replication slave on \*.\* to 'repl'@'192.168.174.%' identified by 'repl';  
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> select version();  
\+---------------+  
| version()     |  
\+---------------+  
| 5.7.17-11-log |  
\+---------------+  
1 row in set (0.00 sec)

创建测试数据  
主库1  
 mysql> create database sale;  
Query OK, 1 row affected (0.06 sec)  
  
mysql> use sale;  
Database changed  
mysql> create table sale\_record(id int);  
Query OK, 0 rows affected (0.16 sec)  
  
mysql> insert into sale\_record values(10),(20);  
Query OK, 2 rows affected (0.95 sec)  
Records: 2  Duplicates: 0  Warnings: 0  
  
mysql> commit;  
Query OK, 0 rows affected (0.01 sec)

主库2  
 mysql> use market;  
Database changed  
mysql> create table market\_record(id int)  
    -> ;  
Query OK, 0 rows affected (0.04 sec)

mysql> insert into market\_record values (100),(200);  
Query OK, 2 rows affected (0.25 sec)  
Records: 2  Duplicates: 0  Warnings: 0  
  
mysql> commit;  
Query OK, 0 rows affected (0.01 sec)

3. 备份主库1和主库2  
主库1  
 \[root@MySQL01 mysql\_software\_57\]\# bin/mysqldump -uroot -p'root' -h 127.0.0.1 -q --single-transaction --master-data=2 -B sale > /tmp/20171211\_sale.sql  
   
主库2  
\[root@localhost mysql\_software\_57\]\# bin/mysqldump -uroot -p'root' -S /mysql\_data\_57/mysql.sock -q --single-transaction --master-data=2 -B market > /tmp/20171211\_market.sql

将主库1和主库2上面的备份文件拷贝到从库上面，进行恢复  
在从库上面操作  
 \[root@MySQL03 mysql\_software\_57\]\# bin/mysql -uroot -p'root' -h 127.0.0.1 < /tmp/20171211\_sale.sql   
mysql: \[Warning\] Using a password on the command line interface can be insecure.  
\[root@MySQL03 mysql\_software\_57\]\# bin/mysql -uroot -p'root' -h 127.0.0.1 < /tmp/20171211\_market.sql   
mysql: \[Warning\] Using a password on the command line interface can be insecure.

4. 搭建多源复制  
搭建到主库1的复制，将通道起名为master-1  
 mysql> change master to  
    ->      master\_host='192.168.174.201',  
    ->      master\_port=3306,  
    ->      master\_user='repl',  
    ->      master\_password='repl',  
    ->      master\_log\_file='mysql-bin.000017',  
    ->      master\_log\_pos=1209  
    ->      FOR CHANNEL 'master-1';  
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State:   
                  Master\_Host: 192.168.174.201  
                  Master\_User: repl  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000017  
          Read\_Master\_Log\_Pos: 1209  
               Relay\_Log\_File: mysqld-relay-bin-master@002d1.000001  
                Relay\_Log\_Pos: 4  
        Relay\_Master\_Log\_File: mysql-bin.000017  
             Slave\_IO\_Running: No  
            Slave\_SQL\_Running: No  
              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: 1209  
              Relay\_Log\_Space: 154  
              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: 0  
                Last\_IO\_Error:   
               Last\_SQL\_Errno: 0  
               Last\_SQL\_Error:   
  Replicate\_Ignore\_Server\_Ids:   
             Master\_Server\_Id: 0  
                  Master\_UUID:   
             Master\_Info\_File: mysql.slave\_master\_info  
                    SQL\_Delay: 0  
          SQL\_Remaining\_Delay: NULL  
      Slave\_SQL\_Running\_State:   
           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-1的IO和SQL线程  
mysql> START SLAVE IO\_THREAD FOR CHANNEL 'master-1';  
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE SQL\_THREAD FOR CHANNEL 'master-1';  
Query OK, 0 rows affected (0.01 sec)  
  
mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: 192.168.174.201  
                  Master\_User: repl  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000017  
          Read\_Master\_Log\_Pos: 1209  
               Relay\_Log\_File: mysqld-relay-bin-master@002d1.000002  
                Relay\_Log\_Pos: 320  
        Relay\_Master\_Log\_File: mysql-bin.000017  
             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: 1209  
              Relay\_Log\_Space: 541  
              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: 1  
                  Master\_UUID: 203fe772-177e-11e7-b15c-000c296b3b20  
             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的IO线程和SQL线程  
 mysql> change master to  
    ->      master\_host='192.168.174.202',  
    ->      master\_port=3306,  
    ->      master\_user='repl',  
    ->      master\_password='repl',  
    ->      master\_log\_file='mysql-bin.000014',  
    ->      master\_log\_pos=1239  
    ->      FOR CHANNEL 'master-2';  
Query OK, 0 rows affected, 1 warning (0.02 sec)  
  
mysql> START SLAVE IO\_THREAD FOR CHANNEL 'master-2';  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> START SLAVE SQL\_THREAD FOR CHANNEL 'master-2';  
Query OK, 0 rows affected (0.01 sec)  
  
mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: 192.168.174.201  
                  Master\_User: repl  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000017  
          Read\_Master\_Log\_Pos: 1481  
               Relay\_Log\_File: mysqld-relay-bin-master@002d1.000002  
                Relay\_Log\_Pos: 592  
        Relay\_Master\_Log\_File: mysql-bin.000017  
             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: 1481  
              Relay\_Log\_Space: 813  
              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: 1  
                  Master\_UUID: 203fe772-177e-11e7-b15c-000c296b3b20  
             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:   
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: 192.168.174.202  
                  Master\_User: repl  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000014  
          Read\_Master\_Log\_Pos: 1239  
               Relay\_Log\_File: mysqld-relay-bin-master@002d2.000002  
                Relay\_Log\_Pos: 320  
        Relay\_Master\_Log\_File: mysql-bin.000014  
             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: 1239  
              Relay\_Log\_Space: 541  
              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: 2  
                  Master\_UUID: 2efd664c-177f-11e7-8323-000c29fcf2cd  
             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:   
2 rows in set (0.01 sec)

进行测试  
在主库1上面插入数据  
 mysql> insert into sale\_record values(30),(40),(50);  
Query OK, 3 rows affected (0.00 sec)  
Records: 3  Duplicates: 0  Warnings: 0  
  
mysql> commit;  
Query OK, 0 rows affected (0.01 sec)

在主库2上面插入数据  
  mysql> insert into market\_record values(300),(400),(500);  
Query OK, 3 rows affected (0.01 sec)  
Records: 3  Duplicates: 0  Warnings: 0  
  
mysql> commit;  
Query OK, 0 rows affected (0.01 sec)

在从库上面进行查询  
 mysql> select \* from sale.sale\_record;  
\+------+  
| id   |  
\+------+  
|   10 |  
|   20 |  
|   30 |  
|   40 |  
|   50 |  
\+------+  
5 rows in set (0.00 sec)

mysql> select \* from market.market\_record;  
\+------+  
| id   |  
\+------+  
|  100 |  
|  200 |  
|  300 |  
|  400 |  
|  500 |  
\+------+  
5 rows in set (0.00 sec)

常用命令  
  
启动多源复制  
  
启动所有配置的复制通道的IO线程  
mysql> start slave io\_thread;  
Query OK, 0 rows affected (0.01 sec)

启动所有配置的复制通道的SQL线程

mysql> start slave sql\_thread;  
Query OK, 0 rows affected (0.01 sec)  
  
启动指定的复制通道  
mysql> start slave io\_thread for channel 'master-1';  
Query OK, 0 rows affected (0.00 sec)  
  
 停止多源复制  
  
停止所有配置的复制通道的IO线程  
mysql> stop slave io\_thread;  
Query OK, 0 rows affected (0.01 sec)  
  
停止所有配置的复制通道的SQL线程    
 mysql> stop slave sql\_thread;  
Query OK, 0 rows affected (0.00 sec)

停止指定的复制通道  
mysql> stop slave sql\_thread for channel 'master-1';  
Query OK, 0 rows affected, 1 warning (0.00 sec)

重置多源复制的从库  
  
重置所有配置的复制通道  
RESET SLAVE;  
  
重置指定的通道  
RESET SLAVE FOR CHANNEL 'master-1 ' ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2148570/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2148570/