Hello, everyone!
I'm sure you've heard of MySQL's binlog, which records the entire life cycle of the database and can be used to recover data or synchronize data from the database.
So if database deletion occurs by mistake, exactly how to recover data?
Here's an example to show the dawgs how it works, so let's get started.DO IT!
data backup
First of all, the database should be backed up regularly, because if you need to restore the data, you can narrow down the scope of binlog execution
mysqldump -uroot -p --master-data=2 --single-transaction -A > /www/server/data/
A parameter of 2 for master-data allows you to log more details, such as location information, to facilitate binlogging--start-positionlocalization
Binlog Recovery Data
Simulation of accidental deletion
First, add a new piece of test data and then make a backup of the library
Then we added new data, but accidentally emptied the table
So how do we recover the data?
Execute it in the test library first if you can, and then execute it in production if there are no problems.
Restore data
1. Execute the backed up file
You can see that the data has been restored to what it was when mysqldump backed it up
2. Execute binlog
Check the end position of the file backed up by mysqldump, as shown in the figure, it is 1969
Note that this MASTER_LOG_POS is at the top of the file, just use PgUp to flip to the top.
Then query the binlog file mysql-bin.000011, which is garbled if viewed directly by vim, so we output the file in plaintext (-vv) to another file
/www/server/mysql/bin/mysqlbinlog mysql-bin.000011 -vv >
If you are prompted with unknown variable 'default-character-set=utf8'. error, then you need to add the --no-defaults parameter to the mysqlbinlog
Then vim again to view the code is not messed up, from the figure can be seen truncate operation in the location of 902120
Then perform the recovery operation
/www/server/mysql/bin/mysqlbinlog --start-position=1969 --stop-position=902120 /www/server/data/mysql-bin.000011 | mysql -uroot -p
This completes the data recovery
Dang, is not quite simple, all friends of the Taoist practice up if there are differences welcome to the small road message to the
