gtid! A nuclear weapon in MySQL replication

You old iron, this week Lao Zhang's "MySQL King Promotion Road" a book finally published, now can be pre-order!
Pre-order link address: Lao Zhang's database micro-store
After a year of preparation time, it is ten years to sharpen a sword, all the essence of their profession and painstaking efforts are instilled in his book. The book contains all aspects of MySQL knowledge points, is one of my blog "from Bronze to the king, quickly improve your MySQL database Dan full-depth analysis." With a sentence students say to me, the teacher likes your King glory feelings, but more like your technical sentiment. Tell the truth, don't miss it! Special thanks in my technical road, to help my predecessors and brothers, this road of all the bitterness, only you know me best! Thanks to the brothers who have always supported my blog!

Today's blog post, you can quickly understand the gtid features, and flexibility to apply to the production environment, we hope to help.

Introduction to Gtid Principle
Gtid is also called the global transaction ID, which is the number of a committed transaction and is a globally unique number. Gtid replication has been added to the master-slave replication type after the MySQL5.6 version.
Gtid is made up of Server_uuid and transaction IDs, that is, Gtid = server_uuid:transaction_id. Server_uuid are generated automatically during database startup, and each machine's server-uuid is not the same. The UUID is stored under the auto.cnf file in the data directory. The transaction_id is a serial number that is not duplicated by the system order when the transaction commits.

The value of Gtid existence
(1) Gtid use master_auto_position=1 instead of binlog and position based on the master-slave copy of the building, more convenient for master-slave replication.
(2) Gtid can know which instance the transaction was committed on at the very beginning.
(3) Gtid convenient to achieve the master-slave between the failover, no longer have to go to find position and binlog.

Management and maintenance of Gtid in master-slave replication
Gtid brings the most convenient point is the master-slave copy of the building process. It is similar to asynchronous replication, semi-synchronous replication, except that it no longer utilizes the Binlog file and the position number of the traditional copy mode, but rather builds it in the same way that it uses master_auto_position=1 from the library "Change Master". This makes the operation more convenient and reliable.

Precautions in the construction of Gtid
The master and slave libraries need to set the following parameters.
Main Library configuration:

gtid_mode=onenforce_gtid_consistency=onlog_bin=on

Server-id cannot be the same as from a library.

binlog_format=row

From the Library configuration:

gtid_mode=onenforce_gtid_consistency=onlog_slave_updates=1

Although you can turn off log_slave_updates after the MySQL5.7 version, use the gtid_executed table. However, it is recommended that you open it from the library. Server-id cannot be the same as the main library.
After configuring the parameters, the main library has created a copy account, and if it is a newly built master-slave environment, you can execute the change master to statement directly from the library. If the main library has been running for a period of time, you also need to use the backup method to "dump" the data from the main library into the library, complete the Gtid copy based on a point, and then start chasing the main library from the library after that point. With Mysqldump Backup, the backup file will have the SET @ @GLOBAL. gtid_purged= *, using Xtrabackup tool Backup, the backup file will directly record the need to skip the GTID. After initiating replication, the library will skip directly over the Gtid that has been performed and get new Gtid information directly from the main library.
Perform the show Master Status command in the main library and view the executed Gtid through Executed_gtid_set.

After the MySQL5.7 version, the value of gtid_executed is persisted. A new table gtid_executed is added under the MySQL library:

The table records the information of the Gtid collection that has been executed, and with this table, it is not necessary to turn on the log_slave_updates parameter when the MySQL5.6 version is available, from which the library can replicate. Gtid information will be saved in the Gtid_executed table, you can close the Binlog from the library, saving the Binlog record overhead. When you perform reset master, all the data in the table is emptied.
MySQL5.7 also has a parameter, Gtid_executed_compression_period, to control the compression of the gtid_executed table. The default value of this parameter is 1000, which means that table compression begins after 1000 transactions have been executed.

