Detailed explanation of MySQL binlog usage

binlog is a binary log file that records all DML operations of MySQL. Through binlog logs, we can do data recovery, incremental backup, master-master replication, master-slave replication, and so on. Developers may not pay much attention to binlog, but it is very important for operation and maintenance or architecture personnel.

MySQL 5.7 does not enable binlog by default. For details on how to enable it, see https://www.jb51.net/article/207953.htm

After binlog is successfully enabled, the location of the binlog file can be viewed in the my.inf configuration file. You can also view it in the MySQL command line. The command line view code is as follows

show variables like '%log_bin%';

We can also take a look at the current MySQL binlog situation

show master status; 

As can be seen from the above figure, there is currently only one binlog file, the file name is: mysql-bin.000001. Every time we restart, a binlog file will be automatically generated . After the restart, we execute the same command again, and the content is as follows:

There is also such a file in the directory where binlog is stored.

Of course, we can also refresh the binlog file manually. By flushing logs , a new binlog file will also be created. In fact, when the server is restarted, the flush logs operation is also called.

If we want to clear all these files, we can use reset master to handle it

Next, let's look at the operation of a single file. First, we want to look at the contents of the file.

Find the binlog directory, for example, we want to look at mysql-bin.000001

vi mysql-bin.000001 

We see a bunch of gibberish. We know that this is a bunch of binary files, so there must be something wrong with opening the binary file as text. So how do we view the contents of this file?

MySQL provides us with a tool for viewing binlog logs, called mysqlbinlog

mysqlbinlog myql-bin.000001

This file is quite long. What should you do if you cannot finish reading it in one go? You can use Linux pipes. I will not explain it in detail here. You can look up some knowledge about Linux yourself.

Note that there is a position field in the screenshot above. This field is similar to a pointer. The current value of this field is 154, which indicates the current position of the binlog. Every time we perform a DML operation, the position will change. For example, let's create a data test

Before creating, we can clear the binlog log for our convenience, and we can use reset master. In a production environment, this operation is very dangerous, so we can use flush logs to process it and generate a new binlog file. Regardless of the method used, we only need a new binlog file in the test environment. After generating a new binlog file, we can check the status through show master status

Let's execute a dml statement, for example, we want to create a test database

create database test;

Then let's check the status after creation. As shown below, we find that position has changed from 154 to 313, which means that our operation is between 154 and 313. Then let's look at the contents of the binlog.

We intercept the contents of the binlog between 154 and 313 as follows:

# at 154
 
#170708 9:24:02 server id 12345 end_log_pos 219 CRC32 0x30763ffe Anonymous_GTID last_committed=0 sequence_number=1
 
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 
# at 219
 
#170708 9:24:02 server id 12345 end_log_pos 313 CRC32 0x4d0140b3 Query thread_id=5 exec_time=0 error_code=0
 
SET TIMESTAMP=1499477042/*!*/;
 
SET @@session.pseudo_thread_id=5/*!*/;
 
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 
SET @@session.sql_mode=1436549152/*!*/;
 
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 
/*!\C utf8 *//*!*/;
 
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
 
SET @@session.lc_time_names=0/*!*/;
 
SET @@session.collation_database=DEFAULT/*!*/;
 
create database test
 
/*!*/;
 
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
 
DELIMITER ;
 
# End of log file
 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

We can see that MySQL does a lot of implicit operations. Careful friends will see this sentence: create database test;

Let's briefly summarize the binlog:

1. The binlog file will create a new file when the service starts

2. You can manually refresh the logs through flush logs to generate a new binlog file

3. You can view the status of binlog through show master status

4. Binlog log files can be cleared by resetting the master

5. The contents of the binlog log can be viewed through the mysqlbinlog tool

6. By executing dml, mysql will automatically record binlog

This is the end of this article on the detailed use of MySQL binlog log. For more relevant content on the use of MySQL binlog log, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!