通过二进制日志恢复mysql数据

1. 实验环境

服务器操作系统为centos7
mariadb版本为：5.5.68

2. 开启mysql服务的二进制日志

2.1 修改mysql服务的配置文件

vim /etc/my.cnf

在mysqld部分下加一行 log-bin=mysql-bin

3. 开启mysql服务的慢日志

3.1 修改mysql服务的配置文件

vim /etc/my.cnf

在mysqld部分下加慢日志的以下参数

slow_query_log=1 慢查询开启状态，ON开启，OFF关闭
slow_query_log_file=/var/run/mysqld/mysqld-slow.log  慢查询日志存放的位置
long_query_time=1   查询超过多少秒才记录

修改完成之后保存退出

3.2 重启mysql服务

systemctl restart mariadb

4. 进行数据的创建和恢复

4.1 登录mysql数据库进行建库建表的操作

[root@host-137 ~]# mysql -uroot -p456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database school;  创建school数据库
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use school;   使用school数据库
Database changed
MariaDB [school]> create table stu(id int,name varchar(20));   创建stu表
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> insert into stu values(1,'tom');  向表中插入数据tom
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into stu values(2,'jack');   向表中插入数据jack
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into stu values(3,'frank');   向表中插入数据frank
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into stu values(4,'lucy');   向表中插入数据lucy
Query OK, 1 row affected (0.01 sec)

MariaDB [school]> drop database school;   删除school数据库
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;  验证成功删除
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.01 sec)

4.2 根据二进制日志中的position参数进行恢复

mysql二进制日志的存放位置为：/var/lib/mysql/mysql-bin.000001

4.2.1 需求：恢复从创建school数据库到向stu表中插入jack用户的数据的操作

4.2.2 先使用mysqlbinlog查看mysql的二进制日志文件

mysqlbinlog /var/lib/mysql/mysql-bin.000001

找出要恢复操作的起始位置和结束位置

4.2.3 根据起始位置和结束位置进行恢复

 mysqlbinlog --start-position=245  --stop-position=895   /var/lib/mysql/mysql-bin.000001 |mysql -uroot -p456

4.2.4 查看恢复效果

MariaDB [(none)]> show databases; 查看school数据库恢复成功
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [school]> show tables;  查看stu表恢复成功
+------------------+
| Tables_in_school |
+------------------+
| stu              |
+------------------+
1 row in set (0.00 sec)

MariaDB [school]> select * from stu;   查看stu表中的数据恢复成功
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
|    2 | jack |
+------+------+
2 rows in set (0.00 sec)

4.3 根据二进制日志中的时间参数进行恢复

4.3.1 删除上面恢复的school数据库

MariaDB [school]> drop database school;
Query OK, 1 row affected (0.01 sec)

4.3.2 使用mysqlbinlog查看mysql的二进制日志文件

mysqlbinlog /var/lib/mysql/mysql-bin.000001

找出要恢复操作的起始时间和结束时间

4.3.3 根据起始时间和结束时间进行恢复

mysqlbinlog --start-datetime="2021-01-11 16:46:39"  --stop-datetime="2021-01-11 16:50:28"   /var/lib/mysql/mysql-bin.000001 |mysql -uroot -p456

4.3.4 查看恢复效果

MariaDB [(none)]> show databases;   查看school数据库恢复成功
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| stu              |
+------------------+
1 row in set (0.00 sec)

MariaDB [school]> select * from stu;   查看stu表中的数据恢复成功
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jack  |
|    3 | frank |
|    4 | lucy  |
+------+-------+
4 rows in set (0.01 sec)