MYSQL并行复制怎么实现
发布时间：2022-01-19 13:35:59 所属栏目：MySql教程 来源：互联网
导读：本篇内容介绍了MYSQL并行复制怎么实现的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ MySQL5.6开始执行基于库级别的并行复制，MYSQL5.7支持基于表和
|
本篇内容介绍了“MYSQL并行复制怎么实现”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 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并行复制怎么实现”的内容就介绍到这里了，感谢大家的阅读。 （编辑：91站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
