Practices for restoring data using MySQL binlog

Source: Internet
Author: User

Practices for restoring data using MySQL binlog

A few days ago, I worked overtime until. On the way back, a friend called and said that the development and maintenance staff of their company were updating the online system versions, accidentally drop the online database and ask me to fight the fire. Alas! Although I have been in the O & M field for many years, this is also the first time to go back to Kazakhstan. I am excited to go to the scene with excitement and worry. I am excited that I can do a good job and I am afraid that I will not be able to help my friends, alas, I don't talk much about it. I went to the "Battlefield ".

Step 1: Since the database has been killed and no master-slave mechanism is performed, we have to close all related system programs.

Step 2: Check the daily backup package. Fortunately, at half past three a.m., a full backup was made. The backup mode is to use the entire tar package directory, and the binlog function is enabled, today's binlog is still in progress. Decompress the tar package immediately. It takes nearly one hour to decompress the 10 thousand-character 50 GB package, hard disk read/write is really a pitfall.

Step 3: Check whether the backup can be used. In order to save time, directly extract the extracted library folder, directly MV it to mysql datadir, and start mysql, if you select the data of a table, the system prompts that the table does not exist. Do you have any problems with the backup data? The sweat is flowing out. show engines, view the engine, and check my. ini, Haha, I used the InnoDB engine and couldn't directly separate the mv library folder. No matter what, I directly copied the entire backup folder and then checked the data, the data before half past three am is intact. At this time, my mood began to look pretty. Even if I couldn't find the dropped data, at least most of the data was okay, only half-day data from half past three AM to AM is missing.

Step 4: Find the binlog of today, compare the time, find the mysql_bin.0000XX, directly cp to the tmp directory, and enter the command: mysqlbinlog -- start-date = "03:30:00" -- stop-date = "22:00:00" mysql_bin.0000xx>/tmp/test. SQL. Soon, the SQL statement was transferred out. VIM looked at the last SQL statement and confirmed that there was no drop database statement.

Step 5: log on to mysql and directly use source/tmp/test. SQL; wait for 15 minutes. After recovery, let them confirm whether there is any problem with the data, and finally confirm that there is no problem. So far, at half past two AM, I will close the job and go home ......

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.