1.1 实验概要
1.1.1 实验假设
本实验假设已经完成操作系统和MySQL安装部署。
1.1.2 实验目的
MySQL5.7的多源复制技术搭建部署,然后简单测试。
1.1.3 环境信息
操作系统
MySQL版本
服务器地址
服务器角色
Centos7
5.7.18
192.168.102.23
source 1
Centos7
5.7.18
192.168.102.24
source 2
Centos7
5.7.18
192.168.102.25
target
1.1.4 实验规划
服务器地址
服务器角色
MySQL库
账户
192.168.102.23
source 1
emily
repl23
192.168.102.24
source 2
evelyn
repl24
192.168.102.25
target
source 1.emilyàtarget
source 2.evelynàtarget
username:
source 1:repl23
source 2:repl24
1.2 实验操作
1.2.1 源端备份数据库
分别从source1和source2分别备份出emily和evelyn库,然后分别copy到target中。其中操作步骤如下:
source 1:
##备份
[root@dsm-db-102023 11:26:50 /root]
#mysqldump -uroot –pmysql --single-transaction --master-data=2 --databases emily > /root/dump/emily.sql
##传输
[root@dsm-db-102023 11:34:38 /root/dump]
#scp /root/dump/emily.sql root@192.168.102.25:/root/dump/
source 2:
##备份
[root@test-mysql-10224 11:19:47 /root]
#mysqldump -uroot -pmysql --single-transaction --master-data=2 --databases evelyn > /root/dump/evelyn.sql
##传输
[root@dsm-db-102023 11:34:38 /root/dump]
#scp /root/dump/emily.sql root@192.168.102.25:/root/dump/
1.2.2 创建同步账户
分别在source1和source2中创建同步账户repl23、repl24
source1
root@192.168.102.23:3306 [emily]>create user repl23 identified by "repl";
root@192.168.102.23:3306 [(none)]>grant replication slave on *.* to 'repl23'@'%';
source2
root@192.168.102.24:3306 [evelyn]>create user repl24 identified by "repl";
root@192.168.102.24:3306 [evelyn]>grant replication slave on *.* to 'repl24'@'%';
1.2.3 目标端恢复数据库
## 恢复evelyn库
root@192.168.102.25:3306 [(none)]>reset master
[root@dsm-db-102025 14:01:24 /root/dump]
#mysql -uroot -pmysql < evelyn.sql
## 查看gtid_purged
root@192.168.102.25:3306 [(none)]>show global variables like '%gtid_purged%';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| gtid_purged | 921a9068-24d2-11e7-99b5-005056b59593:1-287,
bd783f44-258f-11e7-914b-005056b5d312:1-28071 |
+---------------+------------------------------------------+
## 恢复emily库
root@192.168.102.25:3306 [(none)]>reset master
[root@dsm-db-102025 14:21:22 /root/dump]
#mysql -uroot -pmysql < /root/dump/emily.sql;
##设置source1和source2的gtid_purged
root@192.168.102.25:3306 [(none)]>show variables like '%gtid_purged%';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| gtid_purged | 7937ac78-3c39-11e7-b59e-005056b5d25f:1-4 |
+---------------+------------------------------------------+
## set global gtid_purged='921a9068-24d2-11e7-99b5-005056b59593:1-287,bd783f44-258f-11e7-914b-005056b5d312:1-28071,7937ac78-3c39-11e7-b59e-005056b5d25f:1-4';
root@192.168.102.25:3306 [(none)]>reset master;
Query OK, 0 rows affected (0.01 sec)
root@192.168.102.25:3306 [(none)]>set global gtid_purged='921a9068-24d2-11e7-99b5-005056b59593:1-287,bd783f44-258f-11e7-914b-005056b5d312:1-28071,7937ac78-3c39-11e7-b59e-005056b5d25f:1-4';
Query OK, 0 rows affected (0.00 sec)
root@192.168.102.25:3306 [(none)]>show variables like '%gtid_purged%';
+---------------+------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------------------------------------------------------------------------------------------+
| gtid_purged | 7937ac78-3c39-11e7-b59e-005056b5d25f:1-4,
921a9068-24d2-11e7-99b5-005056b59593:1-287,
bd783f44-258f-11e7-914b-005056b5d312:1-28071 |
+---------------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
root@192.168.102.25:3306 [(none)]>
1.2.4 修改MySQL存储方式
修改MySQL存储master-info和relay-info的方式,即从文件存储改为表存储
## 在线修改
STOP SLAVE;
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';
##修改配置文件
[mysqld]
master_info_repository=TABLE
relay_log_info_repository=TABLE
1.2.5 同步操作
change master
登录slave进行同步操作,分别change master到两台master主机,多源复制需要标注
FOR CHANNEL ‘CHANNEL_NAME’区分
##source 1
root@192.168.102.25:3306 [(none)]>CHANGE MASTER TO MASTER_HOST='192.168.102.23',MASTER_USER='repl23', MASTER_PASSWORD='repl',master_auto_position=1 FOR CHANNEL 'repl23';
##source 2
root@192.168.102.25:3306 [(none)]>CHANGE MASTER TO MASTER_HOST='192.168.102.24',MASTER_USER='repl24', MASTER_PASSWORD='repl',master_auto_position=1 FOR CHANNEL 'repl24';
启动slave
启动所有同步: start slave;
启动单个同步: start slave for channel ‘channel_name’;
##启动source 1
root@192.168.102.25:3306 [(none)]>start slave for channel 'repl23';
##启动source 2
root@192.168.102.25:3306 [(none)]>start slave for channel 'repl24';
检查slave状态
检查所有slave: show slave status\G;
检查单个slave: show slave status for chennel ‘channel_name’\G;
## source 1
root@192.168.102.25:3306 [(none)]>show slave status for channel 'repl23'\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.102.23
Master_User: repl23
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2849
Relay_Log_File: relay-bin-repl23.000008
Relay_Log_Pos: 454
Relay_Master_Log_File: mysql-bin.000002
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: 2849
Relay_Log_Space: 749
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: 233306
Master_UUID: 7937ac78-3c39-11e7-b59e-005056b5d25f
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: 7937ac78-3c39-11e7-b59e-005056b5d25f:5-13
Executed_Gtid_Set: 7937ac78-3c39-11e7-b59e-005056b5d25f:1-13,
921a9068-24d2-11e7-99b5-005056b59593:1-287,
bd783f44-258f-11e7-914b-005056b5d312:1-28090
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: repl23
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
##source 2
root@192.168.102.25:3306 [(none)]>show slave status for channel 'repl24'\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.102.24
Master_User: repl24
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000040
Read_Master_Log_Pos: 185363
Relay_Log_File: relay-bin-repl24.000003
Relay_Log_Pos: 454
Relay_Master_Log_File: mysql-bin.000040
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: 185363
Relay_Log_Space: 4318
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: 1595
Last_IO_Error: Relay log write failure: could not queue event from master
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 243306
Master_UUID: bd783f44-258f-11e7-914b-005056b5d312
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: 170519 16:12:33
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: bd783f44-258f-11e7-914b-005056b5d312:28072-28088
Executed_Gtid_Set: 7937ac78-3c39-11e7-b59e-005056b5d25f:1-11,
921a9068-24d2-11e7-99b5-005056b59593:1-287,
bd783f44-258f-11e7-914b-005056b5d312:1-28088
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: repl24
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
1.2.6 验证同步
##source 1
root@192.168.102.23:3306 [emily]>insert into emily(id,name)values(2,'evelyn');
Query OK, 1 row affected (0.00 sec)
root@192.168.102.23:3306 [emily]>select * from emily;
+------+--------+
| id | name |
+------+--------+
| 1 | emily |
| 2 | evelyn |
+------+--------+
2 rows in set (0.00 sec)
##target验证
root@192.168.102.25:3306 [emily]>select * from emily;
+------+--------+
| id | name |
+------+--------+
| 1 | emily |
| 2 | evelyn |
+------+--------+
2 rows in set (0.00 sec)
## source 2
root@192.168.102.24:3306 [evelyn]>insert into evelyn(id,name)values(2,'emily');
Query OK, 1 row affected (1.00 sec)
root@192.168.102.24:3306 [evelyn]>select * from evelyn;
+------+--------+
| id | name |
+------+--------+
| 1 | evelyn |
| 2 | emily |
+------+--------+
2 rows in set (0.00 sec)
## target端
root@192.168.102.25:3306 [evelyn]>select * from evelyn;
+------+--------+
| id | name |
+------+--------+
| 1 | evelyn |
| 2 | emily |
+------+--------+
2 rows in set (0.00 sec)
1.2.7 监控
root@192.168.102.25:3306 [evelyn]>
select * from performance_schema.replication_connection_status\G;
*************************** 1. row ***************************
CHANNEL_NAME: repl23
GROUP_NAME:
SOURCE_UUID: 7937ac78-3c39-11e7-b59e-005056b5d25f
THREAD_ID: 71
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 78
LAST_HEARTBEAT_TIMESTAMP: 2017-05-19 17:37:05
RECEIVED_TRANSACTION_SET: 7937ac78-3c39-11e7-b59e-005056b5d25f:5-13
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: repl24
GROUP_NAME:
SOURCE_UUID: bd783f44-258f-11e7-914b-005056b5d312
THREAD_ID: 73
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 13
LAST_HEARTBEAT_TIMESTAMP: 2017-05-19 17:37:09
RECEIVED_TRANSACTION_SET: bd783f44-258f-11e7-914b-005056b5d312:28072-28090
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
2 rows in set (0.00 sec)
ERROR:
No query specified
root@192.168.102.25:3306 [emily]>
select * from performance_schema.replication_connection_configuration\G;
*************************** 1. row ***************************
CHANNEL_NAME: repl23
HOST: 192.168.102.23
PORT: 3306
USER: repl23
NETWORK_INTERFACE:
AUTO_POSITION: 1
SSL_ALLOWED: NO
SSL_CA_FILE:
SSL_CA_PATH:
SSL_CERTIFICATE:
SSL_CIPHER:
SSL_KEY:
SSL_VERIFY_SERVER_CERTIFICATE: NO
SSL_CRL_FILE:
SSL_CRL_PATH:
CONNECTION_RETRY_INTERVAL: 60
CONNECTION_RETRY_COUNT: 86400
HEARTBEAT_INTERVAL: 30.000
TLS_VERSION:
*************************** 2. row ***************************
CHANNEL_NAME: repl24
HOST: 192.168.102.24
PORT: 3306
USER: repl24
NETWORK_INTERFACE:
AUTO_POSITION: 1
SSL_ALLOWED: NO
SSL_CA_FILE:
SSL_CA_PATH:
SSL_CERTIFICATE:
SSL_CIPHER:
SSL_KEY:
SSL_VERIFY_SERVER_CERTIFICATE: NO
SSL_CRL_FILE:
SSL_CRL_PATH:
CONNECTION_RETRY_INTERVAL: 60
CONNECTION_RETRY_COUNT: 86400
HEARTBEAT_INTERVAL: 30.000
TLS_VERSION:
2 rows in set (0.00 sec)
ERROR:
No query specified
1.2.8 跳过事务
##GTID
STOP SLAVE FOR CHANNEL ‘CHANNEL_NAME’;
SET SESSION GTID_NEXT=’’;
BEGIN;COMMIT;
SET SESSION GTID_NEXT=’AUTOMATIC’;
START SLAVE FOR CHANNEL ‘CHANNEL_NAME’;
## binlog+position
stop slave sql_thread FOR CHANNEL ‘CHANNEL_NAME’;;
set global sql_slave_skip_counter=1;
start slave sql_thread FOR CHANNEL ‘CHANNEL_NAME’;;
##
root@192.168.102.25:3306 [(none)]>set session gtid_next='bd783f44-258f-11e7-914b-005056b5d312:28083';
Query OK, 0 rows affected (0.00 sec)
root@192.168.102.25:3306 [(none)]>begin;commit;
Query OK, 0 rows affected (0.00 sec)
root@192.168.102.25:3306 [(none)]>set session gtid_next=automatic;
Query OK, 0 rows affected (0.00 sec)
root@192.168.102.25:3306 [(none)]>start slave for channel 'repl24';
Query OK, 0 rows affected (0.00 sec)
1.2.9 遇见错误
1、 Last_IO_Error: Relay log write failure: could not queue event from master ##repl24
Last_IO_Error: Fatal error: Failed to run 'after_read_event' hook ##repl23
stop slave
start slave
最后发现是开启一个源开启了半同步复制,一个源没有开题半同步复制。
2、 清除slave信息
reset slave
## 创建多源复制过程中,发现有一个slave没有channel_name,使用如下语句清除slave信息
reset slave all for channel '';
3、 root@192.168.102.24:3306 [evelyn]>uninstall plugin rpl_semi_sync_master;
Query OK, 0 rows affected (0.01 sec)
root@192.168.102.24:3306 [evelyn]>uninstall plugin rpl_semi_sync_slave;
Query OK, 0 rows affected (0.00 sec)
4、
参考资料:
http://www.cnblogs.com/xuanzhi201111/p/5151666.html
http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
http://www.longlong.asia/2015/10/21/mysql57-new-features.html