什么是多源复制?
首先，我们需要清楚 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='',master_port=,master_auto_position= for channel 'slave1';
server1 连到 slave2 mysql> change master to master_host='10.169.214.33',master_user='repl',master_password='',master_port=,master_auto_position= for channel 'slave2';
server1:
mysql> start slave; //两个通道一起起动了
Query OK, rows affected (0.02 sec) mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| | root | localhost | NULL | Query | | starting | show processlist |
| | system user | | NULL | Connect | | Waiting for master to send event | NULL |
| | system user | | NULL | Connect | | Slave has read all relay log; waiting for more updates | NULL |
| | system user | | NULL | Connect | | Connecting to master | NULL |
| | system user | | NULL | Connect | | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
rows in set (0.00 sec)
mysql> start slave;
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: 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, rows affected (0.03 sec) mysql> show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.24.220.70
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File: log.
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave1.
Relay_Log_Pos:
Relay_Master_Log_File: log.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave1
Master_TLS_Version:
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.169.214.33
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave2.
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error: error connecting to master 'repl@10.169.214.33:3307' - retry-time: retries:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp: ::
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave2
Master_TLS_Version:
rows in set (0.00 sec)
mysql> start slave for channel 'slave1'; // 停止单一通道
Query OK, rows affected (0.00 sec) mysql> show slave status\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.24.220.70
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File: log.
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave1.
Relay_Log_Pos:
Relay_Master_Log_File: log.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave1
Master_TLS_Version:
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.169.214.33
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave2.
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error: error connecting to master 'repl@10.169.214.33:3307' - retry-time: retries:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp: ::
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave2
Master_TLS_Version:
rows in set (0.00 sec)
mysql> show slave status for channel 'slave1'\G //显示指定的通道状态
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.24.220.70
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File: log.
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave1.
Relay_Log_Pos:
Relay_Master_Log_File: log.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave1
Master_TLS_Version:
row in set (0.00 sec)
mysql> stop slave io_thread for channel 'slave1'; //停止批定通道的IO_THREAD
Query OK, rows affected (0.00 sec) mysql> show slave status for channel 'slave1'\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.24.220.70
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File: log.
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave1.
Relay_Log_Pos:
Relay_Master_Log_File: log.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave1
Master_TLS_Version:
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='',master_port=,master_auto_position= for channel 'slave1';
server1 连到 slave2 mysql> change master to master_host='10.169.214.33',master_user='repl',master_password='',master_port=,master_auto_position= for channel 'slave2';
server1:
mysql> start slave; //两个通道一起起动了
Query OK, rows affected (0.02 sec) mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| | root | localhost | NULL | Query | | starting | show processlist |
| | system user | | NULL | Connect | | Waiting for master to send event | NULL |
| | system user | | NULL | Connect | | Slave has read all relay log; waiting for more updates | NULL |
| | system user | | NULL | Connect | | Connecting to master | NULL |
| | system user | | NULL | Connect | | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
rows in set (0.00 sec)
mysql> start slave;
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: 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, rows affected (0.03 sec) mysql> show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.24.220.70
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File: log.
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave1.
Relay_Log_Pos:
Relay_Master_Log_File: log.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave1
Master_TLS_Version:
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.169.214.33
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave2.
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error: error connecting to master 'repl@10.169.214.33:3307' - retry-time: retries:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp: ::
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave2
Master_TLS_Version:
rows in set (0.00 sec)
mysql> start slave for channel 'slave1'; // 停止单一通道
Query OK, rows affected (0.00 sec) mysql> show slave status\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.24.220.70
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File: log.
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave1.
Relay_Log_Pos:
Relay_Master_Log_File: log.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave1
Master_TLS_Version:
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.169.214.33
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave2.
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error: error connecting to master 'repl@10.169.214.33:3307' - retry-time: retries:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp: ::
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave2
Master_TLS_Version:
rows in set (0.00 sec)
mysql> show slave status for channel 'slave1'\G //显示指定的通道状态
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.24.220.70
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File: log.
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave1.
Relay_Log_Pos:
Relay_Master_Log_File: log.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave1
Master_TLS_Version:
row in set (0.00 sec)
mysql> stop slave io_thread for channel 'slave1'; //停止批定通道的IO_THREAD
Query OK, rows affected (0.00 sec) mysql> show slave status for channel 'slave1'\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.24.220.70
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File: log.
Read_Master_Log_Pos:
Relay_Log_File: relay-log-slave1.
Relay_Log_Pos:
Relay_Master_Log_File: log.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1915a42e-ffa2-11e5-a6ca-00163e000601
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name: slave1
Master_TLS_Version:
row in set (0.00 sec)