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

This article mainly introduces how to use MySQL binlog related knowledge, detailed and easy to understand, simple and fast operation, has a certain reference value, I believe that everyone will have a harvest after reading this MySQL binlog how to use the article, let's take a look at it.

MySQL logs are more important binlog (archive log), redo log (redo log) and undo log, so we are mainly related to this article binlog.

1. binlog

Binlog is generally called archive log in Chinese. If you have seen the MySQL master-slave structure sent by Brother Song before, you should have an impression of this log. When we build MySQL master-slave, binlog is indispensable.

binlog is a MySQL Server layer log, not a log that comes with the storage engine. It records all DDL and DML(excluding data query statements) statements, and it is recorded in the form of events. It also contains the time consumed by the execution of statements. It should be noted that:

Binlog is a logical log, which records the original logic of an SQL statement, such as +1 to a certain field. Note that this physical log is different from redo log (what changes have been made on a certain data page).

After 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, which is written cyclically, that is, what is written later may overwrite what is written earlier.

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

According to MySQL official documentation, after opening binlog, there will be about 1% performance loss, but this is still acceptable. Generally speaking, binlog has two important use scenarios:

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

MySQL data recovery, by using mysqlbinlog tool in combination with binlog file, you can restore data to a certain point in the past.

2. Open binlog

For the sake of demonstration, Songge installed MySQL in Docker here, and we used this as an example to start today's demonstration. If your friends still don't understand the use of docker, you can reply to docker in the background of the public account. There are tutorials written by Brother Song.

First we install MySQL in docker, then enter the container, you can check whether binlog is open by the following command:

This OFF means that binlog is a closed state and not open. Next, we will open binlog.

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

For this configuration file, we make the following modifications:

#This parameter indicates that binlog function is enabled, and specifies the storage directory of binlog log-bin=javaboy_logbin#Set the maximum bytes of a binlog file #Set the maximum 100MBmax_binlog_size=104857600#Set the validity period of binlog file (unit: days) expire_logs_days = 7# binlog logs only updates to specified libraries #binlog-do-db=javaboy_db# binlog does not log updates to specified libraries (Used when configuring master-slave replication)#binlog-ignore-db=javaboy_no_db#How many times to write cache, flush disk once, default 0 means that this operation is determined by the operating system according to its own load How often to write disk # 1 means that every transaction submitted will immediately write disk, n means that n transactions will be submitted before writing disk sync_binlog=0#Take a unique id for the current service (configuration required after MySQL 5.7) server-id=1

The meaning of each configuration has been explained in Gaze. Screenshots are as follows:

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

docker restart mysql1

After reboot, run show variables like 'log_bin%' again; you can see binlog is 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 prefix of the binlog file name generated in the future. In other words, according to the configuration you have seen so far, the binlog file name generated in the future is javaboy_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 holds the directory of all binlogs, because binlogs may have multiple binlogs. We can take a look at the javaboy_logbin.index file now:

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

3. Common binlog operations

Next, we will introduce some common binlog commands.

View all binlog logs

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

show master logs;

As you can see, I only have one log file here, the file name is javaboy_logbin.000001, File_size means that the byte size occupied by this file is 154.

View master status

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

At this point, you can see the latest binlog file name and the Position value of the last operation event (what is the use of this value, we will explain in 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 file will be generated, and then all binlog logs will be recorded in the new file. As follows:

As can be seen from the above figure, after refreshing the log, we check the log through show master logs and find that there is a newly generated log file. Then we check the latest log file information through show master status and find that it has also changed to javaboy_logbin.00002.

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, then this command will not work (because the slave uses binlog to achieve database synchronization, the host clears the binlog, and the slave will report an error that binlog cannot be found).

View binlog

Since binlog is a binary log file, if you open it directly, it must be invisible:

I didn't see any useful information.

In order to view binlog, MySQL provides us with two official tools, we look at one by one, the first is the mysqlbinlog command, as follows:

Although it looked messy, there were actually traces to follow when one looked closely. Because I am here a newly installed database, which only created a library named javaboy, and then created a table named user added two pieces of data, nothing else was done, so the script to create the library we can actually find from the miscellaneous files.

The generated log file has an end_log_pos that is the pos point of the log file, which will be useful in future data recovery.

However, this view is not human enough, we say binlog is recorded according to events, so if we can view the log according to events, it will be much better. Let's look at the following command:

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

This means viewing binlog as an event, involving several parameters:

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 binlog recorded operations have a pos point, this is actually equivalent to we can specify which operation to start viewing the log, if not specified, it is from the beginning of the binlog to start viewing.

offset: This is the offset, not specified default is 0.

row_count: View how many rows of records, do not specify is to view all.

Let's look at a simple example:

show binlog events in 'javaboy_logbin.000001';

This is much clearer, we can see all the previous operations, for example:

A library was created between Pos 219-322.

A table was created between Pos 387-537.

A record was added between Pos 677-780.

4. Data Recovery

Well, with the basic knowledge prepared in front, Brother Song will give you a demonstration of a deletion/recovery scene.

Let me tell you about the current state of my database.

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

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

It's now 3:00 AM and the automatic database backup has started. We can backup the database into SQL script by using the following command:

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

Here are a few parameters to explain to you:

-U, -P, these two need not be mentioned.

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

--lock-tables: This means that all tables are locked before starting export. It should be noted that when exporting multiple databases, --lock-tables locks tables for each database separately, so this option does not guarantee the logical consistency of tables in the exported file among databases, and the export status of tables in different databases can be completely different.

-B: This indicates the name of the database specified for export, if--all-databases is used or-A instead of-B indicates all databases are exported.

After the above command is executed, a javaboy.bak.sql file will be generated in the/root directory, which is the sql file backed up.

This happened at 3 a.m. Wednesday morning.

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

Next, Little X quarreled with the leader today and was very unhappy. He decided to delete the escape route:

The leader was shocked and immediately requested to recover the data immediately. Now it's time for you to act.

First of all, we have a backup file from Wednesday morning, so we'll use that file for data recovery first:

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

Data from Wednesday morning at 3:00 a. M. To Thursday is now gone.

At this point we need to use binlog to recover. Remember, when we performed the backup at 3 a.m. on Wednesday, we used a parameter called--flush-logs, which means that from the moment of backup, the new binlog file will be generated in a new log file. For us, the new binlog file is javaboy_logbin.00002, of course. Let's take a look at this file:

show binlog events in 'javaboy_logbin.000002';

The file I generated here is quite long, so I cut a part of it:

As you can see, in 764-865 this Pos occurred in the delete library run event, then we only need to play back the file to restore the data to 764 this location can be.

Since javaboy_logbin.000002 is a new file created after the backup at 3:00 a.m. on Wednesday, the operation of this file from the beginning to 764 Pos is the operation from 3:00 a.m. on Wednesday to the deletion of the library.

So let's look at the binlog command to recover data:

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

There are two parameters involved:

--stop-position=764 indicates that the Pos is restored to 764. If it is not specified, the entire file is restored. If the current file is restored, since 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 restore javaboy.

There is also a parameter that we don't use here called--start-position, which indicates the starting Pos, and if not specified, it means to start data recovery from scratch.

All right, when we're done, check the database:

Data Recovery

Note: Before all operations, remember to backup the backup (to prevent you from operating wrong and not going back), Songge omitted some backup operations in order to save trouble.

About "MySQL binlog how to use" The content of this article is introduced here, thank you for reading! I believe everyone has a certain understanding of "how to use MySQL binlog" knowledge. If you still want to learn more knowledge, please pay attention to 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.