Blog

Binlogs in MySQL are like transactional logs which are also called binary logs. So whatever changes are made to the database, like any insert, delete, etc that information is stored in binlogs. Today in this article, we will learn about how to enable/disable binlogs in MySQL.

1. Open my.cnf file and add the parameter ‘log_bin=binlog_name’ to enable and comment this parameter to disable (Below version MySQL 8). From MySQL 8, by default binlog is enabled. To disable it, add the option  skip-log-bin in my.cnf file and save it.
2. Restart the mysqld services

Syntax :
systemctl stop mysqlds
systemctl start mysqld

3. Check the binlog whether it’s enabled now.

Syntax :
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

or,


mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.03 sec)

mysql> 

we will see the binlogs below format :

Types of Binary Logging in MySQL:

Binary logging is of three types in MySQL as below :

i) Statement-Based Logging: This consists of statements like insert, update, delete, etc.
ii) Row-Based Logging: It consists of events that change individual rows.
iii) Mixed-Based Logging: It consists of both – statement and row-based logging.

we can purge the bin logs also based on the time, date, sequence, etc. Next article we will see that in detail. You can get more information about Binlogs in MySQL Doc as well.