Running Low on Disk Space Using MySQL Replication?

One of the requirements for setting up replication with MySQL is to turn on binary logging. Binary logs contain records of database changes or changes to actual data. The binary logs do not record results of general queries which do not affect data such as SELECT or SHOW. However, results of queries which affect the database and its data, such as DELETE and UPDATE are recorded.

The binary logs allow MySQL to recreate events which led to the current database state. This is necessary to recover from certain errors, and to bring a database fully up-to-date after restoring from backup. Since the binary logs contain all data changing events, it is possible to recover changes which occurred after a database dump was created.

In addition, the binary logs are used by the replication process to convey information about data changes among the MySQL servers. Actual data changes are made on the MySQL replication ‘master’, and details of these changes are written to the binary log. The MySQL replication ‘slaves’ subscribe to the binary logs in order to determine what changes have been made to the replication master’s dataset. The replication slave then applies these changes to its own dataset in order to keep the slave dataset fully synchronized with the master dataset. In this way, full replication is achieved.

Unfortunately, binary logs (often abbreviated ‘bin-logs’) do not have a default expiration in MySQL. This means that binary logs are constantly generated, but never removed. This is of concern because the binary logs are not small, and will soon take up a significant amount of space on disk. The first indication of this is usually a multitude of low disk space warnings (or sudden server errors / crashes due to lack of free space).

The immediate solution to this problem is to purge old and unneeded binary logs. Generally, very old logs are safe to remove. But, if replication is enabled, you need to ensure that all replication slaves have finished reading the logs before removal. This can be done by querying the replication slave’s status.

On each replication slave:

1
2
3
4
5
6
7
8
9
10
11

mysql> SHOW SLAVE STATUS\G

************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.132.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000074
          Read_Master_Log_Pos: 1003370983
                        etc.

Note the binary log file which each replication slave is currently reading. In this case, it is mysql-bin.000074. Since the binary logs are sequential, it is safe to remove any logs created prior to the oldest one being read by replication slaves.

On the replication master:

mysql> PURGE BINARY LOGS TO 'mysql-bin.000074';

Alternatively, logs prior to a certain date can be removed:

mysql> PURGE BINARY LOGS BEFORE '2011-01-01 00:00:00';

This only takes care of the immediate excess of binary logs. It does not prevent them from accumulating and resulting in low disk space again. To prevent this, the MySQL global variable expire_logs_days should be set. This variable determines how many days the binary logs are kept before being automatically purged. By default, the variable is set to ‘0’ which indicates that the binary logs should never be purged. Ideally, the value of the variable should be set to several days to allow replication slaves to catch up. If the replication slaves typically lag several days behind, the variable should be set accordingly.

On each MySQL server with bin-logs:

mysql> SET GLOBAL VARIABLE expire_logs_days=10;

This option can also be set in the MySQL configuration file, /etc/my.cnf:

1
2

[mysqld]
expire_logs_days = 10

Setting this variable will cause MySQL to automatically purge binary logs older than the number of days specified in the variable. In this way, binary logs are retained while they are valuable (e.g. database dumps, and during replication of current data), but they will not begin to accumulate and take up disk space.

It is important to note that while the MySQL binary logs exist as discrete files on disk (usually in /var/lib/mysql, named mysql-bin.000001, etc.), they should not be removed using conventional means. Doing so may result in MySQL errors or failure due to attempts to enumerate the binary logs at run time. The discrete log files should only be removed with rm or a similar utility if they are no longer in the list of binary logs maintained by MySQL but still exist on disk.

You can see the list of binary logs maintained by MySQL by running:

mysql> SHOW BINARY LOGS;

If the log files are listed here, they are still maintained by MySQL and should be removed using the PURGE BINARY LOGS command detailed earlier.

Resources:

• MySQL 5 Reference Manual