Mysql – How to cap the total space that thesql / mariadb binary logs take up

mariadbMySQL

I recently had my disk space completely filled with mysql binary log files (mysql-bin.000123, etc).

I was able to resolve short term by with PURGE BINARY LOGS TO (via this answer).

To try and keep it from happening going forward I set lower limits on expire_logs_days and max_binlog_size. Yet, I'm still worried that a huge number of log files could be generated in a short period without a limit on the total space used by logs or the total number of logs allowed.

Is there something I'm missing? Or do I just need to keep setting expire_logs_days lower or turn off binary logging all together if it keeps being a problem?

EDIT:

I understand it's important to diagnose what's causing the logs to fill up quickly and solve that issue. This question is not about how to diagnose what's making them fill quickly, but rather – how do I safeguard against the disk filling to 100% and crashing the server in the case that something is setup wrong or an unexpected circumstance starts to fill these logs.

Best Answer

max_binlog_size has little effect on keeping disk space down; expire_logs_days is the one to worry about.

How fast are the logs filling up?

One way binlogs can fill up extremely fast is when you accidentally have the same server_id on multiple servers in a Replication setup.

Which version? Which binlog_format? Lots of multi-row UPDATEs/DELETEs?

More

expire_logs_days is the best available builtin tool. There may be 3rd party monitoring tools (or you could write one) that watch for disk space.

In any system, you should have some kind of monitoring on disk space -- it is not just the binlog that can go berserk. The "general log" and the slowlog can, also. And your application can go berserk. And so forth.

Related Solutions

Mysql – Will improper bin-log purging leads to deletion of thesql database

If your slave does not also act as a master to another slave, then you should not have any issues from deleting binary logs. The relay logs are important to the slave.

You need to further investigate on why mysql schema disappeared. Is mysql schema still present on the disk, even though MySQL is not showing it? If you have not stopped the slave instance yet, then you could run under same user as MySQL is running:

lsof | grep '/path_to_mysql'

You might see mysql schema tables marked as deleted in there:

(deleted)

Another possibility is that you are connecting with a user that has limited privileges and just does not see mysql schema. Run SHOW GRANTS; to see what privileges you currently have.

As Rolando pointed out, use PURGE BINARY LOGS as best practice for cleaning up binary logs. If MySQL is down, you could delete the files manually, but then you have to also delete the same file names from the index file. Be careful on master servers, as binary logs might still be needed by slaves.

And if you do need to copy mysql schema, you can do it with just these steps on the slave. Although, I would recommend executing FLUSH TABLES; on master before doing these steps.

/etc/init.d/mysql stop
scp -rp master_server:/var/lib/mysql/mysql /var/lib/mysql/
/etc/init.d/mysql start

Mysql – Automatically purging binlogs after successful replication

Server is responsible to purge their own file. Guess what happens if your master has two slaves and one of it catches up faster deleting logs required for second?!!

So yes expire_logs_days is the way to go. Set that value wisely and let master do its business.

That said why do you want to purge logs? You have purge binary logs to command but I'd still let purging upto master unless disk is critical.

If you want it, then you can script out the manual purging logic: Check all slaves have caught up to latest Purge a logs before the exec_master_log_file.

In my opinion this is absolutely unnecessary unless there some odd requirement that u just can't think about...

Best Answer

Related Solutions

Mysql – Will improper bin-log purging leads to deletion of thesql database

Mysql – Automatically purging binlogs after successful replication

Related Question