Teach you how to play MySQL without running away. Just play with MySQL binlog!

Time:2022-5-26

We often hear that MySQL can be restored to any state within half a month or even a month as long as you like. There are also many stories about deleting databases and running away on the Internet…

So today, brother song wants to talk about binlog in MySQL and teach you how to use binlog to recover data in MySQL. In this way, if you accidentally delete the database in the future, you don’t have to run away.

The important logs in MySQL include binlog (archive log), redo log (redo log) and undo log. The main logs related to this article are binlog. SongGe will introduce the other two logs in detail when he is free in the future.

1. binlog

Binlog is generally called archive log in Chinese. If you have seen the MySQL master-slave setup sent by SongGe before, you should be impressed by this log. When we build MySQL master-slave, we can’t do without binlog (portal:Mysql8 master-slave copy pit Guide)。

Binlog is the log of MySQL server layer, not the log provided by the storage engine. It records all DDL and DML (excluding data query statements) statements in the form of events, as well as the time consumed by the execution of statements. It should be noted that:

  • Binlog is a kind of logical log. What it records is the original logic of an SQL statement. For example, add + 1 to a field. Note that this physical log is different from redo log (what changes have been made on a data page).
  • When the binlog file is full, it will automatically switch to the next log file to continue writing without overwriting the previous log. This is also different from redo log. Redo log is written circularly, that is, what is written later may overwrite what is written earlier.
  • Generally speaking, when configuring binlog, we can specify the validity period of binlog file, so that the log file will be automatically deleted after expiration, so as to avoid taking up more storage space.

According to the introduction of the official MySQL documents, there will be about 1% performance loss after starting binlog, but this is acceptable. Generally speaking, binlog has two important use scenarios:

  • During MySQL master-slave replication: start binlog on the host, the host synchronizes the binlog to the slave, and the slave synchronizes the data through binlog, so as to realize the data synchronization between the host and the slave.
  • MySQL data recovery can recover data to a certain time in the past by using mysqlbinlog tool and binlog file.

2. Start binlog

For the convenience of demonstration, SongGe has installed MySQL in docker. Let’s take this as an example to start today’s demonstration. If you don’t know how to use docker, you can reply to docker in the background of the official account. There is a tutorial written by brother song.

First, install MySQL in docker, and then enter the container. You can check whether binlog is enabled through the following command:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

This off indicates that binlog is in a closed state and not enabled. Next, let’s start binlog.

Opening binlog is mainly to modify the MySQL configuration file mysqld CNF, the file is in the container/etc/mysql/mysql.conf.dDirectory.

Teach you how to play MySQL without running away. Just play with MySQL binlog!

For this configuration file, we make the following modifications:

#This parameter indicates that the binlog function is enabled and specifies the storage directory of binlog
log-bin=javaboy_logbin

#Set the maximum bytes of a binlog file
#Set maximum 100MB
max_binlog_size=104857600

#Set the validity period of binlog file (unit: day)
expire_logs_days = 7

#Binlog logs only record the updates of the specified database (used when configuring master-slave replication)
#binlog-do-db=javaboy_db

#The binlog log does not record the updates of the specified database (used when configuring master-slave replication)
#binlog-ignore-db=javaboy_no_db

#How many times to write the cache, brush the disk once. The default 0 means that the operating system decides how often to write the disk according to its own load
#1 means that each transaction commit will write to the disk immediately, and N means that n transactions commit will write to the disk
sync_binlog=0

#Get a unique ID for the current service (it needs to be configured after MySQL 5.7)
server-id=1

The meaning of each configuration has been explained in the watch. The screenshot is as follows:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

After the configuration is completed, execute the following command to restart the MySQL container (mysql1 is the name of the container here):

docker restart mysql1

After restart, execute againshow variables like 'log_bin%';You can see that binlog has been opened.

Teach you how to play MySQL without running away. Just play with MySQL binlog!

Except for log_ In addition to the bin variable, there are two variable names that deserve our attention:

  • log_ bin_ Basename: This is the name prefix of the binlog log file generated in the future. In other words, according to the current configuration, the name of the binlog log file generated in the future isjavaboy_logbin.xxx, this file will be used to record all DDL and DML statement events.
  • log_ bin_ Index: This is the binlog index file, which saves all binlog directories, because there may be multiple binlogs. We can take a look at the current situationjavaboy_logbin.indexFile:
Teach you how to play MySQL without running away. Just play with MySQL binlog!

At present, only one logbin file can be seen.

3. Common binlog operations

Next, let’s introduce some common binlog operation commands.

  1. View all binlog logs

We can view the binlog log list in the following ways:

show master logs;
Teach you how to play MySQL without running away. Just play with MySQL binlog!

As you can see, there is only one log file namedjavaboy_logbin.000001,File_ Size indicates that the byte size occupied by this file is 154.

  1. View master status

This command is often used when building MySQL master-slave, as follows:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

At this time, you can see the latest binlog log file name and the position value of the last operation event (what is the use of this value? We will introduce it in detail later).

  1. Refresh binlog

Normally, when a binlog is full, it will automatically switch to the next binlog to start writing, but we can also execute oneflush logsCommand to manually refresh binlog. After manually refreshing binlog, a new binlog file will be generated, and then all binlog logs will be recorded in the new file. As follows:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

As can be seen from the above figure, we refresh the log and then go through theshow master logsCheck the log and find that there is a new log file, and then pass itshow master statusCheck the latest log file information and find that it has also changed tojavaboy_logbin.000002

  1. Reset binlog

