MySQL through the Bin-log recovery data method detailed

Today I saw my colleague accidentally deleted the user table in the MySQL database. Fortunately, there is a bin-log of MySQL on the log, so that the log can be used to restore previously backed up data. The specific operation is done by mysqlbinlog this instruction.

The code is as follows	Copy Code
/mysql/bin/mysqlbinlog--database=fox--start-date= "2013-01-22 5:00:00"--stop-date= "2013-01-22 9:00:00"/mysql/data /mysql-bin.000001 | /mysql/bin/mysql-u root-p123456-f

The specific parameters can be understood by looking at the name

Database: Specifying specific databases
Start-date: Start time
Stop-date: End Time
/MYSQL/DATA/MYSQL-BIN.000001:

The specified log file

If you do not back up the data, you must be a restore through the log file, remember the order can not be chaotic. If you don't have backup data, and the log is partially deleted, then it's a tragedy. As a result, restore is only a method, and the most important thing is to back up your data regularly. As long as the backup data is in, the log file is in, everything can be solved.
How to record some action logs at the same time
View All logs:

The code is as follows	Copy Code
Show master logs;

Binlog files currently in use

The code is as follows	Copy Code
Show Binlog events g;

A new Binlog log file is generated

The code is as follows	Copy Code
Flush logs;

Delete all binary logs and start recording from new

The code is as follows	Copy Code
Flush logs; Reset Master;

There is a sudden idea here, whether the previous log is cleaned up every time the data is backed up, so that once a problem occurs, you can start restoring from the most recent log. This is just one of my thoughts, please correct me if you have any questions.
At the same time, if you want to not do the wrong action delete this can be added to the MySQL when the parameters of the-u
Add parameter-u when starting MySQL

-u,–safe-updates only allow UPDATE and DELETE that uses keys.

His role is to prevent the execution of the delete without a conditional statement, if it is not added where the statement does not execute.

An example of a complete Mysqlbin-log log recovery process

1). Create a table:

The code is as follows	Copy Code
mysql>usetest; mysql>createtable t1 (id int); mysql>showtables;

2). Add two rows of data:

The code is as follows	Copy Code
Mysql>insertinto T1 VALUES (1); Mysql>insertinto T1 values (2); Mysql>select *from T1;

3. Back up existing data (09:30):

The code is as follows	Copy Code
#mysqldump-uroot-p123456 test-l-F >/tmp/test.sql

4. Add three rows of data (records) after the backup:

The code is as follows	Copy Code
Mysql>insertinto T1 VALUES (3); Mysql>insertinto T1 values (4); Mysql>insertinto T1 VALUES (5);

5). Suddenly the database is damaged or considered deleted:

The code is as follows	Copy Code
mysql>droptable T1;

6. Restore the database with the backed up Test.sql:

The code is as follows	Copy Code
#mysql-uroot-p123456 Test < Test.sql

7). Recover from Bin-log log:

The code is as follows	Copy Code
#/usr/local/mysql/bin/mysqlbinlog–no-defaults mysql-bin.000006|more//View the Bin-log log, On my imaginary machine, bin-log log starts from mysql-bin.000005. #/usr/local/mysql/bin/mysqlbinlog--no-defaults--stop-position= "272" mysql-bin.000006 |/usr/local/mysql/bin/mysql -uroot-p123456//Recovery Bin-log Log

If you do not open the bin log we can refer to the same method to open

One, Mysqlbin-log log

1. Open Mysqlbin-log Log

#vi/etc/my.cnf

The code is as follows	Copy Code
[Client] #password =your_password Port =3306 Socket =/tmp/mysql.sock Default-character-set-utf8 ....... [Mysqld] Port= 3306 socket=/var/lib/mysql/mysql.sock Log-slow-queries=mysql-slowlog Log-error=mysql.err Log=mysql.log log-bin=mysql-bin//Delete # symbol

2. View binary files (mysql-bin.******)

The code is as follows	Copy Code
#cd/usr/local/mysql/bin/&& ls

3. See if the Mysqbinlog log is open

The code is as follows	Copy Code
Mysql>showvariables like "%log-bin%;

4. Log operations related to Mysqlbin-log

The code is as follows	Copy Code
mysql>flushlogs;//this time there will be one more recent Bin-log log Mysql>showmaster status;//View Last bin Log mysql>resetmaster;//, empty all bin-log logs. Mysql>mysqlbinlog–no-defaults mysql-bin.******|more//View Bin-log Log