Detailed description of mysqlbinlog Based on Log Recovery

Detailed description of mysqlbinlog Based on Log Recovery

If a large number of binary logs are generated every day and these logs are not cleared for a long time, the disk space will be greatly wasted. Therefore, regular log cleaning is an important task for DBA to maintain mysql.

1) RESET MASTER
In the folder where the logs are stored, the binary log name format is mysql-bin. *, * indicates the log serial number, the serial number is increasing, and the mysql-bin.index is the index file of the log, which records the maximum serial number of the log
Run the reset master name to delete all logs.

View deleted logs

As you can see, all previous logs are cleared, and new logs start from 00001.

2) purge master logs to & PURGE MASTER LOGS BEFORE
Run the purge master logs to 'mysql-bin. ******* 'command TO delete all LOGS before the ******' number.
Run the purge master logs before 'yyyy-mm-dd hh: mm: ss' command: all logs earlier than 'ss time' are deleted.

3)-EXPIRE_LOGS_DAYS
This parameter is used to set the log Expiration days. Expired logs will be automatically deleted, which reduces the workload of log management and requires modifying my. cnf.

Here, we set the log to be saved for three days. logs that expire after three days will be automatically deleted.

Restore
Bin-log records all mysql event operations. When a catastrophic mysql error occurs, you can use bin-log for complete recovery, time point-based recovery, and location-based recovery.

Complete recovery: assume that we use mysqldump to back up the database at every morning. However, at the next day, due to database faults, the data cannot be accessed and data needs to be restored, use the files backed up in the early morning of yesterday to restore to the status at AM. 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 the full State before the crash.

Recovery based on time points, due to misoperations, such as deleting a table, it is useless to use the full recovery described above, because the log contains misoperation statements ,, what we need is to restore to the State before the misoperation, Then skip the misoperation statement, and then restore the subsequent operation statement, assuming that the misoperation of a table was deleted, we can use the following statement to recover data to the fault using backup and binlog.

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

Skip the misoperation time and continue executing the binlog

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

Here, -- stop-date = '2017-09-04 9:59:59 'and -- start-date = '2017-09-04 10:01:00' indicate the time point of your misoperation. Of course, you need to calculate this time point by yourself. In addition, this time point can involve not only misoperations, but also correct operations.

Based on location recovery, as mentioned above, recovery based on time points may occur, and there may be misoperations and other correct operations in a time point, therefore, we need a more accurate recovery method.
Use mysqlbinlog to view binary data.

 

Here, the error end_log_pos of drop tables test1 is 8879917, a few times the id, and the operation ids before and after it are 8879916,8879918, respectively.
We will perform location recovery

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

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

The binary log files generated by the server are written in binary format. To check files in these text formats, use the mysqlbinlog utility.
Call mysqlbinlog as follows:
Shell> mysqlbinlog [options] log-files... For example, to display binary log binlog.000003, run the following command:
Shell> mysqlbinlog binlog.0000003 output includes all statements contained in binlog.000003 and other information, such as the time spent by each statement, the thread ID sent by the customer, and the timestamp when the thread is sent.
In general, you can use mysqlbinlog to directly read binary log files and use them on the local MySQL server. 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 parameter option to indicate how to connect to the server, but they are often ignored unless you have also specified the-read-from-remote-server option. These options are-host,-password,-port,-protocol,-socket, and-user.
You can also use mysqlbinlog to read the relay Log Files written from the server during the replication process. The format of the relay log is the same as that of the binary log file.

Mysqlbinlog supports the following options:
·
-Help ,-?
Displays the help message and exits.
·
-Database = db_name,-d db_name
Only list entries of the database (only local logs are used ).
·
-Force-read,-f
This option is used. If mysqlbinlog reads binary log events that it cannot recognize, it prints a warning, ignores the event, and continues. This option is not available. If mysqlbinlog reads this type of event, it stops.
·
-Hexdump,-H
The log hexadecimal dump is displayed in the comment. This output helps debugging During the replication process. This option is added to MySQL 5.1.2.
·
-Host = host_name,-h host_name
Obtain the binary log of the MySQL server on the specified host.
·
-Local-load = path,-l pat
Pre-processes local temporary files for the load data infile in the specified directory.
·
-Offset = N,-o N
Skip the first N entries.
·
-Password [= password],-p [password]
The password used to connect to the server. If you use the short option format (-p), there must be no space between the option and password. If the-password or-p option in the command line does not have a password value, the system prompts you to enter a password.
·
-Port = port_num,-P port_num
The TCP/IP Port number used to connect to the remote server.
·
-Position = N,-j N
-Start-position should be used if it is not in favor of use.
·
-Protocol = {TCP | SOCKET | PIPE |-position

The connection protocol used.
·
-Read-from-remote-server,-R
Read Binary logs from the MySQL server. If this option is not provided, 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 specified file.
·
-Short-form,-s
Only the statements contained in the log are displayed, and other information is not displayed.
·
-Socket = path,-S path
The socket file used for connection.
·
-Start-datetime = datetime
Read data from 1st events whose date time is equal to or later than the datetime parameter in binary logs. The datetime value is relative to the local time zone on the machine that runs mysqlbinlog. The value format must be DATETIME or TIMESTAMP data type. For example:
Shell> mysqlbinlog-start-datetime = "2004-12-25 11:25:56" binlog.000003 this option can help point-to-point recovery.
·
-Stop-datetime = datetime
Stop reading from 1st events whose date time is equal to or later than the datetime parameter in the binary log. For descriptions of datetime values, see the-start-datetime option. This option can be used for timely recovery.
·
-Start-position = N
Read from the event where the second position in the binary log is equal to N.
·
-Stop-position = N
Stop reading from the event where 1st locations are equal to or greater than N in the binary log.
·
-To-last-logs,-t
The end of the binary log requested by the MySQL server is not stopped, but is printed until the end of the last binary log. If the output is sent to the same MySQL server, an infinite loop occurs. This option requires-read-from-remote-server.
·
-Disable-logs-bin,-D
Disable binary logs. If you use the-to-last-logs option to send the output to the same MySQL server, you can avoid infinite loops. This option is also useful for crash recovery, avoiding copying recorded statements. Note: This option requires the SUPER permission.
·
-User = user_name,-u user_name
The MySQL user name used to connect to the remote server.
·
-Version,-V
Display version information and exit.

MySQL 5.6.12 switch binlog binary Log Path

Practices for restoring data using MySQL binlog

MySQL 5.6.12 switch binlog binary Log Path

Mysqlbinlog parsing binlog garbled issue decryption

MySQL uses binlog to restore Data

MySQL binlog group submission and XA (two-phase commit)

MySQL uses backup and binlog for data recovery

This article permanently updates the link address: