MYSQL并行复制怎么实现

本篇内容介绍了“MySQL并行复制怎么实现”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

网站建设公司,为您提供网站建设,网站制作,网页设计及定制网站建设服务,专注于成都企业网站建设,高端网页制作,对广告设计等多个行业拥有丰富的网站建设经验的网站建设公司。专业网站设计,网站优化推广哪家好,专业seo优化优化,H5建站,响应式网站。

MySQL5.6开始执行基于库级别的并行复制，MYSQL5.7支持基于表和行级别的并行复制（Enhanced Multi-Threaded Slave），这里主要是配置MYSQL5.7的基于组提交的并行复制。
一、环境描述

二、主要相关配置参数
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 ;

“MYSQL并行复制怎么实现”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！