ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > MySQL5.7多源复制

MySQL5.7多源复制

原创 MySQL 作者：godsonfly 时间：2017-05-19 18:12:52 0 删除 编辑


      5.7多源复制

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

点击(此处)折叠或打开

  1. root@192.168.102.25:3306 [emily]>show slave status for channel 'repl23'\G;
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.102.23
  5.                   Master_User: repl23
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000002
  9.           Read_Master_Log_Pos: 2849
  10.                Relay_Log_File: relay-bin-repl23.000008
  11.                 Relay_Log_Pos: 454
  12.         Relay_Master_Log_File: mysql-bin.000002
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB:
  16.           Replicate_Ignore_DB:
  17.            Replicate_Do_Table:
  18.        Replicate_Ignore_Table:
  19.       Replicate_Wild_Do_Table:
  20.   Replicate_Wild_Ignore_Table:
  21.                    Last_Errno: 0
  22.                    Last_Error:
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 2849
  25.               Relay_Log_Space: 749
  26.               Until_Condition: None
  27.                Until_Log_File:
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File:
  31.            Master_SSL_CA_Path:
  32.               Master_SSL_Cert:
  33.             Master_SSL_Cipher:
  34.                Master_SSL_Key:
  35.         Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 0
  38.                 Last_IO_Error:
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error:
  41.   Replicate_Ignore_Server_Ids:
  42.              Master_Server_Id: 233306
  43.                   Master_UUID: 7937ac78-3c39-11e7-b59e-005056b5d25f
  44.              Master_Info_File: mysql.slave_master_info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind:
  50.       Last_IO_Error_Timestamp:
  51.      Last_SQL_Error_Timestamp:
  52.                Master_SSL_Crl:
  53.            Master_SSL_Crlpath:
  54.            Retrieved_Gtid_Set: 7937ac78-3c39-11e7-b59e-005056b5d25f:5-13
  55.             Executed_Gtid_Set: 7937ac78-3c39-11e7-b59e-005056b5d25f:1-13,
  56. 921a9068-24d2-11e7-99b5-005056b59593:1-287,
  57. bd783f44-258f-11e7-914b-005056b5d312:1-28090
  58.                 Auto_Position: 1
  59.          Replicate_Rewrite_DB:
  60.                  Channel_Name: repl23
  61.            Master_TLS_Version:
  62. 1 row in set (0.00 sec)

  64. ERROR:
  65. No query specified


## source 2

点击(此处)折叠或打开

  1. root@192.168.102.25:3306 [emily]>show slave status for channel 'repl24'\G;
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.102.24
  5.                   Master_User: repl24
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000040
  9.           Read_Master_Log_Pos: 185903
  10.                Relay_Log_File: relay-bin-repl24.000007
  11.                 Relay_Log_Pos: 724
  12.         Relay_Master_Log_File: mysql-bin.000040
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB:
  16.           Replicate_Ignore_DB:
  17.            Replicate_Do_Table:
  18.        Replicate_Ignore_Table:
  19.       Replicate_Wild_Do_Table:
  20.   Replicate_Wild_Ignore_Table:
  21.                    Last_Errno: 0
  22.                    Last_Error:
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 185903
  25.               Relay_Log_Space: 1019
  26.               Until_Condition: None
  27.                Until_Log_File:
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File:
  31.            Master_SSL_CA_Path:
  32.               Master_SSL_Cert:
  33.             Master_SSL_Cipher:
  34.                Master_SSL_Key:
  35.         Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 0
  38.                 Last_IO_Error:
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error:
  41.   Replicate_Ignore_Server_Ids:
  42.              Master_Server_Id: 243306
  43.                   Master_UUID: bd783f44-258f-11e7-914b-005056b5d312
  44.              Master_Info_File: mysql.slave_master_info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind:
  50.       Last_IO_Error_Timestamp:
  51.      Last_SQL_Error_Timestamp:
  52.                Master_SSL_Crl:
  53.            Master_SSL_Crlpath:
  54.            Retrieved_Gtid_Set: bd783f44-258f-11e7-914b-005056b5d312:28072-28090
  55.             Executed_Gtid_Set: 7937ac78-3c39-11e7-b59e-005056b5d25f:1-13,
  56. 921a9068-24d2-11e7-99b5-005056b59593:1-287,
  57. bd783f44-258f-11e7-914b-005056b5d312:1-28090
  58.                 Auto_Position: 1
  59.          Replicate_Rewrite_DB:
  60.                  Channel_Name: repl24
  61.            Master_TLS_Version:
  62. 1 row in set (0.00 sec)

  64. ERROR:
  65. 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  监控

