MySQL多源复制搭建
2018-06-17 23:45:40来源：未知 阅读 ()
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 *************************** 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
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
- MySQL replace函数怎么替换字符串语句 2020-03-09
- PHP访问MySQL查询超时怎么办 2020-03-09
- mysql登录时闪退 2020-02-27
- MySQL出现1067错误号 2020-02-27
- mysql7.x如何单独安装mysql 2020-02-27
IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash
