MySQL GTID comprehensive summary

MySQL GTID comprehensive summary

01 Introduction to GTID

GTID, the full name of Global transaction identifiers, is also called global transaction ID. MySQL-5.6.2 began to support, and MySQL-5.6.10 was improved. GTID is divided into two parts. One part is the UUID of the service. The UUID is saved in the auto.cnf file in the MySQL data directory.
This is a very important file and cannot be deleted. This part will not change. The following is an example of a uuid value:

[root@dev01 mysql]# cat auto.cnf
 [auto]
server-uuid=ac1ebad0-ef76-11e7-872b-080027a03bb6

The other part is the transaction ID. As the number of transactions increases, the value increases. In other words, GTID is actually composed of UUID+TID. The UUID is the unique identifier of a MySQL instance. TID represents the number of transactions that have been committed on the instance. The following is an example of a GTID:

3db33b36-0e51-409f-a61d-c99756e90155:1-14

02 How GTID works

1. When the master updates data, it will generate a GTID before the transaction and record it in the binlog log.
2. The slave's i/o thread writes the changed binlog to the local relay log.
3. The sql thread obtains the GTID from the relay log, and then compares whether there is a record in the binlog on the slave side.
4. If there is a record, it means that the transaction of the GTID has been executed and the slave will ignore it.
5. If there is no record, the slave will execute the transaction of the GTID from the relay log and record it in the binlog.
6. During the parsing process, it will be determined whether there is a primary key. If not, the secondary index will be used. If not, a full scan will be performed.

03 Advantages and Disadvantages of GTID

advantage:

1. A transaction corresponds to a unique GTID, and a GTID will only be executed once on a server
2.GTID is used to replace the traditional replication method. The biggest difference between GTID replication and ordinary replication mode is that it does not require the specification of binary file name and location
3. Reduce manual intervention and service downtime. When the main machine fails, the software can promote a backup machine from a large number of backup machines to become the main machine.

shortcoming:

