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

Editor to share with you MySQL binlog how to recover data, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

1. Binlog

Binlog is generally called archived log in Chinese. If you have seen the MySQL master-slave building sent by Song GE before, you should be impressed with this log. When we build MySQL master-slave, we can't do without binlog (portal: MySQL8 master-slave copy crater guide).

Binlog is the log of the MySQL Server layer, not the log provided by the storage engine. It records all DDL and DML statements (excluding data query statements), and records them in the form of events, including the time spent on the execution of the statements. Note that:

Binlog is a logical log that records the original logic of a SQL statement, such as + 1 for a field. Note that this is different from the physical log of 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 the fact that redo log,redo log is written in a loop, that is, later writes may overwrite previous writes.

Generally speaking, when we configure binlog, we can specify the validity period of the binlog file, so that the log file will be deleted automatically after it expires, so as to avoid taking up more storage space.

According to the official documentation of MySQL, there will be a 1% performance loss when binlog is enabled, but this is acceptable. Generally speaking, binlog has two important usage scenarios:

When MySQL master-slave replication: open binlog on the host, the host synchronizes the binlog to the slave, and the slave synchronizes the data through binlog, thus realizing the data synchronization between the host and the slave.

MySQL data recovery, by using mysqlbinlog tools combined with binlog files, you can restore the data to a certain point in the past.

two。 Turn on binlog

For the convenience of demonstration, Song GE has installed MySQL in Docker here. Let's start today's demonstration with this as an example. If your friends do not understand the use of docker, you can reply to docker on the official account. There are tutorials written by Brother Song.

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

This OFF means that binlog is closed and not open. Next, let's turn on binlog.

Enabling binlog is mainly to modify the configuration file mysqld.cnf of MySQL, which is located in the / etc/mysql/mysql.conf.d directory of the container.

For this configuration file, we make the following modifications:

# this parameter means to enable the binlog feature And specify the storage directory of binlog log-bin=javaboy_logbin# set the maximum byte of a binlog file # set the maximum 100MBmax_binlog_size=104857600# setting the validity period of the binlog file (in days) expire_logs_days = the binlog log only records updates of the specified library (used when configuring master-slave replication) # the binlog-do-db=javaboy_db# binlog log does not record updates of the specified library (configure master-slave replication How many times will binlog-ignore-db=javaboy_no_db# write cache be used? Scan the disk once. Default 0 means it is up to the operating system to decide how often to write to the disk according to its own load. # 1 means that each transaction commit will write to the disk immediately, and n means that n transaction commits will write to the disk sync_binlog=0# to get a unique id (need to be configured after MySQL5.7) server-id=1 for the current service.

Brother Song has explained the meaning of each configuration in his gaze. The screenshot is as follows:

After the configuration is complete, execute the following command to restart the mysql container (mysql1 is the name of my container):

Docker restart mysql1

After reboot, execute show variables like 'log_bin%'; again to see that binlog has been turned on.

In addition to the log_bin variable, there are two other variable names that deserve our attention:

Log_bin_basename: this is the name prefix of the future generated binlog log file. In other words, according to the configuration you have seen, the future generated binlog log file is called javaboy_logbin.xxx, which will be used to record all DDL and DML statement events.

Log_bin_index: this is the index file for binlog, which holds all the binlog directories, because there may be multiple binlog. We can take a look at the current javaboy_logbin.index file:

As you can see, there is currently only one logbin file.

3. Common binlog operations

Next, let's introduce a few common binlog operation commands.

View all binlog logs

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

Show master logs

As you can see, I currently have only one log file here, and the file name javaboy_logbin.000001,File_size means that the file occupies a byte size of 154.

View master status

This command is often used when building MySQL masters and slaves, as follows:

At this point, you can see the latest binlog log file name and the Position value of the last action event (what is the use of this value, which we will explain in more detail later).

Refresh binlog

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

As you can see from the figure above, after we refresh the log, we use show master logs to view the log and find that there is a newly generated log file. Then we use show master status to check the latest log file information and find that it has also become javaboy_logbin.000002.

Reset binlog

Reset master can reset the binlog log file to start logging again from 000001, but if the current host has one or more slaves running, the command will not run (because the slave synchronizes the database through binlog, the host empties the binlog and cannot find the binlog error from the opportunity).

View binlog

Because binlog is a binary log file, if you open it directly, you won't be able to see it:

