MYSQL並行複製（parallel replication部署篇）

yepkeepmoving發表於2017-07-20
MySql並行Parallel
MySQL5.6開始執行基於庫級別的並行複製，MYSQL5.7支援基於表和行級別的並行複製（Enhanced Multi-Threaded Slave），這裡主要是配置MYSQL5.7的基於組提交的並行複製。
一、環境描述
名稱    版本    備註
作業系統    RHEL6.5_X86_64    
資料庫    5.7.18-15   Percona二進位制版本
複製節點  10.26.7.129 node1 主節點
    10.26.7.142 node2 從節點
二、主要相關配置引數
node1節點：
#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節點
#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：預設值，基於庫的並行複製方式
  LOGICAL_CLOCK：基於組提交的並行複製方式
三、資料庫啟動並配置主從服務
資料庫啟動略
node2從節點執行下面命令：
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)
四、資料庫多執行緒複製測試、狀態監控
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)

多線複製狀態監控：
select * from  replication_applier_status_by_coordinator     ;
select * from   replication_applier_status_by_worker ;




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27067062/viewspace-2142339/，如需轉載，請註明出處，否則將追究法律責任。

相關文章