Starting with MySQL5.7.6, Gtid_mode supports dynamic modification, Gtid_mode desirable values are:
off-does not support Gtid's affairs;
OFFpermissive-The new transaction is anonymous, while allowing the replicated transaction to be either Gtid or anonymous;
on permissive-The new transaction uses Gtid, while allowing the replicated transaction to be Gtid or anonymous;
The on-supports Gtid transactions.
In a production environment, there may be a need to change traditional replication to Gtid replication mode. It is emphasized here that Gtidmode supports dynamic modification, but does not support jump-type modification. It is not possible to modify from on permissive to off. Here's an experiment to show the switching process between traditional and gtid replication.
You can get the received Gtid (Retrieve_gtid_set) and Gtid (Execute_gtid_set) performed by the show slave Status command from the library.

Switching between Gtid replication and traditional replication
The MySQL5.7 version of Gtid replication mode has been set up before, and the process of switching from Gtid copy mode to traditional copy mode follows first.
Environment: The main library is 192.168.56.101, from the library is 192.168.56.102.
Current master-slave status display.

The implementation process is as follows:

(1) Stop the master-slave copy before executing the stop slave from the library. Then adjust to the traditional copy mode and let the master_auto_position=0.
Execute the following command:
CHANGE MASTER TO master_auto_position=0,Master_Host=‘192.168.56.101‘,MASTER_USER=‘bak‘,MASTER_PASSWORD=‘bak123‘,‘Master_Log_File=‘mysql-binlog.000002‘,MASTER_LOG_POS=1141;
After execution completes, turn on the copy function start slave.
(2) need to adjust the Gtid mode to on_permissive at the same time on the master and slave server.

(3) need to adjust the Gtid mode to off_permissive at the same time on the master and slave server.

(4) The Gtid function needs to be turned off at the same time on the master server.

(5) The Gtid_mode=off and Enforce_gtid_consistency=off are then written to the configuration file MY.CNF. The next reboot will take effect directly.
(6) test whether the switch was successful.
First, insert a piece of data into the TT table in the main library's ZS library:

View from the library, this data is synchronized successfully.

Then execute show slave status from the library to see the master-slave replication status, and find that the Gtid value has not increased, proving that the switchover was successful:

the process of switching from traditional copy mode to Gtid Copy mode is then manipulated.
The implementation process is as follows:
(1) Modify the parameter Enforce_gtid_consistency=warn on the master/slave library to ensure that no warning message appears in error log. If you do, you need to fix it before you can continue.

(2) on the master-slave server to change the enforce_gtid_consistency on to ensure gtid consistency.

(3) Adjust the Gtid mode to off_permissive at the same time on the master-slave server.

(4) Adjust the Gtid mode to on_permissive at the same time on the master-slave server.

(5) Confirm that the Ongoing_anonymous_transaction_count parameter from the library is 0, if 0, means there is no waiting for the transaction, you can proceed directly to the next step.

(6) Set the Gtid_mode=on at the same time on the master/slave library.

View the Gtid parameter settings, which are now turned on.

(7) Change the traditional copy mode to Gtid copy. To stop the legacy copy, perform the Stop slave operation before you perform the change master to Master_auto_position=1.
After executing the stop slave, view the current master-slave status as:


Then perform change master to Master_auto_position=1, and turn on master-slave copy start slave.
(8) Verify that the switch is successful.
First, insert a piece of data into the TT table in the main library's ZS library:

View from the library, this data is synchronized successfully.

Then perform the show slave status from the library to see the master-slave replication status, and find that the value of Gtid has increased. proves that the Gtid replication mode is turned on and the switch succeeds.

restrictions in the use of Gtid
Gtid replication is for transactions, a transaction corresponds to only one gtid, and a lot of the limitations lie in this.
(1) You cannot use the CREATE TABLE TABLE_NAME SELECT * FROM TABLE_NAME.
(2) An operation that contains both a transaction table and a non-transactional table in a transaction.
(3) The Create temporary table or DROP temporary table statement operation is not supported.
(4) The syntax for executing the sql_slave_skip_counter parameter is not supported when using Gtid replication to skip an error from a library.

Thank you for supporting Lao Zhang's new book, but also thanks to MySQL to bring us the most simple, the simplest happiness!

gtid! A nuclear weapon in MySQL replication