MySQL 5.7搭建多源复制
发表于：2024-10-05 作者：千家信息网编辑
千家信息网最后更新 2024年10月05日，MySQL 5.7版本支持多源复制，MySQL 5.5和5.6不支持。多源复制可以让多个主节点同时并行进行复制到一个从节点上。一个slave为每个master创建一个复制通道。至少需要两台主库和一台从
千家信息网最后更新 2024年10月05日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安全错误
数据库的锁怎样保障安全
四川系统软件开发报价
广州辉聚捷软件开发有限公司
宁河软件开发哪家不错
银川分布式服务器排名
数据库安装8.0.23
交大继续教育网络技术基础
黯黑网络技术团队
青岛中联科网络技术
曙光服务器管理密码忘记
地中海贫血基因分型数据库
智霆服务器
JAVA三层数据库开发
三级网络技术的题库
数据库结果框怎样快捷键隐藏
服务器需要做双网卡设置吗
人民时评 网络安全
印能捷提示检查服务器防火墙
光伏系统软件开发语言
康威服务器运行内存有多大
照片库服务器
通州区正规软件开发技术指导
企业网络安全的内容
数据库 自主可控
网络安全培训工作落实细则
网络安全知识试题(2)
腾成软件开发
水下通信网络技术
科技互联网新闻资讯网站
win10家庭版装数据库
奇迹暖暖 只看数据库
