MYSQL Gtid-based replication

1. Overview

Starting with MYSQL5.6, MySQL started supporting Gtid replication.

Disadvantages of Log point based replication:

Incremental synchronization from the offset of that binary log, which results in inconsistent data if the specified error causes omission or duplication.

Based on Gtid replication:

1. The slave server tells the Gtid value of the transaction that the primary server has performed.

2. The main library will tell from which Gtid transactions have not been executed.

The same transaction is executed one time from the specified library.

What is Gtid

Gtid is the global transaction ID, which guarantees that every transaction committed at the Lord can generate a unique ID in the replication cluster.

gtid=source_id:transaction_id

SOURCE_ID: Is the server UUID of the main library, in the auto.cnf file of the data catalog.

TRANSACTION_ID: A sequence starting from 1.

2. Steps based on Gtid replication

1. Establish a replication account on the primary DB server.

And the log point is the same.

2. Configure the primary database server

Bin_log =mysql-bin

server_id=1001

Gtid_mode=on

Enforce-gtid-consiste: Enforce transactional consistency and ensure transaction security

Cannot be used:

1.create table.. Select

2. Create a temporary table using create temporary table in the transaction, update the transaction table and the non-transactional table with the association.

Log-slave-updates=on

Log data sent from the primary server is logged from the server.

This parameter must be used with Gtid 5.6, and 5.7 can not be used.

3. Configure the slave server.

server_id=1002

Relay_log=relay_log

Gtid_mode=on

Enforce-gtid-consistency

Recommended configuration

Read_only=on

Guaranteed data security from the server

Master_info_reposistory=table

Relay_log_info_reposistory=table

The information from the server connection to the master server and the relay log are stored in our master_info, and Relay_log.

4. Initialize the data from the server.

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

Xtarbackup–slave-info

Records the last transaction Gtid value at the time of the backup.

Exporting 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;

At the start of the slave times wrong.

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

Workaround:

reset slave; reset slave

Start slave again.

Start slave;

Test synchronization:

1. Create a table in the main library and insert the record.

2. Verify that the query is correct from the library and that the configuration is correct.

MYSQL Gtid-based replication