如何利用MySQL的binlog恢复误删数据库详解

目录
  • 1 查看当前数据库内容并备份数据库
  • 2 开启bin_log功能
  • 3 模拟误操作(插入3条数据,删除数据库)
  • 4 数据恢复
  • 5 总结

1 查看当前数据库内容并备份数据库

查看数据库信息:

备份数据库:

[root@localhost ~]# mysqldump -u root -p t > /mnt/t.sql
Enter password:
[root@localhost ~]# ll /mnt/t.sql
-rw-r--r-- 1 root root 1771 Aug 25 11:56 /mnt/t.sql

2 开启bin_log功能

首先查看数据库是否开启bin_log功能

mysql> show variables like "%log_bin%";

需要修改mysql的配置文件,/etc/的my.cnf,添加一句log_bin = mysql_bin即可

3 模拟误操作(插入3条数据,删除数据库)

mysql> insert into t1 values (3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (5);
Query OK, 1 row affected (0.00 sec)

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

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000003 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

删除数据:

mysql> truncate t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

此时突然数据库损坏或者人为删除

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

4 数据恢复

1 用已经备份的/mnt/t.sql来恢复数据

mysql> source /mnt/t.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------+
| Tables_in_t |
+-------------+
| t1          |
+-------------+
1 row in set (0.00 sec)

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

2 还有三条数据没有恢复,怎么办。只能用bin-log来恢复

[root@localhost ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql_bin.000002 | mysql -u root -p123.com t
mysql> use t;
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
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

mysql>

5 总结

备份数据

mysqldump -uroot -p123456 test -l -F '/tmp/test.sql'
-l:读锁(只能读取,不能更新)
-F:即flush logs,可以重新生成新的日志文件,当然包括log-bin日志

查看binlog日志

mysql>show master status;

导入之前备份数据

mysql -uroot -p t -v -f </mnt/t.sql
-v查看导入的详细信息
-f是当中间遇到错误时,可以skip过去,继续执行下面的语句

恢复binlog-file二进制日志文件

mysqlbinlog --no-defaults binlog-file | mysql -uroot -p t

从某一(367)点开始恢复

mysqlbinlog --no-defaults --stop-position="367" mysql-bin.000001| mysql -uroot -p t

先查好那一点,用more来查看

[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | more

然后恢复

[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | /usr/bin/mysql -uroot -p t

重置binlog日志

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+
mysql> flush logs;#关闭当前的二进制日志文件并创建一个新文件,新的二进制日志文件的名字在当前的二进制文件的编号上加1。

到此这篇关于如何利用MySQL的binlog恢复误删数据库的文章就介绍到这了,更多相关MySQL binlog恢复误删数据库内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • mysql5.7使用binlog 恢复数据的方法

    第一步:保证mysql已经开启binlog show variables like '%log_bin%'; log_bin 为 on是开启. 第二步:进入binlog文件目录,找到二进制日志文件 mysql> show binary logs; #获取binlog文件列表 mysql> show master status: #查看当前正在写入的binlog文件 mysql> reset master; 重置binlog 第三步: 通过mysqlbinlog工具命令查看数据库增删改查记

  • Linux上通过binlog文件恢复mysql数据库详细步骤

     一.binlog 介绍 服务器的二进制日志记录着该数据库的所有增删改的操作日志(前提是要在自己的服务器上开启binlog),还包括了这些操作的执行时间.为了显示这些二进制内容,我们可以使用mysqlbinlog命令来查看. 用途1:主从同步 用途2:恢复数据库(也是线上出现一次数据库文件丢失后,才对这个有所了解并学习的) mysqlbinlog命令用法:shell> mysqlbinlog [options] log_file ... <!--[if !supportLists]-->

  • mysql如何利用binlog进行数据恢复详解

    前言 最近线上误操作了一个数据,由于是直接修改的数据库,所有唯一的恢复方式就在mysql的binlog.binlog使用的是ROW模式,即受影响的每条记录都会生成一个sql.同时利用了binlog2sql项目. MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个: * 数据回复 * 主从数据库.用于slave端执行增删改,保持与master同步. binlog基本配置和格式 binlog基本配置 binlog需要在mysql

  • Mysql的Binlog数据恢复:不小心删除数据库详解

    Mysql的Bin log数据恢复:不小心删除数据库 前言:因为不小心删除了测试机器上Mysql的一整个数据库Schema,因为是测试机所以没有做备份,现在通过MySQL的Bin log方式恢复到删除以前的数据库. 当然做Bin log的数据恢复前提是已经打开Bin log的功能,如果又没做数据备份,又没打开Bin log日志,那你就可能需要考虑快照等其它方式从系统的角度去恢复. Bin log 常用于数据增量备份和恢复,以及数据库主从复制.如果没有开启,可以通过如下方式打开: 1.打开mysq

  • MySQL使用binlog日志做数据恢复的实现

    MySQL的binlog日志是MySQL日志中非常重要的一种日志,记录了数据库所有的DML操作.通过binlog日志我们可以进行数据库的读写分离.数据增量备份以及服务器宕机时的数据恢复. 定期备份固然可以在服务器发生宕机的时候快速的恢复数据,但传统的全量备份不可能做到实时,所以在发生宕机的时候,也会损伤一部分数据,如果这个时候开启了binlog日志,那么可以通过binlog来对没有做备份的这一阶段损失的数据进行恢复.如果了解Redis的朋友,可能想到了,Redis有两种持久化方式,分别是AOF和

  • MySQL使用mysqldump+binlog完整恢复被删除的数据库原理解析

    (一)概述 在日常MySQL数据库运维过程中,可能会遇到用户误删除数据,常见的误删除数据操作有: 用户执行delete,因为条件不对,删除了不应该删除的数据(DML操作): 用户执行update,因为条件不对,更新数据出错(DML操作): 用户误删除表drop table(DDL操作): 用户误清空表truncate(DDL操作): 用户删除数据库drop database,跑路(DDL操作) -等 这些情况虽然不会经常遇到,但是遇到了,我们需要有能力将其恢复,下面讲述如何恢复. (二)恢复原理

  • 解说mysql之binlog日志以及利用binlog日志恢复数据的方法

    众所周知,binlog日志对于mysql数据库来说是十分重要的.在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlog日志恢复增量数据部分),化险为夷! 废话不多说,下面是梳理的binlog日志操作解说: 一.初步了解binlog MySQL的二进制日志binlog可以说是MySQL最重要的日志,它记录了所有的DDL和DML语句(除了数据查询语句select),以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的. ---

  • 教你自动恢复MySQL数据库的日志文件(binlog)

    如果MySQL服务器启用了二进制日志,你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如,从你最后一次备份)直到现在或另一个指定的时间点的数据."mysqlbinlog:用于处理二进制日志文件的实用工具". 要想从二进制日志恢复数据,你需要知道当前二进制日志文件的路径和文件名.一般可以从选项文件(即my.cnf or my.ini,取决于你的系统)中找到路径.如果未包含在选项文件中,当服务器启动时,可以在命令行中以选项的形式给出.启用二进制日志的选项为 --log-b

  • MySQL数据库恢复(使用mysqlbinlog命令)

    1:开启binlog日志记录 修改mysql配置文件mysql.ini,在[mysqld]节点下添加 复制代码 代码如下: # log-bin log-bin = E:/log/logbin.log 路径中不要包含中文和空格.重启mysql服务.通过命令行停止和启动mysql服务 复制代码 代码如下: c:\>net stop mysql; c:\>net start mysql; 进入命令行进入mysql并查看二进制日志是否已经启动 Sql代码 复制代码 代码如下: mysql>sho

  • MySQL中的binlog相关命令和恢复技巧

    操作命令: 复制代码 代码如下: show binlog events in 'mysql-bin.000016' limit 10; reset master 删除所有的二进制日志flush logs  产生一个新的binlog日志文件 show master logs; 或者 show binary logs; 查看二进制文件列表和文件大小 复制代码 代码如下: ./mysqlbinlog --start-datetime="2012-05-21 15:30:00" --stop-

随机推荐