mysql数据库mysqlbinlog二进制日志文件挖掘
点击(此处)折叠或打开
阜平网站建设公司成都创新互联,阜平网站设计制作，有大型网站制作公司丰富经验。已为阜平上千家提供企业网站建设服务。企业网站搭建\成都外贸网站建设公司要多少钱，请找那个售后服务好的阜平做网站的公司定做！
-
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)
-
- 此时数据已经全部恢复到数据删除之前！！
当前题目：mysql数据库mysqlbinlog二进制日志文件挖掘
本文地址：http://shouzuofang.com/article/jecogi.html