mysql备份二进制日志还原+真实案例

1.二进制日志作用：
(1) 主从要依赖二进制日志
(2) 数据恢复时需要依赖于二进制日志
2.如何配置：
可通过在配置文件中添加：log_bin=mysql-bin
后面可以添加自己的路径.5.6版本还需添加binlog_format=row行格式的命令，然后重启服务就可以了，可以在数据库中通过show master status;进行查看。
2二进制日志记录了什么?
记录的数据库所有变更类的操作日志.ddl，dcl，dml

DDL(data definition language)：有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表(TABLE)的构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

DCL(Data Control Language)：是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括(grant,deny,revoke等)语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

DML(data manipulation language)：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

一.环境：
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。
1.备份策略：
每天23:00点，计划任务调用mysqldump执行全备脚本
2.故障时间点：
年底故障演练:模拟周三上午10点误删除数据库.
3.思路：
（1）、停业务，挂维护页,避免数据的二次伤害
（2）、找一个临时库，恢复周二23：00全备
（3）、截取周二23：00 — 周三10点误删除之间的binlog，恢复到临时库
4、测试可用性和完整性
5、
（1） 方法一：直接使用临时库顶替原生产库，前端应用割接到新库
（2） 方法二：将误删除的表导出，导入到原生产库

二.故障模拟演练

1. 准备数据,在周二全备份之前添加的数据

mysql> create database backup;
Query OK, 1 row affected (0.00 sec)

mysql> use backup
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

创建备份目录

[root@localhost /]# mkdir /backup

2. 周二 23：00全备

[root@localhost /]# mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

3. 模拟周二 23：00到周三 10点之间数据变化

mysql> insert into t1 values(11),(22),(33);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values(11),(22),(33);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

4. 模拟故障,删除表(只是模拟，不代表生产操作)
   drop database backup;
5. 恢复过程
   （1）准备临时数据库（多实例3307）

[root@localhost /]# systemctl start mysqld3307

（2）准备全备：

[root@localhost /]# cd /backup
[root@localhost /]# gunzip full_2020-3-14.sql.gz 

（3）截取二进制日志

mysqlbinlog --skip-gtids   --start-position=1704 --stop-position=2117 mysql-bin.000001 >/backup/bin.sql

这里的二进制日志文件还有pos号看的是自己的号，由于是周三早上数据丢失的，所以只用截取周二全备后的数据就可以我这里的是1704到2117
show binlog events in ‘mysql-bin.000001’;
±-----------------±-----±------------±----------±------------±-------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
±-----------------±-----±------------±----------±------------±-------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.68-MariaDB, Binlog ver: 4 |
| mysql-bin.000001 | 245 | Query | 1 | 328 | create database hehe |
| mysql-bin.000001 | 328 | Query | 1 | 417 | use hehe; create table hehe (id int) |
| mysql-bin.000001 | 417 | Query | 1 | 485 | BEGIN |
| mysql-bin.000001 | 485 | Query | 1 | 590 | use hehe; insert into hehe values(1),(2),(3),(4),(5) |
| mysql-bin.000001 | 590 | Xid | 1 | 617 | COMMIT /* xid=34 / |
| mysql-bin.000001 | 617 | Query | 1 | 685 | BEGIN |
| mysql-bin.000001 | 685 | Query | 1 | 780 | use hehe; insert into hehe values(11),(22) |
| mysql-bin.000001 | 780 | Xid | 1 | 807 | COMMIT / xid=448 / |
| mysql-bin.000001 | 807 | Query | 1 | 875 | BEGIN |
| mysql-bin.000001 | 875 | Query | 1 | 971 | use hehe; update hehe set id=100 where id=1 |
| mysql-bin.000001 | 971 | Xid | 1 | 998 | COMMIT / xid=449 / |
| mysql-bin.000001 | 998 | Query | 1 | 1066 | BEGIN |
| mysql-bin.000001 | 1066 | Query | 1 | 1156 | use hehe; delete from hehe where id=3 |
| mysql-bin.000001 | 1156 | Xid | 1 | 1183 | COMMIT / xid=454 / |
| mysql-bin.000001 | 1183 | Query | 1 | 1264 | drop database hehe |
| mysql-bin.000001 | 1264 | Query | 1 | 1351 | create database backup |
| mysql-bin.000001 | 1351 | Query | 1 | 1440 | use backup; create table t1 (id int) |
| mysql-bin.000001 | 1440 | Query | 1 | 1510 | BEGIN |
| mysql-bin.000001 | 1510 | Query | 1 | 1607 | use backup; insert into t1 values(1),(2),(3) |
| mysql-bin.000001 | 1607 | Xid | 1 | 1634 | COMMIT / xid=469 / |
| mysql-bin.000001 | 1634 | Query | 1 | 1704 | BEGIN |
| mysql-bin.000001 | 1704 | Query | 1 | 1804 | use backup; insert into t1 values(11),(22),(33) |
| mysql-bin.000001 | 1804 | Xid | 1 | 1831 | COMMIT / xid=478 / |
| mysql-bin.000001 | 1831 | Query | 1 | 1920 | use backup; create table t2 (id int) |
| mysql-bin.000001 | 1920 | Query | 1 | 1990 | BEGIN |
| mysql-bin.000001 | 1990 | Query | 1 | 2090 | use backup; insert into t2 values(11),(22),(33) |
| mysql-bin.000001 | 2090 | Xid | 1 | 2117 | COMMIT / xid=481 */ |
±-----------------±-----±------------±----------±------------±-------------------------------------------------------+
28 rows in set (0.00 sec)

（4） 恢复备份到临时库

[root@localhost /]# mysql -S /data/3307/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /backup/full_2020-03-14.sql
mysql> source /backup/bin.sql
mysql> set sql_log_bin=1;

可查看数据有没有还原到临时库中
（5） 将临时库导出并恢复到生产

[root@localhost /]# mysqldump   -S /data/3307/mysql.sock -B  backup >/backup/bak.sql
[root@localhost /]# mysql -uroot -p123 
mysql> set sql_log_bin=0;
mysql> source /backup/bak.sql;
mysql> set sql_log_bin=1;