Automated Drupal MySQL Maintenance Script

DB Maintenance Script

A zero-configuration weekly MySQL maintenance script for any Drupal server. Auto-detects all Drupal databases on the system, trims watchdog logs, truncates cache tables, cleans up expired data, and purges MySQL binary logs.

🔍

Auto-Detection

Finds all Drupal 7/8/9/10/11 databases automatically via cache_bootstrap table

📋

Watchdog Cleanup

Trims old log entries, keeps configurable number of days (default: 7)

🗑

Cache Truncation

Truncates all cache_* InnoDB tables over 1MB across every database

💾

Binlog Purge

Safely purges MySQL binary logs when no replicas are connected

Installation

# Download and install
sudo cp db-maintenance.sh /usr/local/bin/db-maintenance.sh
sudo chmod +x /usr/local/bin/db-maintenance.sh

# Add to weekly crontab
(crontab -l 2>/dev/null; echo "0 4 * * 0 /usr/local/bin/db-maintenance.sh >> /var/log/db-maintenance.log 2>&1") | crontab -

Runs at 4 AM every Sunday by default. Adjust the cron schedule to match your maintenance window.

Usage

db-maintenance.sh

Run with default settings (MySQL root/root credentials):

db-maintenance.sh

Preview changes without modifying anything:

db-maintenance.sh --dry-run

Custom credentials and watchdog retention:

db-maintenance.sh -u admin -p secret -d 14

Options

Option	Default	Description
`-u, --user`	`root`	MySQL username
`-p, --pass`	`root`	MySQL password
`-d, --days`	`7`	Number of days of watchdog entries to keep
`--dry-run`	off	Preview mode — shows what would be done without making changes

What It Does

Each run performs these operations on every detected Drupal database:

1. Discover Drupal Databases

Queries information_schema for any database containing a cache_bootstrap table. This table exists in Drupal 7, 8, 9, 10, and 11, regardless of which modules are enabled.

2. Trim Watchdog (if dblog is enabled)

Checks if the watchdog table exists (dblog module) and deletes entries older than the configured retention period. Runs OPTIMIZE TABLE afterward to reclaim disk space.

3. Truncate Cache Tables

Finds all cache_* InnoDB tables and truncates any over 1MB. This is always safe in Drupal — cache tables are rebuilt automatically on next request.

4. Clean Flood & Batch Tables

Removes expired entries from the flood table and stale batches (older than 1 day) from the batch table, if they exist.

5. Purge Binary Logs

If MySQL binary logging is enabled and no replicas are connected, purges binary logs older than 1 day. Skips purge entirely if any replica is actively replicating.

Example Output

[2026-02-16 04:00:01] Starting Drupal MySQL maintenance (keep 7 days of watchdog)...
[2026-02-16 04:00:01] Found Drupal databases: drupal cntest
[2026-02-16 04:00:01] Processing: drupal
[2026-02-16 04:00:01]   watchdog: deleting 145232 rows older than 7 days
[2026-02-16 04:00:03]   truncating cache_entity (1542MB)
[2026-02-16 04:00:03]   truncating cache_render (387MB)
[2026-02-16 04:00:04]   truncating cache_page (89MB)
[2026-02-16 04:00:04] Processing: cntest
[2026-02-16 04:00:04]   watchdog: dblog not enabled, skipping
[2026-02-16 04:00:04]   truncating cache_render (52MB)
[2026-02-16 04:00:05] Binary logs: 2345MB, no replicas connected - purging all but latest
[2026-02-16 04:00:05] Done. MySQL dir: 4.2G, Disk: 12G used / 38G free (24%)

Compatibility

Component	Supported
Drupal	7, 8, 9, 10, 11
MySQL / MariaDB	5.7+, 8.0+, MariaDB 10.3+
OS	Any Linux (Debian, Ubuntu, CentOS, RHEL, Amazon Linux)

Always do a dry run first on a new server to verify which databases are detected and what actions would be taken.