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

昨天一不小心把微信公众号表删除了（我干了件蠢事），八万多条数据没了，还好有备份，虽然恢复了，但是却损失了几千条数据，事后发现可以使用mysqlbinlog二进制日志的方式恢复数据，今天我研究了一下，模拟昨天的操作，插入数据前备份数据库，然后插入新的数据后，删除数据表后导入之前备份的数据，然后从二进制日志文件中恢复没有及时备份的数据。

二进制日志的文件的作用

mysql二进制日志文件用来记录所有用户对数据库操作，即记录用户对数据库操作的sql语句。如果有此文件，当数据库发生意外时，可以通过此文件查看到用户在此文件记录的时间段内用户所做的操作，再和数据库备份配合使用，即可再现用户操作，使数据库恢复。

注意：使用日志文件恢复数据库必须要有一个数据库备份（只要是在二进制日志文件功能开启之后备份的就行）。

二进制日志文件的弊端

二进制日志文件开启后，所有对数据库操作的记录均会被记录到此文件，所以，当长时间开启之后，日志文件会变得很大，占用磁盘空间。

开启日志文件

mysql默认是不开启日志文件的功能的，需要我们手动开启。操作如下：

1、打开mysql安装目录\my.ini（windows系统是my.ini文件，linux是my.cnf文件）。

2、找到[mysqld]这个标签，在此标签下面一行，添加语句：log-bin=binary-log-name。上面语句中，log-bin说明要开启二进制日志文件，binary-log-name是二进制日志文件的路径及名字。(测试的时候我设置的：log-bin="D:/phpStudy/MySQL/mysql_logbin/logbin.log")

3、重启mysql服务。可以在mysql安装目录D:/phpStudy/MySQL/mysql_logbin/ 文件夹下看到“logbin.数字编号”文件，如logbin.000001。以后每重启一次mysql服务，都会重新生成二进制日志文件，文件名中的数字编号依次递增。

此时查看可以看到二进制日志开启成功：

mysql> show variables like 'log_%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| log_error                       |       |
| log_output                      | FILE  |
| log_queries_not_using_indexes   | OFF   |
| log_slave_updates               | OFF   |
| log_slow_queries                | OFF   |
| log_warnings                    | 1     |
+---------------------------------+-------+
8 rows in set (0.00 sec)

然后模拟昨天的失误，并开始数据恢复：

1，首先查看原本的测试数据记录：

mysql> select * from todolist;
+----+---------+------------+--------+-------------+
| id | user_id | title      | status | create_time |
+----+---------+------------+--------+-------------+
|  8 |       2 | 344434     |      1 |   342353463 |
|  9 |       3 | 436458756  |      1 |   555555555 |
| 10 |       1 | 3333333333 |      0 |   232333333 |
| 11 |       2 | 333333333  |      1 |   243563467 |
+----+---------+------------+--------+-------------+
4 rows in set (0.00 sec)

2，接着备份数据：

mysqldump -uroot -proot todo -l -F > D:/phpStudy/MySQL/sql/test.sql

-l:读锁（只能读取，不能更新）

-F:即flush logs，可以重新生成新的日志文件，当然包括log-bin日志

3，新增接了几条数据，查看：

mysql> select * from todolist;
+----+---------+----------------+--------+-------------+
| id | user_id | title          | status | create_time |
+----+---------+----------------+--------+-------------+
|  8 |       2 | 344434         |      1 |   342353463 |
|  9 |       3 | 436458756      |      1 |   555555555 |
| 10 |       1 | 3333333333     |      0 |   232333333 |
| 11 |       2 | 333333333      |      1 |   243563467 |
| 12 |       2 | 111111111113   |      1 |      354365 |
| 13 |       4 | 55555555555555 |      1 |   321453264 |
+----+---------+----------------+--------+-------------+
6 rows in set (0.00 sec)

4，查看二进制日志名称：

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| logbin.000001 |       107 |
| logbin.000002 |       663 |
| logbin.000003 |      1694 |
| logbin.000004 |      1542 |
| logbin.000005 |       438 |
| logbin.000006 |     34598 |
| logbin.000007 |       404 |
| logbin.000008 |       147 |
| logbin.000009 |      1217 |
| logbin.000010 |       445 |
| logbin.000011 |       263 |
| logbin.000012 |       708 |
+---------------+-----------+
12 rows in set (0.00 sec)

5，然后删除了本数据表，

mysql> delete from todolist;
Query OK, 6 rows affected (0.14 sec)

接着刷新，会生成新的日志文件logbin.000013

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

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| logbin.000001 |       107 |
| logbin.000002 |       663 |
| logbin.000003 |      1694 |
| logbin.000004 |      1542 |
| logbin.000005 |       438 |
| logbin.000006 |     34598 |
| logbin.000007 |       404 |
| logbin.000008 |       147 |
| logbin.000009 |      1217 |
| logbin.000010 |       445 |
| logbin.000011 |       263 |
| logbin.000012 |       926 |
| logbin.000013 |       107 |
+---------------+-----------+
13 rows in set (0.00 sec)

