Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of mysqlbinlog tool based on log recovery. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1) RESET MASTER

In the folder where the log is viewed above, the binary log is named in the format that mysql-bin.*,* represents the sequence number of the log, which is incremented, and mysql-bin.index is the index file of the log, which records the maximum sequence number of the log.

We perform RESET MASTER naming to delete all logs

View the deleted log

As you can see, all the previous logs have been emptied, and the new logs start in 00001.

2) PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE

Execute the PURGE MASTER LOGS TO 'mysql-bin.*' command to delete all logs before the' * 'number

Execute the PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss' command to delete all logs before the' yyyy-mm-dd hh:mm:ss' time

3)-EXPIRE_LOGS_DAYS

This parameter sets the number of days that the log expires, and the expired log will be deleted automatically, which is helpful to reduce the workload of managing the log and need to modify the my.cnf.

Here we set the log to be saved for 3 days, and the expired logs will be deleted automatically after 3 days.

Restore

Bin-log is an operation that records all events of mysql. When a catastrophic error occurs in mysql, full recovery, point-in-time recovery, and location-based recovery can be done through bin-log.

Full recovery, suppose we use mysqldump to back up the database at 2: 00 am every day, but at 9: 00 the next morning, due to a database failure, the data cannot be accessed and the data needs to be restored. First, we use the files backed up in the early hours of yesterday morning to restore to the 2: 00 am state, and then use mysqlbinlog to restore the binlog since mysqldump backup.

Mysql localhost mysql-bin.000001 | mysql- uroot-p

In this way, the database can be completely restored to its full state before the crash.

Based on the recovery based on the point in time, due to misoperation, such as deleting a table, it is useless to use the full recovery mentioned above, because there are still misoperation statements in the log, what we need is to restore to the state before the misoperation, and then skip the misoperation statement, and then restore the later operation statement, assuming that the misoperation of deleting a table occurred at 10:00 We can use the following statement to restore data to pre-failure with backup and binlog

Mysqlbinlog-- stop-date='2010-09-04 9 mysql- uroot 59 / var/log/mysql-bin.000001 mysql- uroot-p

Then skip the time point of misoperation and continue with the following binlog

Mysqlbinlog-- start-date='2010-09-04 10 var/log/mysql-bin.000001 01Switzerland 00' / mysql- uroot-p

Among them,-- stop-date='2010-09-04 9lav 59v and-- start-date='2010-09-04 10V 01V 00', where the time is the time of your misoperation, of course, you need to calculate this time point yourself, and this time point can not only involve misoperation, but also the correct operation can be skipped.

Location-based recovery, as mentioned above, using point-in-time recovery may occur, and there may be misoperations and other correct operations at a point in time, so we need a more accurate recovery method.

Using mysqlbinlog to view the binary, you can see

Among them, the misoperation end_log_pos of drop tables test1 is 8879917. After a few clicks of this id, it is concluded that the id before and after its operation is 887991688879918.

We will perform a location recovery operation.

Mysqlbinlog-- stop-position='8879916' / var/log/mysql-bin.000001 | mysql- uroot-p

Mysqlbinlog-- start-position='8879918' / var/log/mysql-bin.000001 | mysql- uroot-p

The binary log files generated by the server are written in binary format. To check these text-formatted files, use the mysqlbinlog utility.

Mysqlbinlog should be called like this:

Shell > mysqlbinlog [options] log-files... For example, to display the contents of the binary log binlog.000003, use the following command:

The shell > mysqlbinlog binlog.0000003 output includes all the statements contained in the binlog.000003, as well as other information such as the time spent on each statement, the thread ID issued by the customer, the timestamp when the thread was issued, and so on.

Typically, you can use mysqlbinlog to read binary log files directly and use them for local MySQL servers. You can also use the-read-from-remote-server option to read binary logs from a remote server.

When reading remote binary logs, you can use the connection parameters option to indicate how to connect to the server, but they are often ignored unless you also specify the-read-from-remote-server option. These options are-host,-password,-port,-protocol,-socket, and-user.

You can also use mysqlbinlog to read relay log files written from the server during replication. The relay log format is the same as the binary log file.

Mysqlbinlog supports the following options:

-help,-?

Display a help message and exit.

-database=db_name,-d db_name

Only entries for the database are listed (local logs only).

-force-read,-f

With this option, if mysqlbinlog reads a binary log event that it does not recognize, it prints a warning, ignores the event, and continues. Without this option, stop if mysqlbinlog reads such an event.

-hexdump,-H

Displays the hexadecimal dump of the log in the comment. This output can help debug during replication. This option was added in MySQL 5.1.2.

-host=host_name,-h host_name

Gets the binary log of the MySQL server on the given host.

-local-load=path,-l pat

Preprocesses local temporary files for LOAD DATA INFILE in the specified directory.

-offset=N,-o N

Skip the first N entries.

-password [= password],-p [password]

The password used when connecting to the server. If you use the short option form (- p), there can be no space between the option and the password. If there is no password value after the-password or-p option on the command line, you are prompted for a password.

-port=port_num,-P port_num

The TCP/ IP port number used to connect to the remote server.

-position=N,-j N

Do not approve of use, should use-start-position.

-protocol= {TCP | SOCKET | PIPE |-position

The connection protocol used.

-read-from-remote-server,-R

Read the binary log from the MySQL server. If this option is not given, any connection parameter options will be ignored. These options are-host,-password,-port,-protocol,-socket, and-user.

-result-file=name.-r name.

Point the output to the given file.

-short-form,-s

Only the statements contained in the log are displayed and no other information is displayed.

-socket=path,-S path

The socket file used for the connection.

-start-datetime=datetime

Starts reading from the first event in the binary log whose date time is equal to or later than the datetime parameter. The datetime value is relative to the local time zone on the machine running mysqlbinlog. The value format should match the DATETIME or TIMESTAMP data type. For example:

Shell > mysqlbinlog-start-datetime= "2004-12-25 11:25:56" binlog.000003 this option helps with peer-to-peer recovery.

-stop-datetime=datetime

Stop reading from the first event in the binary log whose date time is equal to or later than the datetime parameter. See the-start-datetime option for a description of the datetime value. This option helps to recover in a timely manner.

-start-position=N

Start reading from the event when the first position in the binary log is equal to the N parameter.

-stop-position=N

Stop reading from the event when the first position in the binary log is equal to and greater than the N parameter.

-to-last-logs,-t

The requested binary log in the MySQL server does not stop at the end, but continues to print until the end of the last binary log. If the output is sent to the same MySQL server, it will cause an infinite loop. This option requires-read-from-remote-server.

-disable-logs-bin,-D

Disable binary logging. If you use the-to-last-logs option to send the output to the same MySQL server, you can avoid an infinite loop. This option is also useful in crash recovery to avoid copying recorded statements. Note: this option requires SUPER permission.

-user=user_name,-u user_name

The MySQL user name used when connecting to the remote server.

-version,-V

Display version information and exit.

This is the end of the article on "sample analysis of log-based recovery of mysqlbinlog tools". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.