MySQL GTID principle and configuration of master-slave replication

Gtid is a global transaction ID that has been successfully executed based on the original MySQL server, combined with the server ID and transaction ID. This global transaction ID is unique not only on the original server, but also on all MySQL servers where the master-slave relationship exists. It is this feature that makes MySQL's master-slave replication easier and database consistency more reliable. This paper mainly describes the rapid configuration of a master-slave replication architecture based on Gtid for your reference.

First, the concept of Gtid

1. Global Transaction ID: Transaction identifiers.

2, Gtid is a transaction one by one correspondence, and a globally unique ID.

3. A gtid is executed only once on a server, avoiding duplication of data or the fact that the master never agrees.

4, Gtid to replace the traditional method of replication, no longer use Master_log_file+master_log_pos to open replication. Instead, use Master_auto_postion=1 to start copying.

5, MySQL-5.6.5 began to support, after MySQL-5.6.10 began to improve.

6, in the traditional slave end, Binlog is not open, but in gtid slave end of the binlog is must be opened, the purpose is to record the execution of Gtid (mandatory).

Ii. Composition of Gtid

GTID = source_id:transaction_id

SOURCE_ID, used to identify the original server, the MySQL server is the only Server_uuid, because Gtid will be passed to slave, so it can also be understood as the source ID.

TRANSACTION_ID, which is a sequence number of committed transactions on the current server, usually starting from 1 for a self-growing series, a value corresponding to a transaction.

Example: 3e11fa47-71ca-11e1-9e33-c80aa9429562:23

The previous string is the server's Server_uuid, which is 3e11fa47-71ca-11e1-9e33-c80aa9429562, followed by 23 for transaction_id

Third, the advantages of Gtid

1, more simple implementation of failover, do not need to find log_file and Log_pos before.

2, more simple to build master-slave replication.

3, more secure than the traditional copy.

4, Gtid is continuous without empty, to ensure the consistency of data, 0 lost.

Iv. working principle of Gtid

1. When a transaction is executed and committed at the main library side, the Gtid is generated and recorded in the Binlog log together.

2, Binlog transmission to slave, and stored to slave relaylog, read this gtid value set gtid_next variable, that is, tell slave, the next Gtid value to execute.

3. SQL thread gets gtid from relay log and then compares Binlog on slave side for that gtid.

4, if there is a record, indicating that the Gtid transaction has been executed, slave will be ignored.

5, if there is no record, slave will execute the Gtid transaction, and record the Gtid to its own binlog, before reading the execution of the transaction will first check the other session holding the Gtid, to ensure that it is not repeated execution.

6, in the parsing process will determine whether there is a primary key, if not a two-level index, if not the full scan.

V. Configuration Gtid

For Gtid configuration, it mainly modifies several important parameters related to the Gtid feature in the configuration file (recommended use of mysql-5.6.5 or above), as follows:

1, the main:

[Mysqld]

Server_id=1

Gtid_mode=on #开启gtid模式

Enforce_gtid_consistency=on #强制gtid一致性, not supported for specific CREATE table after opening

Log_bin=master-binlog

Log-slave-updates=1

Binlog_format=row #强烈建议, other formats may cause inconsistent data

Logskip_slave_start=1

2, from:

[Mysqld]

Server_id=1

Gtid_mode=on

Enforce_gtid_consistency=on

Log_bin=master-binlog

Log-slave-updates=1

Binlog_format=row

Logskip_slave_start=1

Vi. Configuring Gtid-based replication

1. Newly configured MySQL server

On the primary server, perform the following actions:

> Grant replication Slave on * * to ' rep ' @ ' 192.168.1.% ' identified by ' 123 ';

For the newly configured MySQL server, after configuring the parameter file as described in 5th of this article, perform the following actions on the slave side

> Change MASTER to

Master_host= ' 192.168.1.135 ',

-Master_user= ' rep ',

-master_password= ' 123 ',

master_port=3306,

-master_auto_position = 1;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

> Start slave;

Query OK, 0 rows affected (0.01 sec)

> show slave status \g; #查看是否正常同步

2. The classic copy MySQL server has been run to Gtid replication

(1) According to the 5th described in this article configuration parameters file;

(2) All the server settings global.read_only parameters, waiting for the master-slave server synchronization completed;

> SET @ @global. read_only = on;

(3) Restart the master-slave server in turn;

(4) Use change master to update master-slave configuration;

> Change MASTER to

Master_host= ' 192.168.1.135 ',

-Master_user= ' rep ',

-master_password= ' 123 ',

master_port=3306,

-master_auto_position = 1;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

> Start slave;

Query OK, 0 rows affected (0.01 sec)

> show slave status \g;

MySQL GTID principle and configuration of master-slave replication