Example of using Mysqlbinlog to restore a MySQL database

MYSQL enable log

[Root @ jianshe99] # whereis my. ini
[Root @ jianshe99] # vi/etc/my. cnf
[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql
# Default to using old password format for compatibility with mysql 3.x
# Clients (those using the mysqlclient10 compatibility package ).
Old_passwords = 1
[Mysqld_safe]
Log-error =/var/log/mysqld. log
Long_query_time = 1
Log-slow-queries = slowqueris. log
Log-queries-not-using-indexes = nouseindex. log
Log = mylog. log
Pid-file =/var/run/mysqld. pid

--------------------------------------------------------

View Logs

Whether logs are enabled

Mysql> show variables like 'log _ bin ';

How to know the current log

Mysql> show master status;

Use mysqlbinlog to view binary log files

Shell> mysqlbinlog mail-bin.000001

Or shell> mysqlbinlog mail-bin.000001 | tail

Use a similar command in Windows.

Mysql has the following types of logs:

Error log:-log-err
Query log:-log
Slow query log:-log-slow-queries
Update log:-log-update
Binary log:-log-bin

In the installation directory of mysql, open my. ini, add the above parameters to the end, save and restart the mysql service.
LAST_UPDATED2
-------------------------------------------------------------

If the MySQL server enables binary logs, you can use the mysqlbinlog tool to restore data from the specified time point (for example, from your last backup) until now or another specified time point. For information on enabling binary logs, see section 5.11.3 "binary logs ". For more information about mysqlbinlog, see mysql Manual Section 8.6, "mysqlbinlog: a utility used to process binary log files ".

To restore data from binary logs, you need to know the path and file name of the current binary log file. You can find the path from the option File (my. cnf or my. ini, depending on your system. If it is not included in the option file, you can give it as an option in the command line when the server starts. The option to enable binary logs 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 content from the command line:

Mysql -- user = root-pmy_pwd-e 'show binlog events G'

Replace 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 in the mysqlbinlog statement to specify the start and end times of the DATETIME format. For example, if you run an SQL statement to delete a large table at this morning (April 20, 2005. To restore tables and data, you can restore the backup of the previous night and enter:

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

This command restores all data of the date and time given in DATETIME format in the -- stop-date option. If you do not detect the wrong SQL statement entered in a few hours, you may want to resume subsequent activities. Based on these, you can use the start time to run mysqlbinlog again:

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

In this row, the SQL statement for logging on from AM will run. Combine the dump file on the eve of execution with the mysqlbinlog line to restore all data to one second before am. You should check the log to ensure the exact time. The next section describes how to implement it.

2. Specify the recovery location

You can also use the mysqlbinlog options -- start-position and -- stop-position to specify the log location without specifying the date and time. They serve the same purpose as the start and end date options. The difference is that they provide the location number starting from the log. Using Log locations is a more accurate recovery method, especially when many transactions occur simultaneously due to destructive SQL statements. To determine the location number, run mysqlbinlog to find the time range for executing the unexpected transaction, but re-direct the result to the text file for inspection. The operation is as follows:

Mysqlbinlog -- start-date = "9:55:00" -- stop-date = "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 an incorrect SQL statement is executed. You can open the file in a text editor to find statements that you do not want to repeat. If the location number in the binary log is used to stop and continue the restoration operation, comment out the position number. Use log_pos to add a number to mark the position. After you use the location number to restore the previous backup file, enter the following content 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 above 1st rows restore all transactions until the stop position. The next row restores all transactions from the given starting position to the end of the binary log. Because the output of mysqlbinlog includes the set timestamp statement recorded before each SQL statement, the recovered data and related MySQL logs reflect the original time when the transaction was executed.