Mysql5.6.21-GTID主从复制
什么是GTID:GTID(global transaction id)是对于一个已提交事务的编号,并且是一个全局唯一编号。 组成部分: UUID+TID 什么是UUID:Mysql实例的唯一标识。 什么是TID:TID代表了该实例上已经提交的事务数量,随着事务提交单调递增。 例子:6dec6fd5-eb1f-
什么是GTID:GTID(global transaction id)是对于一个已提交事务的编号,并且是一个全局唯一编号。
组成部分: UUID+TID
什么是UUID:Mysql实例的唯一标识。
什么是TID:TID代表了该实例上已经提交的事务数量,随着事务提交单调递增。
例子:6dec6fd5-eb1f-12e4-6532-000c29e336f3:20
GTID功能目的:
1:根据GTID可以知道事务最初是在哪个实例上提交的。
2:GTID的存在方便了复制的故障转移。
在5.6版本前,Replication的Failover操作过程。
当A服务器宕机,业务需要切换到B服务器上。需要将C的复制源改成B服务器。
执行以下命令:
CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS='nnnn'
难点在于,同一个事务在每台机器上的binlog名字和位置都不一样。怎么找到C服务器当前同步停止点,对应服务器B的master_log_file和master_log_pos是什么的时候就称为了难题。这就是MMM,MHA出现的根本原因。
在5.6版本后,Replication的Failover操作过程。
由于同一个事务GTID在所有节点上的值一致。那么根据C服务器当前停止点的GTID就能唯一定位到服务器B的gtid,甚至由于Master_Auto_position功能的出现,我们根本不需要自动GTID的具体值,直接使用
CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION命令可以完成故障转移工作。
GTID搭建
实验环境:3台服务器,A,B,C
A:192.168.112.131
B:192.168.112.132
C:192.168.112.129
一:A服务器:192.168.112.131
1:添加复制账号.
sql>GRANT REPLICATION SLAVE ON *.* TO 'ruser'@'192.168.112.%' IDENTIFIED BY 'rpass';
2:配置文件添加以下信息,启用GTID模式。
vim /data/mysqldata/3306/my.cnf
---------------------------------------
server-id=1
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=3
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log-events=1
report-host=192.168.112.131
----------------------------------------
3:重启动Mysql服务
二:B服务器:192.168.112.132
1:配置文件添加以下信息,启用GTID模式。
vim /data/mysqldata/3306/my.cnf
server-id=10 log-slave-updates=true |
2:重启动Mysql服务
3:连接Mysql,建立主从关系。
sql>change master to master_host='192.168.112.131', master_user='ruser',master_password='rpass',master_auto_position=1;
sql>start slave;
sql>show slave status\G
4:查看slvae状态,获取关键参数值:
Slave_IO_Running: Yes Slave_SQL_Running: Yes |
三:测试主从同步
1:A主数据库:
sql> create database testhuang;
2:B从数据库:
sql> show databases;
+-----------------------+ | Database | +-----------------------+ | information_schema | | mysql | | performance_schema | | test | | testhuang | +-----------------------+ 5 rows in set (0.00 sec) |
3:B从数据库GTID执行状态
sql> show slave status\G
Retrieved_Gtid_Set: 7edc6fd5-e1bf-11e4-8842-000c29e512d6:1 Executed_Gtid_Set: 7edc6fd5-e1bf-11e4-8842-000c29e512d6:1 |
四:模拟binlog日志文件过期
模拟master-slave运行多时,master服务器的部分binlog因为expire_logs_days过期而被删除,此时需要添加多一台slave数据库。
A:192.168.112.131
B:192.168.112.132
服务器A:
1:查看当前主mysql数据库binlog日志文件,以及GTID。
sql>show master status;
2:模拟添加数据,切换binlog日志。
sql>flush logs;
3:查看binlog日志位置,GTID位置。
4:手动清除binlog,模拟binlog过期被清除,这里清除06之前的文件,就是说,t1-t4表的日志会丢失。
sql>purge binary logs to 'mysql-bin.000006';
5:可以通过gitd_purge状态参数看到,GTID被清除的事务序号1-5。
sql>show global variables like '%gtid%';
在数据库B-slave中,是可以发现t1-t6表的存在,因为已经同步过去了。我们再新增加slave-C的时候,就会发现C无法读取binlog日志而报错。可以通过跳过的方式解决。这样的话就会造成数据库binlog不存在的数据丢失,这个也没办法修复的。因为主从原理就是这样,只能是通过备份/还原的方式去重建。
五:GTID-跳过被清除的事务。
A:192.168.112.131
B:192.168.112.132
C:192.168.112.129
安装mysql参考之前的文章,数据库C添加GTID重要参数。重启动mysql,连接主库-A。
1:修改配置文件,添加以下内容。
vim /data/mysqldata/3306/my.cnf
server-id=12 log-slave-updates=true |
2:重启动mysql服务
3:连接主数据库,知道GTID的好处了吧。
sql>change master to master_host='192.168.112.131', master_user='ruser',master_password='rpass',master_auto_position=1;
sql>start slave;
sql>show slave status\G;
观察报错字段:
Slave_IO_Running: No 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.' |
IO错误:读取主的二进制日志致命错误1236,备库请求的GTID的事务内容被清除。
4:跳过被清除的GTID事务。
刚才我们再主库上通过gtid_pirged状态参数查看到1-5的二进制日志文件已经丢失。那么我们跳过该事务。
sql>stop slave;
sql>reset master;
sql>set global gtid_purged = '7edc6fd5-e1bf-11e4-8842-000c29e512d6:1-5';
sql>start slave
不知道我们是否发现,虽然跳过了1-5的事务,但是实际testhuang数据库还是没有创建的,如果跳过的该事务,肯定还会报错。报错没有找到testhuang数据库。
查看slave状态参数:sql>showslave status\G
Slave_IO_Running: Yes Last_SQL_Error: Worker 2 failed executing transaction '' at master log mysql-bin.000006, end_log_pos 346; Error 'Unknown database 'testhuang'' on query. Default database: 'testhuang'. Query: 'create table t5(id int)' Retrieved_Gtid_Set: 7edc6fd5-e1bf-11e4-8842-000c29e512d6:6-7 Executed_Gtid_Set: 7edc6fd5-e1bf-11e4-8842-000c29e512d6:1-5 |
可以看到,IO线程正常了,但是sql线程异常,确实提示是没有找到testhuang数据库。
在看看后面两个参数,先解释一下:
Retrieved_Gtid_Set:记录了relay日志从Master获取了binlog日志的位置,没错吧,只能拿到事务6-7的日志了。
Executed_Gtid_Set:记录本机执行的binlog日志位置(如果是从机,包括Master的binlog日志位置和slave本身的binlog日志位置)可以从Last_SQL_Error看到创建t5失败。所以这里还是执行1-5,等于没执行。。
5:手动建立testhuang数据库,重新执行跳过事务。
sql>create database testhuang;
sql>stop slave;
sql>reset master;
sql>set global gtid_purged = '7edc6fd5-e1bf-11e4-8842-000c29e512d6:1-5';
sql>start slave
下图是我拼起来的,观察几个重要的参数就可以了
sql>show slave status\G
呵呵,slave3 建立起来了,虽然数据丢失了,不是我们想要的结果,没办法,日志都没了,怎么复制,不然就违背mysql的复制原理了,但不可否认,是不是符合前面说的,同一个事务所有GTID都是一致的。
六:GTID-完整Slave创建。
A:192.168.112.131
B:192.168.112.132
C:192.168.112.129
还是拿服务器C来做完全恢复,来个最干净的环境,把上面的数据库初始化。
前面说过日志文件丢失了,就没办法做恢复了,我们可以把数据备份出来,然后导入到C服务器上,再进行主从数据同步。考虑到A是主库,生产部建议在主库上做备份。因为这里的备份考虑到数据的一致性,我们需要先把表都锁起来。禁止写入,但是生产啊,怎么能这样做呢,,只要锁从库-B了。。
1:B库,锁表,禁止写入数据。
记住,一定要停止主从关系后锁表,哈哈,主从都停止了,还有数据写入么。。停止了直接被备份就好了。。。
sql> stop slave;
sql> flush tables with read lock;
2:模拟主库现在又有数据写入了。。。。有真实感了没有?
A库:插入几行数据。
sql>flush logs;
sql>create table t7(id int);
sql>create table t8(id int);
sql>flush logs;
sql>create table t9(id int);
sql>create table t10(id int);
3:备份B库。全备?单库备,任你选,但是恢复的方式不一样。因为全备会把GTID信息备份过去,单库备份还原的话不会。
全备:
$ mysqldump -umysql-admin -p --all-databases > all.sql
Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. |
发现报错,不过是Warning,说的就是导出GTID,默认情况下导出所有事务,如果你不是用来做slave,就添加--set-gtid-purged=OFF。一个完整的转储, --all-databases --triggers --routines --events。
这样执行就不爆Warning了。
$mysqldump -umysql-admin -p --all-databases --triggers --routines --events --set-gtid-purged=ON > all.sql
4:释放从库B锁,启动slave线程
sql>unlock tables;
sql>start slave;
5:导入数据库C。
$ /usr/local/mysql56/bin/mysql -umysql-admin -p
报错了,就是说要开启GTID。因为C库是刚初始化的,还没有在my.cnf里面添加GTID参数,添加完后,重起mysql,再次导入。
添加GTID参数后,重启动mysql,再次导入。
$ /usr/local/mysql56/bin/mysql -umysql-admin -p
大功告成。。。。
6:重建立主从关系。
先查看几个表数据的情况。
sql>show databases;
sql>use testuhang;
sql>show tables;
可以看到t1-t6表就对了。锁表前,就是写到t6。
sql>show master status;
看看GTID事务,,数据非常吻合。因为备份的时候,刚好执行了7个事务。
连接主数据库。
sql>change master to master_host='192.168.112.131', master_user='ruser',master_password='rpass',master_auto_position=1;
如果看到以下结果,恭喜你。恢复成功。
sql>show slave status\G
可以看到retrieved_Gtid_Set的值是8-11,因为1-7的事务是通过恢复出来的,并不是从主库拉取过来的。

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

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

