MySQL binary logs administration

This is a wiki page. Be bold and improve it!

If you have any questions about the content on this page, don't hesitate to open a new ticket and we'll do our best to assist you.

Documentation

MySQL 8.0 Reference Manual / The Binary Log / 5.4.4 The Binary Log
https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

17.1.6.4 Binary Logging Options and Variables
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.h...

MySQL 8.0 Reference Manual / MySQL Programs / Administrative and Utility Programs / 4.6.8 mysqlbinlog — Utility for Processing Binary Log Files
https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html

MySQL 8.0 Reference Manual / MySQL Programs / Administrative and Utility Programs / mysqlbinlog — Utility for Processing Binary Log Files
4.6.8.3 Using mysqlbinlog to Back Up Binary Log Files
https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-backup.html

Overview

Before doing anything, you must have a proper picture of the current situation: gather some information and have a proper understanding of key settings and configuration.

Are binary logs enabled?
From MySQL 8.0, binary logging is enabled by default.

Where are binary logs saved?
They are usually saved within the MySQL data directory:

datadir = /var/lib/mysql

but if log-bin is set to a specific path, then that is used instead.

What is the base name of the binary logs?

By default, the base name of the binary logs is the name of the host machine, unless log-bin is set to a specific path and base name.

SQL > SHOW VARIABLES LIKE 'log_bin_basename';

On this topic, see:
17.1.6.4 Binary Logging Options and Variables >> --log-bin[=base_name]
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.h...

Which are the current binary log files?

SQL >  SHOW BINARY LOGS;

Given the log_bin_basename and the output of the previous query, you can have a full picture of the current binary files. If you plan to touch, move or rename those files, expect breakage unless you follow strict, proper procedure...

Other key settings affecting binary logs, space requirements, etc...

From the variable binlog_expire_logs_seconds, we can extrapolate the number of days of binary logs the server will keep. You can estimate the space requirements for one day of binary logs, and thus estimate the total space that a full extent of binary logs will take.

Below, 5 days of logs are kept, and each log file will take 500MB of space. It only remains to estimate how long it takes for the server to fill in that much logs:

binlog_expire_logs_seconds = 432000 # 5 days = 3600 * 24 * 5
max_binlog_size         = 500M

Binary logs location

Considerations

It is advisable to store the binary logs in a different partition or even a different disk than the primary MySQL data directory, for at least two reasons: performance, and data security.

In the event that the storage disk where the MySQL data dir is fails, all the data would be lost. We can restore the databases from the latest backup, but then, we would miss all the data from the backup time to the time of the disk failure.
If we had the binary logs within the MySQL data dir (as per default), then the logs would be lost at the same time as the rest.
On the other hand, if we stored the binary logs on a different drive, we could still restore the MySQL server up to the time of the disk failure.

See:
MySQL 8.0 Reference Manual / Backup and Recovery / Example Backup and Recovery Strategy /
7.3.2 Using Backups for Recovery
https://dev.mysql.com/doc/refman/8.0/en/recovery-from-backups.html

Moving the binary logs to a new location

1) Check the physical location and the base name of the binary logs. See above.

2)Reduce the number of files to physically move. Purge to the last available one:

SQL > SHOW BINARY LOGS;
SQL > PURGE BINARY LOGS TO 'mysql-bin.000123';
SQL > SHOW BINARY LOGS;

See also:
MySQL 8.0 Reference Manual / SQL Statements / Replication Statements / SQL Statements for Controlling Source Servers
13.4.1.1 PURGE BINARY LOGS Statement
https://dev.mysql.com/doc/refman/8.0/en/purge-binary-logs.html

3) Prepare the place where you want to put the binary logs:

# mkdir /path/to/mysql/binary.logs
# chown mysql:mysql /path/to/mysql/binary.logs/

4) Configure MySQL:

log-bin = '/path/to/mysql/binary.logs/mysql-binlogs

5) Stop MySQL.

# service mysql stop

6) Move the binary log files to the new location:

# mv /var/lib/mysql/mysql-bin.* /path/to/mysql/binary.logs/

7) If your move involves renaming the base name of the binary logs, you must rename each file accordingly:

# mv mysql-bin.000123 mysql-binlogs.000123
# mv mysql-bin.index  mysql-binlogs.index

and you must also edit the .index file (which is a simple text file), to change the list of file name contained within.

8) Restart MySQL.

# service mysql start