How to enable MySQL binlog

This article will introduce to you how to open MySQL's binlog log. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

binlog is binary log, binary log file. This file records all dml operations of mysql. Through binlog logs, we can perform data recovery, master-live replication, master-slave replication, etc. Developers may not pay much attention to binlog, but it is very important for operation and maintenance or architect 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

to specify three parameters.

The first parameter is to open the binlog log

The second parameter is the basic file name of the binlog log, and an identifier will be appended to represent each file

The third parameter specifies the index file of the binlog file. This file manages all binlog files. Directory

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 according to this configuration In the on state, the log_bin_index file is automatically set to the file name you specify followed by .index

After these configurations are completed, it should be OK for versions below 5.7, but if we are using 5.7 and above at this time version, restarting the mysql service will report an error. At this time, we must also specify a parameter

server-id=123454

Randomly specify a string that cannot have the same name as the machine in other clusters. If there is only one machine, you can specify it casually

With the above configuration, we can restart our mysql

# CentOS 6
service mysqld restart


# CentOS 7
systemctl restart mysqld

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

show variables like '%log_bin%'

Below we can find this directory and take a look

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

At this point, our binlog log will be opened.

Recommended learning: php video tutorial

The above is the detailed content of How to enable MySQL binlog. For more information, please follow other related articles on the PHP Chinese website!