真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

MYSQL并行復(fù)制怎么實現(xiàn)

本篇內(nèi)容介紹了“MySQL并行復(fù)制怎么實現(xiàn)”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

網(wǎng)站建設(shè)公司,為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁設(shè)計及定制網(wǎng)站建設(shè)服務(wù),專注于成都企業(yè)網(wǎng)站建設(shè),高端網(wǎng)頁制作,對廣告設(shè)計等多個行業(yè)擁有豐富的網(wǎng)站建設(shè)經(jīng)驗的網(wǎng)站建設(shè)公司。專業(yè)網(wǎng)站設(shè)計,網(wǎng)站優(yōu)化推廣哪家好,專業(yè)seo優(yōu)化優(yōu)化,H5建站,響應(yīng)式網(wǎng)站。

MySQL5.6開始執(zhí)行基于庫級別的并行復(fù)制，MYSQL5.7支持基于表和行級別的并行復(fù)制（Enhanced Multi-Threaded Slave），這里主要是配置MYSQL5.7的基于組提交的并行復(fù)制。
一、環(huán)境描述

名稱    版本    備注
操作系統(tǒng)    RHEL6.5_X86_64    
數(shù)據(jù)庫    5.7.18-15   Percona二進制版本
復(fù)制節(jié)點  10.26.7.129 node1 主節(jié)點
    10.26.7.142 node2 從節(jié)點

二、主要相關(guān)配置參數(shù)
node1節(jié)點：
#replicate
server-id=1001
skip-slave-start = false
read-only = false
log-slave-updates = 1
expire_logs_days = 2
max_binlog_size = 1G
max_binlog_cache_size = 2G
log-bin = /home/mysql/mysql-bin
log-bin-index = /home/mysql/bin-index
sync_binlog = 1
binlog_format = row
log-slow-slave-statements = 1
max-relay-log-size = 1G
relay-log = /home/mysql/mysql-relay
relay-log-index = /home/mysql/relay-index
relay_log_recovery=ON
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE

node2節(jié)點
#replicate
server-id=1002
skip-slave-start = false
read-only = false
log-slave-updates = 1
expire_logs_days = 2
max_binlog_size = 1G
max_binlog_cache_size = 2G
log-bin = /home/mysql/mysql-bin
log-bin-index = /home/mysql/bin-index
sync_binlog = 1
binlog_format = row
log-slow-slave-statements = 1
max-relay-log-size = 1G
relay-log = /home/mysql/mysql-relay
relay-log-index = /home/mysql/relay-index
relay_log_recovery=ON
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE

#parallel slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8

##slave-parallel-type
  DATABASE：默認值，基于庫的并行復(fù)制方式
  LOGICAL_CLOCK：基于組提交的并行復(fù)制方式
三、數(shù)據(jù)庫啟動并配置主從服務(wù)
數(shù)據(jù)庫啟動略
node2從節(jié)點執(zhí)行下面命令：
change master to master_host='10.26.7.129',master_user='rpl_user',master_password='rpl_pass',master_port=3306,master_auto_position=1;
start slave;
show slave status \G

點擊(此處)折疊或打開

  1. (root:localhost:Thu Jul 20 11:21:10 2017)[(none)]>show slave status \G

  2. *************************** 1. row ***************************

  3.                Slave_IO_State: Waiting for master to send event

  4.                   Master_Host: 10.26.7.129

  5.                   Master_User: rpl_user

  6.                   Master_Port: 3306

  7.                 Connect_Retry: 60

  8.               Master_Log_File: mysql-bin.000006

  9.           Read_Master_Log_Pos: 190

  10.                Relay_Log_File: mysql-relay.000002

  11.                 Relay_Log_Pos: 355

  12.         Relay_Master_Log_File: mysql-bin.000006

  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: 190

  25.               Relay_Log_Space: 550

  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: 1001

  43.                   Master_UUID: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec

  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: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec:1-28,

  56. 35e38786-66bb-11e7-bcc3-b8ca3a6a61a4:1

  57.                 Auto_Position: 1

  58.          Replicate_Rewrite_DB:

  59.                  Channel_Name:

  60.            Master_TLS_Version:

  61. 1 row in set (0.00 sec)

四、數(shù)據(jù)庫多線程復(fù)制測試、狀態(tài)監(jiān)控
node1:

點擊(此處)折疊或打開

  1. (root:localhost:Thu Jul 20 11:23:05 2017)[(none)]>drop database db01;

  2. Query OK, 1 row affected (0.02 sec)

  4. (root:localhost:Thu Jul 20 11:23:17 2017)[(none)]>use dbtest;

  5. Database changed

  6. (root:localhost:Thu Jul 20 11:23:22 2017)[dbtest]>create table t(id int);

  7. Query OK, 0 rows affected (0.01 sec)

  9. (root:localhost:Thu Jul 20 11:25:03 2017)[dbtest]>insert into t values(10);

  10. Query OK, 1 row affected (0.01 sec)

