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

This article mainly introduces "how to deploy MySQL Group Replication". In daily operation, I believe many people have doubts about how to deploy MySQL Group Replication. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to deploy MySQL Group Replication". Next, please follow the editor to study!

I. Environmental preparation

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

10.26.7.142node2

10.26.7.166node3

II. Detailed deployment steps of MGR

1. MYSQL5.7 installation

This learning experiment uses the binary version of Percona-Server-5.7.18-15murLinux.x8664.ssl101, and the specific installation process is brief.

2. Node 1my.cnf parameter configuration (primary write node)

# replicate

Server-id=1001

Skip-slave-start = false

Read-only = false

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

Binlog_format = row

Log-slave-updates = 1

Sync_binlog = 1

Log-slow-slave-statements = 1

Max-relay-log-size = 1G

Relay-log = / home/mysql/mysql-relay

Relay-log-index = / home/mysql/relay-index

Gtid_mode=ON

Enforce_gtid_consistency=ON

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

Binlog_checksum=NONE

# group replication

Transaction_write_set_extraction = XXHASH64

Loose-group_replication_group_name = "2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec" # be sure to configure it in uuid form

Loose-group_replication_start_on_boot = off

Loose-group_replication_local_address = "10.26.7.129 MYSQL 24001" # different nodes configure different nodes' own IP addresses and ports to distinguish port 3306 of the node itself

Loose-group_replication_group_seeds = "10.26.7.129Vor24001Magol 10.26.7.142VOL24001JEL 10.26.7.16614001"

Loose-group_replication_bootstrap_group = off

3. Create a replication account (primary write node)

Set sql_log_bin=0

Create user rpl_user@'%'

Grant replication slave on *. * to rpl_user@'%' identified by 'rpl_pass'

Flush privileges

Set sql_log_bin=1

Change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery'

4. Install the group replication plug-in and start the group replication (primary write node)

Install the plug-in

Install plugin group_replication soname 'group_replication.so'

Check that the plug-in is installed correctly

Show plugins

+-+

| | Name | Status | Type | Library | License | |

+-+

| | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | |

+-+

Start group replication

Set global group_replication_bootstrap_group=ON

Start group_replication

Set global group_replication_bootstrap_group=OFF

Check group replication members and status

Select * from performance_schema.replication_group_members

+-+

| | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | |

+-+

| | group_replication_applier | 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec | node1 | 3306 | ONLINE | |

5. Add group replication member instances node2 and node3

* before adding nodes, be sure to do DNS resolution. If no DNS resolution server is configured, you need to configure hosts resolution / etc/hosts*** on each node.

10.26.7.166 node3

10.26.7.142 node2

10.26.7.129 node1

* Node my.cnf parameter files server-id and loose-group_replication_local_address = "node2:24001 needs to be changed respectively" *

Node2

Set sql_log_bin=0

Create user rpl_user@'%'

Grant replication slave on *. * to rpl_user@'%' identified by 'rpl_pass'

Flush privileges

Set sql_log_bin=1

Change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery'

Install plugin group_replication soname 'group_replication.so'

Show plugins

Set global group_replication_allow_local_disjoint_gtids_join=ON

Start group_replication

Node3 also executes the above command

Then check the group replication information

Select * from performance_schema.replication_group_members

+-+

| | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | |

+-+

| | group_replication_applier | 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec | node1 | 3306 | ONLINE | |

| | group_replication_applier | 35e38786-66bb-11e7-bcc3-b8ca3a6a61a4 | node2 | 3306 | ONLINE | |

| | group_replication_applier | 3bbedb1e-66bb-11e7-8fc0-b8ca3a6a7c48 | node3 | 3306 | ONLINE | |

+-+

Test whether the group replication is normal:

(root:localhost:Sat Jul 15 13:26:33 2017) [(none)] > create database dbtest

Query OK, 1 row affected (0.01sec)

(root:localhost:Sat Jul 15 13:26:40 2017) [(none)] > use dbtest

Database changed

(root:localhost:Sat Jul 15 13:26:45 2017) [dbtest] > create table T1 (id int primary key)

Query OK, 0 rows affected (0.01 sec)

(root:localhost:Sat Jul 15 13:26:54 2017) [dbtest] > insert into T1 values (1)

Query OK, 1 row affected (0.00 sec)

Node2 and node3 execute queries

(root:localhost:Sat Jul 15 12:57:32 2017) [db01] > use dbtest

Database changed

(root:localhost:Sat Jul 15 13:27:26 2017) [dbtest] > select * from T1

+-- +

| | id |

+-- +

| | 1 |

+-- +

1 row in set (0.00 sec)

Third, error questions and summaries:

1. Error case 01

Error message: 2017-07-15T01:36:06.929941Z 4 [ERROR] Plugin group_replication reported: 'The group name' group-replication-test' is not a valid UUID'

Cause of error: the loose-group_replication_group_name parameter is not specified in UUID format and is considered invalid to set this parameter

Solution: change the loose-group_replication_group_name parameter value to, loose-group_replication_group_name = "2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec"

2. Error case 02

Error message:

2017-07-15T01:29:27.271909Z 0 [Warning] unknown variable 'loose-group_replication_group_name=group-replication-test'

2017-07-15T01:29:27.271926Z 0 [Warning] unknown variable 'loose-group_replication_start_on_boot=off'

