什么是多源复制?
首先，我们需要清楚 multi-master 与multi-source 复制不是一样的. Multi-Master 复制通常是环形复制，
你可以在任意主机上将数据复制给其他主机。
Multi-source 是不同的. MySQL在这个新版本中修复了一个复制限制 , 这限制是一个从站只能有一个主站.
这是一个在我们设计复制环境中的限制因素，也有一些极客使它正常工作了。但是现在有一个官方的解决办
法了。所以。简单的说， Multi-Source 意味着一个从站能有一个以上主站. 现在,
像下图一样的复制环境是可能的:
这将帮助我们创建一些复制的层次结构，这在过去是不可能的。 举个例子,你可以 将一个从站放在你的办公室
里。在办公室里从所有主站中复制数据传播到世界各地。
它是怎么工作的呢?
现在我们有了通信渠道的概念，每一个通信渠道都是一个从服务器从主服务器获得2进制日志的链接。
这意味着每个通信渠道都得有一个IO_THREAD .我们需要运行不同的 “CHANGE MASTER” 命令, 对于
每一个主服务器。我们需要用到 “FOR CHANNEL”这个参数来提供通信链接的名字。
CHANGE MASTER MASTER_HOST='something', MASTER_USER=... FOR CHANNEL="name_of_channel";
很容易. 有一个单一的先决条件。从服务器需要在mysql5.6上配置安全功能，这意味着信息通常包含在主服
务器上。信息或日志应该在一个表中，让我们开始配置吧
注意
与dba交流了下，对于多源复制，还是有些东西要注意的（有说的不对的请指正）：
1. 初次配置耗时较长，需要将各个“master”的数据dump下来，再source到“slave”上；
2. 需要考虑各“master”数据增长频率，“slave”的数据增长频率是这些的总和，如果太高，会导致大量的磁盘IO，造成数据更新延迟，最严重的是会影响正常的查询；
3. 如果多实例中存在同名的库，则同名库的表都会放到一个库中；
4. 如果同名库中的表名相同且结构相同，则数据会到一起；如果结构不同，则先建的有效。
一从两主:多源复制
每台mysql 服务器都需要加
my.cnf要加两个参数才可以在GTID多源复制 master-info-repository=TABLE relay-log-info-repository=TABLE
server1:只接受下面两种数据库，这两种数据来源不同的通道,系统表不复制
replicate_do_db=db1 replicate_do_db=db2
server1 连到 slave1
mysql> change master to master_host='10.24.220.70',master_user='repl',master_password='123',master_port=3306,master_auto_position=1 for channel 'slave1';
server1 连到 slave2 mysql> change master to master_host='10.169.214.33',master_user='repl',master_password='123',master_port=3306,master_auto_position=1 for channel 'slave2';
server1:
mysql> start slave; //两个通道一起起动了 Query OK, 0 rows affected (0.02 sec) mysql> show processlist; +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ | 2 | root | localhost | NULL | Query | 0 | starting | show processlist | | 8 | system user | | NULL | Connect | 3 | Waiting for master to send event | NULL | | 9 | system user | | NULL | Connect | 3 | Slave has read all relay log; waiting for more updates | NULL | | 10 | system user | | NULL | Connect | 3 | Connecting to master | NULL | | 11 | system user | | NULL | Connect | 3 | Slave has read all relay log; waiting for more updates | NULL | +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ 5 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.24.220.70
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log.000001
Read_Master_Log_Pos: 714
Relay_Log_File: relay-log-slave1.000002
Relay_Log_Pos: 642
Relay_Master_Log_File: log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1,db2
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: 714
Relay_Log_Space: 850
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: 101
Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601
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: 1915a42e-ffa2-11e5-a6ca-00163e000601:3
Executed_Gtid_Set: 1915a42e-ffa2-11e5-a6ca-00163e000601:1-3,
191683db-ffa2-11e5-a424-00163e0007d3:1-3,
191f7a9f-ffa2-11e5-a825-00163e00242a:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: slave1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.169.214.33
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000001
Read_Master_Log_Pos: 714
Relay_Log_File: relay-log-slave2.000002
Relay_Log_Pos: 915
Relay_Master_Log_File: bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1,db2
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: 714
Relay_Log_Space: 1123
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: 102
Master_UUID: 191683db-ffa2-11e5-a424-00163e0007d3
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: 191683db-ffa2-11e5-a424-00163e0007d3:1-3
Executed_Gtid_Set: 1915a42e-ffa2-11e5-a6ca-00163e000601:1-3,
191683db-ffa2-11e5-a424-00163e0007d3:1-3,
191f7a9f-ffa2-11e5-a825-00163e00242a:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: slave2
Master_TLS_Version:
2 rows in set (0.00 sec)
mysql> stop slave; //两个通道同时停止 Query OK, 0 rows affected (0.03 sec) mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.24.220.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-log-slave1.000002 Relay_Log_Pos: 355 Relay_Master_Log_File: log.000001 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: 154 Relay_Log_Space: 563 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: 101 Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601 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: 1 Replicate_Rewrite_DB: Channel_Name: slave1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Master_Host: 10.169.214.33 Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-log-slave2.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: 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: 0 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: 2003 Last_IO_Error: error connecting to master 'repl@10.169.214.33:3307' - retry-time: 60 retries: 4 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: 160517 12:38:11 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: slave2 Master_TLS_Version: 2 rows in set (0.00 sec)
mysql> start slave for channel 'slave1'; // 停止单一通道 Query OK, 0 rows affected (0.00 sec) mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.24.220.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-log-slave1.000003 Relay_Log_Pos: 355 Relay_Master_Log_File: log.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: 154 Relay_Log_Space: 764 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: 101 Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601 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: 1 Replicate_Rewrite_DB: Channel_Name: slave1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Master_Host: 10.169.214.33 Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-log-slave2.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: 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: 0 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: 2003 Last_IO_Error: error connecting to master 'repl@10.169.214.33:3307' - retry-time: 60 retries: 4 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: 160517 12:38:11 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: slave2 Master_TLS_Version: 2 rows in set (0.00 sec)
mysql> show slave status for channel 'slave1'G //显示指定的通道状态 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.24.220.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-log-slave1.000003 Relay_Log_Pos: 355 Relay_Master_Log_File: log.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: 154 Relay_Log_Space: 764 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: 101 Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601 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: 1 Replicate_Rewrite_DB: Channel_Name: slave1 Master_TLS_Version: 1 row in set (0.00 sec)
mysql> stop slave io_thread for channel 'slave1'; //停止批定通道的IO_THREAD Query OK, 0 rows affected (0.00 sec) mysql> show slave status for channel 'slave1'G *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.24.220.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-log-slave1.000003 Relay_Log_Pos: 355 Relay_Master_Log_File: log.000001 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: 154 Relay_Log_Space: 764 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: 101 Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601 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: 1 Replicate_Rewrite_DB: Channel_Name: slave1 Master_TLS_Version: 1 row in set (0.00 sec)
转自
MySQL 5.7 的多源复制 - wolf - CSDN博客 http://blog.csdn.net/yujin2010good/article/details/50904578
mysql 5.7 多源复制 原创 - zengkefu - 博客园 https://www.cnblogs.com/zengkefu/p/5501130.html
mysql的GTID复制和多源复制 - 李世侠 - 博客园 https://www.cnblogs.com/caibird2005/p/5181101.html
简单讲解MySQL中的多源复制_Mysql_脚本之家 http://www.jb51.net/article/64569.htm
MySQL 5.7多源复制功能的应用 - CSDN博客 http://blog.csdn.net/mj158518/article/details/52311605
一从两主:多源复制
每台mysql 服务器都需要加
my.cnf要加两个参数才可以在GTID多源复制 master-info-repository=TABLE relay-log-info-repository=TABLE
server1:只接受下面两种数据库，这两种数据来源不同的通道,系统表不复制
replicate_do_db=db1 replicate_do_db=db2
server1 连到 slave1
mysql> change master to master_host='10.24.220.70',master_user='repl',master_password='123',master_port=3306,master_auto_position=1 for channel 'slave1';
server1 连到 slave2 mysql> change master to master_host='10.169.214.33',master_user='repl',master_password='123',master_port=3306,master_auto_position=1 for channel 'slave2';
server1:
mysql> start slave; //两个通道一起起动了 Query OK, 0 rows affected (0.02 sec) mysql> show processlist; +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ | 2 | root | localhost | NULL | Query | 0 | starting | show processlist | | 8 | system user | | NULL | Connect | 3 | Waiting for master to send event | NULL | | 9 | system user | | NULL | Connect | 3 | Slave has read all relay log; waiting for more updates | NULL | | 10 | system user | | NULL | Connect | 3 | Connecting to master | NULL | | 11 | system user | | NULL | Connect | 3 | Slave has read all relay log; waiting for more updates | NULL | +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ 5 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.24.220.70
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log.000001
Read_Master_Log_Pos: 714
Relay_Log_File: relay-log-slave1.000002
Relay_Log_Pos: 642
Relay_Master_Log_File: log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1,db2
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: 714
Relay_Log_Space: 850
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: 101
Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601
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: 1915a42e-ffa2-11e5-a6ca-00163e000601:3
Executed_Gtid_Set: 1915a42e-ffa2-11e5-a6ca-00163e000601:1-3,
191683db-ffa2-11e5-a424-00163e0007d3:1-3,
191f7a9f-ffa2-11e5-a825-00163e00242a:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: slave1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.169.214.33
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000001
Read_Master_Log_Pos: 714
Relay_Log_File: relay-log-slave2.000002
Relay_Log_Pos: 915
Relay_Master_Log_File: bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1,db2
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: 714
Relay_Log_Space: 1123
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: 102
Master_UUID: 191683db-ffa2-11e5-a424-00163e0007d3
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: 191683db-ffa2-11e5-a424-00163e0007d3:1-3
Executed_Gtid_Set: 1915a42e-ffa2-11e5-a6ca-00163e000601:1-3,
191683db-ffa2-11e5-a424-00163e0007d3:1-3,
191f7a9f-ffa2-11e5-a825-00163e00242a:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: slave2
Master_TLS_Version:
2 rows in set (0.00 sec)
mysql> stop slave; //两个通道同时停止 Query OK, 0 rows affected (0.03 sec) mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.24.220.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-log-slave1.000002 Relay_Log_Pos: 355 Relay_Master_Log_File: log.000001 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: 154 Relay_Log_Space: 563 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: 101 Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601 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: 1 Replicate_Rewrite_DB: Channel_Name: slave1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Master_Host: 10.169.214.33 Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-log-slave2.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: 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: 0 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: 2003 Last_IO_Error: error connecting to master 'repl@10.169.214.33:3307' - retry-time: 60 retries: 4 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: 160517 12:38:11 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: slave2 Master_TLS_Version: 2 rows in set (0.00 sec)
mysql> start slave for channel 'slave1'; // 停止单一通道 Query OK, 0 rows affected (0.00 sec) mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.24.220.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-log-slave1.000003 Relay_Log_Pos: 355 Relay_Master_Log_File: log.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: 154 Relay_Log_Space: 764 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: 101 Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601 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: 1 Replicate_Rewrite_DB: Channel_Name: slave1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Master_Host: 10.169.214.33 Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-log-slave2.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: 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: 0 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: 2003 Last_IO_Error: error connecting to master 'repl@10.169.214.33:3307' - retry-time: 60 retries: 4 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: 160517 12:38:11 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: slave2 Master_TLS_Version: 2 rows in set (0.00 sec)
mysql> show slave status for channel 'slave1'G //显示指定的通道状态 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.24.220.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-log-slave1.000003 Relay_Log_Pos: 355 Relay_Master_Log_File: log.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: 154 Relay_Log_Space: 764 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: 101 Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601 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: 1 Replicate_Rewrite_DB: Channel_Name: slave1 Master_TLS_Version: 1 row in set (0.00 sec)
mysql> stop slave io_thread for channel 'slave1'; //停止批定通道的IO_THREAD Query OK, 0 rows affected (0.00 sec) mysql> show slave status for channel 'slave1'G *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.24.220.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-log-slave1.000003 Relay_Log_Pos: 355 Relay_Master_Log_File: log.000001 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: 154 Relay_Log_Space: 764 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: 101 Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601 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: 1 Replicate_Rewrite_DB: Channel_Name: slave1 Master_TLS_Version: 1 row in set (0.00 sec)