5.7多源复制
1.1 实验概要
1.1.1 实验假设
本实验假设已经完成操作系统和MySQL安装部署。
1.1.2 实验目的
MySQL5.7的多源复制技术搭建部署,然后简单测试。
1.1.3 环境信息
操作系统 | MySQL版本 | 服务器地址 | 服务器角色 |
5.7.18 | 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:
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 [emily]>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
点击(此处)折叠或打开
- root@192.168.102.25:3306 [emily]>show slave status for channel 'repl24'\G;
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- 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: 185903
- Relay_Log_File: relay-bin-repl24.000007
- Relay_Log_Pos: 724
- Relay_Master_Log_File: mysql-bin.000040
- 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: 185903
- Relay_Log_Space: 1019
- 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: 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:
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set: bd783f44-258f-11e7-914b-005056b5d312:28072-28090
- 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: 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 [emily]>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: 139
- LAST_HEARTBEAT_TIMESTAMP: 2017-05-19 18:07:35
- 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: 74
- LAST_HEARTBEAT_TIMESTAMP: 2017-05-19 18:07:39
- 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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14968506/viewspace-2139616/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14968506/viewspace-2139616/