当前位置： 维易PHP培训学院 > MYSQL教程 > 内容正文

MySQL5.7多源复制Mysql入门

作者：VEPHP   时间 2017-09-27

《MySQL5.7多源复制Mysql入门》要点：
本文介绍了MySQL5.7多源复制Mysql入门，希望对您有用。如果有疑问，可以联系我们。

导读：MySQL5.7开始支持多源复制,也就是多主一从的复制架构：使用多源复制的考虑：1、灾备作用：将各个库汇总在一起,就算是其他库都挂了(整个机房都无法连接了),还有最后一个救命稻草；
MySQL5.7开始支持多源复制,也就是多主一从的复制架构：

MySQL5.7多源复制

使用多源复制的考虑：
1、灾备作用：将各个库汇总在一起,就算是其他库都挂了(整个机房都无法连接了),还有最后一个救命稻草；
2、备份：直接在这个从库中做备份,不影响线上的数据库；
3、减少成本：不需要每个库都做一个实例,也减少了DBA的维护成本；
4、数据统计：后期的一些数据统计,需要将所有的库汇总在一起.
... ...

实验环境：
Master1：10.157.26.132:3307
Master2：10.157.26.133:3307
Slave：10.157.26.134:3307
多源复制是支持GTID和Binlog+Position,我这里是GTID复制.

MySQL5.7的安装这里不再介绍,可以查看http://www.cnblogs.com/lizhi221/p/6813907.html

一、配置文件：

Master1和Master2:

  1. #GTID 
  2. gtid-mode = on 
  3. binlog_gtid_simple_recovery=1 
  4. enforce_gtid_consistency=1 
  5. binlog_format = row 
  6. skip_slave_start = 1 
  7. log-bin = /data/mysql/mysql_3307/logs/binlog/mysql-bin 

Slave:

  1. #binlog 
  2. binlog_format = row 
  3. server-id = 1343307 
  4. log-bin = /data/mysql/mysql_3307/logs/binlog/mysql-bin 
  5. #GTID 
  6. gtid-mode = on 
  7. binlog_gtid_simple_recovery=1 
  8. enforce_gtid_consistency=1 
  9. #修改MySQL存储master-info和relay-info的方式,即从文件存储改为表存储 
  10. master_info_repository=TABLE 
  11. relay_log_info_repository=TABLE 
  12. replicate_ignore_db=mysql　　#忽略mysql库的同步 
  13. skip_slave_start = 1 
二、主库创建复制账号：

  1. Master1：grant replication slave,replication client on *.* to sampson_132@'10.157.26.%'identified by 'sampson_132'; 
  2. Master2：grant replication slave,replication client on *.* to sampson_133@'10.157.26.%'identified by 'sampson_133'; 
三：从库启动复制：

  1. mysql> change master to master_host='10.157.26.132',master_port=3307,master_user='sampson_132',master_password='sampson_132',master_auto_position=1 for channel 'Master_132'; 
  2. mysql>change master to master_host='10.157.26.133',master_port=3307,master_user='sampson_133',master_password='sampson_133',master_auto_position=1 for channel 'Master_133'; 
  3. mysql>start slave; 
也可以start slave for channel 'Master_132 '启动单个channel的复制.

