加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

MySQL5.7多源复制Mysql入门

发布时间：2020-12-12 00:49:44 所属栏目：MySql教程 来源：网络整理
导读：《MySQL5.7多源复制Mysql入门》要点： 本文介绍了MySQL5.7多源复制Mysql入门，希望对您有用。如果有疑问，可以联系我们。 导读：MySQL5.7开始支持多源复制,也就是多主一从的复制架构：使用多源复制的考虑：1、灾备作用：将各个库汇总在一起,就算是其他库都挂

《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_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?statusG?
  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_statusG?
  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,(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,'133'),'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?statusG?
  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应用?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同