mysql通过binlog去恢复被删除的数据

问题:因为有些误操作而导致数据不小心被删除，这个时候就比较麻烦，可以利用binlog来进行数据恢复。

binlog介绍:

mysql-binlog是MySQL数据库的二进制日志，用于记录用户对数据库操作的SQL语句（(除了数据查询语句）信息。可以使用mysqlbin命令查看二进制日志的内容。

binlog的格式也有三种：STATEMENT、ROW、MIXED 。

1、STATMENT模式：基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。

优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。

缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

2、基于行的复制(row-based replication, RBR)：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。

缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3、混合模式复制(mixed-based replication, MBR)：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式
(来自linux运维菜)

配置binlog

1.开启参数
vim /etc/my.cnf

server_id=4
log_bin=/data/mysql/binlog/mysql-bin

说明:
/mysql_data/binlog/mysql-bin: 提前定制好的目录,而且要有mysql.mysql的权限
mysql-bin : 二进制日志文件名的前缀

2.创建目录和设置权限
mkdir -p /data/mysql/binlog/mysql-bin
chown -R mysql.mysql /data/mysql/binlog/mysql-bin

3.重启
/etc/init.d/mysqld restart

查看binlog

1.查看二进制日志位置
mysql>show variables like ‘%log_bin%’;

2.查看所有已存在的二进制日志
mysql>show binary logs;

3.查看正在使用的二进制日志
mysql>show master status;

4.查看二进制日志事件，
4.1先创建数据库binlog
mysql>create database binlog charset utf8mb4;
mysql>use binlog
mysql>create table t1(id int);
mysql>insert into t1 values(1);
4.2查看使用的二进制日志
mysql> show master status ;
4.3查看binlog
mysql> show binlog events in ‘mysql-bin.000002’ limit 5;
5.查看二进制日志内容
mysqlbinlog mysql-bin.000002
mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
mysqlbinlog -d haoge mysql-bin.000002

6.模拟binlog恢复数据

模拟数据:
mysql>create database haoge charset utf8mb4;
mysql> use haoge;
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> commit;
模拟故障:
mysql>drop database haoge;
分析和截取binlog
mysqlbinlog --start-position=219 --stop-position=1420 /data/binlog/mysql-bin.000004 >/tmp/bin.sql
219为刚执行命令的时候，1420为最后一条执行命令的时候

7.恢复binlog
mysql> set sql_log_bin=0; —>临时关闭恢复时产生的新日志
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1; set sql_log_bin=1; —>改回来

8.查看数据库,数据已经恢复
mysql>show databses;