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新特性实践,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对网站的支持!

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.

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.

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 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 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 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.

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.

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


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

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
A free and powerful IDE editor launched by Microsoft

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

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
Chinese version, very easy to use