How to restore mysql log-Mysql Tutorial-php.cn

Mysql log recovery method: First specify the recovery time, the code is [mysqlbinlog --stop-date=""]; then specify the recovery location, the code is [mysqlbinlog --start-date="2020-10 -09 9:55:00"].

How to restore mysql log

Mysql log recovery method:

To recover data from the binary log, you need to know the current binary log The path and file name of the file. The path can generally be found from the options file (i.e. my.cnf or my.ini, depending on your system). If not included in the options file, it can be given as an option on the command line when the server starts. 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

Copy after login

You can also enter the following from the command line:

mysql --user=root -pmy_pwd -e &#39;SHOW BINLOG EVENTS /G&#39;

Copy after login

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

1. Specify the recovery time

For MySQL 4.1.4, you can specify the start and end time in DATETIME format through the --start-date and --stop-date options in the mysqlbinlog statement. For example, assume that at 10:00 this morning (today is April 20, 2006), execute a SQL statement 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

Copy after login

This command will restore all backups up to the date and time given in the --stop-date option in DATETIME format data. If you don't detect an incorrect SQL statement entered several hours later, you may want to resume the activity that occurred later. Based on these, you can run mysqlbinlog again using the date and time:

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

Copy after login

In this line, the SQL statement for the login from 10:01 AM will be run. Combining the dump file from the night before the execution and two lines from mysqlbinlog can restore all data to one second before 10:00 AM. You should check the logs to make sure the times are accurate. The next section explains how to do this.

2. Specify the recovery location

You can also specify the log location by using the mysqlbinlog options --start-position and --stop-position without specifying the date and time. They have the same effect as the start and end date options, except that they give a position number from the log. Using log locations is a more accurate recovery method, especially when many transactions are occurring simultaneously due to destructive SQL statements. To determine the position number, you can run mysqlbinlog to find the time range in which the unexpected transaction was performed, but redirect the results to a 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

Copy after login

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 look for statements you don't want to repeat. Position numbers in the binary log should be commented if they are used to stop and continue recovery operations. Use log_pos plus a number to mark the position. After restoring the previous backup file using the position 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 /

Copy after login

Line 1 above will restore all transactions up to the stop point. The next line will restore 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 logged, the recovered data and related MySQL logs will reflect the original time of transaction execution.

1. 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 actually writes all the data in the master database to the backup database at the same time. Implement hot backup of mysql database.

2. If you want to realize hot backup of two machines, you must first understand the version requirements of the master-slave database server. To achieve hot standby, the MySQL version must be higher than 3.2. Another basic principle is that the database version of the slave database can be higher than the master server database version, but it cannot be lower than the master server database version.

3. Set up the main database server:

a. First check whether the version of the main server supports hot standby. Then check whether the configuration of the mysqld configuration block in my.cnf (unix-like) or my.ini (windows) has log-bin (record database change log). Because the replication mechanism of mysql is a log-based replication mechanism, the main server must It needs to support change logs. Then set the database to which the log should be written or the database where the log should not be written. This way only changes to the database that interest you are written to the database's log.

server-id=1 //The id of the database should be 1 by default and does not need to be changed
log-bin=log_name //The name of the log file, you can specify the log here If there is no setting in other directories, a log name of the default host name
binlog-do-db=db_name //Database for recording logs
binlog-ignore-db=db_name //No recording Log database
If there are multiple databases above, use "," to separate them, and then set the user account for synchronizing the database
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO
[email='repl'@'%.mydomain.com']'repl'@'%.mydomain.com'[/email]
IDENTIFIED BY 'slavepass';
4.0.2 以前的版本, 因为不支持 REPLICATION 要使用下面的语句来实现这个功能
mysql> GRANT FILE ON *.*
-> TO
[email='repl'@'%.mydomain.com']'repl'@'%.mydomain.com'[/email]
IDENTIFIED BY 'slavepass';

设置好主服务器的配置文件后重新启动数据库

b.锁定现有的数据库并备份现在的数据

锁定数据库

mysql> FLUSH TABLES WITH READ LOCK;

Copy after login

备份数据库有两种办法一种是直接进入到 mysql 的 data 目录然后打包你需要备份数据库的文件夹，第二种是使用 mysqldump 的方式来备份数据库但是要加上"--master-data " 这个参数，建议使用第一种方法来备份数据库.

解除表的锁定的方法：

unlock tables;

Copy after login

c.查看主服务器的状态

mysql> show master statusG;

+---------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| mysql-bin.003 | 73 | test | manual,mysql |

+---------------+----------+--------------+------------------+

记录 File 和 Position 项目的值，以后要用的。