mysql5.7-07-gtid-mysqldump+binlog恢复数据

 备份单个库或多个库

　　备份所有库，-B换-A

适用于MyISAM存储引擎。
[root@test-huanqiu ~]# mysqldump --lock-all-tables --flush-logs --master-data=2 -u root -p yanqi > backup_sunday_1_PM.sql

适用于innodb存储引擎
mysqldump -uroot -pyanqi -B -F --triggers -R -E --master-data=2 --single-transaction --set-gtid-purged=off yanqi|gzip >/opt/data/backup/yanqi_$(date +%F).sql.gz

 注意：如果时主从环境备份时去掉 --set-gtid-purged=off 参数。

 

备份单个表或多个例子3 world数据库下的city,country表

例子3 world数据库下的city,country表
mysqldump -uroot -p world city country >/backup/bak1.sql
以上备份恢复时:必须库事先存在,并且ues才能source恢复。恢复数据前先

示例：
mysqldump -uroot -p yanqi student >/tmp/yanqi.student.sql

mysql -uroot -p123
set sql_log_bin=0
use yanqi
source /tmp/yanqi.student.sql
set sql_log_bin=1
注释：直接覆盖现有的student表；

　注意：如果一次备份了多个表，后续只需要恢复其中一个表的时候，其他表也会覆盖原有的数据，谨慎使用！！！　建议单表单备份。

　　

 

1、开启gtid和binlog日志

vim /etc/my.cnf
[mysqld]

#####bin_log#####
log_bin=/opt/data/binlog/mysql-bin
binlog_format=row

#####gtid#####
gtid-mode=on
enforce-gtid-consistency=true

重启mysql服务

注意：每次mysql重启，都会调用flush logs;，新创建一个binlog日志！

 

2、查看是否开启

root@db1 (none) 08:54:29 >show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      625 |              |                  | 21967b4d-0c4d-11eb-8c72-000c2947d788:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+

　

3、常用的binlog日志操作命令

show master logs;    查看所有binlog日志列表

show master status;  查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

flush logs;  flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件
注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

reset master;  重置(清空)所有binlog日志
注意：主从模式禁止使用

　

4、恢复案例　

4.1、背景环境

正在运行的网站系统：mysql-5.7.20数据库，数据量50G，日业务增量1-5M

#备份策略
每天23:00点，计划任务调用mysqldump执行全备脚本

#故障时间点
模拟周三上午10点误删除数据库，并进行恢复

#恢复思路
1、停业务，避免数据的二次伤害
2、找一个临时库，恢复周三23:00全备
3、截取周二23:00 至 周三10点误删除之间的binlog日志，恢复到临时库
4、测试可用性和完整性
5、方法一：直接使用临时库顶替原生产库，前端应用割接到新库；
   方法二：将误删除的表导出，导入到原生产库；
6、开启业务

　

4.2、创建数据

#建库
create database yanqi;
use yanqi;

#建表
create table student(id int primary key,name char(8) not null,age int,class_id int not null);
create table class(id int primary key,cname char(20) not null);

#插入数据
insert into class values(1,'一班'),(2,'二班');
insert into student values(1,'李1',18,1),(2,'李2',45,1),(3,'李3',30,2);　

　再建一个库吧

#建库
create database aaa;
use aaa;

#建表
create table student(id int primary key,name char(8) not null,age int,class_id int not null);
create table class(id int primary key,cname char(20) not null);

#插入数据
insert into class values(1,'1班'),(2,'2班');
insert into student values(1,'a1',18,1),(2,'a2',45,1),(3,'a3',30,2);

　

4.3、全备yanqi库

mysqldump -uroot -pyanqi -B -F --triggers -R -E --master-data=2 --single-transaction --set-gtid-purged=off yanqi|gzip >/opt/data/backup/yanqi_$(date +%F).sql.gz

-----------------
参数说明：
--set-gtid-purged=off    单库备份时使用
-A 全备
-B 数据库名   //指定要备份数据库
--triggers 备份触发器
-R 备份存储过程及函数
-E 备份事
-F 刷新binlog日志，生成新文件，将来增量恢复从这个文件开始
在备份完数据后执行改操作。
--master-data=2 备份时记录binglogpod节点
--single-transaction 支持对innodb的热备
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
gzip  对备份数据进行压缩
-----------------

注释：因为使用了-F 参数，数据备份操作刚开始时就刷新了binlog，这样就会产生一个新的binlog日志。这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作。

4.4、继续写入数据

insert into yanqi.student values('4','李4','40','2');
insert into yanqi.student values('5','李5','50','1');

insert into aaa.student values('4','a4','40','1');
insert into aaa.student values('5','a5','50','1');


use aaa;
insert into student values(8,'a8',88,1);

use yanqi
insert into student values(8,'李8',88,1);


update yanqi.student set name='黄5' where id=5;
update aaa.student set name='黄5' where id=5;

　　操作后的库信息如下：

root@db1 yanqi 01:01:15 >select * from class;
+----+--------+
| id | cname  |
+----+--------+
|  1 | 一班   |
|  2 | 二班   |
+----+--------+
2 rows in set (0.00 sec)

