search
HomeDatabaseMysql TutorialMySQL 5.6 GTID new feature practice_MySQL

GTID Introduction

What is GTID

GTID (Global Transaction ID) is the number of a submitted transaction and is a globally unique number.

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 this instance, and increases monotonically as transactions are committed. The following is the specific form of a GTID

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

For a more detailed introduction, please see: official documentation

The role of GTID

So what is the purpose of the GTID function? The specific summary mainly includes the following two points:

According to the GTID, you can know on which instance the transaction was initially submitted. The existence of the GTID facilitates Replication's Failover. The second point is explained in detail here. We can take a look at the operation process of replication failover before the GTID of MySQL 5.6 appeared. Suppose we have an environment as shown below

At this time, the server of Server A is down and the business needs to be switched to Server B. At the same time, we need to change the replication source of Server C to Server B. The command syntax for copy source modification is very simple, namely CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=nnnn. The difficulty is that since the same transaction has different binlog names and locations on each machine, it becomes a problem to find the current synchronization stop point of Server C and what the corresponding master_log_file and master_log_pos of Server B are. This is an important reason why M-S replication clusters need to use additional management tools such as MMM and MHA.

This problem seems to be very simple after the GTID of 5.6 appeared. Since the GTID of the same transaction has the same value on all nodes, the GTID on Server B can be uniquely located based on the GTID of the current stop point of Server C. Even due to the emergence of the MASTER_AUTO_POSITION function, we do not need to know the specific value of GTID. We can directly use the CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION command to directly complete the failover work. So easy, isn’t it?

Introduction to GTID-based master-slave replication

Building

The construction is based on the mysql_sandbox script, and a location-based replication environment with one master and three slaves is first created. Then through configuration modifications, the entire architecture is designed for GTID-based replication.

Based on the GTID construction suggestions given in the official MySQL documentation. It is necessary to modify the configuration of the master and slave nodes once and restart the service. Such an operation is obviously unacceptable when upgrading in a production environment. Facebook, Booking.com, and Percona have all optimized this through patches and achieved more elegant upgrades. The specific operation methods will be introduced in future blog posts. Here we will conduct an experimental upgrade according to the official documentation.

The main upgrade steps are as follows:

Ensure master-slave synchronization. Configure read_only on the master to ensure that no new data is written to modify my.cnf on the master. Restart the service to modify my.cnf on the slave. Restart the service and execute change master to on the slave with master_auto_position. =1 Enabling GTID-based replication is an experimental environment, so restarting read_only and services will not cause any problems. As long as you follow the official GTID construction recommendations, you can successfully complete the upgrade. The detailed process will not be described here. Listed below are some common errors that may be encountered during the upgrade process.

Common mistakes

gtid_mode=ON, log_slave_updates, and enforce_gtid_consistency must be configured in my.cnf at the same time. Otherwise, the following error will appear in mysql.err

2016-10-08 20:11:08 32147 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates
2016-10-08 20:13:53 32570 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 requires --enforce-gtid-consistency

Warnings after changing master to

After changing master to according to the documentation, you will find two warnings. In fact, they are two security warnings, which do not affect normal synchronization (interested readers can read the detailed introduction of this warning. The specific content of the warning is as follows:

slave1 [localhost] {msandbox} ((none)) > stop slave;
Query OK, 0 rows affected (0.03 sec)
slave1 [localhost] {msandbox} ((none)) > change master to master_host='127.0.0.1',master_port =21288,master_user='rsandbox',master_password='rsandbox',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
slave1 [localhost] {msandbox} ((none)) > show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. |
| Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Experiment 1: If the GTID corresponding to the transaction required by the slave has been purged on the master

According to the command results of show global variables like '%gtid%', we can see that there is a gtid_purged among the variables related to GTID. From the literal meaning and the official documentation, we can know that what is recorded in this variable is the gtid_set that has been executed on the local machine but has been cleaned up by the purge binary logs to command.
In this section, we will test what will happen if the master purges some gtid events that have not been fetched by the slave.

The following instructions are executed on the master

master [localhost] {msandbox} (test) > show global variables like '%gtid%';
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | ON |
| gtid_executed | 24024e52-bd95-11e4-9c6d-926853670d0b:1 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+---------------------------------+----------------------------------------+
7 rows in set (0.01 sec)
master [localhost] {msandbox} (test) > flush logs;create table gtid_test2 (ID int) engine=innodb;
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.02 sec)
master [localhost] {msandbox} (test) > flush logs;create table gtid_test3 (ID int) engine=innodb;
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.04 sec)
master [localhost] {msandbox} (test) > show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 | 359 | | | 24024e52-bd95-11e4-9c6d-926853670d0b:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > purge binary logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.03 sec)
master [localhost] {msandbox} (test) > show global variables like '%gtid%';
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | ON |
| gtid_executed | 24024e52-bd95-11e4-9c6d-926853670d0b:1-3 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 24024e52-bd95-11e4-9c6d-926853670d0b:1 |
| simplified_binlog_gtid_recovery | OFF |
+---------------------------------+------------------------------------------+
7 rows in set (0.00 sec)

