欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

當前位置： 維易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.snjht.com/jiaocheng/5578.html


    標簽：