MYSQL并行复制怎么实现

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

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)
   

3. 
   

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)
   

8. 
   

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)
    

12. 
    

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并行复制怎么实现"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！