Brother nag

In the last article, I published the process of setting up the MySQL master-slave replication cluster, but it seems that my friends are not interested in this article. However, out of my love for technology and responsibility for my friends, I still want to write another implementation method of master-slave replication: GTID. These skills are really important and I hope you can learn them.

GTID profile

A new master-slave replication mode has been added from MySQL 5.6.5: GTID, which stands for Global Transaction Identifier. GTID ensures that each transaction committed by the master library has a unique transaction ID in the cluster. The consistency between master and slave databases and fault tolerance of recovery data are enhanced. In the case of a master/slave switchover when the master library is down. In GTID mode, other slave libraries can automatically find the location of the new master database replication, and GTID can ignore the transactions that have been executed, reducing the probability of data error.

GTID composition

GTID is the number of a committed transaction and is globally unique. GTID consists of UUID and TID. The UUID is the unique identifier of the MySQL instance, and the TID represents the number of committed transactions on the instance, which increases with the number of committed transactions.

For example: 3e11FA47-71CA-11E1-9e33-c80AA9429562:23, the colon is preceded by UUID and followed by TID.

How GTID works

  • The GTID is generated when the master commits a transaction and is recorded in the binlog

  • The master binlog file is read from the salve I/O thread and stored in the slave relay log. Slave sets the master’s GTID value to gtid_next, the next GTID value to be read.

  • The slave reads the GtiD_next and compares the GTID to its own binlog

  • If this record exists, the GTID transaction has already been executed and can be ignored

  • If there is no record, the SLAVE performs the GTID transaction and records it in its own binlog. Before reading and executing transactions, the GTID is checked by other sessions to ensure that it is not executed repeatedly.

  • During parsing, it determines whether there is a primary key, uses a secondary index if not, and uses a full scan if not.

GTID implementation

The environment

Here we have two machines, one master and one slave.

  • Master: 192.168.216.111
  • From (salve) : 192.168.216.222

Master Configures the master library

In [mysqld], you need to restart the configuration

#GTID:
server_id=111  The server ID is set to the last three digits of the machine IP address
gtid_mode=on  # Enable gTID mode

# Enforce gTID consistency. This function is not supported for certain CREATE tables
enforce_gtid_consistency=on  #binlog Log_bin = Directory for storing binary log fileslog-slave-updates=true  # Strongly suggest that other formats may cause data inconsistencies binlog_format=row  #relay log skip_slave_start=1 Copy the code

Slave Configures the slave library

In [mysqld], you need to restart the configuration

#GTID:
gtid_mode=on # Enable gTID mode
enforce_gtid_consistency=on

The server ID is set to the last three digits of the machine IP address
server_id=222  #binlog log-bin=slave-binlog log-slave-updates=true  # Strongly suggest that other formats may cause data inconsistencies binlog_format=row  #relay log skip_slave_start=1 Copy the code

Check whether GTID is enabled

show variables like '%gtid%';
Copy the code

The master library establishes authorized users

Create an authorized user
GRANT REPLICATION SLAVE ON*. *TO 'Username'@'from the machine IP' IDENTIFIED BY 'password';
MySQL > alter table system permissions
FLUSH PRIVILEGES;
Copy the code

Salve connects to master

CHANGE MASTER TO  
MASTER_HOST='the master IP'.MASTER_USER='Username'.MASTER_PASSWORD='password'.MASTER_PORT= port number,# 1 indicates that the GTID protocol is used for replication # 0 means using the old binlog copy MASTER_AUTO_POSITION = 1; Copy the code

Enable primary/secondary replication

start slave;
Copy the code

Viewing slave Status

show slave status\G
Copy the code

View salve information on the master

show slave hosts;
Copy the code

At this pointGTIDAfter the master-slave replication mode is set up, you can operate the master database to verify whether the data is synchronized with the slave database.

IT brother

A big factory to do advanced Java development program ape

Follow wechat public account: IT elder brother

Java foundation, Java Web, JavaEE all tutorials, including Spring Boot, etc

Reply: Resume template, you can get 100 beautiful resumes

Reply: Java learning route, you can get the latest and most complete a learning roadmap

Re: Java ebooks, get 13 must-read books for top programmers

This article is formatted using MDNICE