Mysql gtid-based replication and MYSQLGTID Replication

Mysql gtid-based replication and MYSQLGTID Replication

1. Overview

Mysql supports GTID replication starting from MYSQL5.6.

Disadvantages of log point replication:

Incremental synchronization is performed from the offset of the binary log. If an error is specified, omissions or duplicates may occur, resulting in data inconsistency.

GTID-based replication:

1. The slave server will tell the master server the GTID value of the transaction that has been executed.

2. The master database will tell from which GTID transactions are not executed.

The same transaction is executed once in the specified slave database.

What is GTID?

GTID is the global transaction ID. It ensures that a unique ID can be generated for each transaction committed on the master node in the replication cluster.

GTID = source_id: transaction_id

Source_id: the server UUID of the master database, which is in the auto. cnf file of the data directory.

Transaction_id: A Sequence starting from 1.

2. GTID-based replication steps

1. Create a replication account on the master DB server.

It is the same as a log point.

2. Configure the master database server

Bin_log = mysql-bin

Server_id = 1001

Gtid_mode = on

Enforce-gtid-consiste: enforces transaction consistency to ensure transaction security.

Unavailable:

1. create table .. Select

2. Use create temporary table to create a temporary table in the transaction, and use Association to update the transaction table and non-transaction table.

Log-slave-updates = on

Record the log data transmitted from the master server on the slave server.

This parameter must be used when GTID 5.6 is used. This parameter is optional when GTID 5.7 is used.

3. Configure the slave server.

Server_id = 1002

Relay_log = relay_log

Gtid_mode = on

Enforce-gtid-consistency

Recommended Configuration

Read_only = on

Data security of slave servers

Master_info_reposistory = TABLE

Relay_log_info_reposistory = TABLE

The information and relay logs of the master server connected to the slave server are stored in master_info and relay_log.

4. initialize slave server data.

Mysqldump -- master-data = 2-single-transaction

Xtarbackup-slave-info

Record the last transaction GTID value during Backup.

Export data

Mysqldump -- single-transaction -- master-data = 2 -- triggers-routines -- all-databases-uroot-p-P3308> all2. SQL

Import Data

Mysql-uroot-p-P3309 <all2. SQL

5. Start GTID-based Replication

Change master to master-host = 'master service ip ',

Master_user = 'repl ',

Master_password = 'Password ',

Master_auto_position = 1

Change master to MASTER_HOST = '192. 168.1.106 ',
MASTER_PORT = 3308,
MASTER_USER = 'repl ',
MASTER_PASSWORD = 'repl ',
Master_auto_position = 1;

 

Start slave;

Show slave status \ G;

An error is reported when slave is started.

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

Solution:

Reset slave; reset slave

Start slave again

Start slave;

Test synchronization:

1. Create a table in the master database and insert records.

2. Check whether the slave database is correct and whether the configuration is correct.