How to set up MariaDB or MySQL automatic backups using cron

Automated backups turn a failed upgrade, a fat-fingered DELETE, or a dying disk into a restore operation instead of a post‑mortem. A regular logical dump provides a known-good recovery point that exists outside the running database process. Scheduled runs via cron remove the “someone remembered” dependency.

mysqldump creates a logical backup by exporting schema and data as SQL statements that can be imported later with the mysql client. A small shell script provides consistent options, predictable filenames, compression, and retention, while cron runs that script on a fixed schedule. Using a MySQL option file for credentials keeps secrets out of the crontab and command history.

Logical dumps can be large and sensitive, so storage, permissions, and retention matter as much as the schedule. Consistent snapshots work best with InnoDB tables via --single-transaction, while non-transactional tables can still change during a dump. Cron runs with a minimal environment, so absolute paths and explicit logging avoid surprises during unattended runs.

1. Create the backup directory at /var/backups/mysql.

   $ sudo install --directory --owner=root --group=root --mode=0700 /var/backups/mysql

   Mode 0700 keeps backup archives readable only by root.

2. Confirm the backup directory permissions.

   $ ls -ld /var/backups/mysql
   drwx------ 2 root root 4096 Dec 24 23:58 /var/backups/mysql

3. Create a dedicated database user named backup for mysqldump.

   $ sudo mysql --execute "CREATE USER IF NOT EXISTS 'backup'@'localhost' IDENTIFIED BY 'BackupPass123!'; ALTER USER 'backup'@'localhost' IDENTIFIED BY 'BackupPass123!'; GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES ON *.* TO 'backup'@'localhost'; FLUSH PRIVILEGES;"

   On some MariaDB installs the client binary is mariadb, while mysql remains a compatible entry point.

4. Verify the backup user grants.

   $ sudo mysql --execute "SHOW GRANTS FOR 'backup'@'localhost';"
   Grants for backup@localhost
   GRANT SELECT, RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO `backup`@`localhost`

5. Create the MySQL client option file at /root/.my.cnf.

   $ sudo install --mode=0600 /dev/null /root/.my.cnf

6. Add the backup credentials to /root/.my.cnf.

   $ sudo tee /root/.my.cnf >/dev/null <<'EOF'
   [client]
   user=backup
   password=BackupPass123!
   host=localhost
   EOF

   Plaintext credentials live in /root/.my.cnf, so the file must stay mode 0600.

7. Verify /root/.my.cnf is readable only by root.

   $ sudo stat --format '%A %U %G %n' /root/.my.cnf
   -rw------- root root /root/.my.cnf

8. Test the option file with a non-interactive query.

   $ sudo mysql --defaults-extra-file=/root/.my.cnf --execute "SELECT NOW() AS backup_test;"
   +---------------------+
   | backup_test         |
   +---------------------+
   | 2025-12-25 00:01:29 |
   +---------------------+

9. Create the backup script at /usr/local/sbin/mysql-backup.sh.

   $ sudo tee /usr/local/sbin/mysql-backup.sh >/dev/null <<'EOF'
   #!/usr/bin/env bash
   set -euo pipefail
    
   BACKUP_DIR="/var/backups/mysql"
   DEFAULTS_FILE="/root/.my.cnf"
   RETENTION_DAYS="14"
   LOCKFILE="/var/lock/mysql-backup.lock"
    
   umask 077
    
   if [[ ! -f "$DEFAULTS_FILE" ]]; then
     echo "Missing defaults file: $DEFAULTS_FILE"
     exit 1
   fi
    
   mkdir -p "$BACKUP_DIR"
    
   timestamp="$(date +%F_%H%M%S)"
   outfile="$BACKUP_DIR/mysql-$timestamp.sql.gz"
   tmpfile="$outfile.tmp"
    
   cleanup() {
     rm -f "$tmpfile"
   }
   trap cleanup EXIT
    
   exec 9>"$LOCKFILE"
   if ! flock -n 9; then
     echo "Backup already running: $LOCKFILE"
     exit 1
   fi
    
   extra_opts=()
   if mysqldump --help 2>/dev/null | grep -q -- '--no-tablespaces'; then
     extra_opts+=(--no-tablespaces)
   fi
    
   mysqldump \
     --defaults-extra-file="$DEFAULTS_FILE" \
     --all-databases \
     --single-transaction \
     --routines \
     --events \
     --triggers \
     --hex-blob \
     --quick \
     "${extra_opts[@]}" \
     | gzip --stdout --best > "$tmpfile"
    
   mv -f "$tmpfile" "$outfile"
    
   find "$BACKUP_DIR" -maxdepth 1 -type f -name 'mysql-*.sql.gz' -mtime +"$RETENTION_DAYS" -print -delete
    
   echo "Backup written: $outfile"
   EOF

   The retention delete is automatic, so confirm BACKUP_DIR and RETENTION_DAYS before relying on unattended runs.

10. Make the backup script executable.

    $ sudo chmod 0700 /usr/local/sbin/mysql-backup.sh

11. Run the backup script to generate a dump.

    $ sudo /usr/local/sbin/mysql-backup.sh
    Backup written: /var/backups/mysql/mysql-2025-12-25_000031.sql.gz

12. Confirm a new archive exists in /var/backups/mysql.

    $ sudo ls -lh /var/backups/mysql
    total 860K
    -rw------- 1 root root 857K Dec 25 00:00 mysql-2025-12-25_000031.sql.gz

13. Confirm the archive begins with a SQL dump header.

    $ sudo gzip --stdout --decompress /var/backups/mysql/mysql-2025-12-25_000031.sql.gz | head -n 12
    -- MySQL dump 10.13  Distrib 8.0.44, for Linux (aarch64)
    --
    -- Host: localhost    Database:
    -- ------------------------------------------------------
    -- Server version	8.0.44
    ##### snipped #####

    The header string differs between MySQL and MariaDB, while the restore workflow remains SQL import.

14. Open root's crontab for editing.

    $ sudo crontab -e

15. Add a nightly cron entry for /usr/local/sbin/mysql-backup.sh.

    MAILTO=
    SHELL=/bin/bash
    PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
    
    0 2 * * * /usr/local/sbin/mysql-backup.sh >>/var/log/mysql-backup.log 2>&1

    Output lands in /var/log/mysql-backup.log for quick post-run verification.

16. Confirm the cron entry is installed.

    $ sudo crontab -l
    MAILTO=
    SHELL=/bin/bash
    PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
    
    0 2 * * * /usr/local/sbin/mysql-backup.sh >>/var/log/mysql-backup.log 2>&1