node2:

點擊(此處)折疊或打開

  1. (root:localhost:Thu Jul 20 11:23:12 2017)[(none)]>show databases;

  2. +--------------------+

  3. | Database |

  4. +--------------------+

  5. | information_schema |

  6. | dbtest |

  7. | mysql |

  8. | performance_schema |

  9. | sys |

  10. +--------------------+

  11. 5 rows in set (0.00 sec)

  13. (root:localhost:Thu Jul 20 11:25:12 2017)[(none)]>use dbtest;

  14. Database changed

  15. (root:localhost:Thu Jul 20 11:25:15 2017)[dbtest]>select * from t;

  16. +------+

  17. | id |

  18. +------+

  19. | 10 |

  20. +------+

  21. 1 row in set (0.00 sec)

  22. (root:localhost:Thu Jul 20 11:25:20 2017)[dbtest]>show slave status \G

  23. *************************** 1. row ***************************

  24.                Slave_IO_State: Waiting for master to send event

  25.                   Master_Host: 10.26.7.129

  26.                   Master_User: rpl_user

  27.                   Master_Port: 3306

  28.                 Connect_Retry: 60

  29.               Master_Log_File: mysql-bin.000006

  30.           Read_Master_Log_Pos: 732

  31.                Relay_Log_File: mysql-relay.000002

  32.                 Relay_Log_Pos: 897

  33.         Relay_Master_Log_File: mysql-bin.000006

  34.              Slave_IO_Running: Yes

  35.             Slave_SQL_Running: Yes

  36.               Replicate_Do_DB:

  37.           Replicate_Ignore_DB:

  38.            Replicate_Do_Table:

  39.        Replicate_Ignore_Table:

  40.       Replicate_Wild_Do_Table:

  41.   Replicate_Wild_Ignore_Table:

  42.                    Last_Errno: 0

  43.                    Last_Error:

  44.                  Skip_Counter: 0

  45.           Exec_Master_Log_Pos: 732

  46.               Relay_Log_Space: 1092

  47.               Until_Condition: None

  48.                Until_Log_File:

  49.                 Until_Log_Pos: 0

  50.            Master_SSL_Allowed: No

  51.            Master_SSL_CA_File:

  52.            Master_SSL_CA_Path:

  53.               Master_SSL_Cert:

  54.             Master_SSL_Cipher:

  55.                Master_SSL_Key:

  56.         Seconds_Behind_Master: 0

  57. Master_SSL_Verify_Server_Cert: No

  58.                 Last_IO_Errno: 0

  59.                 Last_IO_Error:

  60.                Last_SQL_Errno: 0

  61.                Last_SQL_Error:

  62.   Replicate_Ignore_Server_Ids:

  63.              Master_Server_Id: 1001

  64.                   Master_UUID: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec

  65.              Master_Info_File: mysql.slave_master_info

  66.                     SQL_Delay: 0

  67.           SQL_Remaining_Delay: NULL

  68.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

  69.            Master_Retry_Count: 86400

  70.                   Master_Bind:

  71.       Last_IO_Error_Timestamp:

  72.      Last_SQL_Error_Timestamp:

  73.                Master_SSL_Crl:

  74.            Master_SSL_Crlpath:

  75.            Retrieved_Gtid_Set: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec:29-31

  76.             Executed_Gtid_Set: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec:1-31,

  77. 35e38786-66bb-11e7-bcc3-b8ca3a6a61a4:1

  78.                 Auto_Position: 1

  79.          Replicate_Rewrite_DB:

  80.                  Channel_Name:

  81.            Master_TLS_Version:

  82. 1 row in set (0.00 sec)

(root:localhost:Thu Jul 20 11:27:34 2017)[information_schema]>use performance_schema;
Database changed
(root:localhost:Thu Jul 20 11:27:38 2017)[performance_schema]>show tables like '%replica%';    
+-------------------------------------------+
| Tables_in_performance_schema (%replica%)  |
+-------------------------------------------+
| replication_applier_configuration         |
| replication_applier_status                |
| replication_applier_status_by_coordinator |
| replication_applier_status_by_worker      |
| replication_connection_configuration      |
| replication_connection_status             |
| replication_group_member_stats            |
| replication_group_members                 |
+-------------------------------------------+
8 rows in set (0.00 sec)

多線復(fù)制狀態(tài)監(jiān)控：
select * from  replication_applier_status_by_coordinator     ;
select * from   replication_applier_status_by_worker ;

“MYSQL并行復(fù)制怎么實現(xiàn)”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！


分享標(biāo)題：MYSQL并行復(fù)制怎么實現(xiàn)
文章位置：http://weahome.cn/article/jjehpj.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220（工作日）

18980820575（7×24）

提交需求

返回頂部