MySQL recovers data by Binlog logs

Brief introduction

There is a business scenario in the production process: for example, I backed up the database by mysqldump in 2016-11-19 09:30:00, but the database crashed in 2016-11-19 10:30:00. If we restore through our backup file, we can only restore to 2016-11-19 09:30:00 this time point, and 9.30 minutes to 10:30 minutes of this one hours of data recovery is not possible, then we will be able to use the MySQL binlog log to recover the hour of no backup to the data.

1. Turn on MySQL binlog log (not enabled by default)

Add a row #vim/etc/my.cnf (you can also specify the path to the Binlog log)
Log-bin = Mysql-bin

#mysql5.7 By default, the Binlog log file is placed in the/usr/local/mysql/data/directory by default

2, with Mysqlbinlog can view the information in the Binlog log

./mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin. 000003

3, according to the location recovery #mysqlbinlog--start-position=134--stop-position=330 mysql-binglog.000001 >/root/test1.txt
Enter MySQL Import
Mysql> Source/root/test1.txt
--start-position start position--stop-position End position 4, according to date #./mysqlbinlog--start-date= "2016-11-10 17:30:05"--stop-date= " 2016-11-10 18:00:00 "mysql-binlog.000002 >/root/test2.sql
Mysql> source/root/test2.txt--start-date start time--stop-date end time
Or
#./mysqlbinlog--start-date= "2016-11-10 17:30:05"--stop-date= "2016-11-10 18:00:00" mysql-binlog.000002 |./mysql- Uroot-padmin
Import SQL statements from 2016-11-10 17:30:05 to 2016-11-10 18:00:00 in the mysql-bin.000002 log file into MySQL test
----If there are multiple Binlog files, separated by spaces, complete path 5, supplemental mysqldumpbackup [[email protected] tmp]#/usr/local/mysql/bin/mysqldump-uroot-padmin test-l-F >/tmp/test.sql #备份 database Test to Tmpl Recorded in the Test.sql file,-L represents a read lock, in the process of backup MySQL can not write data, so as to ensure the integrity of the data-F means flust logs, regenerate new log files, of course, including Bin-log logs./mysql-uroot-padmin Test-v-F </tmp/test.sql #通过/tmp/test.sql File Recovery Test Database-V View import details---encountered an error during the import process, skipped, and continued down execution

MySQL recovers data by Binlog logs