Shulou(Shulou.com)06/01 Report--

GTID is a global transaction ID that has been successfully executed based on the original mysql server, which is composed of server ID and transaction ID. This global transaction ID is unique not only on the original server, but also on all mysql servers that have master-slave relationships. It is precisely because of this feature that master-slave replication of mysql becomes easier and database consistency is more reliable. This paper mainly describes the rapid configuration of a master-slave replication architecture based on GTID for your reference.

I. the concept of GTID

1. Global transaction identity: global transaction identifiers.

2. GTID is an one-to-one correspondence of transactions and a globally unique ID.

3. A GTID is executed only once on a server to avoid data confusion or inconsistency caused by repeated execution.

4. GTID is used to replace the traditional replication method, and MASTER_LOG_FILE+MASTER_LOG_POS is no longer used to enable replication. Instead, you start copying using MASTER_AUTO_POSTION=1.

5. It is supported by MySQL-5.6.5 and improved after MySQL-5.6.10.

6. In the traditional slave terminal, the binlog does not need to be turned on, but in the GTID, the binlog of the slave terminal must be turned on in order to record the GTID that has been executed.

II. The composition of GTID

GTID = source_id:transaction_id

Source_id, which is used to identify the original server, that is, the unique server_uuid of the mysql server, can also be understood as the source ID because the GTID is passed to the slave.

Transaction_id, a sequence number of committed transactions on the current server, usually growing from 1, with a numeric value corresponding to a transaction.

Example: 3E11FA47-71CA-11E1-9E33-C80AA9429562:23

The first string is the server_uuid of the server, namely 3E11FA47-71CA-11E1-9E33-C80AA9429562, and the latter 23 is transaction_id

III. Advantages of GTID

1. It's easier to implement failover, instead of looking for log_file and log_pos as before.

2. Easier to build master-slave replication.

3. It is more secure than traditional replication.

4. GTID is continuous without emptiness, ensuring data consistency and zero loss.

Fourth, the working principle of GTID

1. When a transaction is executed and committed on the main database, a GTID is generated and recorded in the binlog log.

2. After the binlog is transferred to slave and stored in the relaylog of slave, read the value of this GTID and set the gtid_next variable, that is, tell Slave the next GTID value to be executed.

3. The SQL thread gets the GTID from the relay log, and then compares whether the binlog on the slave side has the GTID.

4. If there is a record, the transaction of the GTID has been executed, and slave will ignore it.

5. If there is no record, slave will execute the GTID transaction and record the GTID to its own binlog. Before reading the execution transaction, it will check that other session holds the GTID to ensure that it is not repeated.

6. During the parsing process, it will determine whether there is a primary key, if not, use a secondary index, and if not, use a full scan.

5. Configure GTID

For the configuration of GTID, mainly modify several important parameters related to the GTID feature in the / etc/my.cnf configuration file (above mysql-5.6.5 is recommended), as follows:

1. Lord:

[mysqld]

Server_id=1

Gtid_mode=on # turns on gtid mode

Enforce_gtid_consistency=on # enforces gtid consistency. When enabled, it is not supported for specific create table

Log_bin=master-binlog

Log-slave-updates=1

Binlog_format=row # strongly recommends that other formats may cause data inconsistencies

Skip_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

Skip_slave_start=1

Configure GTID-based replication

1. Newly configured mysql server

Do the following on the primary server:

> 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 the fifth point of this article, do the following on the server

> 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; # check whether synchronization is normal

2. Mysql server that has run Classical replication has been transferred to GTID replication

(1) configure the parameter file as described in the fifth point of this article.

(2) all servers set global.read_only parameters and wait for the synchronization between master and slave servers to complete.

> SET @ @ global.read_only = ON

(3) restart the master and slave servers 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.