MySQL 5.7搭建多源复制
发表于：2026-11-01 作者：千家信息网编辑
千家信息网最后更新 2026年11月01日，MySQL 5.7版本支持多源复制，MySQL 5.5和5.6不支持。多源复制可以让多个主节点同时并行进行复制到一个从节点上。一个slave为每个master创建一个复制通道。至少需要两台主库和一台从
千家信息网最后更新 2026年11月01日MySQL 5.7搭建多源复制MySQL 5.7版本支持多源复制，MySQL 5.5和5.6不支持。
多源复制可以让多个主节点同时并行进行复制到一个从节点上。一个slave为每个master创建一个复制通道。
至少需要两台主库和一台从库。
多源复制中的主库，可以配置成基于全局事务标准(GTID)的复制，或者基于二进制日志的复制。
IP规划
主库01192.168.174.201
主库02192.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)
mysql> show global variables like '%repositor%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
2 rows in set (0.01 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';
多源复制可以让多个主节点同时并行进行复制到一个从节点上。一个slave为每个master创建一个复制通道。
至少需要两台主库和一台从库。
多源复制中的主库，可以配置成基于全局事务标准(GTID)的复制，或者基于二进制日志的复制。
IP规划
主库01192.168.174.201
主库02192.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)
mysql> show global variables like '%repositor%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
2 rows in set (0.01 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';
通道
参数
文件
线程
多源
配置
数据
日志
格式
信息
节点
测试
命令
备份
数据库
用户
中继
支持
相同
事务
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
互联网科技公司经营范围
科技互联网项目
互联网品科技
金融科技创新监管互联网金融
紫鲸互联网科技
融势互联网科技
金融科技和互联网金融
中国的互联网科技企业的崛起
互联网金融中的现代科技
互联网科技公司实习生
民营互联网公司国防科技
乌鲁木齐新华智原互联网科技学校
西安小鹰速贷互联网科技有限公司
华夏恒生科技与互联网科技
佛山市拉法互联网科技有限公司
阿里互联网科技企业
互联网数据科技专业
美国互联网科技实力
互联网与数字科技关系
科技和互联网行业的特点
奇酷互联网络科技
互联网科技ppt模板
互联网科技论坛
中国最先进互联网科技公司
成都玖崟互联网科技有限公司乐商圈怎么样
互联网科技寓意
苏州五指互联网络科技有限
深圳无忧互联网科技
杭州融税互联网科技有限公司
恒生科技指数 中概互联网
