What should I do if mysql accidentally deletes the database?

The solution to mysql accidentally deleting the database: first open the binlog function of mysql; then check the binary log status; then check the operation log of the binary log file; finally restore the data through the Bin log.

Add a line of log-bin code under [mysqld], as follows:

# Replication Master Server (default)
 # binary logging is required for replication
 log-bin=mysql-bin

 # binary logging format - mixed recommended
 binlog_format=mixed.  

2. Use the following method to view the binary log status: whether it is enabled

mysql> show variables like 'log_%';

3. View all binary log files:

mysql> show libary logs;

mysql> show binary logs;
 +------------------+------------+
 | Log_name | File_size |
 +------------------+------------+
 | mysql-bin.000001 | 201 |
 | mysql-bin.000002 | 351 |
 | mysql-bin.000003 | 276 |
 | mysql-bin.000004 | 201 |
 | mysql-bin.000005 | 16509 |

4. Mysql checks the operation log of the binary log file

#mysqlbinlog –start-position=0 /mydata/data/mysql-bin.000089

[root@test mysql]# mysqlbinlog --start  -position=0 --stop-position=500 mysql-bin.000091
 Warning: option 'start-position': unsigned value 0 adjusted to 4
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 # at 4
 #151022 18:00:43 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.38-log created 151022 18:00:43 at startup
 # Warning: this binlog is either in use or was not closed properly.
 ROLLBACK/*!*/;
 BINLOG & # 39;
 y7MoVg8BAAAAZwAAAGsAAAABAAQANS41LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 AAAAAAAAAAAAAAAAAAADLsyhWEzgNAAgAEgAEBAQEEgAVAAEGggAAAAICAgCAA==
 '/*!*/;
 # at 107
 #151022 23:27:50 server id 1 end_log_pos 198 Query thread_id=2 exec_time=0 error_code=0
 SET TIMESTAMP=1445527670/*!*/;
 SET @@session.pseudo_thread_id=2/*!*/;
 SET @@session.foreign_key_checks=0, @@session.sql_auto_is_null=0, @@session.unique_checks=0, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=1608515584/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\\\\C utf8 *//*!*/;
 SET @@session.character_set_client=33,@@session.collation_cOnnection=33,@@session.collation_server=8/*!*/;
 SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 DROP SCHEMA IF EXISTS `pandora`
/*!*/;
 # at 198
 #151022 23:27:50 server id 1 end_log_pos 346 Query thread_id=2 exec_time=0 error_code=0
5. Restore data through Bin log  .Because I deleted the entire Schema and didn’t back it up, I just turned on the bin log log, so I re-executed the historical bin-log and restored it to the version before the accidental deletion, (I have a total of 91 here  Files, batch processing): (9999999999999: to save the need to find the start and end positions of each bin-log log file, set an infinite number to simplify operations.)
#mysqlbinlog /var/lib/mysql/mysql-bin.000001 --start-position=0 --stop-position=9999999999999 | mysql -uroot -p123456
 #mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-position=0 --stop-position=9999999999999 | mysql -uroot -p123456
 #mysqlbinlog /var/lib/mysql/mysql-bin.000003 --start-position=0 --stop-position=9999999999999 | mysql -uroot -p123456
 ... ...
So the conclusion is: 

1. Remember to back up regularly;
2. If you have a backup, you can restore it  Faster, you can do incremental backups from the backup point in time, you don’t need to run all 91 files in batches from the beginning like I do here, of course I use the editor to batch process it is pretty fast;
3  , In addition, you must open the Bin-log log. If you have not made a backup, you can also restore it through the Bin-log log.  
4. Be careful in operation.  

Others:
1. There is also a sql_log
mysql> show variables like 'sql_log_  %';
Mysql opens and closes the sql binary log: 
mysql> set sql_log_bin=0; // close
set session sql_log_bin=0;
2. Find the file location:
find / -name my.cnf
3. Linux View the full path of the current directory
find / -name my.cnf
3.  strong>

pwd command:
/var/lib/mysql
4. View the current binary log:
mysql>show master status;
5. Set the binary logs rollback days in my.cnf/my.ini:
expire_logs_days =  7
6. View Master’s bin log
mysql> show master logs;
 +-----------------+-----------+
 | Log_name | File_size |
 +-----------------+-----------+
 | log-bin.000001 | 98 |
 +-----------------+-----------+
 1 row in set (0.00 sec)
 ---------------------
The above is the detailed content of how to do if mysql accidentally deletes the database. For more information, please pay attention to 1024programmer.com Other related  article!