利用Mysql的二进制日志文件,进行数据库数据的恢复

1、先创建一个数据库test_bin

create database test_bin;

use test_bin;

create table test_bin_table
(  id int
);

insert into test_bin_table values(1),(2),(3);

select * from test_bin_table;

+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

2、对数据库进行备份操作

mysqldump -u root -p1234 test_bin -l -F >/etc/test_bin.sql

备份的脚本文件如下:

DROP TABLE IF EXISTS `test_bin_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_bin_table` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test_bin_table`
--

LOCK TABLES `test_bin_table` WRITE;
/*!40000 ALTER TABLE `test_bin_table` DISABLE KEYS */;
INSERT INTO `test_bin_table` VALUES (1),(2),(3);
/*!40000 ALTER TABLE `test_bin_table` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

3、在数据库备份后,又对数据库进入了插入操作

flush logs;  #生成新的log-bin文件
insert into test_bin_table values(4),(5),(6);

4、然后,数据库test_bin里面的test_bin_table表,不小心被删除了.现在进行数据的恢复.

     1、利用mysqldump备份的数据,进行恢复    

mysql -u root -p1234 test_bin < /etc/test_bin.sql

    这个时候,恢复的只是一部分数据,因为备份后插入的三条数据是恢复不了的

select * from test_bin_table;

+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

  2、利用mysql的二进制日志,来恢复另外的数据

 show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      364 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

查看这个二进制文件的内容,

# at 207
#160406  7:20:20 server id 3  end_log_pos 333 CRC32 0x4aac2ebd Query thread_id=3exec_time=0error_code=0
use `test_bin`/*!*/;
SET TIMESTAMP=1459952420/*!*/;
insert into test_bin_table values(4),(5),(6)
/*!*/;

在里面找到这个内容，利用二进制日志文件,进行另外一部分数据的恢复

mysqlbinlog --no-defaults mysql-bin.000007 | mysql -u root -p1234 test_bin;

再次,查看数据,发现另外一部分数据也恢复回来了.

select * from test_bin_table;

+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

5、关于mysq二进制恢复的,可选position点的恢复

mysqlbinlog --no-defaults --stop-position="644" mysql-bin.00002  |  /usr/local/mysql/bin/mysql -u root -p1234 test_db 
       
mysqlbinlog --no-defaults --start-positon="106" --stop-position="644" mysql-bin.00002  |  /usr/local/mysql/bin/mysql -u root -p1234 test_db