1. Non-transactional engines are not supported
2. The create table ... select statement replication is not supported (the master database will directly report an error)
Principle: (Two SQLs will be generated, one is the DDL to create the table SQL, and the other is the insert into SQL to insert data.
Since DDL will cause automatic submission, this SQL requires at least two GTIDs, but in GTID mode, only one GTID can be generated for this SQL.
3. One SQL statement is not allowed to update a transaction engine table and a non-transaction engine table at the same time
4. Enabling GTID requires restart (except 5.7)
5. The create temporary table and drop temporary table statements are not supported.
6. sql_slave_skip_counter is not supported

04 Test environment construction

node:
server1 192.168.197.128 3306 Master
server2 192.168.197.137 3306 Slave
server3 192.168.197.136 3306 Slave

To enable GTID, you need to enable these three parameters:

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates = 1

The steps to build a test environment are as follows:

1. Create a replication user on the master node and enable the GTID option of the master node;

mysql> grant replication slave on *.* to 'repluser'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2. Perform the change master to operation on the slave node to build the master-slave relationship as follows:

mysql> change master to
     -> master_host='192.168.197.128',
    -> master_user='repluser',
    -> master_password='123456',
    -> master_port=3306,
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

3. After the installation is successful, check whether the slave node has joined on the master node 197.128:

mysql> show slave hosts;
+-----------+------+------+----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+----------+--------------------------------------+
| 3 | | 3306 | | 969488f5-c486-11e8-adb7-000c29bf2c97 |
| 2 | | 3306 | | bb874065-c485-11e8-8b52-000c2934472e |
+-----------+------+------+----------+--------------------------------------+
 rows in set (.sec)

View the connection:

mysql> show processlist;
+----+----------+------------------+------+------------------+------+--------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+------------------+------+------------------+------+--------------------------------------------------------------+------------------+
| | root | localhost | NULL | Query | 0 | starting | show processlist |
| 3 | repluser | work_NAT_4:60051 | NULL | Binlog Dump GTID | | Master has sent all binlog to slave; waiting for more updates | NULL |
| | repluser | work_NAT_5: | NULL | Binlog Dump GTID | 5970 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+----------+------------------+------+------------------+------+--------------------------------------------------------------+------------------+
 rows in set (.sec)

4. The UUIDs of the three test environments are:

197.128
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| bd0d-8691-11e8-afd6-4c3e51db5828 |
+--------------------------------------+
 row in set (0.00 sec)

197.137
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
|bb874065-c485-11e8-8b52-000c2934472e |
+--------------------------------------+
 row in set (0.00 sec)

197.136
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
|f5-c486-11e8-adb7-000c29bf2c97 |
+--------------------------------------+
 row in set (0.00 sec)

05 Start testing

The test environment is mainly divided into the following aspects:

a. Test replication failover

b. Copy error skip

1 Test replication failover

Let's first look at the failover of the test replication:

(1) First, stop the replication process of server 3

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

(2) Create some data on server 1

mysql> create table yyy.a(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table yyy.b(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table yyy.c(id int);
Query OK, 0 rows affected (0.02 sec)

(3) Check the data results on the other two machines:

server 
mysql> show tables from yyy;
+---------------+
| Tables_in_yyy |
+---------------+
| a |
| b |
| c |
+---------------+
 rows in set (0.00 sec)

server 
mysql> show tables from yyy;
Empty set (0.00 sec)

(4) At this point, we can find that the data on server 2 is newer than that on server 3. We then stop server 1 to simulate a crash on the primary server:

[root@work_NAT_1 init.d]# service mysqld stop
Shutting down MySQL............ [ OK ]

(5) At this point we find that the other two nodes can no longer access server 1

mysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Reconnecting after a failed master event read
         Master_Host: 192.168.197.128
         Master_User: repluser
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-bin.000006
     Read_Master_Log_Pos: 1364
        Relay_Log_File:mysql-relay-bin.000004
        Relay_Log_Pos: 1569
    Relay_Master_Log_File: mysql-bin.000006
       Slave_IO_Running: Connecting
      Slave_SQL_Running: Yes
     Exec_Master_Log_Pos: 1364
       Relay_Log_Space: 2337 
        Master_SSL_Key: 
    Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
        Last_IO_Errno: 2003
        Last_IO_Error: error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 1
        Last_SQL_Errno: 0

(6) We need to set server 2 as the primary database for server 3 because the data on server 2 is newer. If you use the previous method at this time, you need to calculate the log_pos of the previous main database and the log_pos to be set as the current main database, which is likely to go wrong. Therefore, some high availability tools such as MHA and MMM have emerged to solve the problem.

After MySQL 5.6, this problem was easily solved. Because the GTID of the same transaction has the same value on all nodes, the GTID on server2 can be located based on the GTID of the current stop point on server3, so you can directly execute the change on server3:

mysql> change master to 
  -> master_host='192.168.197.137',
  -> master_user='repluser',
  -> master_password='123456',
  -> master_port=,
  -> master_auto_position=;
Query OK, rows affected, warnings (0.01 sec)

(7) Now check the data on server 3 and you will find that the data has been synchronized.

2 Copy Error Skip

In the above test, the final result is that server 2 is the primary node and server 3 is the secondary node. Now let's verify the method of skipping replication errors.

(1) First, we execute a drop statement on the slave node to make the data on both sides inconsistent, as follows:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DBAs |
| customer |
| inc_db |
|mysql |
| performance_schema |
|sys|
|testdb|
|yeyz|
|yyy|
+--------------------+
 rows in set (.sec)

mysql> drop database yyy;
Query OK, rows affected (. sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DBAs |
| customer |
| inc_db |
|mysql |
| performance_schema |
|sys|
|testdb|
|yeyz|
+--------------------+
 rows in set (.sec)

(2) Then we execute the drop database yyy operation on server 2 as follows:

mysql> drop database yyy;
Query OK, 3 rows affected (0.02 sec)

(3) At this point, we see that the master-slave out-of-sync error warning has appeared on server 3, because it does not have the yyy database (it has been deleted in the previous step). The error is as follows;

mysql> show slave status\G
*************************** . row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 192.168.197.137
         Master_User: repluser
         Master_Port: 
        Connect_Retry: 
       Master_Log_File: mysql-bin.
     Read_Master_Log_Pos: 
        Relay_Log_File: mysql-relay-bin.
        Relay_Log_Pos: 
    Relay_Master_Log_File: mysql-bin.
       Slave_IO_Running: Yes
      Slave_SQL_Running: No
          Last_Errno: 
          Last_Error: Error 'Can't drop database 'yyy'; database doesn't exist' on query. Default database: 'yyy'. Query: 'drop database yyy'
         Skip_Counter: 
     Exec_Master_Log_Pos: 
       Relay_Log_Space: 
        Last_SQL_Error: Error 'Can't drop database 'yyy'; database doesn't exist' on query. Default database: 'yyy'. Query: 'drop database yyy'
 Replicate_Ignore_Server_Ids: 
       Master_Server_Id: 
         Master_UUID: bb874065-c485-e8-b52-c2934472e
       Master_Info_File: mysql.slave_master_info
      Retrieved_Gtid_Set: bd0d--e8-afd6-c3e51db5828:-,
bb874065-c485-e8-b52-c2934472e:
      Executed_Gtid_Set: db33b36-e51-f-a61d-c99756e90155:-,
bd0d--e8-afd6-c3e51db5828:-,
f5-c486-e8-adb7-c29bf2c97:
        Auto_Position: 
     Replicate_Rewrite_DB: 
         Channel_Name: 
      Master_TLS_Version: 
 row in set (0.00 sec)

(4) When we use the traditional method to skip this error, it will prompt that it is not allowed in GTID mode, as follows:

mysql> set global sql_slave_skip_counter=;
ERROR (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

So how to skip this error in this way?

(5) Because we are replicating via GTID, we also need to skip this transaction and continue replicating. This transaction can be viewed in the binlog on the master. Because we do not know which GTID caused the error, we do not know how to skip which GTID. However, we can find POS:2012 in the executing Master from the information in show slave status, which is the 18th line of code in step (3) above. Now we use pos:2012 to search in the log of server 2 and find the following information:

# at 2012
#190305 20:59:07 server id 2 end_log_pos 2073 GTID last_committed=9 sequence_number=10 rbr_only=no
SET @@SESSION.GTID_NEXT= 'bb874065-c485-11e8-8b52-000c2934472e:1'/*!*/;
# at 2073
#190305 20:59:07 server id 2 end_log_pos 2158 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=/*!*/;
drop database yyy
/*!*/;

(6) We can see that the value of GTID_NEXT is

, and then we restore the master-slave replication by the following method:

mysql> stop slave;
Query OK, rows affected (0.00 sec)

mysql> set session gtid_next='bb874065-c485-11e8-8b52-000c2934472e:1';
Query OK, rows affected (0.00 sec)

mysql> begin;
Query OK, rows affected (0.00 sec)

mysql> commit;
Query OK, rows affected (0.01 sec)

mysql> set session gtid_next=automatic;
Query OK, rows affected (0.00 sec)

mysql> start slave;
Query OK, rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 192.168.197.137
         Master_User: repluser
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File:mysql-bin.000002
     Read_Master_Log_Pos: 2158
        Relay_Log_File:mysql-relay-bin.000003
        Relay_Log_Pos: 478
    Relay_Master_Log_File: mysql-bin.000002
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
     Exec_Master_Log_Pos: 2158
       Relay_Log_Space: 1527
       Until_Condition: None
       Master_Server_Id: 2
         Master_UUID: bb874065-c485-11e8-8b52-000c2934472e
       Master_Info_File: mysql.slave_master_info
          SQL_Delay: 0
     SQL_Remaining_Delay: NULL
   Slave_SQL_Running_State: Slave has read all relay logs; waiting for more updates
      Master_Retry_Count: 
      Retrieved_Gtid_Set: bd0d-8691-11e8-afd6-4c3e51db5828:-7,
bb874065-c485-11e8-8b52-000c2934472e:
      Executed_Gtid_Set: db33b36-0e51-409f-a61d-c99756e90155:-14,
bd0d-8691-11e8-afd6-4c3e51db5828:-7,
f5-c486-11e8-adb7-000c29bf2c97:,
bb874065-c485-11e8-8b52-000c2934472e:
        Auto_Position: 
     Replicate_Rewrite_DB: 
         Channel_Name: 
      Master_TLS_Version: 
 row in set (0.00 sec)

The above is the detailed content of the comprehensive summary of MySQL GTID. For more information about MySQL GTID, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MYSQL database GTID realizes master-slave replication (super convenient)
  • An example of changing traditional replication to GTID replication without stopping business in MySQL 5.7
  • Enabling or disabling GTID mode in MySQL online

<<:  CSS Sticky Footer Several Implementations

>>:  Scoring rules of YSlow, a webpage scoring plugin developed by Yahoo

Recommend

A brief introduction to Linux environment variable files

In the Linux system, environment variables can be...

Special commands in MySql database query

First: Installation of MySQL Download the MySQL s...

Detailed explanation of installing redis in docker and starting it as a configuration file

Update: Recently, it was discovered that the serv...

React's context and props explained

Table of contents 1. context 1. Usage scenarios 2...

Docker+daocloud realizes automatic construction and deployment of front-end projects

Automated project deployment is more commonly use...

A complete example of Vue's multi-level jump (page drill-down) function for related pages

background During the project development process...

Six tips to increase web page loading speed

Secondly, the ranking of keywords is also related ...

Detailed tutorial on installing Hbase 2.3.5 on Vmware + Ubuntu18.04

Preface The previous article installed Hadoop, an...

Pure CSS custom multi-line ellipsis problem (from principle to implementation)

How to display text overflow? What are your needs...

Detailed tutorial on how to log in to the system using the root user in the new version of Ubuntu 20.04

Ubuntu 20.04 does not have root login enabled by ...

Solve the problem that the commonly used Linux command "ll" is invalid or the command is not found

question: The commonly used command "ll"...

Why Nginx is better than Apache

Nginx has taken over the majority of the Web serv...

Nginx external network access intranet site configuration operation

background: The site is separated from the front ...

Mysql optimization techniques for querying dates based on time

For example, to query yesterday's newly regis...

Docker container source code deployment httpd use storage volume to deploy the website (recommended)

Table of contents Deploy httpd with docker contai...