Enable / Disable the binary log in MySQL

Understand the concept of Binary log in MySQL

Binary log having information of changes to the table data or ddl operations.
It used for replication and restore operations for point in time recovery in MySQL.
Note: log_bin system variable is set to ON OR using the –log-bin option during startup.

Check bin log is enabled (ON) or disabled (OFF)

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

Show the list of binary file created.

mysql> SHOW BINARY LOGS;
+-----------------+-----------+-----------+
| Log_name        | File_size | Encrypted |
+-----------------+-----------+-----------+
| RAC1-bin.000001 |       179 | No        |
| RAC1-bin.000002 |   2088087 | No        |
| RAC1-bin.000003 |       179 | No        |
| RAC1-bin.000004 |       179 | No        |
+-----------------+-----------+-----------+
4 rows in set (0.00 sec)

Enable the Binary Log in MySQL

--For enable, edit the my.ini file and enter following parameter
log-bin="RAC1-bin"

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

Disable the Binary log in MySQL

-- For disable the binary log, Edit the my.ini file
#log-bin="RAC1-bin"  --Comment the log-bin parameter
skip-log-bin   -- enter this in my.ini file and save and restart the mysql

--On reconnecting we find value off.
mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.03 sec)

binlog_format parameter

binary logging format explicitly by starting the MySQL server with –binlog-format = type.
Types are:
STATEMENT causes logging to be statement based. It is used in Replication.
ROW causes logging to be row based.
MIXED causes logging to use mixed format

--- Change the parameter value
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

--Current value in the database
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

expire_logs_days Parameter
This will ensure that the logs are retained and get deleted only afterwards.

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.01 sec)

SET GLOBAL expire_logs_days = number_of_days;

mysqlbinlog:
Display the contents of binary log files with the mysqlbinlog utility.


mysqlbinlog log_file | mysql -h server_name

Example of using 

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\RAC1-bin.000002" > C:\1.txt

Note:
the binary log is synchronized to disk at each write (sync_binlog=1).
If sync_binlog was not enabled, and the operating system or machine crashed, there is a chance of binary log last statement lost.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.