mysql数据库mysqlbinlog二进制日志文件挖掘

2025-04-14 技术教程 151

点击(此处)折叠或打开

1.查看mysql数据库是否开启二进制日志log_bin的value值为ON为开启

mysql> show variables like 'log_%';
+----------------------------------------+-------------------------------------+
| Variable_name | Value |
+----------------------------------------+-------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/db/mysql/3306/mysql-bin |
| log_bin_index | /data/db/mysql/3306/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /data/db/mysql/3306/mariadb.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
+----------------------------------------+-------------------------------------+
21 rows in set (0.01 sec)

2.查看时间

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2017-10-20 19:26:55 |
+---------------------+
1 row in set (0.00 sec)

3.查看bin日志文件

mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000044 | 870441798 |
+------------------+-----------+
1 rows in set (0.00 sec)

4.创建测试表插入数据
mysql> create table t(id int,name varchar(10));
Query OK, 0 rows affected (0.26 sec)
mysql> select * from t;
Empty set (0.00 sec)

mysql> insert into t(id,name)values (1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(id,name)values (1,'a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t(id,name)values (2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(id,name)values (2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(id,name)values (3,'c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(id,name)values (3,'c');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 1 | a |
| 2 | b |
| 2 | b |
| 3 | c |
| 3 | c |
+------+------+
6 rows in set (0.00 sec)

mysql> insert into t select * from t;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from t;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 1 | a |
| 2 | b |
| 2 | b |
| 3 | c |
| 3 | c |
| 1 | a |
| 1 | a |
| 2 | b |
| 2 | b |
| 3 | c |
| 3 | c |
+------+------+
12 rows in set (0.00 sec)

5.删除数据
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2017-10-20 19:27:46 |
+---------------------+
1 row in set (0.00 sec)

mysql> delete from t;
Query OK, 12 rows affected (0.01 sec)

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

mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000044 | 870441798 |
| mysql-bin.000045 | 154 |
| mysql-bin.000046 | 2690 |
| mysql-bin.000047 | 448 |
+------------------+-----------+
4 rows in set (0.00 sec)

刷新日志后会看到有三个二进制bin文件生成

6.提取bin文件中的sql(基于时间的数据恢复)
[root@msp binlog]# ls
bak.sql bin.sql test.sql
[root@msp binlog]# mysqlbinlog --start-datetime="2017-10-20 19:26:55" --stop-datetime="2017-10-20 19:27:46" /data/db/mysql/3306/mysql-bin.000045 >/root/binlog/t.sql
[root@msp binlog]# ls
bak.sql bin.sql test.sql t.sql

7.进行数据恢复

mysql> source /root/binlog/t.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[root@msp binlog]# rm -f t.sql
[root@msp binlog]# ls
bak.sql bin.sql test.sql
[root@msp binlog]# mysqlbinlog --start-datetime="2017-10-20 19:26:55" --stop-datetime="2017-10-20 19:27:46" /data/db/mysql/3306/mysql-bin.000046 >>/root/binlog/t.sql
[root@msp binlog]# ls
bak.sql bin.sql test.sql t.sql
[root@msp binlog]# ll
total 2715356
-rw-r--r--. 1 root root 6834 Oct 19 17:28 bak.sql
-rw-r--r--. 1 root root 2780497429 Oct 19 17:21 bin.sql
-rw-r--r--. 1 root root 9193 Oct 19 17:36 test.sql
-rw-r--r--. 1 root root 2112 Oct 20 19:44 t.sql
[root@msp binlog]# mysqlbinlog --start-datetime="2017-10-20 19:26:55" --stop-datetime="2017-10-20 19:27:46" /data/db/mysql/3306/mysql-bin.000047 >>/root/binlog/t.sql
[root@msp binlog]# ll
total 2715356
-rw-r--r--. 1 root root 6834 Oct 19 17:28 bak.sql
-rw-r--r--. 1 root root 2780497429 Oct 19 17:21 bin.sql
-rw-r--r--. 1 root root 9193 Oct 19 17:36 test.sql
-rw-r--r--. 1 root root 2448 Oct 20 19:44 t.sql

mysql> source /root/binlog/t.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from t;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 1 | a |
| 2 | b |
| 2 | b |
| 3 | c |
| 3 | c |
| 1 | a |
| 1 | a |
| 2 | b |
| 2 | b |
| 3 | c |
| 3 | c |
+------+------+
12 rows in set (0.00 sec)

此时数据已经全部恢复到数据删除之前！！