"Go" "MySQL" MySQL through bin-log recovery data method detailed

MySQL bin-log in MySQL by default is not open, we have to turn on MySQL to turn on the Bin-log function, and then through the backup of the Bin-log database recovery.

The specific operation is done by Mysqlbinlog this command.

/MySQL/bin/--database=fox--start-date= "2013-01-22 5:00:00"  

Specific parameters look at the name to understand

Database: Specify the 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 have to pass the log file a restore, remember the order can not mess. If you do not back up the data, and the log is partially deleted, then it is tragic. Therefore, restoring is just one way, and most importantly, backing up data on a regular basis. As long as the backup data is in, the log file is in, everything can be resolved.
How to record some operation logs at the same time
View All logs:

Show master logs;

Binlog files currently in use

Generate a new Binlog log file

Flush logs;

Delete all binary logs and record from new start

flush logs;  Reset Master;

There is a sudden idea of whether to clean up the previous log after each backup, 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 operate the error delete this can be added to MySQL when you start the parameter
When you start MySQL, add the parameter-u

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

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

A complete instance of the Mysqlbin-log log recovery process

1). Create a table:

MySQL>usetest;mysql>int); MySQL>showtables;

2). Add two rows of data:

MySQL>values(1); MySQL>values(2  ); MySQL>Select* from T1;

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

-uroot--->/tmp/Test.sql

4). Add three rows of data (record) after the backup is complete:

MySQL>values(3); MySQL>values(4 ); MySQL>values(5);

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

MySQL>droptable t1;

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

-uroot-< Test.sql

7). Restore from Bin-log log:

#/Usr/Local/Mysql/Bin/Mysqlbinlog–no-Defaults MySQL-Bin.000006|More//View Bin-Log logs, on my virtual Force machine bin-Log logs from MySQL-bin.000005 Start #/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. Turn on Mysqlbin-log log

#vi/etc/my.cnf

[Client]#password=Your_passwordport=3306Socket=/Tmp/Mysql.sockdefault-character-Set-UTF8 .....[mysqld]Port= 3306Socket= /var/Lib/Mysql/Mysql.sockLog-Slow-Queries=Mysql-SlowlogLog-Error=Mysql.errLog=Mysql.LogLog-Bin=Mysql-Bin//Delete # symbol

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

#cd/usr/local/mysql/bin/&& ls

3. Check if the Mysqbinlog log is turned on

MySQL> like "%log-bin%;

4. Log operations related to Mysqlbin-log

MySQL>flushlogs; // at this time there will be one more up-to-date bin-log logs mysql>showmaster status; // View last bin log MySQL >resetmaster; // Clear All bin-log logs mysql>mysqlbinlog–no-defaults mysql- bin. ******| More// View bin-log logs

Original address: http://www.android100.org/html/201406/20/28082.html

"Go" "MySQL" MySQL through bin-log recovery data method detailed