- Published on
1. MySQL的binlog恢复（Windows下）
- Authors
- Name
- Nix Echo
- @Nix_Echo
初识binlog
MySQL的binlog就是大家经常所说的Binary Log，即bin-log，是MySQL存储对数据库改动的二进制文件，也就是记录了所有DDL与DML（select除外）语句.
首先查看 mysqlbinlog 是否已经打开:
-- 查看MySQL的日志是否打开(log_bin项)
mysql> SHOW VARIABLES LIKE 'log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
2 rows in set (0.01 sec)
-- 查看日志过期时间(过期之后日志会自动删除)
mysql> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
1 row in set (0.00 sec)
如果 log_bin 是关闭状态的话,我们需要修改mysql配置文件.比如我的mysql安装位置是:D:\mysql\bin\mysql.exe. 所以修改D:\mysql\my.ini 文件,添加以下配置:
# 如果 log_bin=mysql-bin 则日志文件会默认存放在 C:\ProgramData\MySQL\MySQL Server 5.5\data\
log-bin=D:/mysql/data/mysql-bin
binlog_format=mixed
# 设置日志永不过期(一般设置为10 即十天之后过期删除)
expire_logs_days = 0
修改之后执行 services.msc 打开服务,重启mysql服务生效.
数据恢复测试
1.准备表person,插入基本数据:
mysql> CREATE TABLE `person` (
-> `id` varchar(255) ,
-> `name` varchar(255) ,
-> `age` int(10)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> select * from person;
Empty set (0.01 sec)
mysql> INSERT INTO person VALUES (uuid(),'张三',18);INSERT INTO person VALUES (uuid(),'王五',30);
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> select * from person;
+--------------------------------------+------+------+
| id | name | age |
+--------------------------------------+------+------+
| bcaa790d-6499-11ec-86bc-8c16453d981b | 张三 | 18 |
| bcaae031-6499-11ec-86bc-8c16453d981b | 王五 | 30 |
+--------------------------------------+------+------+
2 rows in set (0.00 sec)
2.模拟误删除person表:
mysql> delete from person;
Query OK, 2 rows affected (0.00 sec)
3.尝试恢复表数据:
- 查看当前日志名称:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1856 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
- 查看当前日志节点:
mysql> show binlog events in 'mysql-bin.000001';
+------------------+------+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.28-log, Binlog ver: 4 |
| mysql-bin.000001 | 107 | Query | 1 | 263 | use `test`; CREATE TABLE `person` (
`id` varchar(255) ,
`name` varchar(255) ,
`age` int(10)
) |
| mysql-bin.000001 | 263 | Query | 1 | 371 | use `test`; DROP TABLE `person` /* generated by server */ |
| mysql-bin.000001 | 371 | Query | 1 | 522 | use `test`; CREATE TABLE `person` (
`id` varchar(255) ,
`name` varchar(255) ,
`age` int(10)
) |
| mysql-bin.000001 | 522 | Query | 1 | 768 | use `test`; ALTER TABLE `person`
MODIFY COLUMN `id` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL FIRST ,
MODIFY COLUMN `name` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL AFTER `id` |
| mysql-bin.000001 | 768 | Query | 1 | 836 | BEGIN |
| mysql-bin.000001 | 836 | Table_map | 1 | 887 | table_id: 36 (test.person) |
| mysql-bin.000001 | 887 | Write_rows | 1 | 967 | table_id: 36 flags: STMT_END_F |
| mysql-bin.000001 | 967 | Xid | 1 | 994 | COMMIT /* xid=112 */ |
| mysql-bin.000001 | 994 | Query | 1 | 1062 | BEGIN |
| mysql-bin.000001 | 1062 | Query | 1 | 1197 | use `test`; -- INSERT INTO person VALUES (uuid(),'张三',18);
delete from person |
| mysql-bin.000001 | 1197 | Xid | 1 | 1224 | COMMIT /* xid=119 */ |
| mysql-bin.000001 | 1224 | Query | 1 | 1292 | BEGIN |
| mysql-bin.000001 | 1292 | Table_map | 1 | 1343 | table_id: 36 (test.person) |
| mysql-bin.000001 | 1343 | Write_rows | 1 | 1425 | table_id: 36 flags: STMT_END_F |
| mysql-bin.000001 | 1425 | Xid | 1 | 1452 | COMMIT /* xid=123 */ |
| mysql-bin.000001 | 1452 | Query | 1 | 1520 | BEGIN |
| mysql-bin.000001 | 1520 | Table_map | 1 | 1571 | table_id: 36 (test.person) |
| mysql-bin.000001 | 1571 | Write_rows | 1 | 1653 | table_id: 36 flags: STMT_END_F |
| mysql-bin.000001 | 1653 | Xid | 1 | 1680 | COMMIT /* xid=124 */ |
| mysql-bin.000001 | 1680 | Query | 1 | 1748 | BEGIN |
| mysql-bin.000001 | 1748 | Query | 1 | 1829 | use `test`; delete from person |
| mysql-bin.000001 | 1829 | Xid | 1 | 1856 | COMMIT /* xid=152 */ |
+------------------+------+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
23 rows in set (0.00 sec)
可以看待在1680节点开始到1856节点结束是处理最后的删除表操作.所以我们恢复数据的时候,只需要恢复数据到1680节点之前就行
通过binlog恢复数据
打开命令窗,切换路径到与安装的mysql执行文件同目录。比如我的mysql安装路径是:D:\mysql\bin\mysql.exe，所以目录切换到**D:\mysql\bin** 。
一般有两种恢复操作：一种是将从日志文件中抽取到的sql存为件，然后执行.sql文件;另一种是直接将抽取到的sql执行.
恢复命令格式如下：
# 抽取sql存为文件
mysqlbinlog --start-position=开始节点号 --stop-posion=结束节点号 日志文件名称(保险起见使用使用绝对路径) > 存储位置名称
# 执行.sql文件
source 路径名称.sql
# 直接执行抽取到的sql
mysqlbinlog --start-position=开始节点号 --stop-posion=结束节点号 日志文件名称(保险起见使用使用绝对路径) | mysql -uroot -p123456
通过3.1的分析,可以得知需要执行的起始节点位置和日志路径.所以执行命令如下:
# 第一种情况: 先存.sql 然后执行(两条命令分开执行)
mysqlbinlog --start-position=4 --stop-position=1680 D:\mysql\data\mysql-bin.000001 > d:\\test1.sql
source d://test1.sql
# 第二种情况: 直接执行
mysqlbinlog --start-position=4 --stop-position=1680 D:\mysql\data\mysql-bin.000001 | mysql -uroot -p1216
执行完毕之后可以看到,数据已经恢复到以前两条数据没有删除的状态.