How to set up keyboard startup on Gigabyte's motherboard. First, if it needs to support keyboard startup, it must be a PS2 keyboard! ! The setting steps are as follows: Step 1: Press Del or F2 to enter the BIOS after booting, and go to the Advanced (Advanced) mode of the BIOS. Ordinary motherboards enter the EZ (Easy) mode of the motherboard by default. You need to press F7 to switch to the Advanced mode. ROG series motherboards enter the BIOS by default. Advanced mode (we use Simplified Chinese to demonstrate) Step 2: Select to - [Advanced] - [Advanced Power Management (APM)] Step 3: Find the option [Wake up by PS2 keyboard] Step 4: This option The default is Disabled. After pulling down, you can see three different setting options, namely press [space bar] to turn on the computer, press group

What graphics card is good for Core i73770? RTX3070 is a very powerful graphics card with excellent performance and advanced technology. Whether you're playing games, rendering graphics, or performing machine learning, the RTX3070 can handle it with ease. It uses NVIDIA's Ampere architecture, has 5888 CUDA cores and 8GB of GDDR6 memory, which can provide a smooth gaming experience and high-quality graphics effects. RTX3070 also supports ray tracing technology, which can present realistic light and shadow effects. All in all, the RTX3070 is a powerful and advanced graphics card suitable for those who pursue high performance and high quality. RTX3070 is an NVIDIA series graphics card. Powered by 2nd generation NVID

