Mining mysqlbinlog binary Log Files in mysql Database
Click (here) to collapse or open
1. Check whether the mysql database enables the binary log log _ bin. The value value is ON. Enable.
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)
two。 View time
Mysql > select now ()
+-+
| | now () |
+-+
| | 2017-10-20 19:26:55 |
+-+
1 row in set (0.00 sec)
3. View bin log files
Mysql > show master logs
+-+ +
| | Log_name | File_size |
+-+ +
| | mysql-bin.000044 | 870441798 | |
+-+ +
1 rows in set (0.00 sec)
4. Create a test table to insert data
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
Query OK, 1 row affected (0.00 sec)
Mysql > insert into t (id,name) values
Query OK, 1 row affected (0.01sec)
Mysql > insert into t (id,name) values (2)
Query OK, 1 row affected (0.00 sec)
Mysql > insert into t (id,name) values (2)
Query OK, 1 row affected (0.00 sec)
Mysql > insert into t (id,name) values (3dc')
Query OK, 1 row affected (0.00 sec)
Mysql > insert into t (id,name) values (3dc')
Query OK, 1 row affected (0.01sec)
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. Delete data
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.01sec)
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)
After refreshing the log, you will see that three binary bin files are generated
6. Extract sql from bin files (time-based data recovery)
[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. Perform data recovery
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)
At this time, all the data has been restored to before the data was deleted!
