广州中心网站建设,外贸营销网站建设公司,三创大赛网站建设,兼职网站的建设目标怎么写文章目录 1.GTID的基本概念1.1.为什么要引入GTID1.2.什么是GTID 2.开启GTID全局事务标识符的功能3.模拟产生Binlog日志观察开启GTID功能的区别3.1.模拟产生Binlog日志3.2.观察Binlog日志中的事件信息3.2.观察节点状态有什么变化3.3.观察Binlog日志会有什么变化 4.使用GTID来截取… 文章目录 1.GTID的基本概念1.1.为什么要引入GTID1.2.什么是GTID 2.开启GTID全局事务标识符的功能3.模拟产生Binlog日志观察开启GTID功能的区别3.1.模拟产生Binlog日志3.2.观察Binlog日志中的事件信息3.2.观察节点状态有什么变化3.3.观察Binlog日志会有什么变化 4.使用GTID来截取Binlog中部分日志4.1.使用GTID来截取Binlog日志的方法4.2.模拟误删除的场景4.3.使用GTID来截取要恢复的Binlog日志4.4.GTID的幂等性问题4.5.利用GTID截取的Binlog还原误删除的数据。 1.GTID的基本概念
1.1.为什么要引入GTID
对于误删除操作进行数据恢复是从Binlog日志中根据事件的开始标识位号和结束标识位号截取需要的Binlog日志然后进行数据恢复在Binlog中查找想要恢复的数据对应的事件开始和结束标识位一条SQL可能就包含一个开始/结束标识位号查找开始/结束标识位是非常麻烦的一个过程。
还有一个场景当我们想要恢复的数据对应的事件位于开始位200到结束位3000之间的数据在这个标识位范围内如果还包含了多次DELETE误删除的语句这时我们不想截取DELETE语句产生的SQL避免数据恢复的不完整那么此时还用老办法截取日志就非常麻烦我们还需要一个个的找出DELETE语句对应的事件在这个事件之前进行截取如果少量的DELETE还好如果发生了多次DELETE的误删除此时还利用标识位在Binlog中还原数据将会非常复杂。
基于这种场景通过事件的开始结束标识位号截取Binlog日志非常复杂MySQL从5.6版本开始加了一个新的特性也就是GTIDGTID在MySQL主从复制中会使用到通过GTID来解决此问题。
1.2.什么是GTID
GTID的全称为全局事务标识符global transaction identifiner是MySQL5.6版本中引入的新特性并且在MySQL5.7版本中进行了加强在MySQL5.7版本中GTID即使不开启也会有自动生成。
GTID可以保证MySQL数据库中的每一个事务都有一个全局唯一的标识号这个标识号在当前MySQL实例甚至是MySQL主从复制集群中都会保证是全局唯一的标识号从而保证数据的一致性。
在GTID中DDL、DCL每一条语句事件都会被当成一个事务并且会拥有一个GTID号。DML语句的每一个完整的事务都会占用一个GTID号。
GTID开启后会在Binlog中针对每一个事务增加一个GTID号我们可以根据这个GTID号去截取Binlog数据。
当Binlog中增加了GTID后我们就不需要再通过事件的开始/结束标识位号去截取某个范围的Binlog日志我们可以直接通过GTID号来截取想要的事务操作的数据并且mysqlbinlog中还有参数可以排除不想截取的GTID号当多个事务标识号GTID中有误删除的操作时我们不想截取删除的操作此时可以在截取日志时排除对应事务的GTID号保障截取的Binlog都是我们需要的日志内容。
GTID的定义如下
GTID server_uuid:transaction_id
例如e0a2c0cc-f835-11ec-8a3c-005056b791aa:27server_uuid也就是当前实例的UUID号32字节1字节的字符串在MySQL第一次启动时会生成这个UUID并将这个UUID会保存在数据目录中的auto.conf文件中如果该配置文件丢失MySQL会重新生成一个UUID相同server uuid中的事务对应的transaction_id全局事务唯一ID在Binlog日志中是自增并且连续有序的。
[rootmysql ~]# cat /data/mysql/auto.cnf
[auto]
server-uuide0a2c0cc-f835-11ec-8a3c-005056b791aa或者mysql system cat /data/mysql/auto.cnf
[auto]
server-uuide0a2c0cc-f835-11ec-8a3c-005056b791aa2.开启GTID全局事务标识符的功能
GTID全局事务标识符的功能默认是没有开启的但是在MySQL5.7版本中会有地方字自动生成GTID。
1.开启GTID
[rootmysql ~]# vim /etc/my.cnf
[mysqld]
gtid-modeon #开启GITID
enforce-gtid-consistencytrue #强制GTID的一致性2.重启mysql
[rootmysql ~]# systemctl restart mysqld3.模拟产生Binlog日志观察开启GTID功能的区别
下面我们来模拟产生Binlog日志然后观察开启GTID功能前后在Binlog中会有什么变化。
当前的Binlog记录格式是MIXED类型俗称MBR。 3.1.模拟产生Binlog日志
开启GTID后DDL、DCL语句都会被当做一个事务并且会分配唯一的GTID号DML每一个完整的事务也都会分配一个GTID号。
由于刚刚开启GTID时重启了MySQL后面再执行SQL时就会写入到新的Binlog日志文件中执行以下操作产生新的Binlog日志。
1.创建db_3数据库
mysql create database db_3;2.在db_3数据库中创建一张表
mysql use db_3;
mysql create table table_1 (id int,name varchar(10));3.在表中插入数据、删除数据、再插入数据
mysql insert into table_1 values (1,huabf);
mysql insert into table_1 values (2,jiangxl);
mysql insert into table_1 values (3,haha);
mysql delete from table_1 where id 3;
mysql insert into table_1 values (4,ooo);3.2.观察Binlog日志中的事件信息
产生Binlog日志之后在Binlog日志的事件信息中每一个DDL语句都被看成一个事务会产生一个GTID每一个DML的事务也会产生一个GTID号。
此时查看Binlog日志的事件信息时就不需要再看事件的开始/结束标识位号只需要分析Binlog中产生的全局事务ID即可主要看Info一列无论是DDL语句还是DML语句都会产生一个事务并且都有独立的GTID号。
之前找事件的开始/结束标识位非常麻烦每一条SQL都对应一个开始/结束标识位比较庞大当使用GTID后一个事务内的SQL拥有一个GTID同时可以清晰的看到一个GTID下包含了那些数据的SQL语句截取时按照需求指定数据所在的GTID范围即可成功截取。
由于我的Binlog记录格式是MBR因此针对DML语句可以清晰的看到具体的SQL当我们要截取部分Binlog日志时只需要在Binlog中找到要恢复数据对应的SQL语句然后再找到这些SQL语句对应的GTID号根据GTID号进行截取即可。
mysql show binlog events in mysql-bin.000004;事件信息如下就如我们所说的那样每一个DDL语句都被看做是一个事务并且分配唯一的GTID号每一个DML语句的完整事务也都会分为一个唯一的GTID号GTID号的定义也是节点的UUID加全局事务ID号组成的GTID号会按照顺序自增。 3.2.观察节点状态有什么变化
mysql show master status;在查看节点状态时除了能看到当前正在使用的Binlog日志是哪个外在最后一列Executed_Gtid_Set中简单的展示了当前MySQL数据库中有多少个GTID号。
e0a2c0cc-f835-11ec-8a3c-005056b791aa当前MySQL的UUID号。
1-7这个1-7表示当前MySQL中一共有一个GTID号从1开始到7结束我们使用GTID截取Binlog日志时就可以使用这种连续的方法截取出连续DGTID事务所产生的Binlog日志。 3.3.观察Binlog日志会有什么变化
Binlog日志也会有明显的变化当使用GTID之后几乎就可以不去看事件的开始/结束标识位了如下图所示一个事务就对应一个GTID号在这个GTID内可能会包含多条SQL语句当我们需要截取部分Binlog时根据我们的需求分析要截取的部分位于那些GTID中然后根据涉及的GTID范围进行截取即可。 4.使用GTID来截取Binlog中部分日志
当Binlog中具备GTID之后就可以通过GTID来截取某些事务的BInlog日志。
4.1.使用GTID来截取Binlog日志的方法
语法格式如下
mysqlbinlog --include-gtidsGTID号范围 --exclude-gtids排除不截取的GTID号 Binlog日志路径include-gtids截取那些GTID号产生的Binlog日志可以是一个也可以是多个连续的GTID号例如我们想截取6/7/8/9/10/11这5个GTID产生的Binlog在include-gtids参数中无需填写多个完整的GTID直接写范围即可如e0a2c0cc-f835-11ec-8a3c-005056b791aa:6-11如果6-11这5个GTID中7/8是不想截取的GTID那么还可以通过 --exclude-gtids在截取过程中排除那些不想截取的GTID产生的binlog日志接着include-gtids后续举例如果6-11这5个GTID中7/8这两个GTID是不想截取的可能包含误删除操作那么就可以通过--exclude-gtids参数将7和8两个GTID排除在--exclude-gtids参数中如是连续排序的多个GTID那么也可以写成e0a2c0cc-f835-11ec-8a3c-005056b791aa:7-8这种形式如果是不连续的几个GTID在排除时只能一个一个指定了。
例子 截取9-15这些GTID但是排除11-13这三个GTID。 mysqlbinlog --include-gtidse0a2c0cc-f835-11ec-8a3c-005056b791aa:9-15 --exclude-gtidse0a2c0cc-f835-11ec-8a3c-005056b791aa:11-13 /data/mysql/mysql-bin.000006截取9-15这些GTID排除9/11这两个GTID。 mysqlbinlog --include-gtidse0a2c0cc-f835-11ec-8a3c-005056b791aa:9-15 --exclude-gtidse0a2c0cc-f835-11ec-8a3c-005056b791aa:9,e0a2c0cc-f835-11ec-8a3c-005056b791aa:11 /data/mysql/mysql-bin.0000064.2.模拟误删除的场景
下面我们来模拟误删除的场景首先创建一张表再里面正常插入数据然后误删除一条数据最后误删除整张表。
1.创建table_1表
mysql use db_3
mysql create table table_1 (id int,name varchar(10));2.正常插入的数据
mysql insert into table_1 values (1,huabf);
mysql insert into table_1 values (2,jiangxl);
mysql insert into table_1 values (3,haha);3.误删除了id为3的数据
mysql delete from table_1 where id 3;4.又正常插入了id为4的数据
mysql insert into table_1 values (4,ooo);5.又误删除了整张表
mysql drop table table_1;4.3.使用GTID来截取要恢复的Binlog日志
table_1这张表一下有两次误操作行为了我们需要通过Binlog日志来恢复误删除的数据。
1首先查看当前数据库使用的是哪个Binlog日志
使用的是mysql-bin.000006这个Binlog日志。
mysql show master status;
-------------------------------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------------------------------
| mysql-bin.000006 | 1992 | | | e0a2c0cc-f835-11ec-8a3c-005056b791aa:1-15 |
-------------------------------------------------------------------------------------------------------2确定要截取Binlog日志的那些GTID号
通过事件信息来确定我们要还原那些GTID产生的Binlog数据。
mysql show binlog events in mysql-bin.000006;我们首先找到创建table_1这张表的事务GTID号找到之后就是从这个GTID号处开始截取如下图所示创建这张表的GTID是9然后一直找到删除这张表的GTID号如下图所示删除表的GTID是15那么截取GTID号的范围就是9-15之间但是在9-15之间又包含了误删除的操作如果我们不排除掉那么恢复之后数据还是丢失的DELETE误删除的操作为13GTID号中DROP删除表的误操作行为位于15这个GTID号因此我们就可以确定要截取的GTID号范围是9-15但是在截取过程中排除掉13和15这两个GTID号。
这里为什么要一直找到删除表的GTID呢因为我们不能确定在删除表之前那条数据时该表最后的写入数据因此到删除表的GTID是最全面的截取的时候排除掉这些删除行为的GTID就可以了。 3截取日志
截取9-15这6个GTID产生的Binlog日志然后排除掉13/15这两个GTID产生的Binlog日志最后输出到SQL文件里。
[rootmysql backup]# mysqlbinlog --include-gtidse0a2c0cc-f835-11ec-8a3c-005056b791aa:9-15 --exclude-gtidse0a2c0cc-f835-11ec-8a3c-005056b791aa:13,e0a2c0cc-f835-11ec-8a3c-005056b791aa:15 /data/mysql/mysql-bin.000006 gtid-binlog.sql成功截取了9-15这些GTID产生的Binlog但是同时也排除了13和15这两个GTID产生的数据。 虽然我们成功的截取到了要恢复的Binlog日志但是此时拿着这个日志去还原数据会报错这就要说说GTID的幂等性问题了。 4.4.GTID的幂等性问题
虽然在4.3中用GTID截取除了Binlog日志也输出到SQL文件中了但是此时如果使用这个Binlog去恢复数据那么就会报错会提示还原的数据中有指定的GTID号和当前数据库的GTID冲突重复的GTID对应的事务就不会执行了这就是GTID的幂等性问题。
当我们开启了GTID还使用事件的标识位截取Binlog时也会遇到此问题。
如何解决这个问题呢其实也很简单只需要将截取的Binlog日志中关于GTID声明的语句剔除就可以了。 手动删除可能会漏mysqlbinlog的--skip-gtids参数可以跳过Binlog日志中的GTID属性跳过后我们就可以正常恢复数据了。
[rootmysql backup]# mysqlbinlog --skip-gtids --include-gtidse0a2c0cc-f835-11ec-8a3c-005056b791aa:9-15 --exclude-gtidse0a2c0cc-f835-11ec-8a3c-005056b791aa:13,e0a2c0cc-f835-11ec-8a3c-005056b791aa:15 /data/mysql/mysql-bin.000006 gtids-binlog.sql跳过GTID属性的Binlog日志和原Binlog日志是由很大区别的因此不建议手动的改直接通过--skip-gtids参数跳过即可。 4.5.利用GTID截取的Binlog还原误删除的数据。
1.临时关闭Binlog日志记录避免还原时又写一遍Binlog
mysql set sql_log_bin0;
Query OK, 0 rows affected (0.00 sec)2.恢复数据
mysql source /root/backup/gtids-binlog.sql3.查看数据是否恢复成功
mysql select * from table_1;
---------------
| id | name |
---------------
| 1 | huabf |
| 2 | jiangxl |
| 3 | haha |
| 4 | ooo |
---------------
#四条数据均已恢复
