Automatic recovery of Mysql database log files ideas sharing and solutions _mysql

If the MySQL server has binary logging enabled, you can use the Mysqlbinlog tool to recover data starting at a specified point in time (for example, from your last backup) until now or another specified point in time. "Mysqlbinlog: Utility for processing 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 option file (that is, my.cnf or 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

You can also enter the following from the command line:

MySQL--user=root-pmy_pwd-e ' show binlog EVENTS \g '

Replace the password my_pwd with the server's root password.

1. Specify recovery time
For MySQL 4.1.4, you can specify the start and end times of the DateTime format through the--start-date and--stop-date options in the Mysqlbinlog statement. For example, suppose that at 10:00 today (today is April 20, 2006), execute the SQL statement to delete a large table. To recover the tables and data, you can restore the previous night's backup and enter:

Mysqlbinlog--stop-date= "2005-04-20 9:59:59"/var/log/mysql/bin.123456 \ 
 | mysql-u root-pmypwd

This command restores all data up to the date and time given in datetime format in the--stop-date option. If you do not detect the wrong SQL statement entered after a few hours, you may want to restore the activity that occurred later. Depending on these, you can use the date and time to run the Mysqlbinlog again:

Mysqlbinlog--start-date= "2005-04-20 10:01:00"/var/log/mysql/bin.123456 \ 
 | mysql-u root-pmypwd \

In this row, the SQL statement that logs in from 10:01 will run. The two rows of the dump file and Mysqlbinlog on the eve of the combined execution can restore all data to one second before 10:00. You should check the log to make sure the time is accurate. The next section describes how to implement.

2. Specify the recovery location

You can also specify the log location by using Mysqlbinlog options--start-position and--stop-position without specifying a date and time. They function the same as the start and end dates, and the difference is given the number of positions from the log. Using log locations is a more accurate method of recovery, especially when many transactions occur concurrently due to destructive SQL statements. To determine the location number, you can run Mysqlbinlog to find a time range that performs an unexpected transaction, but you should point the results back to a text file for inspection. The action method is:

Mysqlbinlog--start-date= "2005-04-20 9:55:00"--stop-date= "2005-04-20 10:05:00" \ 
 /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

This command creates a small text file in the/tmp directory and displays the SQL statement when the incorrect SQL statement is executed. You can open the file in a text editor and look for statements that you don't want to repeat. If the location number in the binary log is used to stop and resume the recovery operation, you should comment. Mark position with Log_pos plus a number. After restoring the previous backup file using the location number, you should enter the following from the command line:

Mysqlbinlog--stop-position= "368312"/var/log/mysql/bin.123456 \ mysql-u root-pmypwd 
- start-position= "368315"/var/log/mysql/bin.123456 \ 
| mysql-u root-pmypwd \

The 1th line above reverts to all transactions until the stop position. The next line restores all transactions from the given starting position to the end of the binary log. Because the output of Mysqlbinlog includes the set timestamp statements that precede each SQL statement, the recovered data and the associated MySQL log reflect the original time of the transaction execution.

1, the MySQL database does not have an incremental backup mechanism, when the amount of data is too large backup is a big problem. Fortunately, the MySQL database provides a master-slave backup mechanism, which is to write all the data in the primary database at the same time to the backup database. To achieve a hot backup of the MySQL database.

2, to realize the dual-machine hot standby first to understand the requirements of the master and slave database server version. To achieve a hot standby version of MySQL is higher than 3.2, there is also a basic principle is that the database version from the database can be higher than the primary server database version, but not lower than the primary server version of the database.

3, set up the main database server:

A. First see if the version of the primary server is a backup-hot version. Then look at the configuration of the MYSQLD configuration block in My.cnf (Unix-like) or My.ini (Windows) there is no Log-bin (logging database change log), because the MySQL replication mechanism is based on the log replication mechanism, so the primary server must support the change log. Then set up the database to write to the log or do not write to the log. This will only make changes to the database that you are interested in writing to the database log.
　　

Server-id=1//Database ID This should default to be 1 without changing
the name of the Log-bin=log_name//log file, where you can make a log to another directory if not set then a log name
for the default host name Binlog-do-db=db_name//Log database
Binlog-ignore-db=db_name//Log database
above if more than one database is separated by "," then set the user account for the synchronization database
mysql> GRANT REPLICATION SLAVE on *.*
-> to 
[email= ' repl ' @ '%.mydomain.com '] ' repl ' @ '%.mydomain.com　　' [/email]
identified by ' Slavepass ';
4.0.2 previous versions, because REPLICATION to use the following statement to implement this feature
mysql> GRANT FILE on *.*
-> to 
[email= ' Repl ' @]%.my Domain.com '] ' repl ' @ '%.mydomain.com ' [/email]
identified by ' Slavepass ';

Restart the database after setting up the configuration file for the primary server

B. Lock the existing database and back up the current data
Lock the database
mysql> FLUSH TABLES with READ lock;
There are two ways to back up a database one way is to go directly to MySQL's data directory and then pack the folders where you need to back up the database, and the second is to use mysqldump to back up the database but add the "--master-data" parameter, 　　It is recommended that you use the first method to back up the database
C. View the status of the primary server
mysql> show Master STATUSG
+---------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | | test | manual,mysql | +---------------+----------+--------------+------------------+
Record the values of File and Position items for later use.