四：查看slave状态
【也可以直接show slave status for channel 'Master_132' \G查看各个channel的复制状态】

  1. mysql> show slave status\G 
  2. *************************** 1. row *************************** 
  3.                Slave_IO_State: Waiting for master to send event 
  4.                   Master_Host: 10.157.26.132 
  5.                   Master_User: sampson_132 
  6.                   Master_Port: 3307 
  7.                 Connect_Retry: 60 
  8.               Master_Log_File: mysql-bin.000001 
  9.           Read_Master_Log_Pos: 154 
  10.                Relay_Log_File: relay_log-master_132.000002 
  11.                 Relay_Log_Pos: 367 
  12.         Relay_Master_Log_File: mysql-bin.000001 
  13.              Slave_IO_Running: Yes 
  14.             Slave_SQL_Running: Yes 
  15.               Replicate_Do_DB: 
  16.           Replicate_Ignore_DB: mysql 
  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: 154 
  25.               Relay_Log_Space: 579 
  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: 1323307 
  43.                   Master_UUID: 8785129a-3ace-11e7-9f13-fa163e48cafd 
  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: 
  55.             Executed_Gtid_Set: 
  56.                 Auto_Position: 1 
  57.          Replicate_Rewrite_DB: 
  58.                  Channel_Name: master_132 
  59.            Master_TLS_Version: 
  60. *************************** 2. row *************************** 
  61.                Slave_IO_State: Waiting for master to send event 
  62.                   Master_Host: 10.157.26.133 
  63.                   Master_User: sampson_133 
  64.                   Master_Port: 3307 
  65.                 Connect_Retry: 60 
  66.               Master_Log_File: mysql-bin.000001 
  67.           Read_Master_Log_Pos: 154 
  68.                Relay_Log_File: relay_log-master_133.000002 
  69.                 Relay_Log_Pos: 367 
  70.         Relay_Master_Log_File: mysql-bin.000001 
  71.              Slave_IO_Running: Yes 
  72.             Slave_SQL_Running: Yes 
  73.               Replicate_Do_DB: 
  74.           Replicate_Ignore_DB: mysql 
  75.            Replicate_Do_Table: 
  76.        Replicate_Ignore_Table: 
  77.       Replicate_Wild_Do_Table: 
  78.   Replicate_Wild_Ignore_Table: 
  79.                    Last_Errno: 0 
  80.                    Last_Error: 
  81.                  Skip_Counter: 0 
  82.           Exec_Master_Log_Pos: 154 
  83.               Relay_Log_Space: 579 
  84.               Until_Condition: None 
  85.                Until_Log_File: 
  86.                 Until_Log_Pos: 0 
  87.            Master_SSL_Allowed: No 
  88.            Master_SSL_CA_File: 
  89.            Master_SSL_CA_Path: 
  90.               Master_SSL_Cert: 
  91.             Master_SSL_Cipher: 
  92.                Master_SSL_Key: 
  93.         Seconds_Behind_Master: 0 
  94. Master_SSL_Verify_Server_Cert: No 
  95.                 Last_IO_Errno: 0 
  96.                 Last_IO_Error: 
  97.                Last_SQL_Errno: 0 
  98.                Last_SQL_Error: 
  99.   Replicate_Ignore_Server_Ids: 
  100.              Master_Server_Id: 1333307 
  101.                   Master_UUID: 3d5ae510-3acf-11e7-82a4-fa163e8e91ad 
  102.              Master_Info_File: mysql.slave_master_info 
  103.                     SQL_Delay: 0 
  104.           SQL_Remaining_Delay: NULL 
  105.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 
  106.            Master_Retry_Count: 86400 
  107.                   Master_Bind: 
  108.       Last_IO_Error_Timestamp: 
  109.      Last_SQL_Error_Timestamp: 
  110.                Master_SSL_Crl: 
  111.            Master_SSL_Crlpath: 
  112.            Retrieved_Gtid_Set: 
  113.             Executed_Gtid_Set: 
  114.                 Auto_Position: 1 
  115.          Replicate_Rewrite_DB: 
  116.                  Channel_Name: master_133 
  117.            Master_TLS_Version: 
  118. rows in set (0.01 sec) 
