DB Maintenance Script for Drupal MySQL Servers (Optional)

DB maintenance script

db-maintenance.sh is an optional server script that ships with the Turbo Indexer. It has nothing to do with indexing: it is a weekly MySQL cleanup for servers that host Drupal sites, run from cron on the database machine.

What one run does

1. Finds every database on the server that has a Drupal cache_bootstrap table.
2. In each one, when the watchdog table exists (Database Logging is on): deletes the rows older than 7 days, then optimizes the table.
3. Empties every InnoDB cache_ table of 2 MB or more. Drupal fills them again as pages are requested.
4. Deletes expired flood rows and batch rows older than one day, when those tables exist.
5. When binary logging is on and no replica is connected, purges the binary logs older than one day. With a replica connected, it leaves them alone.
6. Prints the size of /var/lib/mysql and the disk use of /.

Install

The script is in the module's folder; with the standard Composer layout that is web/modules/contrib/opensolr_turbo_indexer/.

sudo cp web/modules/contrib/opensolr_turbo_indexer/db-maintenance.sh /usr/local/bin/
sudo chmod +x /usr/local/bin/db-maintenance.sh

# See what it would do, change nothing
db-maintenance.sh -u USER -p PASSWORD --dry-run

Then add it to root's crontab (crontab -e). This line runs it every Sunday at 04:00:

0 4 * * 0 /usr/local/bin/db-maintenance.sh -u USER -p PASSWORD >> /var/log/db-maintenance.log 2>&1

Options

Good to know

• Without -u and -p it logs in as root with the password root. Pass your own.
• It talks to the MySQL server on the same machine; there is no host option.
• It needs bash and the mysql command-line client.
• Run it with --dry-run once on every new server, to see which databases it finds.