二进制日志-数据恢复

二进制日志-数据恢复

作用:记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录
记录所有DDL、DCL等语句
总之，二进制日志会记录所有对数据库发生修改的操作

二进制日志模式:
statement：语句模式,将update语句进行记录（默认模式）。
row：行模式，即数据行的变化过程.
mixed：以上两者的混合模式。
企业推荐使用row模式

优缺点:

statement模式：

优点：简单明了，容易被看懂，就是sql语句，记录时不需要太多的磁盘空间。
缺点：记录不够严谨。

row模式：

优点：记录更加严谨。
缺点：有可能会需要更多的磁盘空间，不太容易被读懂。

binlog的作用:

1）如果我拥有数据库搭建开始所有的二进制日志，那么我可以把数据恢复到任意时刻
2）数据的备份恢复
3）数据的复制

*在mysql5.7中开启binlog必须要加上server-id

[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row
server_id=7

#物理查看
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql      285 Mar  6  2017 mysql-bin.000001
#命令行查看
mysql> show binary logs;
mysql> show master status;
#查看binlog事件
mysql> show binlog events in 'mysql-bin.000001';

[root@db01 data]# mysqlbinlog mysql-bin.000001   #目录要一致,如果在my.cnf里写的/usr/local/mysql/data/mysql-bin.000001,那就保持一致

事件（event）特性

1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。

row模式下二进制日志分析及数据恢复

#查看binlog信息
mysql> show master status;
#创建一个zz库
mysql> create database zz;
#使用zz库
mysql> use zz;
#创建zz_table表
mysql> create table zz_table(id int);
#查看binlog信息
mysql> show master status;
#插入数据1
mysql> insert into zz_table values(1);
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#查看binlog信息
mysql> show master status;
#插入数据2
mysql> insert into zz_table values(2);
#插入数据3,4,5
mysql> insert into zz_table values(3);
mysql> insert into zz_table values(4);
mysql> insert into zz_table values(5);
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#删除数据1
mysql> delete from zz_table where id=4;
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#查看数据
mysql> select * from zz_table;
#删表
mysql> drop table zz_table;     #也可以用delete
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1337 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#删库
mysql> drop database zz;

可以恢复在之前的任意自己想要的那个状态

我选择恢复到删除之前

#进入data目录查看binlog
[root@db01 ~]# cd /usr/local/mysql/data/
[root@db01 data]# ll 
#使用mysqlbinlog来查看
[root@db01 data]# mysqlbinlog mysql-bin.000001        #目录要一致,如果在my.cnf里写的/usr/local/mysql/data/mysql-bin.000001,那就保持一致
#截取二进制日志,放入自定义文件
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=1412 mysql-bin.000001 > /tmp/zz.sql
#临时关闭binlog
mysql> set sql_log_bin=0;
#执行sql文件
mysql> source /tmp/binlog.sql
#查看删除的库
mysql> show databases;
#进binlog库
mysql> use zz;
#查看删除的表
mysql> show tables;
#查看表中内容
mysql> select * from zz_table;

存在问题:

数据库或表被误删除的是很久之前创建的（一年前）
如果基于binlog全量恢复，成本很高
1）可以用备份恢复+短时间内二进制日志，恢复到故障之前
2）非官方方法，binlog2sql，binlog取反，类似于Oracle的flushback
3）延时从库如果同一时间内和故障库无关的数据库都有操作，在截取binlog时都会被截取到
想一个办法过滤出来？
1）grep？
其他过滤方案？
1）-d 参数接库名

#刷新一个新的binlog
mysql> flush logs;
#创建db1库
mysql> create database db1;

#库db1操作
mysql> use db1
#创建t1表
mysql> create table t1(id int);
#插入5条数据
mysql> insert into t1 values(1),(2),(3),(4),(5);
#提交
mysql> commit;

#查看binlog事件
mysql> show binlog events in 'mysql-bin.000014';
#查看db1的操作
[root@db01 data]# mysqlbinlog -d db1 --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000014

刷新binlog日志
1）flush logs;
2）重启数据库时会刷新
3）二进制日志上限（max_binlog_size）

删除二进制日志
1）原则
在存储能力范围内，能多保留则多保留
基于上一次全备前的可以选择删除

• 1.根据存在时间删除日志

#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

• 2.使用purge命令删除

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

• 3.根据文件名删除

PURGE BINARY LOGS TO 'mysql-bin.000010';

• 4.使用reset master

mysql> reset master;