Shulou(Shulou.com)06/01 Report--

How to recover mysql logs? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

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

The method of mysql log recovery:

To recover data from a binary log, you need to know the path and file name of the current binary log file. You can usually find the path in the options file (i.e. 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 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

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 time of the DATETIME format with the-- start-date and-- stop-date options in the mysqlbinlog statement. For example, suppose that at 10:00 today (today is April 20, 2006), the SQL statement is executed to delete a large table. To restore tables and data, you can restore the backup from the previous night and type:

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 don't detect the wrong SQL statement entered a few hours later, you may want to resume the activity that occurs later. Based on these, you can use the date and time to run mysqlbinlog again:

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 will run. Combining the dump file the night before execution and the two lines of mysqlbinlog can restore all data to the second before 10:00 You should check the log to make sure the time is right. The next section describes how to implement it.

two。 Specify recovery location

You can also use the mysqlbinlog options-- start-position and-- stop-position instead of specifying a date and time to specify the log location. They work the same as the start and end date options, except that they give the location number from the log. Using log locations 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 frame in which an unwanted transaction was executed, but the result should be redirected to the text file for checking. The method of operation 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 that displays the SQL statement when the wrong SQL statement was executed. You can open the file with a text editor and look for statements you don't want to repeat. Comments should be made if the location number in the binary log is used to stop and continue the restore operation. 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-pmypwdmysqlbinlog-- start-position= "368315" / var/log/mysql/bin.123456 / mysql-u root-pmypwd /

Line 1 above resumes all transactions up to the stop position. The next line resumes all transactions from the given starting position to the end of the binary log. Because the output of mysqlbinlog includes the previous SET TIMESTAMP statement recorded by each SQL statement, the recovered data and the associated MySQL log will reflect the original time of transaction execution.

1. There is no incremental backup mechanism for mysql database, so backup is a big problem when the amount of data is too large. Fortunately, mysql database provides a master-slave backup mechanism, which is to write all the data of the master database to the backup database at the same time. Realize the hot backup of mysql database.

2. In order to realize the hot backup of dual computers, we must first understand the requirements of the version of the master-slave database server. In order to achieve a hot backup mysql version higher than 3.2, another basic principle is that the database version of the slave database can be higher than that of the master server, but not lower than the database version of the master server.

3. Set up the master database server:

a. First check to see if the version of the primary server supports hot backup. Then check whether the configuration of the mysqld configuration block in my.cnf (class unix) or my.ini (windows) has log-bin (logging database change logs). Because the replication mechanism of mysql is a log-based replication mechanism, the master server must support change logs. Then set the database to write to the log or the database not to write to the log. In this way, only changes to the database that you are interested in are written to the log of the database.

The id of server-id=1 / / database should not be changed if it is 1 by default.

The name of the log-bin=log_name / / log file, where you can specify a log name for the log to another directory if it is not set, the default hostname

Binlog-do-db=db_name / / Database for logging

Binlog-ignore-db=db_name / / databases that do not log

If more than one database is split with ",", and then set up the user account of the synchronous 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 to use the following statement to implement this function

Mysql > GRANT FILE ON *. *

-> TO

[email='repl'@'%.mydomain.com'] 'repl'@'%.mydomain.com' [/ email]

IDENTIFIED BY 'slavepass'

Restart the database after setting up the configuration file for the master 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 the database. One is to go directly to the data directory of mysql and then package the folder where you need to backup the database. The second is to use mysqldump to back up the database but add the parameter "--master-data". It is recommended to use the first method to back up the database.

How to unlock a table:

Unlock tables

c. View the status of the primary 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 projects, which you will use later.

Thank you for reading! After reading the above, do you have a general understanding of the method of mysql log recovery? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.