Using Mysqlbinlog to recover MySQL database under CentOS

Source: Internet
Author: User
Tags mysql manual

What if the database was accidentally accidentally manipulated and not backed up in time? This is probably a kind of problem that the general coder often encounters.
I accidentally deleted a database today, but the last backup was 1 weeks ago, and the only solution was to recover it through mysqlbinlog. The solution is as follows:

If the MySQL server has binary logging enabled, you can use the Mysqlbinlog tool to recover data from a specified point in time (for example, from your last backup) until now or another specified point in time.
For information on enabling binary logging, see section 5.11, 3, "binary logs". For more information on Mysqlbinlog, see MySQL Manual section 8.6, "Mysqlbinlog: Utility for working with binary log files".
To recover data from a binary log, you need to know the path and file name of the current binary log file.
You can generally find the path from the configuration file (typically, Linux under MY.CNF, under Windows System for My.ini, depending on your system). If it is not included in the options file, it can be given as an option on the command line when the server is started.
The option to enable binary logging is –log-bin.
To determine the file name of the current binary log file, enter the following MySQL statement:
SHOW BINLOG EVENTS \g;
Alternatively, you can enter the following from the command line:
Mysql–user=root-pmypasswd-e ' SHOW BINLOG EVENTS \g ' replace the password mypasswd with the root password of your MySQL server.

For example, the resulting log file name is:
mysql-bin.000001 1. Specify the recovery time for MySQL5.1.54, you can specify the start and end time of the DateTime format in the Mysqlbinlog statement through the –start-date and –stop-date options.

For example, today 14:02 (today is March 15, 2012), accidentally executed SQL statement deleted a data table, but found that there is no latest backup (of course, this is only the development environment, not a formal production environment, the formal environment has to do regular data backup). To recover tables and data, you can restore a backup of a specified time by Mysqlbinlog, entering:
Mysqlbinlog–stop-date= "2012-03-15 14:02:00″/data1/log/mysql/mysql-bin.000001 | Mysql-u root-pmypasswd
This command restores all data as of the date and time given in datetime format in the –stop-date option.

If you do not have an SQL statement that detects an input error, you may want to restore the database activity that occurred later.
Based on these, you can use the date and time to run Mysqlbinlog again:
Mysqlbinlog–start-date= "2012-03-15 00:01:00″/data1/log/mysql/mysql-bin.000001 | Mysql-u root-pmypasswd

In this row, the SQL statement that is logged in from 0:01 today will run, and the combined execution of the dump file before the eve and the two lines of Mysqlbinlog can restore all data to a second before 0:01 today.
You should check the logs to make sure the time is correct. The next section describes how to implement it.

2. Specify the time period to recover the database activity records for the specified time period through mysqlbinlog–start-date and –stop-date, as follows:
Mysqlbinlog–start-date= "2012-03-09 02:00:00″–stop-date=" 2012-03-15 14:00:00″/data1/log/mysql/mysql-bin.000001 >/tmp/mysql_restore_030915.sql
In this way, you can get the last backup of the file time 2012-03-09 02:00:00 to today database active transaction operations for the period 2012-03-15 14:02 before the database is deleted

Using Mysqlbinlog to recover MySQL database under CentOS

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.