在slave2上重新做一次主从,以下命令在slave2上执行

slave2 [localhost] {msandbox} ((none)) > change master to master_host='127.0.0.1',master_port =21288,master_user='rsandbox',master_password='rsandbox',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
slave2 [localhost] {msandbox} ((none)) > start slave;
Query OK, 0 rows affected (0.01 sec)
slave2 [localhost] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
......
Slave_IO_Running: No
Slave_SQL_Running: Yes
......
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 151
......
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
Last_SQL_Errno: 0
Last_SQL_Error:
......
Auto_Position: 1
1 row in set (0.00 sec)

实验二:忽略purged的部分,强行同步

那么实际生产应用当中,偶尔会遇到这样的情况:某个slave从备份恢复后(或者load data infile)后,DBA可以人为保证该slave数据和master一致;或者即使不一致,这些差异也不会导致今后的主从异常(例如:所有master上只有insert没有update)。这样的前提下,我们又想使slave通过replication从master进行数据复制。此时我们就需要跳过master已经被purge的部分,那么实际该如何操作呢?

我们还是以实验一的情况为例:

先确认master上已经purge的部分。从下面的命令结果可以知道master上已经缺失24024e52-bd95-11e4-9c6d-926853670d0b:1这一条事务的相关日志

master [localhost] {msandbox} (test) > show global variables like '%gtid%';
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | ON |
| gtid_executed | 24024e52-bd95-11e4-9c6d-926853670d0b:1-3 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 24024e52-bd95-11e4-9c6d-926853670d0b:1 |
| simplified_binlog_gtid_recovery | OFF |
+---------------------------------+------------------------------------------+
7 rows in set (0.00 sec)

在slave上通过set global gtid_purged='xxxx'的方式,跳过已经purge的部分

slave2 [localhost] {msandbox} ((none)) > stop slave;
Query OK, 0 rows affected (0.04 sec)
slave2 [localhost] {msandbox} ((none)) > set global gtid_purged = '24024e52-bd95-11e4-9c6d-926853670d0b:1';
Query OK, 0 rows affected (0.05 sec)
slave2 [localhost] {msandbox} ((none)) > start slave;
Query OK, 0 rows affected (0.01 sec)
slave2 [localhost] {msandbox} ((none)) > show slave status\G 
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
......
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 359
Relay_Log_File: mysql_sandbox21290-relay-bin.000004
Relay_Log_Pos: 569
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
Exec_Master_Log_Pos: 359
Relay_Log_Space: 873
......
Master_Server_Id: 1
Master_UUID: 24024e52-bd95-11e4-9c6d-926853670d0b
Master_Info_File: /data/mysql/rsandbox_mysql-5_6_23/node2/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
......
Retrieved_Gtid_Set: 24024e52-bd95-11e4-9c6d-926853670d0b:2-3
Executed_Gtid_Set: 24024e52-bd95-11e4-9c6d-926853670d0b:1-3
Auto_Position: 1
1 row in set (0.00 sec)

可以看到此时slave已经可以正常同步,并补齐了24024e52-bd95-11e4-9c6d-926853670d0b:2-3范围的binlog日志。

以上所述是小编给大家介绍的MySQL 5.6 GTID新特性实践,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对网站的支持!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Tools

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use