什么是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的事务是通过恢复出来的,并不是从主库拉取过来的。

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.


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

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

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.

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.

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

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software
