mysql备份-恢复模拟演练2--通过Gtid记录切割日志

最新推荐文章于 2025-12-08 03:51:29 发布

原创最新推荐文章于 2025-12-08 03:51:29 发布 · 288 阅读

0 ·

CC 4.0 BY-SA版权

数据库和中间件专栏收录该内容

22 篇文章

订阅专栏

本文详细介绍了如何在MySQL中配置GTID模式，进行全量备份，并结合binlog实现增量数据恢复，包括创建数据库、表，数据操作，全备流程，binlog切割与数据恢复步骤。

1、修改my.cnf文件和创建一个数据库 tangbin

vim /etc/my.cnf 开启gtid


[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
#关闭自动提交
autocommit=0
log_error=/tmp/mysql3306.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
#开启GTID模式
gtid-mode=on
enforce-gtid-consistency=true
## 开关
slow_query_log=1
## 文件位置及名字
slow_query_log_file=/data/mysql/slow.log
## 设定慢查询时间
long_query_time=0.1
## 没走索引的语句也记录
log_queries_not_using_indexes

[mysql]
socket=/tmp/mysql.sock

重启 /etc/init.d/mysqld restart

mysql> create database tangbin charset utf8mb4;

2、在tangbin下创建一张表t1

   mysql> use tangbin;
   mysql> create table t1(id int);

3、插入5行任意数据

   mysql> insert into t1 values (1),(2),(3),(4),(5);
   commit;

4、全备

 mysqldump -uroot -p1 -A -R -E --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

 gunzip full_2019-11-04.sql.gz

vim gunzip full_2019-11-04.sql

SET @@GLOBAL.GTID_PURGED='701fb2d3-fa49-11e9-a10f-000c29b6cbc2:1-41';
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=5418;

5、插入两行数据，任意修改3行数据，删除1行数据

   mysql>insert into t1 values (10),(10);
    mysql>commit;
   mysql> update t1 set id=10 where id>4;
    mysql>commit;
    mysql>delete from t1 where id=4;
    mysql>commit;

mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
| 10 |
| 10 |
+------+
6、删除所有数据

  mysql>delete from t1;
  mysql>commit;

7、再t1中又插入5行新数据，修改3行数据

mysql> insert into t1 values (1),(2),(3),(4),(5);
mysql>commit;
mysql>update t1 set id=10 where id>2;
mysql>commit;

mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 10 |
| 10 |
| 10 |
+------+

查看binlog

mysql> show binlog events in 'mysql-bin.000011';

得到删除行操作gtid记录为45
SET @@SESSION.GTID_NEXT= '701fb2d3-fa49-11e9-a10f-000c29b6cbc2:45'

共提交了6次从42 到47

8、通过gtid记录切割日志
--skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息。
--include-gtids 指定需要回滚的gtid,支持gtid的单个和范围两种形式。
--exclude-gtids 指定不需要回滚的gtid，用法同include-gtids

mysqlbinlog --skip-gtids --include-gtids='701fb2d3-fa49-11e9-a10f-000c29b6cbc2:42-47' --exclude-gtids='701fb2d3-fa49-11e9-a10f-000c29b6cbc2:45' /data/binlog/mysql-bin.000011>/backup/bin.sql

vim /backup/bin.sql 检查以下

9、恢复数据

mysql -uroot -p 
set sql_log_bin=0; #临时关闭记录binlog
source /backup/full_2019-11-04.sql
source /backup/bin.sql
set sql_log_bin=1;#开启记录binlog

mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 10 |
| 10 |
| 10 |
| 10 |
| 1 |
| 2 |
| 3 |
| 10 |
| 10 |

扩展参数 ***
在构建主从时,使用AUTO/ON
--set-gtid-purged=AUTO/ON

仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=OFF

SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';

--max_allowed_packet=128M 控制的是备份时传输数据包的大小.

mysqldump -uroot -p123 -A -R --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz