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

MySQL GTID Replication:

From MySQL5.6, a powerful GTID (Global Transaction ID, global transaction ID) feature has been added to strengthen the database's primary and standby consistency, fault recovery, and fault tolerance. Used to replace the traditional master-slave replication (that is, asynchronous replication based on binlog and position).

With GTID, other slave of MySQL can automatically find the correct replication location on the new master when the master / slave switch occurs, which greatly simplifies the maintenance of the cluster under the complex replication topology and reduces the risk of misoperation of artificially setting the replication position. In addition, GTID-based replication can ignore transactions that have already been executed, reducing the risk of data inconsistencies.

GTID composition:

GTID is composed of server_uuid and transaction id, that is, GTID=server_uuid:transaction_id.

Server_uuid is automatically generated and persisted to the auto.cnf file when MySQL is first started (stored in the data directory, the server_uuid of each machine is different.

Transaction_id, which is a self-incrementing count starting at 1, represents the nth transaction executed on this main library. MySQL ensures 1:1 mapping between transactions and GTID, such as b6af5b5c-666f-11e9-bed3-000c29b85ea6:1

Represents the first database transaction executed on a MySQL instance uniquely identified by b6af5b5c-666f-11e9-bed3-000c29b85ea6.

A set of consecutive transactions can be represented by the transaction sequence number range of the "-" connection. For example: b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-5

Advantages based on GTID replication:

1. According to the traditional replication principle, when the connection fails, you need to reconnect to the master host, you need to find binlog and position, and then change master to connects to the master host. This process needs to be done manually, which is troublesome and easy to make mistakes, especially when there are many master write operations, it is not easy to determine the position. If flush table with read lock, it is bound to affect the online business. The GTID replication method does not need to find the binlog and position of master, but only needs to know the ip, port and account password of master to copy. MySQl will automatically find synchronization (MASTER_AUTO_POSITION=1) through the internal mechanism.

To put it simply: simplify replication. Traditional replication is based on file and position, while file and position are artificially determined, file is better, but position changes in real time, it is difficult to determine, unless a read lock is added to the whole library, but this is bound to have an impact on online business. GTID will automatically find position for data synchronization.

2. Multithreaded replication (based on libraries). In previous versions of MySQL5.6, slave replication was single-threaded. The reading application of an event. Master writes concurrently, so latency is unavoidable. The only effective way is to put multiple libraries on multiple slave, which is a bit of a waste of servers. In MySQL5.6, we can put multiple tables in multiple libraries, so we can use multithreaded replication. When there is only one library, multithreaded replication is useless (that is, the so-called parallel replication).

To put it simply: related to multithreaded replication. Multithreaded replication is based on group commit, which is stored in GTID

The role of GTID:

1. According to GTID, you can know on which instance the transaction was originally committed.

2. The existence of GTID facilitates the Failover of Replication.

How the GTID replication implementation works:

1. When master updates data, GTID will be generated before the transaction and recorded in the binlog log together.

2. The I / O thread on the slave side writes the changed binlog to the local relay log

3. The SQL thread gets the GTID from the relay log, and then compares whether the binlog on the slave side has a record (so the MySQL5.6 slave side must turn on binlog)

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 transaction of the GTID from relay log and record it to binlog

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

GTID usage restrictions:

1. Only after MySQL5.7 can you dynamically switch parameters related to GTID.

2. CREATE TABLE is not supported. SELECT statements

3. CREATE TEMPORARY TABLE statements inside transactions is not supported

4. Transaction or statement updates both transaction tables and non-transaction tables.

5. When skipping errors from the library using GTID replication, the syntax for executing sql_slave_skip_counter parameters is not supported.

Introduction to GTID-related status variables:

It is recommended to read the original English work on the official website of MySQL.

GTID-related status variables can be viewed on Master and Slave

Mysql > show global variables like'% gtid%'

+-+

| | Variable_name | Value |

+-+

| | enforce_gtid_consistency | ON |

| | gtid_executed | b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-7 | |

| | gtid_mode | ON |

| | gtid_owned |

| | gtid_purged |

+-+

5 rows in set (0.00 sec)

The following is the information collected by the author from blogs, forums, etc., which is not guaranteed to be correct, but for reference only:

1 、 gtid_executed

The GTID collection executed on the current instance contains virtually all the transactions logged to the binlog. Therefore, transactions executed after setting set sql_log_bin=0 will not generate binlog events and will not be logged to gtid_executed. Execute reset master to empty the variable

2 、 gtid_purged

Binlog cannot stay on the server permanently and needs to be cleaned regularly (such as through expire_logs_days), otherwise it will run out of disk space sooner or later. Gtid_purged is used to record a collection of binlog transactions that have been cleared, which is a subset of gtid_executed. This variable can only be set manually if gtid_executed is empty, and gtid_executed is also updated to the same value as gtid_purged. An empty gtid_executed means that either GTID-based replication has not been started before, or reset master has been performed. When you execute reset master, you also leave gtid_purged empty, that is, you always keep gtid_purged as a subset of gtid_executed.

3 、 gtid_next

Session-level variables indicating how to generate the next GTID. Possible values:

AUTOMATIC, which automatically generates the next GTID. In implementation, it allocates a GTID with the lowest sequence number that has not been executed on the current instance.

ANONYMOUS. Executing a transaction after setting will not generate GTID.

Explicitly specified GTID, can specify any form of legal GTID value, but cannot be the GTID already contained in the current gtid_executed, otherwise, an error will be reported the next time the transaction is executed

4 、 gtid_mode

Whether to enable GTID replication function

5. Enforce-gtid-consistency = ON

Enable to force the consistency of GTID. If the GTID feature is enabled, this parameter must be enabled. When slave makes synchronous replication, it does not need to find the binlog log and POS point, it can change master to master_auto_position=1 directly, and automatically synchronize data according to GTID.

Extend:

Multithreaded replication:

In previous versions of MySQL5.6, synchronous replication was single-threaded, queued and could only be performed one by one. In MySQL5.6, multithreaded replication between multiple libraries can be achieved. For example, if the user table, commodity table, price list and order table are stored in the database, then each business table can be replicated by multithreading in a separate database, but multithreaded replication of tables in a database is invalid (because multithreaded copying to the Slave of the same library will cause problems) Only one thread can be used per database (that is, MySQL5.6 multithreaded replication is based on the library), and multithreaded replication makes sense when replication involves multiple databases. The control parameter for multithreaded replication on Slave is slave-parallel-workers=0 (0 means multithreading is disabled)

Out of the question:

The main role of this article is to record the use of GTID, advantages and disadvantages, use restrictions, introduction of state variables, etc., mostly from the MySQL official website or some blog forums excerpt understanding. In line with the principle that practice is the only criterion for testing truth, recording more functions of this article paves the way for practical application in the future. Therefore, the author does not guarantee the accuracy of the content, only for reference. Welcome the industry leaders to point out the inadequacies, and I would like to express my thanks first!

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.