通过查看performance_schema相关的表查看同步状态：

  1. mysql> select * from performance_schema.replication_connection_status\G 
  2. *************************** 1. row *************************** 
  3.              CHANNEL_NAME: master_132 
  4.                GROUP_NAME: 
  5.               SOURCE_UUID: 8785129a-3ace-11e7-9f13-fa163e48cafd 
  6.                 THREAD_ID: 89 
  7.             SERVICE_STATE: ON 
  8. COUNT_RECEIVED_HEARTBEATS: 1 
  9.  LAST_HEARTBEAT_TIMESTAMP: 2017-05-17 16:59:45 
  10.  RECEIVED_TRANSACTION_SET: 
  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: master_133 
  16.                GROUP_NAME: 
  17.               SOURCE_UUID: 3d5ae510-3acf-11e7-82a4-fa163e8e91ad 
  18.                 THREAD_ID: 91 
  19.             SERVICE_STATE: ON 
  20. COUNT_RECEIVED_HEARTBEATS: 1 
  21.  LAST_HEARTBEAT_TIMESTAMP: 2017-05-17 16:59:45 
  22.  RECEIVED_TRANSACTION_SET: 
  23.         LAST_ERROR_NUMBER: 0 
  24.        LAST_ERROR_MESSAGE: 
  25.      LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 
  26. rows in set (0.01 sec) 

五、数据同步检查：
Master1建库建表并插入数据：

  1. mysql>CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sampson_132` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; 
  2. mysql>use sampson_132 
  3. mysql> create table t_132(id int primary key auto_increment,name varchar(20) not null); 
  4. mysql> insert into t_132(id,name)values(1,'132'),(2,'132'),(3,'132'); 
  5. mysql> select * from t_132; 
  6. +----+------+ 
  7. | id | name | 
  8. +----+------+ 
  9. |  1 | 132  | 
  10. |  2 | 132  | 
  11. |  3 | 132  | 
  12. +----+------+ 
  13. rows in set (0.00 sec) 

MYSQL应用Master2建库建表并插入数据：

 

  1. mysql> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sampson_133` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; 
  2. mysql> use sampson_133 
  3. mysql> create table t_133(id int primary key auto_increment,name varchar(20) not null); 
  4. mysql> insert into t_133(id,name)values(1,'133'),(2,'133'),(3,'133'); 
  5. mysql> select * from sampson_133.t_133; 
  6. +----+------+ 
  7. | id | name | 
  8. +----+------+ 
  9. |  1 | 133  | 
  10. |  2 | 133  | 
  11. |  3 | 133  | 
  12. +----+------+ 
  13. rows in set (0.00 sec) 

MYSQL应用Slave查看数据是否同步：

 

  1. mysql> show databases; 
  2. +--------------------+ 
  3. | Database           | 
  4. +--------------------+ 
  5. | information_schema | 
  6. | mysql              | 
  7. | performance_schema | 
  8. | sampson_132        | 
  9. | sampson_133        | 
  10. | sys                | 
  11. +--------------------+ 
  12. rows in set (0.00 sec) 
  13.   
  14. mysql> select * from sampson_132.t_132; 
  15. +----+------+ 
  16. | id | name | 
  17. +----+------+ 
  18. |  1 | 132  | 
  19. |  2 | 132  | 
  20. |  3 | 132  | 
  21. +----+------+ 
  22. rows in set (0.00 sec) 
  23.   
  24. mysql> select * from sampson_133.t_133; 
  25. +----+------+ 
  26. | id | name | 
  27. +----+------+ 
  28. |  1 | 133  | 
  29. |  2 | 133  | 
  30. |  3 | 133  | 
  31. +----+------+ 
  32. rows in set (0.00 sec) 

