Shulou(Shulou.com)06/01 Report--

Bin log data recovery of Mysql: accidentally delete the database

Because I accidentally deleted a whole database Schema of Mysql on the test machine, I didn't make a backup because it was a test machine, and now I use the Bin log method of MySQL to restore the previous database.

Of course, the prerequisite for data recovery of Bin log is that you have turned on the function of Bin log. If you have not backed up the data or opened the Bin log log, then you may need to consider snapshots and other ways to restore from the system's point of view.

Bin log is commonly used for incremental data backup and recovery, as well as database master-slave replication. If it is not enabled, you can open it in the following ways:

1. Turn on the binlog function of mysql

Mysql supports incremental backups, but turn on mysql's bin log feature.

Modify the configuration file for mysql. Linux is / etc/my.cnf,windows is the installation directory of mysql / my.ini

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

# Replication Master Server (default) # binary logging is required for replicationlog-bin=mysql-bin# binary logging format-mixed recommendedbinlog_format=mixed.

2. Check the binary log status as follows: whether to enable it or not

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 views the operation log of binary log files.

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

[root@test mysql] # mysqlbinlog-- start-position=0-- stop-position=500 mysql-bin.000091Warning: option 'start-position': unsigned value 0 adjusted to 4pm "50530 SET @ SESSION.PSEUDOVLAVEE models 1" SET "40019 SET @ @ session.max inserted delayedthreads" 0 "SET" 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 proper.ROLBACKG words on account of BINLOG 'y7MoVg8BAAAAAZwAAAGsAAABAAQANS41LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAv v 151022 18:00:43 at startup# Warning: this binlog is either in use or was not closed properly.ROLLACKAAAAAADLsyhWEgAEBAEEgAAAABAAAAQANS41LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAv 5.5.38-log created 151022 18:00:43 at startup# Warning: server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.38-log created 151022 18:00:43 MoVg8BAAAAAZWxvZwAAAAAAAAAAAAAAAA SET @ @ session.foreign_key_checks=0, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=0, @ @ session.session .sqlchocolate modewords 1608515584, SET @ @ session.session .autoincrementalization offsetbacks @ @ session.auto_increment_increment=1, @ @ session.autoincrementalization offsetbacks @ @ session. Session * / / *! * /; SET @ @ session. Session. SET @ @ session.lcroomtimekeeper namespace 0xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; # at 19851022 23:27:50 server id 1 end_log_pos 346 Query thread_id=2 exec_time=0 error_code=0

5. Recover the data through Bin log. Because I deleted the entire Schema and did not back up, I happened to open the bin log log, so I re-executed the historical bin-log and restored it to the previous version mistakenly deleted. (I have a total of 91 files here, which are processed in batches): (9999999999999: in order to omit to find the start and end position of each bin-log log file, set an infinite number to simplify the operation.)

# 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, be sure to back up regularly; 2, if there is a backup, then the recovery is faster, you can do incremental backup from the backup point in time, you do not need to run all 91 files from scratch here, of course, I use the editor batch processing is also quite fast; 3, be sure to open the Bin-log log, if you do not do a backup can also be restored through the Bin-log log. 4. Be careful in operation.

Other:

1. There is also a sql_log

Mysql > show variables like 'sql_log_%'

Mysql enables and closes sql binary logs:

Mysql > set sql_log_bin=0; / / close

Set session sql_log_bin=0

2. Find the file location:

Find /-name my.cnf

3. Linux to view the full path of the current directory

Pwd command:

/ var/lib/mysql

4. Check the current binary log:

Mysql > show master status

5. Set the number of days of binary logs rollback in my.cnf/my.ini:

Expire_logs_days = 7

6. View the bin log log of Master

Mysql > show master logs +-+ | Log_name | File_size | +-+-+ | log-bin.000001 | 98 | +-+-+ 1 row in set (0. 00 sec)-

The above is the Mysql Binlog data recovery and integration introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.