Because I was asked such a question during the interview in the first two days, if a colleague accidentally deleted a table, how should you recover?

After thinking for a while, the blogger didn't encounter this problem, but he knew a little about it, so he replied to recover through the mysql binlog log.

The interviewer asked about the specific process. I'm a little confused. So I summarized that the premise must be backup!

The recovery method of a table deleted by mistake in the database is based on the premise that the database that is backed up every day and the binlog log is enabled. If there is no backup and the binlog log is recovered, it will be very, very troublesome. Therefore, we advise you to back up! Backup! Backup!

/*Check whether the binlog log is enabled in the database*/
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /home/mysql/mysql/lib/mysql-bin       |
| log_bin_index                   | /home/mysql/mysql/lib/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+
6 rows in set (0.00 sec)

/*First, check the data in the data table*/

MariaDB [drop_test]> select * from python_test;
+----+-----------+------------+
| id | name      | class_time |
+----+-----------+------------+
|  1 | dictionary      |          3 |
|  2 | list      |          2 |
|  3 | function      |          5 |
|  4 | Decorator    |          2 |
|  5 | iterator     |          2 |
+----+-----------+------------+
5 rows in set (0.00 sec)

/*backups*/
mysqldump -uroot -p111111 -B drop_test >drop_test.sql

/*Delete the database after inserting data again*/

MariaDB [drop_test]> INSERT INTO python_test(id,name,class_time) value(6,'generator ',2);
Query OK, 1 row affected (0.01 sec)

MariaDB [drop_test]> INSERT INTO python_test(id,name,class_time) value(7,'Method of class',5);
Query OK, 1 row affected (0.00 sec)

Delete database

MariaDB [drop_test]> drop database drop_test ;
Query OK, 1 row affected (0.01 sec)

Remember not to have any operation at this time!!!

View current binlog
1 mysql> show master status\G;
2 *************************** 1. row ***************************
3              File: mysql-bin.000001
4          Position: 4666
5      Binlog_Do_DB: 
6  Binlog_Ignore_DB: 
7 Executed_Gtid_Set: 
8 1 row in set (0.00 sec)

/*At this time, copy the current binlog logs to other directories to avoid subsequent operations affecting the binlog logs*/

cp /var/lib/mysql/mysql-bin.000001 /home

/*Execute command*/ conversion binlog Log is sql
mysqlbinlog -d drop_test mysql-bin.000001 >001bin.sql

Edit 001bin SQL deletes all the misoperation commands (DROP command) inside

Start data recovery after saving

/*Restore backup files first*/
mysql -uroot -p111111 drop_test < drop_test.sql 

/*Viewing database backup files*/

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| drop_test          |
| for_bak            |
| lhc                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

/*The backed up data has been recovered*/

MariaDB [drop_test]> show tables;
+---------------------+
| Tables_in_drop_test |
+---------------------+
| python_test         |
+---------------------+
1 row in set (0.00 sec)

MariaDB [drop_test]> select * from python_test;
+----+-----------+------------+
| id | name      | class_time |
+----+-----------+------------+
|  1 | dictionary      |          3 |
|  2 | list      |          2 |
|  3 | function      |          5 |
|  4 | Decorator    |          2 |
|  5 | iterator     |          2 |
+----+-----------+------------+
5 rows in set (0.00 sec)

Next, restore the data deleted after the backup

mysql -uroot -p111111 drop_test < 001bin.sql 

Error reporting

Edit 009bin SQL file, delete all the files below 293 lines prompted in the error message

Then re import

/*View data after recovery*/
MariaDB [drop_test]> select * from python_test;
+----+--------------+------------+
| id | name         | class_time |
+----+--------------+------------+
|  1 | dictionary         |          3 |
|  2 | list         |          2 |
|  3 | function         |          5 |
|  4 | Decorator       |          2 |
|  5 | iterator        |          2 |
|  6 | generator        |          2 |
|  7 | Method of class     |          5 |
+----+--------------+------------+
7 rows in set (0.00 sec)

 

The above is the recovery method for database tables or data deleted by mistake!

*******************Summary************************

This method can only recover mysql that starts binlog logs

Any operation on the database is prohibited during the recovery process

Database is the top priority of an enterprise. Backup must be done. Do not feel that backup is safe. Restore backup files regularly to check whether the backup files are synchronized with the production database data!