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

Mysql的Bin log数据恢复:不小心删除数据库

前言:因为不小心删除了测试机器上Mysql的一整个数据库Schema,因为是测试机所以没有做备份,现在通过MySQL的Bin log方式恢复到删除以前的数据库。

当然做Bin log的数据恢复前提是已经打开Bin log的功能,如果又没做数据备份,又没打开Bin log日志,那你就可能需要考虑快照等其它方式从系统的角度去恢复。

Bin log 常用于数据增量备份和恢复,以及数据库主从复制。如果没有开启,可以通过如下方式打开:

1、打开mysql的binlog功能

mysql是支持增量备份,但要打开mysql的bin log功能。

修改mysql的配置文件。linux是/etc/my.cnf,windows是mysql的安装目录/my.ini
在[mysqld]下面加上log-bin一行代码,如下面:

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed。

2、用如下方式查看二进制日志状态:是否开启

mysql> show variables like 'log_%';

3、查看所有二进制日志文件:

mysql> show libary logs;

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       351 |
| mysql-bin.000003 |       276 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |     16509 |

4、Mysql查看二进制日志文件的操作日志

#mysqlbinlog --start-position=0 /mydata/data/mysql-bin.000089

[root@test mysql]# mysqlbinlog --start-position=0 --stop-position=500 mysql-bin.000091
Warning: option 'start-position': unsigned value 0 adjusted to 4
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#151022 18:00:43 server id 1 end_log_pos 107  Start: binlog v 4, server v 5.5.38-log created 151022 18:00:43 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
y7MoVg8BAAAAZwAAAGsAAAABAAQANS41LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADLsyhWEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#151022 23:27:50 server id 1 end_log_pos 198  Query  thread_id=2   exec_time=0   error_code=0
SET TIMESTAMP=1445527670/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=0, @@session.sql_auto_is_null=0, @@session.unique_checks=0, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1608515584/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP SCHEMA IF EXISTS `pandora`<pre name="code" class="sql">/*!*/;
# at 198
#151022 23:27:50 server id 1 end_log_pos 346  Query  thread_id=2   exec_time=0   error_code=0

5、通过Bin log恢复数据. 因为我整个Schema都删掉了,又没备份,正好开启了bin log日志,所以把历史的bin-log都重新执行了一遍,重新恢复到误删以前的版本,(我这里总共有91个文件,批量处理的):(9999999999999:是为了省掉去查找每一个bin-log日志文件的起始结束位置,设的一个无穷大的数字,简化操作.)

#mysqlbinlog /var/lib/mysql/mysql-bin.000001 --start-position=0 --stop-position=9999999999999 | mysql -uroot -p123456
#mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-position=0 --stop-position=9999999999999 | mysql -uroot -p123456
#mysqlbinlog /var/lib/mysql/mysql-bin.000003 --start-position=0 --stop-position=9999999999999 | mysql -uroot -p123456
... ...

所以总结结论是:

  1. 1、切记一定要定期备份;
  2. 2、有备份的话恢复也快一点,可以从备份的时间点做增量备份,不需要像我这里从头开始91个文件全部批量跑一遍,当然我用编辑器批量处理的也还算快;
  3. 3、另外一定要打开Bin-log日志,如果没做备份也可以通过Bin-log日志恢复。
  4. 4、操作要小心。

其它:

1、还有个sql_log

mysql> show variables like 'sql_log_%';

Mysql开启关闭sql二进制日志:
mysql> set sql_log_bin=0; //关闭
set session sql_log_bin=0;

2、查找文件位置:

find / -name my.cnf

3、linux 查看当前所在目录的全路径

pwd命令:
/var/lib/mysql

4、查看当前binary log的情况:

mysql>show master status;

5、在my.cnf/my.ini中设定binary logs回滚天数:

expire_logs_days = 7

6、查看Master的bin log日志

mysql> show master logs;
+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| log-bin.000001 |        98 | 
+-----------------+-----------+
1 row in set (0.00 sec)
---------------------

以上所述是小编给大家介绍的Mysql的Binlog数据恢复详解整合,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!

(0)