6，从备份中恢复数据：

mysql -uroot -proot todo -v -f < D:/phpStudy/MySQL/sql/test.sql

-v查看导入的详细信息

-f是当中间遇到错误时，可以skip过去，继续执行下面的语句，

然后查看数据，可以看到有部分数据恢复了：

mysql> select * from todolist;
+----+---------+------------+--------+-------------+
| id | user_id | title      | status | create_time |
+----+---------+------------+--------+-------------+
|  8 |       2 | 344434     |      1 |   342353463 |
|  9 |       3 | 436458756  |      1 |   555555555 |
| 10 |       1 | 3333333333 |      0 |   232333333 |
| 11 |       2 | 333333333  |      1 |   243563467 |
+----+---------+------------+--------+-------------+
4 rows in set (0.00 sec)

7，接着就是重点了，怎么通过二进制日志恢复数据：

在通过mysql的binlog日志恢复数据时，我们可以指定恢复到具体时间点，这有点像服务器快照管理。所以我们现在要恢复，可以从删除之前找一个时间点（操作点），并恢复到那个时间点（操作点）即可。即通过操作点恢复和通过时间点恢复，这里主要讲通过操作点恢复方法：

7.1:，查看二进制日志记录：

C:\Users\BaAGee>mysqlbinlog D:/phpStudy/MySQL/mysql_logbin/logbin.000012
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170715 10:46:15 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.38-log created 170715 10:46:15
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
94FpWQ8BAAAAZwAAAGsAAAABAAQANS41LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#170715 10:46:48 server id 1  end_log_pos 175   Query   thread_id=2     exec_time=1        error_code=0
SET TIMESTAMP=1500086808/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 175
# at 203
#170715 10:46:48 server id 1  end_log_pos 203   Intvar
SET INSERT_ID=12/*!*/;
#170715 10:46:48 server id 1  end_log_pos 378   Query   thread_id=2     exec_time=0        error_code=0
use `todo`/*!*/;
SET TIMESTAMP=1500086808/*!*/;
INSERT INTO `todolist` (`user_id`, `title`, `status`, `create_time`) VALUES ('2', '111111111113', '1', '354365')
/*!*/;
# at 378
#170715 10:46:48 server id 1  end_log_pos 405   Xid = 214
COMMIT/*!*/;
# at 405
#170715 10:48:46 server id 1  end_log_pos 473   Query   thread_id=2     exec_time=0        error_code=0
SET TIMESTAMP=1500086926/*!*/;
BEGIN
/*!*/;
# at 473
# at 501
#170715 10:48:46 server id 1  end_log_pos 501   Intvar
SET INSERT_ID=13/*!*/;
#170715 10:48:46 server id 1  end_log_pos 681   Query   thread_id=2     exec_time=0        error_code=0
SET TIMESTAMP=1500086926/*!*/;
INSERT INTO `todolist` (`user_id`, `title`, `status`, `create_time`) VALUES ('4', '55555555555555', '1', '321453264')
/*!*/;
# at 681
#170715 10:48:46 server id 1  end_log_pos 708   Xid = 217
COMMIT/*!*/;
# at 708
#170715 10:51:17 server id 1  end_log_pos 776   Query   thread_id=1     exec_time=0        error_code=0
SET TIMESTAMP=1500087077/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
BEGIN
/*!*/;
# at 776
#170715 10:51:17 server id 1  end_log_pos 859   Query   thread_id=1     exec_time=0        error_code=0
SET TIMESTAMP=1500087077/*!*/;
delete from todolist
/*!*/;
# at 859
#170715 10:51:17 server id 1  end_log_pos 886   Xid = 223
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

看输出语句，可以发现：在每个操作之前，都会有一个独特的编号(# at 数字)，数字就是编号：

此编号随着操作数增多而变大。和时间一样是一个标记。

7.2，通过操作点恢复的语句如下：

mysqlbinlog --stop-position=708 D:/phpStudy/MySQL/mysql_logbin/logbin.000012 | mysql -uroot -proot

7.3，执行之后查看数据：

mysql> select * from todolist;
+----+---------+----------------+--------+-------------+
| id | user_id | title          | status | create_time |
+----+---------+----------------+--------+-------------+
|  8 |       2 | 344434         |      1 |   342353463 |
|  9 |       3 | 436458756      |      1 |   555555555 |
| 10 |       1 | 3333333333     |      0 |   232333333 |
| 11 |       2 | 333333333      |      1 |   243563467 |
| 12 |       2 | 111111111113   |      1 |      354365 |
| 13 |       4 | 55555555555555 |      1 |   321453264 |
+----+---------+----------------+--------+-------------+
6 rows in set (0.00 sec)

相关推荐

mysql优化总结

评论