MYSQL应用再次查看从库状态：

 

  1. mysql> show slave status\G 
  2. *************************** 1. row *************************** 
  3.                Slave_IO_State: Waiting for master to send event 
  4.                   Master_Host: 10.157.26.132 
  5.                   Master_User: sampson_132 
  6.                   Master_Port: 3307 
  7.                 Connect_Retry: 60 
  8.               Master_Log_File: mysql-bin.000001 
  9.           Read_Master_Log_Pos: 930 
  10.                Relay_Log_File: relay_log-master_132.000002 
  11.                 Relay_Log_Pos: 1143 
  12.         Relay_Master_Log_File: mysql-bin.000001 
  13.              Slave_IO_Running: Yes 
  14.             Slave_SQL_Running: Yes 
  15.               Replicate_Do_DB: 
  16.           Replicate_Ignore_DB: mysql 
  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: 930 
  25.               Relay_Log_Space: 1355 
  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: 1323307 
  43.                   Master_UUID: 8785129a-3ace-11e7-9f13-fa163e48cafd 
  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: 8785129a-3ace-11e7-9f13-fa163e48cafd:1-3 
  55.             Executed_Gtid_Set: 3d5ae510-3acf-11e7-82a4-fa163e8e91ad:1-3, 
  56. 8785129a-3ace-11e7-9f13-fa163e48cafd:1-3 
  57.                 Auto_Position: 1 
  58.          Replicate_Rewrite_DB: 
  59.                  Channel_Name: master_132 
  60.            Master_TLS_Version: 
  61. *************************** 2. row *************************** 
  62.                Slave_IO_State: Waiting for master to send event 
  63.                   Master_Host: 10.157.26.133 
  64.                   Master_User: sampson_133 
  65.                   Master_Port: 3307 
  66.                 Connect_Retry: 60 
  67.               Master_Log_File: mysql-bin.000001 
  68.           Read_Master_Log_Pos: 930 
  69.                Relay_Log_File: relay_log-master_133.000002 
  70.                 Relay_Log_Pos: 1143 
  71.         Relay_Master_Log_File: mysql-bin.000001 
  72.              Slave_IO_Running: Yes 
  73.             Slave_SQL_Running: Yes 
  74.               Replicate_Do_DB: 
  75.           Replicate_Ignore_DB: mysql 
  76.            Replicate_Do_Table: 
  77.        Replicate_Ignore_Table: 
  78.       Replicate_Wild_Do_Table: 
  79.   Replicate_Wild_Ignore_Table: 
  80.                    Last_Errno: 0 
  81.                    Last_Error: 
  82.                  Skip_Counter: 0 
  83.           Exec_Master_Log_Pos: 930 
  84.               Relay_Log_Space: 1355 
  85.               Until_Condition: None 
  86.                Until_Log_File: 
  87.                 Until_Log_Pos: 0 
  88.            Master_SSL_Allowed: No 
  89.            Master_SSL_CA_File: 
  90.            Master_SSL_CA_Path: 
  91.               Master_SSL_Cert: 
  92.             Master_SSL_Cipher: 
  93.                Master_SSL_Key: 
  94.         Seconds_Behind_Master: 0 
  95. Master_SSL_Verify_Server_Cert: No 
  96.                 Last_IO_Errno: 0 
  97.                 Last_IO_Error: 
  98.                Last_SQL_Errno: 0 
  99.                Last_SQL_Error: 
  100.   Replicate_Ignore_Server_Ids: 
  101.              Master_Server_Id: 1333307 
  102.                   Master_UUID: 3d5ae510-3acf-11e7-82a4-fa163e8e91ad 
  103.              Master_Info_File: mysql.slave_master_info 
  104.                     SQL_Delay: 0 
  105.           SQL_Remaining_Delay: NULL 
  106.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 
  107.            Master_Retry_Count: 86400 
  108.                   Master_Bind: 
  109.       Last_IO_Error_Timestamp: 
  110.      Last_SQL_Error_Timestamp: 
  111.                Master_SSL_Crl: 
  112.            Master_SSL_Crlpath: 
  113.            Retrieved_Gtid_Set: 3d5ae510-3acf-11e7-82a4-fa163e8e91ad:1-3 
  114.             Executed_Gtid_Set: 3d5ae510-3acf-11e7-82a4-fa163e8e91ad:1-3, 
  115. 8785129a-3ace-11e7-9f13-fa163e48cafd:1-3 
  116.                 Auto_Position: 1 
  117.          Replicate_Rewrite_DB: 
  118.                  Channel_Name: master_133 
  119.            Master_TLS_Version: 
  120. rows in set (0.00 sec) 

MYSQL应用 

转载请注明本页网址：
http://www.vephp.com/jiaocheng/5578.html


    标签：  




同类教程排行


特辑教程