Using Gtid to make database asynchronous replication for Galera clusters

First, why do you want to do galera cluster asynchronous replication

The Galera cluster solves the problem of high database availability, but there are limitations, such as time-consuming transactions that can lead to a dramatic drop in cluster performance and even congestion. Unfortunately, similar reports and other business needs to do a large number of data query operations, in order not to affect the Galera cluster efficiency, the need to do asynchronous data replication, resulting in a library to adapt to the time-consuming data operation requirements.

Because of the particularity of Galera cluster, we can't use the general master-slave replication to realize the requirement of asynchronous data replication. Each mariadb in the cluster will be recorded separately Binlog, so that the general master-slave configuration can only get the change event of single data, if there are data changes on other mariadb in the cluster, it cannot be synchronized to the slave library.

Master-slave replication based on Gtid solves this problem, with each transaction having a unique ID, and synchronizing against the transaction ID is not restricted by the database because all the database nodes in the cluster use the unique Gtid.

Second, installation Xtrabackup

1, yum installation, download Percona Source:

Yum Install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm

2. Start the installation

Yum Install percona-xtrabackup-24

Third, backup data

1, the full amount of backup data on the main library:

Innobackupex--user=dbuser--passwor= ' Dbpassword '/dir_for_backup

Note The password parameter, if there is a key character in the password, you need to use single quotation marks to bring the password, otherwise you cannot log on to MySQL, unable to back up data.

2. After a full backup on the main library, the transaction must be applied to the backup file to make the backup file fully available

Innobackupex--user=dbuser--password= ' Dbpassword '--apply-log/dir_for_backup/2018-07-12_10-39-56/

3. Transfer the backed-up data files to the library from the main library.

Scp-r/dir_for_backup/2018-07-12_10-39-56/[Email protected]_server_ip:/slave_server_data_dir

Iv. starting from the library

1. Modify the data file name from the library, owner

mv/slave_server_data_dir/2018-07-12_10-39-56//slave_server_data_dir/mysqldata

Chown-r mysql:mysql/slave_server_data_dir/mysqldata/

2. Start the database from the library

Configure the my.conf file, specify the DataDir directory to/slave_server_data_dir/mysqldata, and then start the database.

V. Master and slave configuration

1, all nodes in the Galera cluster need to do the following configuration:

[Mysqld]

Master-info-repository=table

Relay-log-info-repository=table

Log_slave_updates = 1

Sync-master-info=1

slave-parallel-threads=2

Binlog-checksum=crc32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

Log-bin=mysql-bin

Binlog_format=row

Log_slave_updates = 1

server-id=4567

Restart the server after the configuration is complete.

Enter the main library (any node in the Galera cluster) to authorize the master-slave replication User:

GRANT REPLICATION slave,replication CLIENT on * * to ' slaveuser ' @ ' 10.30.254.9 ' identified by ' slaveuser ';

2, from the library configuration

[Mysqld]

Master-info-repository=table

Relay-log-info-repository=table

Log_slave_updates = 1

Sync-master-info=1

slave-parallel-threads=2

Binlog-checksum=crc32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

#这是与主库配置不同的地方

Relay_log = Relay-bin

server_id=7890

Log_bin=binlog

Log_slave_updates=1

Binlog_format=row

After the configuration is complete, restart the database from the library.

3. Set Gtid copy point information from library

L View gtid information in Xtrabackup backup data

Cat/slave_server_data_dir/mysqldata Xtrabackup_info

UUID = ffa57fe6-8676-11e8-8b3a-00163e08d213

Name =

Tool_name = Innobackupex

Tool_command =--user=root--password= .../mnt

Tool_version = 2.4.11

Ibbackup_version = 2.4.11

Server_version = 10.2.6-mariadb-log

Start_time = 2018-07-13 16:26:09

End_time = 2018-07-13 16:30:28

Lock_time = 0

Binlog_pos = filename ' mysql-bin.000019 ', Position ' 82930255 ', GTID of the last change ' 0-4567-3446 '

INNODB_FROM_LSN = 0

INNODB_TO_LSN = 42353602070

partial = N

incremental = N

format = File

Compact = N

compressed = N

encrypted = N

L Configure Gtid Master-slave replication

Stop master-slave replication: Stop SLAVE;

Reset master/Slave configuration: Reset SLAVE all;

Set Gtid point: Set GLOBAL gtid_slave_pos= ' 0-4567-3446 ';

Configure Master-Slave configuration:

Change MASTER to master_host= ' 10.30.253.222 ', master_port=3306, master_user= ' Slaveuser ', master_password= ' Slaveuser ' , Master_use_gtid=slave_pos;

View master and slave configuration status: Show SLAVE status;

Six, Gtid synchronization error, how to restore Master-slave replication

Exception information:

Last_sql_error:error ' Duplicate entry ' 4 ' for key ' PRIMARY "on query. Default database: ' Test '. Query: ' INSERT INTO T VALUES (NULL, ' Salazar ') '

Retrieved_gtid_set:7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5

Executed_gtid_set:7d72f9b4-8577-11e2-a3d7-080027635ef5:1-4

Because it is a gtid copy, the set global sql_slave_skip_counter=n is not useful here, but you can solve the problem by inserting an empty transaction:

STOP SLAVE;

SET gtid_next= "7d72f9b4-8577-11e2-a3d7-080027635ef5:5";

BEGIN; COMMIT;

SET gtid_next= "AUTOMATIC";

START SLAVE;

[...]

Retrieved_gtid_set:7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5

Executed_gtid_set:7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5

Using Gtid to make database asynchronous replication for Galera clusters