MYSQL并行复制怎么实现
本篇内容介绍了“MySQL并行复制怎么实现”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
网站建设公司,为您提供网站建设,网站制作,网页设计及定制网站建设服务,专注于成都企业网站建设,高端网页制作,对广告设计等多个行业拥有丰富的网站建设经验的网站建设公司。专业网站设计,网站优化推广哪家好,专业seo优化优化,H5建站,响应式网站。
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
点击(此处)折叠或打开
(root:localhost:Thu Jul 20 11:21:10 2017)[(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.26.7.129
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 190
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 355
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 190
Relay_Log_Space: 550
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1001
Master_UUID: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec:1-28,
35e38786-66bb-11e7-bcc3-b8ca3a6a61a4:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
四、数据库多线程复制测试、状态监控
node1:
点击(此处)折叠或打开
(root:localhost:Thu Jul 20 11:23:05 2017)[(none)]>drop database db01;
Query OK, 1 row affected (0.02 sec)
(root:localhost:Thu Jul 20 11:23:17 2017)[(none)]>use dbtest;
Database changed
(root:localhost:Thu Jul 20 11:23:22 2017)[dbtest]>create table t(id int);
Query OK, 0 rows affected (0.01 sec)
(root:localhost:Thu Jul 20 11:25:03 2017)[dbtest]>insert into t values(10);
Query OK, 1 row affected (0.01 sec)
node2:
点击(此处)折叠或打开
(root:localhost:Thu Jul 20 11:23:12 2017)[(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbtest |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
(root:localhost:Thu Jul 20 11:25:12 2017)[(none)]>use dbtest;
Database changed
(root:localhost:Thu Jul 20 11:25:15 2017)[dbtest]>select * from t;
+------+
| id |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
(root:localhost:Thu Jul 20 11:25:20 2017)[dbtest]>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.26.7.129
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 732
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 897
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 732
Relay_Log_Space: 1092
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1001
Master_UUID: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec:29-31
Executed_Gtid_Set: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec:1-31,
35e38786-66bb-11e7-bcc3-b8ca3a6a61a4:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
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并行复制怎么实现”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站,小编将为大家输出更多高质量的实用文章!
网页名称:MYSQL并行复制怎么实现
网页URL:http://pwwzsj.com/article/jjehpj.html