wp: Server down Mysql binlog overflow

Well, this is a strange one. Three years ago we had a bunch of instability with WordPress on DigitalOcean because we were running out of memory, so I installed a swapfile with some magic commands. Then two years ago, I had another set of WordPress crashes because certbot on two old multi-user sites that no longer exist, and then more WordPress crashes because I was out of the 25GB of disk space on my tiny little droplet, so I bought a 25GB volume and connected it to the uploads file to alleviate this.

Now today, I kept on getting “database connection lost“, a sure sign something is wrong, logging into the DigitalOcean droplet, I see that the root is completely used up and that my volume with uploads is only 3GB full on 25GB.

So some more investigation and the offending /var/lib/mysql is just filled with binlog files. It looks like there are binary log files that you can use on crashes to playback and recover it. The default is 30 days worth of files and for some reason on many days, the maximum 100MB is used for these. I’m not sure what the problem is because it is not like I’ve been using these very much. Inspecting the files with mysqlbinlog tells me that they are filled with text representations of massive binary blobs. That is pretty confusing, I’m wondering why WordPress stores binary in there. It is supposed to use the uploads directory for all of it.

In any case, I learned how to log in as root and then run mysql and then look for the latest binlog files and run purge binary log to binlog.1700 or whatever is a recent file seems to do the trick. Note that you have to have MySQL running, but sudo service start mysql kept failing since it is completely out of disk so I had to do a sudo reboot and start again.

So now here’s the dilemma, should I change the 30 days manually in the MySQL file or just hope that this doesn’t happen again? Alternatively, I could attach a volume to the /var directory instead of just /home/1264501.cloudwaysapps.com/eeaewzxvun/public_html/uploads. This isn’t such a bad idea because then I will have the entire working system just running against it.

Alternatively, I looked at using an S3 sync plugin that copies the uploaded files to S3 or Digital Ocean Spaces, but with only 3GB of images after years of use, this hardly seems worth it. I think I’m going to try the /var/ trick and then if the thing crashes it should be easier to bring it up.

Final solution 50GB /var disk

The final solution is to just bite the bullet and pay $5/month for a 50GB volume that runs on /var. This has some big advantage, the first being that I should be able to build a new Droplet and just push this in as /var is by definition where the applications should be putting their data.

It is actually sort of tricky to do, but here’s the easiest way to swap in a new block storage volume for /var is:

  1. Create a new volume. Note that DigitalOcean on first creation will kindly format the drive and mount it in /dev/disk for you.
  2. Now in Digital Ocean do a snapshot of the entire Droplet.
  3. Now you have to stop the major services running with sudo service apache2 stop && sudo service mysql stop
  4. Then you can mount it with sudo mkdir /var.new and then you should edit /etc/fstab by connecting the drive to /var.new and reloading fstab without restarting with a mount -a command
  5. Now you should be able to do a full copy with rsync -avP /var/ /var.new and note the trailing slash in the /var/ that is important
  6. Ok, now you are ready to save your old /var with sudo mv /var /var.old && mkdir /var and make space for the new mount.
  7. Finally change the fstab so that the new drive points to /var and do a mount -a and it should all work

I’m Rich & Co.

Welcome to Tongfamily, our cozy corner of the internet dedicated to all things technology and interesting. Here, we invite you to join us on a journey of tips, tricks, and traps. Let’s get geeky!

Let’s connect