We users should be able to understand the diversity of some functions when using this platform. We know that the lyrics of some songs are very well written. Sometimes we even listen to it several times and feel that the meaning is very profound. So if we want to understand the meaning of it, we want to copy it directly and use it as copywriting. However, if we want to use it, we still need to You just need to learn how to copy lyrics. I believe that everyone is familiar with these operations, but it is indeed a bit difficult to operate on a mobile phone. So in order to give you a better understanding, today the editor is here to help you. A good explanation of some of the above operating experiences. If you also like it, come and take a look with the editor. Don’t miss it.

The shortcut key for copying is "Ctrl+c", and the corresponding paste key is "Ctrl+v"; on the computer, use the mouse to drag and select text, hold down Ctrl, and then click the C key to complete the copy; A shortcut key refers to completing an operation through certain specific keys, key sequences, or key combinations.

Which tablet is suitable for musicians? The 12.9-inch speaker in Huawei’s iPad is a very good product. It comes with four speakers and the sound is excellent. Moreover, it belongs to the pro series, which is slightly better than other styles. Overall, ipad pro is a very good product. The speaker of this mini4 mobile phone is small and the effect is average. It cannot be used to play music externally, you still need to rely on headphones to enjoy music. Headphones with good sound quality will have a slightly better effect, but cheap headphones worth thirty or forty yuan cannot meet the requirements. What tablet should I use for electronic piano music? If you want to buy an iPad larger than 10 inches, I recommend using two applications, namely Henle and Piascore. Provided by Henle

In the process of using the Windows 10 operating system developed by Microsoft, many users are curious and confused about the new technology called Cortana. Cortana's official name in the Chinese context is "Cortana", which is actually a built-in function of the Windows 10 system. Cortana, an artificial intelligence (AIassistant) service program. Frequently asked questions and solutions. How to open Cortana and not respond. Solution steps. Chinese solution is not supported. How to put the search box into Cortana. What software is Cortana? Answer: "Cortana" It is a cloud platform personal intelligent assistant carefully built by Microsoft. It has two usage modes: login and non-login. When you are logged in

What configurations are needed to use CAD smoothly? To use CAD software smoothly, you need to meet the following configuration requirements: Processor requirements: In order to run "Word Play Flowers" smoothly, you need to be equipped with at least one Intel Corei5 or AMD Ryzen5 or above processor. Of course, if you choose a higher-performance processor, you'll be able to get faster processing speeds and better performance. Memory is a very important component in the computer. It has a direct impact on the performance and user experience of the computer. Generally speaking, we recommend at least 8GB of memory, which can meet the needs of most daily use. However, for better performance and smoother usage experience, it is recommended to choose a memory configuration of 16GB or above. This ensures that the

What driver is good to install on rx5808g? 20.5.1 and 20.4.2WHQL refer to the version number of the software or driver. These version numbers are typically used to identify updates or fixes to software or drivers. In the computer world, WHQL stands for Windows Hardware Quality Labs, which is an institution used by Microsoft to test and verify the compliance and stability of hardware and drivers. Therefore, 20.5.1 and 20.4.2WHQL indicate that these software or drivers have passed Microsoft's testing and verification and can be safely used in the Windows operating system. AMDrx580 graphics card relatively stable drivers 20.5.1 and 20.4.2WHQL refers to the version number of the software or driver. These version numbers are passed
