mysql binlog恢复数据

叁歲伎倆 2022-09-25 01:16 250阅读 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