Binary logs play an important role in replication and data recovery. In replication, source databases communicate data changes recorded in its binary logs to the replica’s relay logs. They can also be used to repair a replica experiencing data drift. In a similar way, binary logs are essential when performing a Point-in-Time recovery. This quick guide will give you the foundational knowledge to start using binary logs.
What Are Binlogs?
Binary logs, often called binlogs for short, log data in binary format. They track data changes that modify the database. Typically, SELECT statements are not logged unless they are a part of a transaction that modifies data in the database.
The type of changes logged is dependent on the value of --binary-format. For example, --binary-format takes the values MIXED, STATEMENT, or ROW. The default is ROW as of MySQL 5.7.7 , which means events that change individual table rows are logged. Prior to MySQL 5.7.7, the default is STATEMENT. Statement-based logging tracks the SQL statements that modify data. Mixed logging uses statement-based logging first and switches to ROW when it makes more sense to use row-based logging. For more information on binary logging formats, check out the reference manual.
Managing Binlogs
The system variable log_bin is used to enable binlogs and is not dynamic; changing it from default will require restarting the database. The default location for the binlogs is in the data directory. If a value is specified for log_bin, it will be used as the default filename. If the base name includes the absolute path, the binlogs will be located there.
Prior to MySQL 8.0, binlogs are disabled by default. To enable them globally, invoke --log-bin at startup or set log_bin in the configuration file and restart the database. Once enabled, the binary logs can be disabled during a session by setting sql_log_bin to OFF.
As of MySQL 8.0, binlogs are enabled by default unless mysqld is used to initialize the database by invoking --initialize or --initialize-insecure at startup. Disable binlogs at startup using --skip-log-bin or --disable-log-bin. Note that these variables will be ignored if log_bin is specified in the configuration file.
No matter which MySQL version you have, it is important to manage the size and rotation of the binlogs. Since binlogs track database modifications, they can suddenly take up a lot of disk space if a lot of changes are made in a short period of time. If available disk space is less than 25%, a process or transaction that does a lot of writes or deletes can make your database vulnerable to a crash.
In situations like this, you can manually purge the binlogs. Use the PURGE BINARY LOGS statement below. Purging binlogs requires the BINLOG_ADMIN privilege.
PURGE { BINARY | MASTER } LOGS {
TO 'log_name'
| BEFORE datetime_expr
}Although it is possible to delete binlogs from the command line, it is not recommended because the MySQL server will lose track of them and might throw an error message that the binlogs are missing if needed.
Proactively, other “house cleaning” measures can be taken. The binary logs are flushed automatically when the database is restarted, which might not be ideal. The system variable expire_logs_days can set an interval to flush logs. This and other variables that control binlog size and rotation are listed in the table below.
| Variable Name | Version(s) | Function | Default |
expire_logs_days | 5.6, 5.7, 8.0 | Number of days for automatic binlog removal. | 0(no automatic removal) |
binlog_expire_logs_seconds | 8.0 | Interval in seconds when the blog files will be removed. | 2592000 |
binlog_expire_logs_auto_purge | 8.0.29 | Enables and disables variables that enable automatic purging. | ON |
max_binlog_size | 5.6, 5.7, 8.0 | Starts a new binlog file when the size specified is exceeded. | 1073741824 |
binlog_row_event_max_size | 5.6, 5.7, 8.0 | Sets a soft limit on binlog files when row-based logging is used. | 256 |
binlog_transaction_compression | 8.0.20 | Before being written to the binlog, payloads are compressed. | OFF |
binlog_transaction_compression_level_zstd | 8.0.20 | Numeric value from 1 (lowest) to 20 (highest) that indicates the level of compression efforts. | 3 |
Reading Binlogs
Binary format looks like gibberish if you try to cat them. To make sense of it, the mysqlbinlog utility is used on the command line. The example below is an example using row-based formatting.
You are ready to start using binlogs! To learn about using them in replication, read Replication and Binary Logging Options and Variables. Read Point-in-Time Recovery Using Binary Log to get more information about using binlogs to recover data.