How to open MySQL binlog log

binlog is a binary log file, which records all mysql dml operations. Through binlog logs, we can perform data recovery, 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.

How to enable mysql binlog?

Add three lines directly to the my.inf main configuration file

log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index

Three parameters to specify,
The first parameter is to open the binlog log. The second parameter is the base file name of the binlog log. The third parameter specifies the index file of the binlog file, which manages the directory of all binlog files.

Of course, there is also a simple configuration, which can be done with one parameter

log-bin=/var/lib/mysql/mysql-bin

The function of this parameter is the same as the above three. MySQL will automatically set log_bin to on state according to this configuration, and automatically set the log_bin_index file to the file name you specify followed by .index

After these configurations are completed, it should be possible for versions below 5.7, but if we are using version 5.7 or above at this time, restarting the MySQL service will result in an error. At this time we must also specify a parameter

server-id=123454

Randomly assign a string that cannot be the same as the name of other machines in the cluster. If there is only one machine, you can assign it at random.

With the above configuration, we can restart our mysql

CentOS 6
service mysqld restart
 
 
CentOS 7
systemctl restart mysqld

After successful startup, we can log in to see if our configuration works

show variables like '%log_bin%' 

Let's find this directory and take a look.

Because I have already operated the database here, I can see that there are multiple mysql-bin files and a mysql-bin.index file. We can open this file to take a look.

At this point, our binlog log is opened.

For specific operations on binlog logs, please refer to
Detailed explanation of binlog log: https://www.jb51.net/article/207954.htm
Use binlog to restore data: https://www.jb51.net/article/207957.htm

This is the end of this article about the steps to enable MySQL binlog. For more information about enabling MySQL binlog, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!