Mysql binary logs not rotating, partition out of space, replication-related admin queries hang

Posted on

Mysql binary logs not rotating, partition out of space, replication-related admin queries hang – Managing your servers can streamline the performance of your team by allowing them to complete complex tasks faster. Plus, it can enable them to detect problems early on before they get out of hand and compromise your business. As a result, the risk of experiencing operational setbacks is drastically lower.

But the only way to make the most of your server management is to perform it correctly. And to help you do so, this article will share nine tips on improving your server management and fix some problem about linux, mysql, , , .

I’ve got a box where the root partition is full.

The most immediate explanation is the mysql binary logs aren’t rotating. Despite expire_log_days being set to 10, there are logs older than 10 days. show master status, show slave status, and purge binary logs all hang indefinitely.

Solution :

I’d try a few things, but make sure / being full is your problem. Normally Mysql stores data in /var/lib/mysql and doesn’t use / for much other than /tmp.

First try to clear some space. Delete old logs from /var/log/ or whack that src code you downloaded and never used. If you can get / down to less than 100% you Mysql might return to a state where it’s not wedged.

Assuming you’ve cleared some space try to purge logs. I’d try PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); from the Mysql commandline. If it still hangs after ten minutes you’ll probably need to shut Mysql down if can or kill it if you can’t.

Normally it’s never a good idea to delete logs manually, but in this case it might be your only choice. Delete the oldest log and try to restart Mysql. It’ll probably have to do some table checks. Keep an eye on the mysql error log until it’s up and running properly. Then try to purge logs again.

Once it’s up and running make sure expire_logs_days is really set by checking show variables; in Mysql. You may also want to set max_binlog_size = 100M if your filesystem is small. Normally Mysql rotates logs at 1G which might not be often enough. Mysql only rotates logs when the current log reaches max size.

This depends entirely on the version of MySQL you are running

The problem lies with the memory map of of all binary logs.

When mysqld starts, it reads the index of mysql binary logs into memory.

That memory map is sensitive to the presence of binary logs on disk.

If you do commands such as:
1. PURGE BINARY LOGS TO ‘< binary log filename >’;
2. PURGE BINARY LOGS BEFORE ‘< date and time >’;

mysqld will use the memory map instead of the binary log index file.

If the memory map does not precisely match the binary logs files on disk (i.e., not in sych), log rotation with expire_logs_days will fail.

You cannot simply edit the mysql binary log index file, because when you issue a shutdown for mysqld, the memory map will overwrite the binary log index file.

The problem usually happens when someone deletes the binary logs in the operating system rather that using one of the aformentioned three(3) MySQL Commands. mysqld will not just pick up on the sudden disappearance of binary logs.

The only immediate correction possible is to delete all binary logs, delete the binary log index file, and start mysql back up, or you could do the following:

  1. shutdown mysql
  2. delete binary logs you you do not want
  3. edit the binary log index file (remove binary logs that do not exist from the file)
  4. start mysqld

To watch out for disappearing binary logs thereafter, run SHOW BINARY LOGS; if any of the binary logs entries comes back with file size 0, the memory map of binary logs is out of sych. The course of action then is to do the following:

  1. shutdown mysql
  2. edit the binary log index file (remove binary logs that do not exist from the file)
  3. start mysqld

My advice is once you get it rescued, put /var/lib/mysql and /var/log/mysql on their own LVM volumes.