mysql日志回滚-二进制日志文件

MySQL 的二进制日志（binary logs）用于记录数据库的更改操作，包括插入、更新和删除操作等。这些日志可以用于恢复数据库到特定的时间点或进行复制（replication）等操作。

在某些情况下，你可以将二进制日志复制到其他服务器上进行恢复或分析。

二进制日志文件通常位于 MySQL 数据目录下，文件名类似于 mysql-bin.000001，其中包含了递增的编号。

1、显示 MySQL 服务器上二进制日志文件的列表

mysql> SHOW BINARY LOGS;

+———————-+———-+
| Log_name | File_size |
+————————–+————+
| mysqld-bin.000001 | 15740 |
| mysqld-bin.000002 | 3319 |

如果没有开启此功能，则会显示：

mysql> SHOW BINARY LOGS;

ERROR 1381 (HY000): You are not using binary logging

二进制日志文件默认会存放在 /var/lib/mysql 目录下

$ ls -l /var/lib/mysql/
-rw-rw—-. 1 mysql mysql 15740 Aug 28 14:57 mysqld-bin.000001
-rw-rw—-. 1 mysql mysql 3319 Aug 28 14:57 mysqld-bin.000002

2、使用mysqlbinlog

$ mysqlbinlog mysqld-bin.000001

#mysqlbinlog默认会显示为以下内容：

/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/;# at 4#170726 14:57:37 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log created 170726 14:57:37 at startup# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG ‘ IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC ‘/*!*/;

# at 106

#170726 14:59:31 server id 1 end_log_pos 182 Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1501095571/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=0/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C latin1 *//*!*/;

SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;

..

..

..

# at 14191

#170726 15:20:38 server id 1 end_log_pos 14311 Query thread_id=4 exec_time=0 error_code=0SET TIMESTAMP=1501096838/*!*/;

insert into salary(name,dept) values(‘Ritu’, ‘Accounting’)/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*

日志文件如果除了创建表和删除表的sql外，操作数据库的语句都加密了。

那么使用参数”–base64-output=decode-row -v”查看具体的sql语句，如下命令

$ mysqlbinlog –base64-output=decode-row -v /var/lib/mysql/mysql-bin.000001

数据回滚步骤

1、备份当前数据库

2、确定还原点时间

每个二进制日志文件都有一个唯一的日志位置。

1、使用mysqlbinlog读取binlog日志：

$ /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000063

2、登录服务器，并查看(推荐此种方法)

mysql> show binlog events [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count];

参数解释：

• IN ‘log_name’ ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
• FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
• LIMIT [offset,] ：偏移量(不指定就是0)
• row_count ：查询总条数(不指定就是所有行)

mysql> show binlog events in ‘mysql-bin.000063’\G;

3、使用 mysqlbinlog 分析日志，并抽取出.sql文件

mysqlbinlog –database=db_name –start-position=<position> mysql-bin.000001 > restore.sql

4、过滤数据表

使用 grep 命令可以把某表的二进制日志过滤出来

grep -B3 -w test_borough /usr/local/mysql/var/restore.sql > /usr/local/mysql/var/tb_borough .sql

5、还原数据库

mysql -u username -p < restore.sql

还原数据库：

mysqlbinlog mysql-bin.000001 | mysql -u username -p

分析日志：

mysqlbinlog mysql-bin.000001 > log.txt