Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to achieve MYSQL parallel replication". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MySQL5.6 begins to perform parallel replication based on library level, and MYSQL5.7 supports parallel replication based on table and row level (Enhanced Multi-Threaded Slave). Here, the main thing is to configure group commit-based parallel replication of MYSQL5.7.

I. description of the environment

Name version remarks operating system RHEL6.5_X86_64 database 5.7.18-15Percona binary version replication node 10.26.7.129node1 master node

10.26.7.142node2 slave node

Second, the main related configuration parameters

Node1 node:

# 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 node

# 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: default value, based on parallel replication of the library

LOGICAL_CLOCK: parallel replication based on group submission

The database starts and configures the master-slave service

Database startup strategy

Node2 executes the following command from the node:

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

Click (here) to collapse or open

(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)

Database multithreaded replication testing and status monitoring

Node1:

Click (here) to collapse or open

(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.01sec)

Node2:

Click (here) to collapse or open

(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)

Multiline replication status monitoring:

Select * from replication_applier_status_by_coordinator

Select * from replication_applier_status_by_worker

This is the end of "how to implement MYSQL parallel replication". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.