相关推荐

  • MySQL数据库遭到攻击篡改(使用备份和binlog进行数据恢复)

    本文主要描述了MySQL遭到攻击篡改数据,利用从库的备份和主库的binlog进行不完全恢复. 欢迎转载,请注明作者.出处. 作者:张正 QQ:176036317 如有疑问,欢迎联系. 一.发现问题 今天是2014-09-26,开发大清早就说昨晚数据库遭到了攻击.数据库中某文章表的文章内容字段遭到篡改,全部改成了同一篇文章. 通过查看日制 发现 数据是在 2014-09-25 21:53:57 遭到篡改. 所有的内容全部被改成了如下: 复制代码 代码如下: subject: 桂林阳朔自助游    

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

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

  • MySQL 自动清理binlog日志的方法

    说明: 开启MySQL binlog日志的服务器,如果不设置自动清理日志,默认binlog日志一直保留着,时间一长,服务器磁盘空间被binlog日志占满,导致MySQL数据库出错. 使用下面方法可以安全清理binlog日志 一.没有主从同步的情况下清理日志 mysql -uroot -p123456 -e 'PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ),INTERVAL 5 DAY)'; #mysql 定时清理5天前的binlog mysql -u root

  • Mysql Binlog快速遍历搜索记录及binlog数据查看的方法

    目标,开发人员说有个数据莫名其妙添加了,但是不知道是从哪里添加的,而且应用功能里面不应该添加这样的数据,为了查清楚来源,所以我就准备去binlog里面找了,但是binlog有好几个月的数,我这样一个个mysqlbinlog下去,也不是办法,所以想到准备用脚本循环来操作. 1,去binlog目录复制所有的binlog到临时目录/tmp/bl/ cp /home/data/mysql/binlog/mysql-bin.* /tmp/bl 2,写脚本遍历 [root@wgq_idc_dbm_3_61

  • MySQL binlog 远程备份方法详解

    以前备份binlog时,都是先在本地进行备份压缩,然后发送到远程服务器中.但是这其中还是有一定风险的,因为日志的备份都是周期性的,如果在某个周期中,服务器宕机了,硬盘损坏了,就可能导致这段时间的binlog就丢失了. 而且,以前用脚本对远程服务器进行备份的方式,有个缺点:无法对MySQL服务器当前正在写的二进制日志文件进行备份.所以,只能等到MySQL服务器全部写完才能进行备份.而写完一个binlog的时间并不固定,这就导致备份周期的不确定. 从MySQL5.6开始,mysqlbinlog支持将

  • 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日志的两种方法

    mysq 正确清理binlog日志 前言: MySQL中的binlog日志记录了数据库中数据的变动,便于对数据的基于时间点和基于位置的恢复,但是binlog也会日渐增大,占用很大的磁盘空间,因此,要对binlog使用正确安全的方法清理掉一部分没用的日志. [方法一]手动清理binlog 清理前的准备: ① 查看主库和从库正在使用的binlog是哪个文件 show master status\G show slave status\G ② 在删除binlog日志之前,首先对binlog日志备份,以

  • Mysql数据库之Binlog日志使用总结(必看篇)

    binlog二进制日志对于mysql数据库的重要性有多大,在此就不多说了.下面根据本人的日常操作经历,并结合网上参考资料,对binlog日志使用做一梳理: 一.binlog日志介绍 1)什么是binlog binlog日志用于记录所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句.语句以"事件"的形式保存,它描述数据更改. 2)binlog作用 因为有了数据更新的binlog,所以可以用于实时备份,与master/slave主从复制结合. 3)和b

  • 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-

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

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

  • mysql数据库详解(基于ubuntu 14.0.4 LTS 64位)

    1.mysql数据库的组成与相关概念 首先明白,mysql是关系型数据库,和非关系型数据库中最大的不同就是表的概念不一样. +整个mysql环境可以理解成一个最大的数据库:A +用mysql创建的数据库B是属于A的,是数据的仓库,相当于系统中的文件夹 +数据表C:是存放数据的具体场所,相当于系统中的文件,一个数据库B中包含若干个数据表C(注意此处的数据库B和A不一样) +记录D:数据表中的一行称为一个记录,因此,我们在创建数据表时,一定要创建一个id列,用于标识"这是第几条记录",id

  • mysql触发器实时检测一条语句进行备份删除思路详解

    问题描述:用户有一个这样一个需求,在一张表里会不时出现 "违规" 字样的字段,需要在出现这个字段的时候,把整行的数据删掉.这是个采集任务,如果发现有"违规"字样的数据,会整点或者什么时间进行统一上报,也无法对源头进行控制让这种数据不生成. 现在需要实现以下需求: 1.实时检测这条数据的产生,发现后删除 2.在删除之前作备份这条数据 解决思路: 需要明确解决思路, 1.首先是如何实时探测删除?询问开发,这条数据的生成方式为insert,就可以做一个当表做插入的时候,然

  • oracle数据库的删除方法详解

    1.图形界面删除 练习之前记得创建快照 执行命令之前要保证数据库属于open状态 SQL> alter database open; [oracle@localhost ~]$ dbca 2.静默删除 使用DBCA进行删除的数据库必须处于OPEN状态,否则不能删除. SQL> alter database open; [oracle@localhost ~]$ dbca -silent -deleteDatabase -sourceDB OCPLHR1 3.SQL窗口 startup ##启动

  • mysql主从同步原理及应用场景示例详解

    目录 基础知识 MySQL 主从同步的主要应用场景有: 原理 设置主从同步,还有以下几个前提: 实验环境模拟实现主从同步 首先在 docker 中拉取 mysql 5.7 版本的镜像: 通过以下命令进入到 Master 容器内部: 接下来,进入数据库: 接下来执行以下命令: 基础知识 随着业务复杂度的增加,单台 MySQL 数据库服务器已不能满足实际的需求,取而代之的是数据库服务器集群.MySQL 具有支持分布式的特性,能轻松搭建一个支持高并发的 MySQL 数据库服务器集群.在集群中我们必须保

  • mysql的存储过程、游标 、事务实例详解

    mysql的存储过程.游标 .事务实例详解 下面是自己曾经编写过的mysql数据库存储过程,留作存档,以后用到的时候拿来参考. 其中,涉及到了存储过程.游标(双层循环).事务. [说明]:代码中的注释只针对当时业务而言,无须理会. 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS `transferEmailTempData`$$ CREATE PROCEDURE transferEmailTempData(IN jobId VARCHAR(24)) BEG

  • MySql 5.7.17免安装配置教程详解

    1.下载mysql-5.7.17-winx64.zip安装包(链接:https://dev.mysql.com/downloads/mysql/) 2.解压安装包. D:\DevelopTool\mysql-5.7.17-winx64   #解压目录 3.在解压目录下创建一个名为data的文件夹,用来存放数据 D:\DevelopTool\mysql-5.7.17-winx64\data 4.配置启动文件 把 D:\DevelopTool\mysql-5.7.17-winx64\my-defau

  • MySQL 设计和命令行模式下建立详解

    MySQL 设计和命令行模式下建立详解 系列文章: MySQL 设计和命令行模式下建立详解 C++利用MySQL API连接和操作数据库实例详解 1.数据表的设计 MySQL数据库管理系统(DBMS)中,包含的MySQL中定义数据字段的类型对你数据库的优化是非常重要的.MySQL支持多种类型,大致可以分为三类:数值.日期/时间和字符串(字符)类型. 下面以大学熟悉的学生选课管理系统中用到的数据库为例,来设计相应的数据表.主要有三张表:学生表,课程表和选课表. 学生表设计: 字段(Field) 类

  • MySQL的视图和索引用法与区别详解

    MySQL的视图 简单来说MySQL的视图就是对SELECT 命令的定义的一个快捷键,我们查询时会用到非常复杂的SELECT语句,而这个语句我们以后还会经常用到,我们可以经这个语句生产视图.视图是一个虚拟的表,它不存储数据,所用的数据都在真实的表中. 这样做的好处有: 1.防止有未经允许的租户访问到敏感数据 2.将多个物理表抽象成一个逻辑表 3.结果容易理解 4.获得数据更容易,很多人对SQL语句不太了解,我们可以通过创建视图的形式方便用户使用. 5.显示数据更容易. 6.维护程序更方便.调试视

  • MySQL 普通索引和唯一索引的区别详解

    1 概念区分 普通索引和唯一索引 普通索引可重复,唯一索引和主键一样不能重复. 唯一索引可作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引.(一般设置学号字段为主键) 主键和唯一索引 主键保证数据库里面的每一行都是唯一的,比如身份证,学号等,在表中要求唯一,不重复.唯一索引的作用跟主键的作用一样. 不同的是,在一张表里面只能有一个主键,主键不能为空,唯一索引可以有多个,唯一索引可以有一条记录为空,即保证跟别人不一样就行. 比如学生表,在学校里

随机推荐