首页 > 代码库 > MySQL多源复制搭建
MySQL多源复制搭建
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=http://www.mamicode.com/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=http://www.mamicode.com/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 *************************** ERROR: |
|
##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
MySQL多源复制搭建