Mysql根据binlog恢复数据

一、准备数据

#创建db
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create database db2;
Query OK, 1 row affected (0.00 sec

#db1创建表并插入测试数据
mysql> use db1;
Database changed

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

mysql> insert into ti values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from ti;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

#db2创建表并插入数据
mysql> use db2;
Database changed
mysql> create table t2(id int);
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

---

二、模拟删除数据

mysql> drop table ti;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

---

三、使用binlog恢复

使用mysqlbinlog工具查看日志的起始点

mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000008
#起点
# at 2787
#211108 13:26:12 server id 1  end_log_pos 2878 CRC32 0x0357015f 	Query	thread_id=exec_time=0	error_code=0
SET TIMESTAMP=1636349172/*!*/;
create database db1
#结束
# at 3722
#211108 13:29:20 server id 1  end_log_pos 3753 CRC32 0x958a7d9c 	Xid = 230
COMMIT/*!*/;

截取这一段日志，但是我们会发现这一段日志不只有db1的操作还有db2的操作 我们需要截取db1库的 所以需要添加参数-d

mysqlbinlog -d db1 --start-position=2787 --stop-position=3722 /application/mysql/data/mysql-bin.000008 >/tmp/back2.sql

登陆db导入数据

#先关闭binlog记录
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

#导入截取的binlog
mysql> source /tmp/back2.sql;
Query OK, 0 rows affected (0.00 sec)

#查看数据已经恢复
mysql> use db1;
Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| ti            |
+---------------+
1 row in set (0.00 sec)

mysql> select * from ti;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

当然这个方法其实局限性比较大只能恢复近期的数据 如果是很早之前创建的库的话 代价就比较大了

• 1）可以用备份恢复+短时间内二进制日志，恢复到故障之前
• 2）非官方方法，binlog2sql，binlog取反，类似于Oracle的flushback
• 3）延时从库

---

四、binlog日志的日常操作

#物理查看
[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.000007';

设置binlog保留时间

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

清除binlog

#根据时间删除
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

#根据文件名删除
PURGE BINARY LOGS TO 'mysql-bin.000010';

#重置binlog
mysql> reset master;