An automatic MySQL backup should run every night without anyone touching it, produce a dump that is consistent and restorable, keep a history, leave the server, and raise an alarm when something breaks. This guide builds exactly that with mysqldump, a dedicated backup user, a shell script and cron or a systemd timer, and shows how to test the result.

The problem: the backup that nobody checks

Many MySQL servers do have a backup: a line in cron written years ago. Nobody notices when the password changes and the job starts failing, when the disk fills up and dumps come out empty, or when the files never leave the server. The day the database is corrupted or a table is dropped by mistake, the team discovers that the newest usable copy is far older than they thought, or does not exist.

For an online store, ERP or SaaS, that means lost orders, invoices and customer accounts. The fix is not only to schedule mysqldump, but to automate the whole chain: consistency, verification, retention, offsite copy and alerts.

Step 1: create a dedicated backup user

Do not run backups as root. Create a user with only the privileges a dump needs:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'use-a-long-random-password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD, REPLICATION CLIENT
  ON *.* TO 'backup'@'localhost';

RELOAD and REPLICATION CLIENT are only needed if you record the binary log position (see step 3). Keep the password out of scripts with an option file readable only by root:

# /root/.my-backup.cnf  (chmod 600)
[client]
user=backup
password=use-a-long-random-password
host=localhost

Alternatively, mysql_config_editor stores the credentials in an obfuscated login path. Never put -p'password' on the command line: it is visible in the process list.

Step 2: the consistent dump command

mysqldump --defaults-extra-file=/root/.my-backup.cnf \
  --single-transaction --quick \
  --routines --triggers --events \
  --databases shop | gzip > shop.sql.gz
  • --single-transaction takes a consistent snapshot of InnoDB tables without blocking writes. It is the most important option for a live server.
  • --quick streams rows instead of loading whole tables into memory.
  • --routines --triggers --events include stored procedures, triggers and scheduled events, which are easy to forget.
  • --defaults-extra-file must be the first option.

Tables using MyISAM are not covered by --single-transaction; convert them to InnoDB or accept a short lock. On MariaDB, the same options work with mariadb-dump.

Step 3: the automatic backup script

#!/usr/bin/env bash
# /usr/local/sbin/mysql-auto-backup.sh
set -euo pipefail

CNF=/root/.my-backup.cnf
DEST=/var/backups/mysql
KEEP_DAYS=14
STAMP=$(date +%F_%H%M)
mkdir -p "$DEST/$STAMP"

# One file per database (system schemas excluded)
DBS=$(mysql --defaults-extra-file=$CNF -N -e "SELECT schema_name FROM information_schema.schemata
  WHERE schema_name NOT IN ('mysql','information_schema','performance_schema','sys')")

for db in $DBS; do
  mysqldump --defaults-extra-file=$CNF \
    --single-transaction --quick --routines --triggers --events \
    --databases "$db" | gzip > "$DEST/$STAMP/$db.sql.gz"

  # Verify: gzip is valid and the dump finished
  gzip -t "$DEST/$STAMP/$db.sql.gz"
  zcat "$DEST/$STAMP/$db.sql.gz" | tail -n 1 | grep -q "Dump completed" \
    || { echo "Incomplete dump: $db" >&2; exit 1; }
done

# Users and grants, as replayable SQL statements
mysql --defaults-extra-file=$CNF -N -e "SELECT CONCAT('SHOW CREATE USER ''',user,'''@''',host,''';',
  'SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user NOT LIKE 'mysql.%' AND user <> ''" \
  | mysql --defaults-extra-file=$CNF -N | sed 's/$/;/' > "$DEST/$STAMP/users.sql"

chmod -R go-rwx "$DEST"
find "$DEST" -mindepth 1 -maxdepth 1 -type d -mtime +"$KEEP_DAYS" -exec rm -rf {} +
echo "MySQL backup OK: $DEST/$STAMP"

The check for the -- Dump completed footer catches dumps that were cut off by a full disk or a lost connection, which would otherwise look like valid files. set -o pipefail makes the script fail if mysqldump fails inside the pipe.

For point-in-time recovery between nightly dumps, enable binary logs on the server, add --source-data=2 (MySQL 8.0.26 and later; --master-data=2 on older versions and MariaDB) to record the log position in the dump, and copy the binary logs offsite continuously. The MySQL backup best practices guide explains this in detail.

Step 4: schedule the automatic MySQL backup

With cron:

chmod 700 /usr/local/sbin/mysql-auto-backup.sh
crontab -e
# every day at 01:30
30 1 * * * /usr/local/sbin/mysql-auto-backup.sh >> /var/log/mysql-auto-backup.log 2>&1

Or with a systemd timer, which also runs a missed backup after a reboot (Persistent=true). The automatic Linux backup guide has a complete timer example with failure alerts. Run the job outside peak hours: even with --single-transaction, a dump adds read load.

Step 5: copy dumps offsite

Dumps in /var/backups/mysql disappear with the server. Send them to another location, encrypted, for example with restic to S3-compatible storage, or with rsync over SSH to a backup server that pulls the files (so the database server has no permission to delete old copies). Keep at least one copy that cannot be altered from production, in line with the 3-2-1-1-0 rule.

Step 6: test the restore every month

# On a test server or container with the same MySQL major version
zcat /var/backups/mysql/2026-10-25_0130/shop.sql.gz | mysql --defaults-extra-file=/root/.my-test.cnf
mysql --defaults-extra-file=/root/.my-test.cnf -e "SELECT COUNT(*) FROM shop.orders;"

Compare counts of key tables with production and time the restore. A 20 GB dump can take hours to import because indexes are rebuilt; if that is longer than your business can wait, consider physical backups with Percona XtraBackup or mariadb-backup.

Common mistakes in automatic MySQL backups

  • No --single-transaction: tables locked during the dump or inconsistent data.
  • Password on the command line or in the script.
  • No verification, so truncated or empty dumps go unnoticed.
  • Routines, triggers, events or users missing from the backup.
  • Dumps kept only on the database server.
  • Copying /var/lib/mysql while MySQL is running instead of dumping.

For the broader picture across engines, read the database backup guide. Backnix is being built to run consistent MySQL and MariaDB backups automatically, encrypt them and store them offsite, with alerts when a job fails. See the database backup page for the current status and the plans and prices.

References

Topics: Automatic backupMariaDBMySQLmysqldump