How to Enable and Use Binary Log in MySQL/MariaDB

In MySQL or MariaDB database, when you make any changes in the database every event is logged. All events are stored in the binary log.

In a simple term, the binary log is a set of log files that contains information of data modifications made to a MySQL server instance. It contains all information including, update database, delete database, create and delete table and more.

The binary log is very useful when you are using MySQL replication. In this case, a binary log will send the data from the master MySQL server to the slave server. You can also use the binary log to perform any recovery operations in MySQL.

In this post, we will show you how to enable and use binary log in MySQL on Linux.

# Prerequisite

• A server running Linux with MariaDB installed.
• A root password is set up in the server.

# Verify Binary Log

Before starting, you will need to verify whether the bin-log option is turned on or not and what binary log format is used.

To check it, first log in to the MySQL with the following command:

mysql

Once login, run the following command to check the binary log status:

MariaDB [(none)]> show variables like '%bin%';

You should get the following output:

+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| bind_address                            | 127.0.0.1            |
| binlog_annotate_row_events              | ON                   |
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_commit_wait_count                | 0                    |
| binlog_commit_wait_usec                 | 100000               |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_file_cache_size                  | 16384                |
| binlog_format                           | MIXED                |
| binlog_optimize_thread_scheduling       | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_stmt_cache_size                  | 32768                |
| encrypt_binlog                          | OFF                  |
| gtid_binlog_pos                         |                      |
| gtid_binlog_state                       |                      |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | OFF                  |
| log_bin_basename                        |                      |
| log_bin_compress                        | OFF                  |
| log_bin_compress_min_len                | 256                  |
| log_bin_index                           |                      |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| read_binlog_speed_limit                 | 0                    |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
| wsrep_forced_binlog_format              | NONE                 |
+-----------------------------------------+----------------------+

As you can see, the binary log is off in the MySQL server.

Next, check whether the binary log is used or not with the following command:

MariaDB [(none)]> show binary logs;

You should see the following output:

ERROR 1381 (HY000): You are not using binary logging

Next, check the binary log data directory with the following command:

MariaDB [(none)]> show variables like 'datadir';

You should get the following output:

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.003 sec)

# Enable Binary Log in MySQL

You will need to edit the MySQL main configuration file to enable the binary logging.

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add the following lines:

log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size        = 100M
binlog_format = mixed

Save and close the file when you are finished. Next, restart the MySQL service to apply the changes.

systemctl restart mariadb

Now, connect to the MySQL and verify the binary log status with the following command:

MariaDB [(none)]> show variables like '%bin%';

You should see that binary log is now enabled:

+-----------------------------------------+--------------------------------+
| Variable_name                           | Value                          |
+-----------------------------------------+--------------------------------+
| bind_address                            | 127.0.0.1                      |
| binlog_annotate_row_events              | ON                             |
| binlog_cache_size                       | 32768                          |
| binlog_checksum                         | CRC32                          |
| binlog_commit_wait_count                | 0                              |
| binlog_commit_wait_usec                 | 100000                         |
| binlog_direct_non_transactional_updates | OFF                            |
| binlog_file_cache_size                  | 16384                          |
| binlog_format                           | MIXED                          |
| binlog_optimize_thread_scheduling       | ON                             |
| binlog_row_image                        | FULL                           |
| binlog_stmt_cache_size                  | 32768                          |
| encrypt_binlog                          | OFF                            |
| gtid_binlog_pos                         |                                |
| gtid_binlog_state                       |                                |
| innodb_locks_unsafe_for_binlog          | OFF                            |
| log_bin                                 | ON                             |
| log_bin_basename                        | /var/log/mysql/mysql-bin       |
| log_bin_compress                        | OFF                            |
| log_bin_compress_min_len                | 256                            |
| log_bin_index                           | /var/log/mysql/mysql-bin.index |
| log_bin_trust_function_creators         | OFF                            |
| max_binlog_cache_size                   | 18446744073709547520           |
| max_binlog_size                         | 104857600                      |
| max_binlog_stmt_cache_size              | 18446744073709547520           |
| read_binlog_speed_limit                 | 0                              |
| sql_log_bin                             | ON                             |
| sync_binlog                             | 0                              |
| wsrep_forced_binlog_format              | NONE                           |
+-----------------------------------------+--------------------------------+

Next, check whether the binary log is used or not with the following command:

MariaDB [(none)]> show binary logs;

You should see the following output:

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.000 sec)

By default, all binary log files are stored in the /var/log/mysql directory. You can check them with the following command:

ls /var/log/mysql

You should get the following output:

error.log         mysql-bin.000002  mysql-bin.000004  mysql-bin.000006  mysql-bin.000008  mysql-bin.index
mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  mysql-bin.000007  mysql-bin.000009

# Working with Binary Log

The mysqlbinlog is a command-line tool used to view the content of the binary log in a human readable format.

For example, to view the content of mysql-bin.000001 binary file run the following command:

mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.000001

You should get the following output:

#210303 11:52:15 server id 1  end_log_pos 503 CRC32 0x7260a26e  GTID 0-1-2 ddl
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
# at 503
#210303 11:52:15 server id 1  end_log_pos 596 CRC32 0x2b572f9f  Query   thread_id=38    exec_time=0 error_code=0
SET TIMESTAMP=1614772335/*!*/;
create database testdb1
/*!*/;
# at 596
#210303 11:53:26 server id 1  end_log_pos 619 CRC32 0xa1f8c5c4  Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

As you can see, the above command will display a lot of data from all databases.

In this case, you can use the -d option to specify the specific database and display the information of the given database.

For example, run the following command to dump all the events of testdb database in the testdb.txt file.

mysqlbinlog --no-defaults -d testdb /var/log/mysql/mysql-bin.000001 > testdb.txt

If you want to display only SQL queries from the binary log file, run the following command:

mysqlbinlog --no-defaults -s /var/log/mysql/mysql-bin.000001

In some cases, you don't want to log events in binary log file, for example database recovery process. In that case, you can disable the binary log with the following command:

mysqlbinlog --no-defaults -D /var/log/mysql/mysql-bin.000001

# Conclusion

In the above guide, you learned how to enable the binary log in MySQL. You also learned how to view a binary log with mysqlbinlog command. I hope this will help you in your day-to-day operations.