Mysql使用mysqlbinlog恢复数据

一、关于mysqlbinlog

mysqlbinlog是数据库的二进制文件,开启mysqlbinlog日志后,平时我们操作的sql语句例如增删改,都会被记录到日志文件中,如果我们误删某条记录数据表数据库,只要我们合理的使用mysqlbinlog,都能对其进行恢复。

二、开启mysqlbinlog

2.1 查看是否开始mysqlbinlog
mysql> show variables like 'log_%';
+----------------------------------------+----------------------------------+
| Variable_name                          | Value                            |
+----------------------------------------+----------------------------------+
| log_bin                                | ON                               |
| log_bin_basename                       | /www/server/data/mysql-bin       |
| log_bin_index                          | /www/server/data/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                              |
| log_bin_use_v1_row_events              | OFF                              |
| log_error                              | ./VM_0_10_centos.err             |
| log_output                             | FILE                             |
| log_queries_not_using_indexes          | OFF                              |
| log_slave_updates                      | OFF                              |
| log_slow_admin_statements              | OFF                              |
| log_slow_slave_statements              | OFF                              |
| log_throttle_queries_not_using_indexes | 0                                |
| log_warnings                           | 1                                |
+----------------------------------------+----------------------------------+
13 rows in set (0.00 sec)

2.2 开启mysqlbinlog

windows开启方式:

mysql配置文件中新增

log-bin=D:\phpStudy\PHPTutorial\MySQL\data    //这是保存mysqlbinlog日志的路径
binlog-format=mixed  

linux开启方式:

mysql配置文件中新增

log-bin=mysql-bin
binlog_format=mixed

其中,mysqlbinlog的三个参数及含义:

参数名 含义
Row 日志中会记录成每一行数据被修改的形式,会产生大量的日志数据
Statement 日志中每条数据被修改后就才会被记录到日志里 ,弥补了Row的不足
mixed 两者相结合的形式,根据实际场景来进行相应的记录,减少I/O消耗,增加性能

mysqlbinlog三个参数更详细的介绍

2.3 重启mysql服务

三、恢复数据前的数据准备

3.1 选择一个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| RedLetter          |
| RedPacket          |
| bishe              |
| brushorder         |
| caipiao            |
| daijia             |
| dht                |
| linmaocheng        |
| ljk                |
| mysql              |
| mytool             |
| performance_schema |
| smallfox           |
| studyfast          |
| yuerjia            |
+--------------------+
16 rows in set (0.00 sec)

使用dht这个数据库:

mysql> use dht
Database changed
3.2 选择一个数据表
mysql> show tables;
+---------------+
| Tables_in_dht |
+---------------+
| content       |
| user          |
+---------------+
2 rows in set (0.00 sec)

使用user表进行测试:

mysql> select * from user;
+----+-----------+--------+
| id | name      | pwd    |
+----+-----------+--------+
|  1 | 小王      | 123    |
|  2 | username  | 123456 |
|  3 | root      | 123456 |
|  4 | 侯亮平    | 123456 |
+----+-----------+--------+
4 rows in set (0.00 sec)
3.3 添加一条测试数据
mysql> insert into user(name,pwd) values('光头强',123321);
Query OK, 1 row affected (0.00 sec)

查看是否添加上:

mysql> select * from user;
+----+-----------+--------+
| id | name      | pwd    |
+----+-----------+--------+
|  1 | 小王      | 123    |
|  2 | username  | 123456 |
|  3 | root      | 123456 |
|  4 | 侯亮平    | 123456 |
|  5 | 光头强    | 123321 |
+----+-----------+--------+
5 rows in set (0.00 sec)
3.4 删除刚刚添加的测试数据
mysql> delete from user where id = 5;
Query OK, 1 row affected (0.00 sec)

查看是否删除成功:

mysql> select * from user;
+----+-----------+--------+
| id | name      | pwd    |
+----+-----------+--------+
|  1 | 小王      | 123    |
|  2 | username  | 123456 |
|  3 | root      | 123456 |
|  4 | 侯亮平    | 123456 |
+----+-----------+--------+
4 rows in set (0.00 sec)

发现光头强这条数据已经没有了,下面就进行数据恢复。

四、进行数据恢复

查看最近的操作被保存到哪个日志里面去了:show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000038 |      688 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看日志偏移信息:show binlog events in 'mysql-bin.000038';

mysql> show binlog events in 'mysql-bin.000038';
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                             |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
| mysql-bin.000038 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.44-log, Binlog ver: 4                            |
| mysql-bin.000038 | 120 | Query       |         1 |         197 | BEGIN                                                            |
| mysql-bin.000038 | 197 | Intvar      |         1 |         229 | INSERT_ID=5                                                      |
| mysql-bin.000038 | 229 | Query       |         1 |         354 | use `dht`; insert into user(name,pwd) values('光头强',123321)    |
| mysql-bin.000038 | 354 | Query       |         1 |         432 | COMMIT                                                           |
| mysql-bin.000038 | 432 | Query       |         1 |         509 | BEGIN                                                            |
| mysql-bin.000038 | 509 | Query       |         1 |         610 | use `dht`; delete from user where id = 5                         |
| mysql-bin.000038 | 610 | Query       |         1 |         688 | COMMIT                                                           |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
8 rows in set (0.00 sec)

在这里插入图片描述
可以看到Pos为120,End_log_pos为432的偏移量正是插入这条数据的BEGINCOMMIT

[root@VM_0_10_centos /]# /www/server/mysql/bin/mysqlbinlog  /www/server/data/mysql-bin.000038 --start-position 120 --stop-position 432 | mysql -u root -p dht;
Enter password: 
[root@VM_0_10_centos /]#

执行完成后,查看是否已经恢复:

mysql> select  * from user;
+----+-----------+--------+
| id | name      | pwd    |
+----+-----------+--------+
|  1 | 小王      | 123    |
|  2 | username  | 123456 |
|  3 | root      | 123456 |
|  4 | 侯亮平    | 123456 |
|  5 | 光头强    | 123321 |
+----+-----------+--------+
5 rows in set (0.00 sec)

我们看到光头强这个测试数据已经恢复了,这个只是恢复数据实战,如果你误删数据表或者数据库,同样可以用此方法进行恢复,找到对应的偏移量即可。

五、总结

  • 如果偏移量过大的话,可以把日志文件下载下来进行搜索建表建库时候的sql语句,方便查询对应的偏移量进行恢复。
  • 如果,你删了一个库,那这个偏移量是很大的,你只需要找到对应的偏移量,不需要担心恢复之前删掉别的表的数据,毕竟负负得正嘛。
  • 找到相应的偏移量非常重要,只有精准的找到需要恢复的数据偏移量,我们能够很大程度的减少数据丢失的损失。
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值