mysql binlog恢复数据

最新推荐文章于 2024-07-31 10:02:10 发布

爱代码也爱生活

最新推荐文章于 2024-07-31 10:02:10 发布

阅读量826

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/nuli888/article/details/52118226

版权

数据库专栏收录该内容

40 篇文章 0 订阅

订阅专栏

常用binlog操作命令：

1、查看所有binlog日志列表
mysql> show master logs;
2、查看当前正在写入的binlog文件
mysql> show master status;
3、刷新log日志，自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
4、清空所有binlog日志
mysql> reset master;
5、获取binlog文件列表
mysql> show binary logs;
6、查看指定binlog文件的内容
mysql> show binlog events in 'mysql-bin.000006';

根据整个binlog文件恢复

#选择hadoop库
mysql> use hadoop;

#刷新binlog日志，新开一个，现在会在/var/lib/mysql/目录下面生成一个mysql-bin.000002的文件，以下的操作都会记录其中
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       231 |
| mysql-bin.000002 |       106 |
+------------------+-----------+
2 rows in set (0.00 sec)

#创建一个表
mysql> create table user(
    -> id int auto_increment primary key,
    -> username char(30),
    -> password char(32)
    -> )engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

#插入几条测试数据
mysql> insert into user(username,password)values(1,1),(2,2),(3,3),(4,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

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

#新开一个binlog日志，现在会在/var/lib/mysql/目录下面生成一个mysql-bin.000003的文件，以下的操作都会记录其中
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

#删除表数据
mysql> delete from user;
Query OK, 4 rows affected (0.00 sec)

#删除表
mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)

#验证是否删掉
mysql> select * from user;
ERROR 1146 (42S02): Table 'hadoop.user' doesn't exist
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       231 |
| mysql-bin.000002 |       497 |
| mysql-bin.000003 |       267 |
+------------------+-----------+
3 rows in set (0.00 sec)

mysql> \q
Bye

#进入binlog目录
[root@hd3 ~]# cd /var/lib/mysql/
#查看mysql-bin.000002里面的内容
[root@hd3 mysql]# mysqlbinlog --no-defaults mysql-bin.000002 | more
#恢复
[root@hd3 mysql]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -uroot -p123456

#验证是否恢复成功
[root@hd3 mysql]# mysql -uroot -p
mysql> use hadoop;
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 user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 1        | 1        |
|  2 | 2        | 2        |
|  3 | 3        | 3        |
|  4 | 4        | 4        |
+----+----------+----------+
4 rows in set (0.00 sec)

mysql>

根据binlog的位置恢复

#先插入2条
mysql> insert into user(username,password)values(5,5),(6,6);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

#删除
mysql> delete from user where id=5 or id=6;
Query OK, 2 rows affected (0.00 sec)

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

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |       195 |         106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| mysql-bin.000001 | 106 | Query       |       195 |         188 | use `hadoop`; DROP TABLE `user`       |
| mysql-bin.000001 | 188 | Rotate      |       195 |         231 | mysql-bin.000002;pos=4                |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000003';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                         |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000003 |    4 | Format_desc |       195 |         106 | Server ver: 5.1.73-log, Binlog ver: 4                                                                                                        |
| mysql-bin.000003 |  106 | Query       |       195 |         187 | use `hadoop`; delete from user                                                                                                               |
| mysql-bin.000003 |  187 | Query       |       195 |         267 | use `hadoop`; drop table user                                                                                                                |
| mysql-bin.000003 |  267 | Query       |       195 |         458 | use `hadoop`; create table user(
id int auto_increment primary key,
username char(30),
password char(32)
)engine=myisam default charset=utf8 |
| mysql-bin.000003 |  458 | Intvar      |       195 |         486 | INSERT_ID=1                                                                                                                                  |
| mysql-bin.000003 |  486 | Query       |       195 |         615 | use `hadoop`; insert into user(username,password)values(1,1),(2,2),(3,3),(4,4)                                                               |
| mysql-bin.000003 |  615 | Query       |       195 |         713 | use `hadoop`; ALTER TABLE `user` RENAME `user2`                                                                                              |
| mysql-bin.000003 |  713 | Query       |       195 |         904 | use `hadoop`; create table user(
id int auto_increment primary key,
username char(30),
password char(32)
)engine=myisam default charset=utf8 |
| mysql-bin.000003 |  904 | Intvar      |       195 |         932 | INSERT_ID=1                                                                                                                                  |
| mysql-bin.000003 |  932 | Query       |       195 |        1061 | use `hadoop`; insert into user(username,password)values(1,1),(2,2),(3,3),(4,4)                                                               |
| mysql-bin.000003 | 1061 | Intvar      |       195 |        1089 | INSERT_ID=5                                                                                                                                  |
| mysql-bin.000003 | 1089 | Query       |       195 |        1206 | use `hadoop`; insert into user(username,password)values(5,5),(6,6)                                                                           |
| mysql-bin.000003 | 1206 | Query       |       195 |        1306 | use `hadoop`; delete from user where id=5 or id=6                                                                                            |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)


#查看mysql-bin.000003日志内容
[root@hd3 ~]# show binlog events in 'mysql-bin.000003';

#根据binlog位置恢复		1089-1206是执行删除语句之前的sql起始和结束位置
[root@hd3 ~]# mysqlbinlog  --no-defaults --start-position=1089 --stop-position=1206 '/var/lib/mysql/mysql-bin.000003' | mysql -uroot -p

#进入数据库验证是否恢复过来
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 1        | 1        |
|  2 | 2        | 2        |
|  3 | 3        | 3        |
|  4 | 4        | 4        |
|  8 | 6        | 6        |
|  7 | 5        | 5        |
+----+----------+----------+
6 rows in set (0.01 sec)

还可以根据时间节点恢复

[root@hd3 ~]# mysqlbinlog  --no-defaults --start-datetime="2015-11-01 13:18:54" --stop-datetime="2015-12-01 13:21:53" '/var/lib/mysql/mysql-bin.000003' | mysql -uroot -p

如果需要指定数据库操作就加上--database=hadoop