If the MySQL careless operation error results in data errors or lost at this time Binlog played a great role in recovering there are several ways 1. Recovery by Time--start-datetime If the point of time is determined, then recovery by time is a good thing, Usually through daily regular backup + differential backup (log) if daily backup at 4 points, the time of error at 12:00, 12:30 found, first we have to confirm 12 points out what is wrong, 12 points after the data can not continue to use, if not affect, So we just have to skip the 12:00 data, and first, restore the 4-point backup. Then, get 4 to 11:59 of the data through Binlog, get the data can directly affect the database, avoid two pollution, the best Mr. SQL file mysqlbinlog --start-datetime= "2014-11-07 04:01:00" --stop-datetime= "2014-11-07 11:59:00" / Data/mysql/data/mysql-bin.000020 >/home/madong/aa.sql is getting data from 12:01-12:30 mysqlbinlog -- Start-datetime= "2014-11-07 12:01:00" --stop-datetime= "2014-11-07 12:30:00" /data/ Mysql/data/mysql-bin.000020 >>/home/madong/aa.sql last via MySQL command line source /home/madong/aa.sql If the data after 12 is not still available then we only need to recover to 12 points, 12:00-12:30 data does not need to recover 2. Through--start-position node thinking and the above, the first to determine the node ID, in the recovery Example: 2014-11-07 13:50 game database data all empty fault discovery time: 13:50 failure time: 13:40 left and right end time: 15:55 recovery process: Failure trigger, game immediately close, maintenance, The cause of the failure is known (the data is gone) 1. First Confirm recovery point recovery with 2014-11-07 13:302. Since the first time, so prepare to recover from the most primitive, not from4-Point full backup node recovery readiness not to operate directly, Mr. Cheng Sql, mysqlbinlog --start-datetime= "2014-11-06 09:50:00" -- Stop-datetime= "2014-11-07 13:30:00" /data/mysql/data/mysql-bin.000020 >/home/madong/ Aa.sql (Aa.sql 500M approx.) 3. After the database is reset and the player registers, if the import data will have a primary key conflict, then empty the database again (keep the base table data) 4. Import SQL into MySQL command line and switch database (use DBNAME) source/ Home/madong/aa.sql execution start time 14:57 end time 15:54 approx. one hours
MySQL recovers data through Binlog