Question
My disk is full. When using du, I could see my MySQL log folder is taking up all the disk. These files are not mysql-bin files, instead, these are mysql-relay-bin files. How can I truncate or purge these files?
Answer
Mysql creates mysql-bin files which are called binary files for MySQL on the master side. It then streams the binary files to the replicas to reflect the changes. Mysql doesn’t really store the binaries on the replica side, hence it is safe to delete the relay-bin files. Although, there are other safe strategies to accomplish this or never fall into a situation of ‘out of space’.
Firstly, we need to understand, expire_log_days MySQL attribute doesn’t work for the replica. The reason is simple, it doesn’t store the binary files. Hence, using this is worthless for replicas. You can’t use ‘PURGE’ SQL command either for Replica log purging. Replica has a special attribute ‘relay_log_purge’ which purges the relay-bin logs periodically. You can also set replica to follow a specific size of the log file by using ‘relay_log_space_limit’ attribute.
Now at a very certain time, if you want to free up space, other than gross and brutal deleting relay-bin log files, what you can do is the following:
# start your mysql console session mysql # stop the slave stop slave; # reset the slave reset slave; # now start the slave again start slave;
Now, once the slave is reset, it will start streaming the bin log from the “pos” it has in its queue to populate the pending jobs.
Once the sync is done, you may check the replica status using:
show slave status \G;
Make sure the following two are set to yes:
Slave_IO_Running: Yes Slave_SQL_Running: Yes
If not, you should look at the ‘Last_SQL_Error’ or ‘Last_IO_Error’ section to find out why these are not pushing.
Good luck.