If the MySQL server has enabled the binary log, you can use the mysqlbinlog tool to recover from a specified point in time Data from the beginning (for example, from the last time you backed up) to the present or another specified point in time."mysqlbinlog: A practical tool for processing binary log files."

To restore data from the binary log, you need to know the path and file name of the current binary log file.Generally, you can find the path from the option file (ie my.cnf or my.ini, depending on your system).If it is not included in the option 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 root password of the server.

1.Specify the recovery time
For MySQL 4.1.4, you can use the--start-date and--stop-date options to specify the start and end times in DATETIME format in the mysqlbinlog statement.For example, suppose that at 10:00 this morning (today is April 20, 2006), a SQL statement is executed to delete a large table.To restore 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 will restore all data up to the date and time given in the DATETIME format in the--stop-date option.If you do not detect the wrong SQL statement entered a few hours later, you may want to resume the activities that occurred later.Based on these, you can run mysqlbinlog again with the date and time:

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

In this line, the SQL statement logged in from 10:01 AM will be executed.Combining the dump file on the eve of execution and the two lines of mysqlbinlog can restore all data to one second before 10:00 am.You should check the log to make sure the time is accurate.The next section describes how to do it.

2.Specify the recovery location

You can also use the mysqlbinlog options--start-position and--stop-position without specifying the date and time to specify the log position.Their function is the same as the start and end date options, except that the position number from the log is given.Using the log location is a more accurate recovery method, especially when many transactions occur at the same time due to destructive SQL statements.To determine the location number, you can run mysqlbinlog to find the time range in which unexpected transactions were executed, but the results should be redirected to the text file for inspection.The operation 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 will create a small text file in the/tmp directory and will display the SQL statement when the wrong SQL statement is executed.You can open the file with a text editor and search for sentences you don't want to repeat.If the position number in the binary log is used to stop and continue the recovery operation, it should be commented.Use log_pos plus a number to mark the position.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
mysqlbinlog--start-position="368315"/var/log/mysql/bin.123456 \
| mysql-u root-pmypwd \

The first line above will resume all transactions up to the stop position.The next line will resume all transactions from the given starting position until the end of the binary log.Because the output of mysqlbinlog includes the SET TIMESTAMP statement before each SQL statement is recorded, the restored data and related MySQL logs will reflect the original time of 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 actually to write all the data of the master database to the backup database at the same time.Realize hot backup of mysql database.

2.If you want to realize dual-system hot backup, you must first understand the version requirements of the master-slave database server.To achieve the hot standby mysql version must be higher than 3.2, and a basic principle is that the database version of the slave database can be higher than the database version of the master server, but not lower than the database version of the master server.

3.Set the main database server:

　a.First check whether the version of the main server is a version that supports hot backup.Then check whether the configuration of the mysqld configuration block in my.cnf (like unix) or my.ini (windows) has log-bin (record database change log), because the replication mechanism of mysql is a log-based replication mechanism, so the primary server must be To support the change log.Then set the database to be written to the log or the database not to be written to the log.In this way, only the changes of the database you are interested in are written to the database log.
　　

server-id=1//The id of the database should be 1 by default, so there is no need to change it
　　Log-bin=log_name//The name of the log file, where you can specify the log to another directory.If not set, the default host name is a log name
　　Binlog-do-db=db_name//Database for logging
　　Binlog-ignore-db=db_name//Database without logging
　　 If there are multiple databases above, use "," to separate, and then set the user account to synchronize the database
　　Mysql> GRANT REPLICATION SLAVE ON *.*
　-≫ TO
[email='repl'@'%.mydomain.com']'repl'@'%.mydomain.com'[/email]
IDENTIFIED BY'slavepass';
　　 Versions prior to 4.0.2, because REPLICATION is not supported, use the following statement to achieve this function
　　Mysql> GRANT FILE ON *.*
　-≫ TO
[email='repl'@'%.mydomain.com']'repl'@'%.mydomain.com'[/email]
IDENTIFIED BY'slavepass';

　　 After setting the configuration file of the main server, restart the database

　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 the database.One is to go directly to the mysql data directory and package the folder you need to back up the database.The second is to use mysqldump to back up the database but add the "--master-data" parameter , It is recommended to use the first method to back up the database
　　C.View the status of the main server
　　Mysql> show master statusG;
+---------------+----------+--------------+------------------+
　　| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
　　| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+
　　 Record the values ​​of the File and Position items, which will be used later.