reset masterThe binlog log file can be reset and the log can be recorded again from 00000 1. However, if one or more slaves of the current host are running, the command will not run (because the slave realizes database synchronization through binlog, the host empties the binlog and reports the error that the binlog cannot be found from the opportunity).

Teach you how to play MySQL without running away. Just play with MySQL binlog!
  1. View binlog

Since binlog is a binary log file, you can’t see it if you open it directly:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

I didn’t see any useful information.

In order to view binlog, MySQL provides us with two official tools. Let’s look at them one by one. The first ismysqlbinlogCommand, as follows:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

Although it looks messy, there are traces to follow when you look carefully. Because I have a newly installed database here, I just created a library called javaboy, and then created a table named user with two pieces of data. I didn’t do anything else, so we can actually find the script for creating the library from the miscellaneous files.

There is an end in the generated log file_ log_ POS is the POS point of the log file, which will be useful in data recovery in the future.

However, this viewing method is not user-friendly. We say that binlog records logs according to events, so it will be much better if we can view logs according to events. Let’s take another look at the following command:

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

This means to view binlog in the form of events. Here, several parameters are involved:

  • log_ Name: you can specify the binlog file name to view. If not specified, it means to view the oldest binlog file.
  • Pos: from which POS point to start viewing. All operations recorded in binlog have a POS point. In fact, this is equivalent to specifying which operation to start viewing the log. If not specified, it is from the beginning of the binlog.
  • Offset: This is the offset. If it is not specified, it is 0 by default.
  • row_ Count: the number of rows of records to view. If it is not specified, all records will be viewed.

Let’s take a simple example:

show binlog events in 'javaboy_logbin.000001';
Teach you how to play MySQL without running away. Just play with MySQL binlog!

This is much clearer. We can see all the previous operations, such as:

  • A library was created between POS 219-322.
  • A table was created between POS 387-537.
  • Added a record between POS 677-780.

4. Data recovery practice

Well, with the previous basic knowledge, let’s show you a scenario of database deletion / recovery.

Let me first talk about the current situation of my database.

This is a newly installed database. I have created a new database named javaboy. A new table named user is created in the javaboy library. There are two records in user, as follows:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

Now suppose we back up the database regularly (at 3 a.m. every Wednesday).

Now it’s three o’clock in the morning, and the automatic database backup starts. We backup the database into SQL script through the following command, as follows:

mysqldump -uroot -p --flush-logs --lock-tables -B javaboy>/root/javaboy.bak.sql
Teach you how to play MySQL without running away. Just play with MySQL binlog!

Here are some parameters to explain:

  • -u. – P these two are needless to say.
  • –Flush logs: this means that the binlog is refreshed before export. After refreshing the binlog, a new binlog file will be generated, and subsequent operations will be saved in the new binlog.
  • –Lock tables: this means that all tables are locked before export. It should be noted that when exporting multiple databases, – lock tables locks the tables for each database respectively. Therefore, this option cannot ensure the logical consistency of the tables in the export file between databases, and the export status of different database tables can be completely different.
  • -B: This indicates the name of the exported database, if used--all-databasesperhaps-Areplace-BIndicates that all databases are exported.

After the execution of the above command, a javaboy.exe will be generated in the / root directory bak. SQL file, which is the backup SQL file.

It happened at 3 a.m. on Wednesday.

Next, on Thursday morning, I came to work. After a meal, I added two operations to the database, as follows:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

Next, Xiao x quarreled with his leader today and decided to delete the running path:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

The leader was surprised and immediately asked to restore the data immediately. It’s your turn to act.

First of all, we have a backup file in the early morning of Wednesday. First use that file for data recovery:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

After the recovery, the data is now available until 3 a.m. on Wednesday morning.

The data from 3 a.m. on Wednesday to Thursday are now gone.

At this time, we need to recover with the help of binlog. As you can remember, when we performed the backup at 3 a.m. on Wednesday, we used a parameter called--flush-logs, using this parameter means that from the moment of backup, the new binlog will be generated in a new log file. For us, the new binlog file is, of coursejavaboy_logbin.000002Now, let’s check the file:

show binlog events in 'javaboy_logbin.000002';

The file I generated here is relatively long. I intercepted part of it:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

It can be seen that the database deletion and running event occurred in the POS 764-865, so we only need to playback the file and restore the data to the location 764.

becausejavaboy_logbin.000002The file is a new file generated after the backup at 3:00 a.m. on Wednesday, so the operation from the beginning of the file to 764 POS is the operation from 3:00 a.m. on Wednesday to deleting the database.

Let’s take a look at the command to recover data through binlog:

mysqlbinlog /var/lib/mysql/javaboy_logbin.000002 --stop-position=764 --database=javaboy | mysql -uroot -p

Then two parameters are involved:

  • –Stop position = 764 means to restore to the POS of 764. If it is not specified, the entire file will be restored. If it is restored according to the current file, because there is a statement to delete the database in the binlog file, the javaboy library will be deleted after the binlog is executed.
  • Database = javaboy means to restore the javaboy library.

Another parameter we don’t use here is called--start-position, this indicates the initial pos. if it is not specified, it indicates data recovery from the beginning.

Well, when you’re done, check the database again:

Teach you how to play MySQL without running away. Just play with MySQL binlog!

Data recovered ~

Note: before all operations, remember to back up the backup (to prevent you from going back again if you make a mistake). SongGe omitted some backup operations to save trouble.

5. Summary

Well, today’s article mainly shares the binlog log of MySQL with my friends, and demonstrates how to realize database deletion and recovery through binlog through a small case. Well, interested partners can try it (don’t try it on the production warehouse) ~