“This is the 22nd day of my participation in the First Challenge 2022. For details: First Challenge 2022”
preface
The Binary Log is a Binary Log. The Binary Log is a Binary Log. The Binary Log is a Binary Log. We haven’t touched on how to view and use the Binary Log yet, so we will introduce it in this article.
Viewing can be done with the mysql and mysqlbinlog commands as described below. Restore using the mysqlbinlog command.
The SHOW BINLOG EVENTS statement
Official documentation.
SHOW BINLOG EVENTS
[IN '*log_name*']
[FROM *pos*]
[LIMIT [*offset*,] *row_count*]
Copy the codeDisplays events in binary logs. If ‘log_name’ is not specified, the first binary log is displayed. SHOW BINLOG EVENTS Requires REPLICATION SLAVE rights.
The LIMIT clause has the same syntax as the SELECT statement. See section 13.2.10, “SELECT Statement.”
Issuing the SHOW BINLOG EVENTS clause without LIMIT can start a very time-consuming and resource-consuming process, as the server returns to the client the complete contents of the binary log (including all statements that the server executed to modify the data). As an alternative to SHOW BINLOG EVENTS, use the mysqlBinlog utility to save binary logs to text files for later review and analysis. See section 4.6.9, “mysqlbinlog – Utility for processing binary log files.”
summary
Instead of using show binlog events, use mysqlbinlog instead.
mysqlbinlog
The official documentation
The server’s binary log consists of files containing “events”, which describe changes to the contents of the database. To display their contents in text format, use the mysqlbinlog utility. You can also use mysqlbinlog to display the contents of the relay log file written by the replica server in the replication Settings, since relay logs have the same format as binary logs. Binary and relay logging are further discussed in section 5.4.4, “Binary Logging,” and section 17.2.4, “Relay Logging and replication Metadata Repository.”
Call mysqlbinlog like this:
mysqlbinlog [*options*] *log_file* ...
Copy the codeFor example, to display the contents of a binary log file named binlog.000003, use the following command:
mysqlbinlog binlog.0000003
Copy the codeOutput all events in binlog.000003. For statement-based logging, the event information includes the SQL statement, the ID of the server that executed it, the timestamp when the statement was executed, the time spent, and so on. For row-based logging, events indicate row changes rather than SQL statements. For information about logging modes, see section 17.2.1, “Replication Formats.”
The event is preceded by a caption comment that provides additional information. Such as:
# at 141
#100309 9:28:36 server id 123 end_log_pos 245
Query thread_id=3350 exec_time=11 error_code=0
Copy the codeIn the first line, the number followed by at indicates the file offset or starting position of the event in the binary log file.
The second line begins with the date and time, indicating when the statement started on the server from which the event originated. For replication, this timestamp is propagated to the replica server. Server ID is server_id value of the server from which the event originated. On a replica, it is the difference between the end execution time on the replica minus the start execution time on the source. The difference is an indicator of how much replication is behind the source. Error_code indicates the result of executing the event. Zero means no error has occurred.
Note that when you use event groups, you can group the file offsets of an event and group the comments of an event. Do not mistake these grouping events for blank file offsets. For additional usage examples, see the discussion later in this section and in Section 7.5, “Point-in-time (Incremental) Recovery.”
You can re-execute the output of mysqlbinlog to redo the statements in the log. This is useful for recovery operations after an unexpected server exit. To execute a BINLOG internal use statement using mysqlbinlog, users need the BINLOG_ADMIN privilege (or SUPER privilege deprecated) or REPLICATION_APPLIER with appropriate privileges to execute each log event.
Readers summary
A brief introduction to mysqlbinlog and its simple viewing usage and presentation format, where the server ID avoids repeating its own statements when switching from master to slave. It is also important to note that mysqlBinlog needs to have permissions when it is used to restore.
Point-in-time (incremental) recovery
The official documentation
Point-in-time recovery refers to the recovery of data changes to a given point in time. Typically, this type of recovery is performed after restoring a full backup that restores the server to the state it was in when the backup was created. (A full backup can be made in a number of ways, such as those listed in Section 7.2, “Database Backup Methods.”) Point-in-time restore then updates the server incrementally from full backup time to a more recent time.
Use binary point in time recovery
This section describes the general idea of using binary logs to perform point-in-time recovery. The next section, Section 7.5.2, “Point-in-time recovery using event locations,” details this operation with an example.
Note that many of the examples in this section and the next use the mysql client to handle binary log output generated by mysqlBinlog. If your binary log contains the \0 (null) character, mysql cannot parse the output unless you call it with the –binary-mode option.
The source of information for point-in-time recovery is a set of binary log files generated after a full backup operation. Therefore, to allow a server to recover to a certain point in time, binary logging must be enabled on it, which is the default setting for MySQL 8.0 (see Section 5.4.4, “Binary Logging”).
To recover data from binary logs, you must know the name and location of the current binary log file. By default, the server creates binary log files in the data directory, but you can use the –log-bin option to specify a pathname to place the files in a different location. To view a list of all binary log files, use the following statement:
mysql> SHOW BINARY LOGS;
Copy the codeTo determine the name of the current binary log file, issue the following statement:
mysql> SHOW MASTER STATUS;
Copy the codeThe mysqlBinlog utility converts events in binary log files from binary format to text so that they can be viewed or applied. Mysqlbinlog has options for selecting the binary log section based on the event time or event location in the log. See section 4.6.9, “mysqlbinlog – Utility for processing binary log files.”
Applying events in binary logs causes the data modifications they represent to be re-executed. This can recover data changes in a given time span. To apply events from binary logs, use the mysql client to process mysqlbinlog output:
$> mysqlbinlog *binlog_files* | mysql -u root -p
Copy the codeIf binary log files are already encrypted, this can start with MySQL 8.0.14. Mysqlbinlog cannot read them directly as in the example above, but can read them from the server using the –read-from-remote-server (-r) option. Such as:
$> mysqlbinlog --read-from-remote-server --host=*host_name* --port=3306 --user=root --password --ssl-mode=required *binlog_files* | mysql -u root -p
Copy the codeIn this case, the option — SSL-mode =required is used to ensure that the data in the binary log file is protected during transmission because it is sent to mysqlbinlog in an unencrypted format.
Viewing the log content can be useful when you need to determine the event time or location to select part of the log content before executing the event. To view events in the log, send the mysqlbinlog output to the pager:
$> mysqlbinlog *binlog_files* | more
Copy the codeAlternatively, save the output in a file and view the file in a text editor:
$> mysqlbinlog *binlog_files* > tmpfile
$> ... *edit tmpfile* ...
Copy the codeAfter editing the file, apply the following:
$> mysql -u root -p < tmpfile
Copy the codeIf you have multiple binary logs applied to the MySQL server, use a single connection to apply the contents of all binary log files that you want to process. Here’s one way:
$> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
Copy the codeAnother way is to write the entire log to a single file and then process that file:
$> mysqlbinlog binlog.000001 > /tmp/statements.sql
$> mysqlbinlog binlog.000002 >> /tmp/statements.sql
$> mysql -u root -p -e "source /tmp/statements.sql"
Copy the codeReaders summary
This section describes how to use commands to view binary logs and the current status, and how to use simple recovery commands.
Time point recovery using event location
For example, suppose that at around 20:06:00 on March 11, 2020, the SQL statement to drop the table is executed. You can perform a point-in-time restore to restore the server to the state before the table was dropped. Here are some sample steps to achieve this:
- Restore the last full backup created before the point in time of interest (called TP, in our example 20:06:00 March 11, 2020). When you’re done, note down the binary log location to which you have restored the server for later use, and then restart the server.
Note that while InnoDB also displays the last restored binary log location after restore and server restart, this is not a reliable way to get the restored end log location because DDL events and non-InnoDB changes can occur after the time the display location reflects. Your backup and restore tool should provide you with the last binary log location to restore: for example, if you are working with mysqlbinlog, check where the binary log replay stopped; If you are using MySQL Enterprise Backup, the last binary log location is already saved in your Backup. See Point-in-time recovery.
- Locate the exact binary log event location that corresponds to the point in time you want to restore the database to. In our example, assuming we know the approximate time (TP) for deleting the table, we can find the log location by examining the log contents before and after that time using the mysqlbinlog utility. Specify a shorter period of time around TP using the –start-datetime and –stop-datetime options, and then look for events in the output. Such as:
$> mysqlbinlog --start-datetime="2020-03-11 20:05:00" \ --stop-datetime="2020-03-11 20:08:00" --verbose \ /var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE" /*! 80014 SET @@session.original_server_version=80019*//*! * /. / *! 80014 SET @@session.immediate_server_version=80019*//*! * /. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*! * /. # at 232 #200311 20:06:20 server id 1 end_log_pos 355 CRC32 0x2fc1e5ea Query thread_id=16 exec_time=0 error_code=0 SET TIMESTAMP=1583971580/*! * /. SET @@session.pseudo_thread_id=16/*! * /. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*! * /. SET @@session.sql_mode=1168113696/*! * /. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*! * /. / *! \C utf8mb4 *//*! * /. SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*! * /. SET @@session.lc_time_names=0/*! * /. SET @@session.collation_database=DEFAULT/*! * /. / *! 80011 SET @@session.default_collation_for_utf8mb4=255*//*! * /. DROP TABLE `pets`.`cats` /* generated by server */ /*! * /. # at 355 #200311 20:07:48 server id 1 end_log_pos 434 CRC32 0x123d65df Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1583971668462467 immediate_commit_timestamp=1583971668462467 Transaction_length =473 # originAL_commit_TIMESTAMP =1583971668462467 (2020-03-11 20:07:48.462467 EDT) # Immediate_commit_timestamp =1583971668462467 (2020-03-11 20:07:48.462467 EDT) /*! 80001 SET @@session.original_commit_timestamp=1583971668462467*//*! * /. / *! 80014 SET @@session.original_server_version=80019*//*! * /. / *! 80014 SET @@session.immediate_server_version=80019*//*! * /. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*! * /. # at 434 #200311 20:07:48 server id 1 end_log_pos 828 CRC32 0x57fac9ac Query thread_id=16 exec_time=0 error_code=0 Xid = 217 use `pets`/*! * /. SET TIMESTAMP=1583971668/*! * /. / *! 80013 SET @@session.sql_require_primary_key=0*//*! * /. CREATE TABLE dogsCopy the codeFrom the mysqlbinlog output, DROP TABLE ‘pets’.’ cats’ can find a binary log fragment between # at 232 and # at 355, which means that the statement occurred after log position 232, Log position 335 is after the DROP TABLE statement.
Note that only the –start-datetime and –stop-datetime options are used to help you find the actual event location of interest. It is not recommended to use these two options to specify the range of binary log segments to apply: there is a higher risk of losing binary log events when using these options. Use –start-positionand –stop-position instead.
- Apply the events from the binary log file to the server, starting at the log location you found in step 1 (let’s say it’s 155) and ending at the location you found in Step 2, the point in time you’re interested inbefore(This is 232)
$> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \
| mysql -u root -p
Copy the codeThis command resumes all transactions from the start position up to the stop position. Because the output of mysqlbinlog contains a SET TIMESTAMP statement before logging each SQL statement, the recovered data and the associated MySQL log reflect the original time when the transaction was executed. (So don’t worry about functions like now() in SQL)
Your database has now been restored to the point in time of interest, TP, just before the table TP ‘ ‘pets. Cats was deleted.
- In addition to the point in time recovery that has already been completed, if you want to re-execute the point in time that you are interested inPlease use all subsequent statements againmysqlbinlogApply all events to the server. We noticed in Step 2 that after the statement we want to skip, the log is in position 355; We can apply it to
--start-positionOption to include any statements after that location:
$> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \
| mysql -u root -p
Copy the codeYour database has recovered the most recent statement recorded in the binary log file, but the selected event has been skipped.
Readers summary
This section shows a specific deletion and recovery example. Use –start-datetime and –stop-datetime to perform initial location, locate the location before and after the deletion, and perform recovery based on the location to skip the selected time.
Display of different Binary Log formats
Insert into test (name) values (‘ you are ‘); Insert the statement’s Binary Log.
Run the SHOW MASTER STATUS command. Statement to query the latest binlog, use mysqlbinlog.
Mysql 8.0 default binlog_format is row.
The row format
# at 831 #220214 0:20:28 server id 1 end_log_pos 909 CRC32 0x8139b6cc Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1644769228/*! * /. BEGIN /*! * /. # at 909 #220214 0:20:28 server id 1 end_log_pos 970 CRC32 0x88895721 Table_map: `my_test`.`test` mapped to number 94 # at 970 #220214 0:20:28 server id 1 end_log_pos 1021 CRC32 0x5cb5c581 Write_rows: table id 94 flags: STMT_END_F BINLOG ' zC8JYhMBAAAAPQAAAMoDAAAAAF4AAAAAAAEAB215X3Rlc3QABHRlc3QAAgj+Av6AAAEBgAID/P8A IVeJiA== zC8JYh4BAAAAMwAAAP0DAAAAAF4AAAAAAAEAAgAC/wABAAAAAAAAAAbkvaDmmK+BxbVc '/*! * /. # at 1021 #220214 0:20:28 server id 1 end_log_pos 1052 CRC32 0x6c0bc906 Xid = 27 COMMIT/*! * /.Copy the codeStatment format
# at 1131 #220214 0:35:54 server id 1 end_log_pos 1219 CRC32 0xed38bc8d Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1644770154/*! * /. BEGIN /*! * /. # at 1219 # at 1251 #220214 0:35:54 server id 1 end_log_pos 1251 CRC32 0x904331cd Intvar SET INSERT_ID=2/*! * /. #220214 0:35:54 server id 1 end_log_pos 1379 CRC32 0x874143f7 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1644770154/*! * /. Insert into 'test' (' name ') values (' you ') /*! * /. # at 1379 #220214 0:35:54 server id 1 end_log_pos 1410 CRC32 0x51637eec Xid = 43 COMMIT/*! * /.Copy the codeMixed format
Mysql selects whether statement or row is a combination of statement and row.