select * from performance_schema.replication_connection_status\G;

点击(此处)折叠或打开

  1. root@192.168.102.25:3306 [emily]>select * from performance_schema.replication_connection_status\G;
  2. *************************** 1. row ***************************
  3.              CHANNEL_NAME: repl23
  4.                GROUP_NAME:
  5.               SOURCE_UUID: 7937ac78-3c39-11e7-b59e-005056b5d25f
  6.                 THREAD_ID: 71
  7.             SERVICE_STATE: ON
  8. COUNT_RECEIVED_HEARTBEATS: 139
  9.  LAST_HEARTBEAT_TIMESTAMP: 2017-05-19 18:07:35
  10.  RECEIVED_TRANSACTION_SET: 7937ac78-3c39-11e7-b59e-005056b5d25f:5-13
  11.         LAST_ERROR_NUMBER: 0
  12.        LAST_ERROR_MESSAGE:
  13.      LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
  14. *************************** 2. row ***************************
  15.              CHANNEL_NAME: repl24
  16.                GROUP_NAME:
  17.               SOURCE_UUID: bd783f44-258f-11e7-914b-005056b5d312
  18.                 THREAD_ID: 73
  19.             SERVICE_STATE: ON
  20. COUNT_RECEIVED_HEARTBEATS: 74
  21.  LAST_HEARTBEAT_TIMESTAMP: 2017-05-19 18:07:39
  22.  RECEIVED_TRANSACTION_SET: bd783f44-258f-11e7-914b-005056b5d312:28072-28090
  23.         LAST_ERROR_NUMBER: 0
  24.        LAST_ERROR_MESSAGE:
  25.      LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
  26. 2 rows in set (0.00 sec)

  28. ERROR:
  29. No query specified
 select * from performance_schema.replication_connection_configuration\G;

点击(此处)折叠或打开

  1. root@192.168.102.25:3306 [emily]>select * from performance_schema.replication_connection_configuration\G;
  2. *************************** 1. row ***************************
  3.                  CHANNEL_NAME: repl23
  4.                          HOST: 192.168.102.23
  5.                          PORT: 3306
  6.                          USER: repl23
  7.             NETWORK_INTERFACE:
  8.                 AUTO_POSITION: 1
  9.                   SSL_ALLOWED: NO
  10.                   SSL_CA_FILE:
  11.                   SSL_CA_PATH:
  12.               SSL_CERTIFICATE:
  13.                    SSL_CIPHER:
  14.                       SSL_KEY:
  15. SSL_VERIFY_SERVER_CERTIFICATE: NO
  16.                  SSL_CRL_FILE:
  17.                  SSL_CRL_PATH:
  18.     CONNECTION_RETRY_INTERVAL: 60
  19.        CONNECTION_RETRY_COUNT: 86400
  20.            HEARTBEAT_INTERVAL: 30.000
  21.                   TLS_VERSION:
  22. *************************** 2. row ***************************
  23.                  CHANNEL_NAME: repl24
  24.                          HOST: 192.168.102.24
  25.                          PORT: 3306
  26.                          USER: repl24
  27.             NETWORK_INTERFACE:
  28.                 AUTO_POSITION: 1
  29.                   SSL_ALLOWED: NO
  30.                   SSL_CA_FILE:
  31.                   SSL_CA_PATH:
  32.               SSL_CERTIFICATE:
  33.                    SSL_CIPHER:
  34.                       SSL_KEY:
  35. SSL_VERIFY_SERVER_CERTIFICATE: NO
  36.                  SSL_CRL_FILE:
  37.                  SSL_CRL_PATH:
  38.     CONNECTION_RETRY_INTERVAL: 60
  39.        CONNECTION_RETRY_COUNT: 86400
  40.            HEARTBEAT_INTERVAL: 30.000
  41.                   TLS_VERSION:
  42. 2 rows in set (0.00 sec)

  44. ERROR:
  45. 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博客 ” ，链接：https://blog.itpub.net/14968506/viewspace-2139616/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Communications link fai分析解决
下一篇： 没有了~
请登录后发表评论 登录
全部评论
godsonfly

注册时间：2010-11-06

  • 博文量
    2
  • 访问量
    3776

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593