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

PHP8.3发布:新特性一览随着技术的不断发展和需求的不断变化,编程语言也在不断更新和改进。作为一种广泛应用于网络开发的脚本语言,PHP一直在不断进步,为开发者提供更强大和高效的工具。最近发布的PHP8.3版本带来了许多期待已久的新特性和改进,下面让我们来看一下这些新特性的一览。非空属性的初始化在过去的PHP版本中,如果一个类的属性没有被明确赋值,它的值

深入解析PHP8的新特性,助您掌握最新技术随着时间的推移,PHP编程语言一直在不断演进和改进。最近发布的PHP8版本为开发者提供了许多令人兴奋的新特性和改进,为我们的开发工作带来了更多便利和效率。在本文中,我们将深入解析PHP8的新特性,并提供具体的代码示例,旨在帮助您更好地掌握这些最新的技术。JIT编译器PHP8引入了JIT(Just-In-Time)编

PHP8.1引入的新的Redis扩展随着互联网的快速发展,大量的数据需要进行存储和处理。为了提高数据处理的效率和性能,缓存成为了一个不可或缺的部分。而在PHP开发中,Redis作为一种高性能的键值对存储系统,被广泛应用于缓存和数据存储的场景。为了进一步提升Redis在PHP中的使用体验,PHP8.1引入了新的Redis扩展,本文将介绍这一扩展的新增功能,并给

CSS3的新特性一览:如何使用CSS3实现过渡效果CSS3作为CSS的最新版本,在众多新特性中,最有趣和实用的应该是过渡效果(transition)。过渡效果可以让我们的页面在交互时更加平滑、漂亮,给用户带来良好的视觉体验。本文将介绍CSS3过渡效果的基本用法,并附带相应的代码示例。transition-property属性:指定需要过渡的CSS属性过渡效果

php8新特性有JIT 编译器、类型推导、命名参数、联合类型、属性、错误处理改进、异步编程支持、新的标准库函数和匿名类的扩展等。详细介绍:1、JIT编译器,PHP8引入了JIT编译器,这是一个重要的性能改进,JIT编译器可以对一些高频执行的代码进行实时编译和优化,从而提高运行速度;2、类型推导,PHP8引入了类型推导功能,允许开发者在声明变量时自动推导出变量的类型等等。

go语言的新特性有:1、Go模块,用于管理Go语言项目的依赖关系;2、错误处理,增加了一个新的错误类型error,使得错误处理更加灵活和简洁;3、上下文包,用于在goroutine之间传递请求范围的值;4、嵌入,即一个结构体可以嵌入到另一个结构体中;5、同步包,更好地控制goroutine之间的同步和通信;6、错误值,更好地区分不同类型的错误;7、泛型,让开发者编写更灵活。

jdk8新特性:1、Lambda表达式;2、Stream API;3、函数式接口;4、默认方法;5、方法引用;6、新的日期和时间API;7、并发增强;8、其他改进等等。

CSS3的新特性一览:如何使用CSS3实现水平居中布局在网页设计和布局中,水平居中布局是一项常见的需求。过去,我们经常使用复杂的JavaScript或CSS技巧实现此目的。然而,CSS3引入了一些新的特性,使得水平居中布局更加简单和灵活。本文将介绍一些CSS3的新特性,并提供一些代码示例,演示如何使用CSS3实现水平居中布局。一、使用flexbox布局fle


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

Dreamweaver CS6
Visual web development tools

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

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.

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),

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment
