mysql數據庫mysqlbinlog二進制日志文件挖掘

發布時間：2020-08-07 19:14:18 來源：ITPUB博客閱讀：221 作者：jiage_Daniel 欄目：MySQL數據庫

點擊(此處)折疊或打開

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)
此時數據已經全部恢復到數據刪除之前！！

向AI問一下細節

91超碰碰碰碰久久久久久综合_超碰av人澡人澡人澡人澡人掠_国产黄大片在线观看画质优化_txt小说免费全本

mysql數據庫mysqlbinlog二進制日志文件挖掘

猜你喜歡

91超碰碰碰碰久久久久久综合_超碰av人澡人澡人澡人澡人掠_国产黄大片在线观看画质优化_txt小说免费全本

mysql數據庫mysqlbinlog二進制日志文件挖掘

猜你喜歡

最新資訊

相關推薦

相關標簽