2017-07-15T01:29:27.271930Z 0 [Warning] unknown variable 'loose-group_replication_local_address=10.26.7.129:3306'

2017-07-15T01:29:27.271935Z 0 [Warning] unknown variable 'loose-group_replication_group_seeds=10.26.7.129:3306,10.26.7.142:3306,10.26.7.166:3306'

2017-07-15T01:29:27.271939Z 0 [Warning] unknown variable 'loose-group_replication_bootstrap_group=off'

Cause of error: because these parameters are set first and the group_replication plug-in is not installed, the database instance cannot recognize these parameters

Solution: install the group replication plug-in, install plugin group_replication soname 'group_replication.so'; (uninstall plugin group_replication uninstall, show plugins view)

3. Error case 03

Error message:

2017-07-15T01:54:54.447829Z 0 [Note] Plugin group_replication reported: 'Unable to bind to 0.0.0.0 Plugin group_replication reported (socket=60, errno=98)!'

2017-07-15T01:54:54.447948Z 0 [ERROR] Plugin group_replication reported: 'Unable to announce tcp port 3306. Port already in use?'

2017-07-15T01:54:54.448101Z 0 [ERROR] Plugin group_replication reported:'[GCS] Error joining the group while waiting for the network layer to become ready.'

Error reason: configured group replication listening port conflicts with MYSQL instance port

Solution: adjust the following parameters

Loose-group_replication_local_address = "10.26.7.129 MYSQL 24001" # different nodes configure different nodes' own IP addresses and ports to distinguish port 3306 of the node itself

Loose-group_replication_group_seeds = "10.26.7.129Vor24001Magol 10.26.7.142VOL24001JEL 10.26.7.16614001"

4. Error case 04

Error message:

2017-07-15T04:20:01.249529Z 21 [ERROR] Slave I group_replication_recovery': error connecting to master O for channel 'group_replication_recovery': error connecting to master' rpl_user@node2:3306'-retry-time: 60 retries: 1, Error_code: 2005

Cause of error: DNS parsing or hosts parsing is not configured and nodes cannot connect to other databases

Solution: configure hosts parsing, adding the following to each node / etc/hosts

10.26.7.166 node3

10.26.7.142 node2

10.26.7.129 node1

5. Error case 05

Error message

2017-07-15T03:42:45.395407Z 288 [ERROR] Slave SQL for channel 'group_replication_recovery': Error' Can't create database 'db01'; database exists' on query. Default database: 'db01'. Query: 'create database db01', Error_code: 1007

2017-07-15T03:42:45.395472Z 288 [Warning] Slave: Can't create database 'db01'; database exists Error_code: 1007

2017-07-15T03:42:45.395503Z [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000005' position 434

Cause of error: this error is due to the node quitting the mgr group for various reasons, followed by the addition of the mgr group, but the previous data still exists.

Solution: delete the database that exists in the replication node to join the group, but the other nodes are not primary write nodes. You need to adjust the parameter set global super_read_only=0; and then execute drop database db01; before rejoining the group.

Set global group_replication_allow_local_disjoint_gtids_join=ON

Start group_replication

6. Error case 06

Error message:

2017-07-15T03:44:09.982428Z 18 [ERROR] Slave SQL for channel 'group_replication_recovery': Error' Can't create database 'db01'; database exists' on query. Default database: 'db01'. Query: 'create database db01', Error_code: 1007

2017-07-15T03:44:09.982493Z 18 [Warning] Slave: Can't create database 'db01'; database exists Error_code: 1007

2017-07-15T03:44:09.982522Z 18 [ERROR] Error running query, slave SQL thread aborted Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000005' position 434

Cause of error: same as above error case 05

Solution: same as error case 05

7. Error case 07

Error message:

2017-07-15T03:49:10.370846Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec:1-4 Group transactions 35e38786-66bb-11e7-bcc3-b8ca3a6a61a4:1 > Group transactions: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec:1-16'

Cause of error: same as error case 05 above, redundant transactions were performed from the library

Solution: same as error case 05 above, just rejoin the node directly.

Set global group_replication_allow_local_disjoint_gtids_join=ON

Start group_replication

8. Error case 08

Error message

ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.

Cause of error: because the primary node creates table T1, but no primary key is specified (at this time, the table structure can be copied to each node, and an error will be reported once the data DML operation is inserted)

Solution: add a primary key to the table, and then do the DML operation (MGR requires each table to have a primary key)

Alter table T1 add primary key (id)

Insert into T1 values (1), (2)

9. Error case 09

Error message:

Mysqldump-R-E-- triggers-- single-transaction-- master-data=2-B db01 > db01.sql

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass-- set-gtid-purged=OFF. To make a complete dump, pass-all-databases-triggers-routines-events.

Mysqldump: Couldn't execute 'SAVEPOINT sp': The MySQL server is running with the-- TransactionMushwrit set extruded actionmakers off option so it cannot execute this statement (1290)

Error reason: mgr does not support transaction consistent backup of mysqldump because it does not support savepoint

Solution: backup via xtrabackup or without-- single-transaction

10. Error case 10

Error message:

Create table T2 as select * from T1

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE. SELECT.

Error reason: configure MGR, enable GTID, all operations that GTID does not support, MGR does not support either

Solution: use create table T2 like T1; insert into T2 select * from t; separate two transactions

At this point, the study on "how to deploy MySQL Group Replication" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.