root@db1 yanqi 01:01:21 >select * from student;
+----+------+------+----------+
| id | name | age  | class_id |
+----+------+------+----------+
|  1 | 李1  |   18 |        1 |
|  2 | 李2  |   45 |        1 |
|  3 | 李3  |   30 |        2 |
|  4 | 李4  |   40 |        2 |
|  5 | 黄5  |   50 |        1 |
|  8 | 李8  |   88 |        1 |
+----+------+------+----------+
6 rows in set (0.01 sec)



root@db1 aaa 12:59:27 >select * from class;
+----+-------+
| id | cname |
+----+-------+
|  1 | 1班   |
|  2 | 2班   |
+----+-------+
2 rows in set (0.00 sec)

root@db1 aaa 12:59:41 >select * from student;
+----+------+------+----------+
| id | name | age  | class_id |
+----+------+------+----------+
|  1 | a1   |   18 |        1 |
|  2 | a2   |   45 |        1 |
|  3 | a3   |   30 |        2 |
|  4 | a4   |   40 |        1 |
|  5 | 黄5  |   50 |        1 |
|  8 | a8   |   88 |        1 |
+----+------+------+----------+
6 rows in set (0.00 sec)

　4.5、误操作，删yanqi库

root@db1 yanqi 01:02:41 >drop database yanqi;
Query OK, 2 rows affected (0.03 sec)

　

恢复过程：

先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；

1、 先备份一下最后一个binlog日志文件：

[root@db1 binlog]# cp -v mysql-bin.000004 /tmp
[root@db1 binlog]# ll /tmp/mysql-bin.000004 
-rw-r----- 1 root root 2598 Oct 14 13:07 /tmp/mysql-bin.000004

 

2、恢复备份数据

恢复数据前要关闭binlog记录： set sql_log_bin=0;

#解压全备份文件
[root@db1 backup]# gzip -cd yanqi_2020-10-14.sql.gz >yanqi.sql
[root@db1 backup]# ll
total 8
-rw-r--r-- 1 root root  999 Oct 14 12:30 yanqi_2020-10-14.sql.gz
-rw-r--r-- 1 root root 2958 Oct 14 13:22 yanqi.sql


root@db1 (none) 01:12:26 >show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

root@db1 (none) 01:18:12 >set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

root@db1 (none) 01:18:32 >system ls -lrt /opt/data/backup
total 8
-rw-r--r-- 1 root root  999 Oct 14 12:30 yanqi_2020-10-14.sql.gz
-rw-r--r-- 1 root root 2958 Oct 14 13:22 yanqi.sql
root@db1 (none) 01:23:12 >source /opt/data/backup/yanqi.sql

root@db1 yanqi 01:25:29 >show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
| yanqi              |
+--------------------+
6 rows in set (0.00 sec)

　这时只是恢复了，备份之前的数据，接下来恢复binlog内的数据。

 3、读取binlog获取数据

查看备份最后的截止事件点：
[root@db1 backup]# grep "MASTER_LOG_POS" /opt/data/backup/yanqi.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=194;

root@db1 yanqi 01:29:50 >show binlog events in 'mysql-bin.000004' from 194;

 

 可以看出：我们需要恢复18~24之间的操作，25最为误操作不恢复。

4、截取binlog，去除不要的数据

mysqlbinlog --skip-gtids --include-gtids='21967b4d-0c4d-11eb-8c72-000c2947d788:18-25' /opt/data/binlog/mysql-bin.000004 >/tmp/yanqi.sql

说明：如果binlog中存在多个不联系的误操作需要剔除可以使用下边这个命令

mysqlbinlog --skip-gtids --include-gtids='7b1b726a-ee6a-11ea-aa98-000c2943b95b:18-23'  --exclude-gtids='7b1b726a-ee6a-11ea-aa98-000c2943b95b:22',7b1b726a-ee6a-11ea-aa98-000c2943b95b:19' /data/binlog/mysql-bin.000004 >/tmp/binlog.sql

　注：这个语句示例：要恢复18至23中间的数据，22和19以外的数据。

　

5、导入数据到yanqi库内

恢复完毕数据后，记得开启binlog日志：set sql_log_bin=1;

root@db1 yanqi 01:45:56 >use yanqi
root@db1 yanqi 01:45:56 >source /tmp/yanqi.sql
root@db1 yanqi 01:51:48 >set sql_log_bin=1;

root@db1 yanqi 01:46:11 >select * from student;
+----+------+------+----------+
| id | name | age  | class_id |
+----+------+------+----------+
|  1 | 李1  |   18 |        1 |
|  2 | 李2  |   45 |        1 |
|  3 | 李3  |   30 |        2 |
|  4 | 李4  |   40 |        2 |
|  5 | 黄5  |   50 |        1 |
|  8 | 李8  |   88 |        1 |
+----+------+------+----------+
6 rows in set (0.00 sec)

　　

 备份时限制备份包大小（未实际测试）

max_allowed_packet=128M

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