I didn't see any useful information.

In order to view binlog,MySQL, we have two official tools. Let's take a look at one. The first is the mysqlbinlog command, as follows:

Although it looks messy, there are traces when you look at it carefully. Because I am here is a newly installed database, which just created a library called javaboy, and then created a table called user with two pieces of data and nothing else, so the script to create the library can actually be found in a variety of files.

One of the resulting log files has an end_log_pos that is the pos point of the log file, which is useful in future data recovery.

However, this kind of viewing is not humanized. We say that binlog logs by event, so it would be much better if we can view the log in the way of events. Let's take a look at the following command:

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

This indicates that the binlog is viewed as an event, and several parameters are involved:

Log_name: you can specify the name of the binlog log file to view, or if not, the oldest binlog file.

Pos: start viewing from which pos point. All operations recorded by binlog have a pos point. This actually means that we can specify which operation to view the log from. If not, it starts from the beginning of the binlog.

Offset: this is the offset. If not specified, the default is 0.

Row_count: view how many rows of records. If you don't specify, you will see all of them.

Let's look at a simple example:

Show binlog events in 'javaboy_logbin.000001'

This makes it much clearer, and we can see all the previous actions, such as:

A library was created between Pos 219,322.

A table is created between Pos 387-537.

A record has been added between Pos 677-780.

...

4. Data recovery actual combat

All right, with the preparation of the previous basic knowledge, Brother Song will show you a deletion / recovery scenario hand in hand.

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

This is a newly installed database, in which I created a new database named javaboy,javaboy, and a new table named user,user has two records, as follows:

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

Now it's 3: 00 in the morning, and the automatic backup of the database starts. We backup the database as a SQL script with the following command, as follows:

Mysqldump-uroot-p-- flush-logs-- lock-tables-B javaboy > / root/javaboy.bak.sql

Here are a few parameters to explain to you:

Needless to say,-u and-p.

-flush-logs: this means that the binlog is refreshed before export. After refreshing the binlog, a new binlog file will be generated, and all subsequent operations will be stored in the new binlog.

-lock-tables: this indicates that all tables are locked before the export starts. It is important to note that when exporting multiple databases,-lock-tables locks the tables for each database separately, so this option does not guarantee the logical consistency of the tables in the exported file between databases, and the export status of different database tables can be completely different.

-B: this indicates the name of the database to be exported. If you use-- all-databases or-An instead of-B to indicate that all databases are exported.

After the above command is executed, a javaboy.bak.sql file is generated in the / root directory, which is the backup sql file.

This happened at three o'clock on Wednesday morning.

Then on Thursday morning, I came to work. After one operation, two more operations were added to the database, as follows:

Next, Xiao X quarreled with the leader today and decided to delete and run away:

The leader discovered the shock and immediately asked for the data to be restored immediately. It's time for you to act.

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

After the recovery, the data are now available by 3: 00 a. M. on Wednesday morning.

The data from 3: 00 a. M. on Wednesday morning to Thursday is now gone.

At this time, we have to recover with the help of binlog. Remember, when we performed the backup at 3: 00 am on Wednesday, we used a parameter called-- flush-logs, which 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 course, javaboy_logbin.000002. Let's take a look at this file:

Show binlog events in 'javaboy_logbin.000002'

The file I generated here is rather long, and I intercept some of it:

As you can see, the deletion event occurred in the Pos, so we just need to play back the file and restore the data to the location of 764.

Since the javaboy_logbin.000002 file is a new file after it was backed up at 3: 00 a.m. on Wednesday, the operation of this file from the beginning to the 764 Pos is the operation from 3: 00 a.m. on Wednesday to before deleting the library.

So 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

So here are two parameters:

-stop-position=764 means to restore to the Pos of 764. If not specified, the entire file will be restored. If the file is restored according to the current file, because there is a statement to delete the database in this binlog file, it will cause the javaboy library to be deleted after the binlog is executed.

-database=javaboy means to restore the javaboy library.

Another parameter that we don't use here is called start-position, which represents the starting Pos. If not specified, it means data recovery from scratch.

All right, when you're done, let's check the database:

The data is restored.

Note: before all operations, remember to back up the backup (in case you make a mistake and can't go back). Brother Song omitted some backup operations in order to save trouble.

The above is all the contents of the article "how to recover data from MySQL's binlog". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.