MySQL Binary Logs

Summary: in this tutorial, you will learn about MySQL binary logs including essential concepts, configurations, and related statements.

Introduction to the MySQL binary logs

Binary logs are files that store the changes to the MySQL database. These logs contain a series of events that represent the modifications to data and database objects such as tables, views, databases, and so on.

For example, when you create a new table, MySQL records the corresponding CREATE TABLE statement in the binary log. Also, when you delete a row from a table, MySQL records the DELETE statement in the binary log.

However, if you execute a SELECT statement, MySQL will not record it in the binary log because the SELECT statement does not change the database.

MySQL uses the binary logs for the following purposes:

Replication: Binary logs provide a reliable and efficient way to replicate data between MySQL servers.
Recovery: Binary logs also play a crucial role in point-in-time recovery.

MySQL supports several types of binary logs, including:

Statement-based (STATEMENT) – enables logging to use row-based.
Row-based (ROW) – enables logging to use the row-based.
Mixed format (MIXED) – enables logging to use mixed format.

Each format has its advantages and use cases.

Binary logging adds some overhead that may impact performance. Therefore, you need to closely monitor it. Also, you may want to disable it in the development and test servers.

Binary log configuration

The following are typical binary log configurations:

1) Enabling binary logging

MySQL enables the binary logs by default. To check if the binary log is enabled, you follow these steps:

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, retrieve the value of the log_bin variable:

show global variables like 'log_bin';Code language: SQL (Structured Query Language) (sql)

Output:


+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

If the log_bin is ON, meaning that the binary log is enabled. If it is OFF, the binary log is disabled.

To enable binary logging, you add the following line to the MySQL configuration file (my.cnf or my.ini):

log-bin=mysql-binCode language: SQL (Structured Query Language) (sql)

2) Binary log format

The binlog_format variable stores the binary log format. For example:

show global variables like 'binlog_format';Code language: SQL (Structured Query Language) (sql)

Output:


+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that MySQL uses the ROW binary log format. To specify a different binary log format, you add the following line to the MySQL configuration file:

binlog_format=STATEMENTCode language: SQL (Structured Query Language) (sql)

The valid values are ROW, STATEMENT, and MIXED.

3) Binary Log Location

By default, MySQL stores the binary logs in the data directory. If you want to store the binary logs in a different location, you can specify an absolute path to the desired directory in the MySQL configuration file:

log-bin=/absolute/path/to/binary/logs/Code language: SQL (Structured Query Language) (sql)

4) Binary Log Retention

Binary logs can consume disk space over time. To prevent storage issues, you can control the lifespan of binary log files via a binary log retention policy.

To set the binary log retention policy, you can set the number of days, the maximum size of the binary log file, and the number of log files in the configuration file.

For example:

expire_logs_days=7 # 7 daysCode language: SQL (Structured Query Language) (sql)

This example sets the retention period to 7 days. If the logs are older than 7 days, MySQL will remove them during the regular rotation process

max_binlog_size=100MCode language: SQL (Structured Query Language) (sql)

In this example, we set the maximum size for a binary log file to 100 megabytes. Note that you can specify the size in bytes, kilobytes (K), megabytes (M), or gigabytes (G).

max_binlog_files=10Code language: SQL (Structured Query Language) (sql)

In this example, we set the maximum number of binary log files to 10. Once the number of log files is 10, MySQL deletes (or purges) the older log files to make room for new ones.

5) Encryption of binary logs

To enhance security, you can encrypt the binary logs using the encrypt-binlog option:

encrypt-binlog=1Code language: SQL (Structured Query Language) (sql)

Binary log statements

We’ll introduce the most commonly used statement for dealing with binary logs.

1) SHOW BINARY LOGS

To show a list of available binary logs, you use the SHOW BINARY LOGS statement:

SHOW BINARY LOGS;Code language: SQL (Structured Query Language) (sql)

Sample output:


+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |     11322 | No        |
| binlog.000002 |       201 | No        |
| binlog.000003 |       483 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) SHOW MASTER STATUS

To display information about the binary log of the source server and the replication position, you use the SHOW MASTER STATUS statement:

SHOW MASTER STATUS;Code language: SQL (Structured Query Language) (sql)

Sample output:


+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      483 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) PURGE BINARY LOGS

To remove old binary logs based on a given file, you use the PURGE BINARY LOGS statement. For example, the following removes the binary files 000001 and 000002:

PURGE BINARY LOGS TO 'binlog.000003';Code language: SQL (Structured Query Language) (sql)

If you display a list of binary logs, you’ll see only the log file binlog.000003 left:

SHOW BINARY LOGS;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000003 |       483 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) FLUSH BINARY LOGS

To force the MySQL server to close the current log file and open a new one, you use the FLUSH BINARY LOGS statement:

FLUSH BINARY LOGS;Code language: SQL (Structured Query Language) (sql)

If you issue a statement that changes the database, you’ll see a new log file. For example, let’s create a new database called sampledb:

CREATE DATABASE sampledb;Code language: SQL (Structured Query Language) (sql)

MySQL will record the new event in a new binary log file.

The following statement displays the log file list:

SHOW BINARY LOGS;Code language: SQL (Structured Query Language) (sql)

Output:


+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000003 |       527 | No        |
| binlog.000004 |       157 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The mysqlbinlog utility: examining binary log files

To view the SQL statements recorded in the binary logs, you use the mysqlbinlog utility.

First, open the Command Prompt on Windows or Terminal on Unix-like systems.

Second, view the contents of the binary log file binlog.000003:

mysqlbinlog /var/lib/mysql/binlog.000003Code language: SQL (Structured Query Language) (sql)

It’ll show the contents of the log file binlog.000003.

If you want to save the contents into a file, you can use the redirection > on Unix-like systems. For example, the following saves the contents of the binary log file into the binlog.txt file.

mysqlbinlog /var/lib/mysql/binlog.000003 > ~/binlog.txtCode language: SQL (Structured Query Language) (sql)

Summary

MySQL uses binary logs to record the change events to the data and database objects.
Use the binary logs for replication and point-in-time recovery purposes.
Use the mysqlbinlog utility to view the contents of binary log files.

Was this tutorial helpful?