How to automate MySQL or MariaDB database backups with cron

Automatic database backups turn a failed upgrade, a bad deployment, or an accidental DELETE into a restore job with a recent recovery point. A scheduled logical dump is often the simplest way to keep a current copy of schemas and data outside the running database server.

Current MySQL systems use mysqldump, while current MariaDB packages prefer mariadb-dump. Both clients can export schemas, routines, events, triggers, and table data as SQL that can be replayed later. A wrapper script keeps the dump options, compression, filenames, and retention policy consistent, and cron runs that script on a fixed schedule without exposing the password in shell history.

These steps assume a Linux host with cron, gzip, and flock available, and they use --single-transaction for an online logical backup of mostly InnoDB workloads. Nontransactional tables such as MyISAM can still change during the dump, and current MySQL docs add extra privilege requirements for some GTID-enabled servers. Current MariaDB docs also note that recent dumps begin with a sandbox-mode line and that the older mysqldump name is being retired in some MariaDB environments.

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

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

   Mode 0700 keeps the backup archives readable only by root.

2. Create a dedicated backup account with the privileges required by this dump pattern.

   $ sudo mysql --execute "CREATE USER 'backup'@'localhost' IDENTIFIED BY 'StrongBackupPasswordHere'; GRANT SELECT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost'; FLUSH PRIVILEGES;"

   On current MariaDB systems, use mariadb if that is the local SQL client command instead of mysql.

   For MySQL, current docs require LOCK TABLES only when --single-transaction is not used, and PROCESS only when --no-tablespaces is not used.

   If the account already exists, use ALTER USER 'backup'@'localhost' IDENTIFIED BY 'StrongBackupPasswordHere'; instead of rerunning CREATE USER. On MySQL servers with gtid_mode=ON and gtid_purged=ON or AUTO, add RELOAD or FLUSH_TABLES for this dump pattern.

3. Verify the grants for the new backup account.

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

4. Create the client option file at /root/.my.cnf so the cron job can authenticate non-interactively.

   $ sudo install --mode=0600 /dev/null /root/.my.cnf
   $ sudo tee /root/.my.cnf >/dev/null <<'EOF'
   [client]
   user=backup
   password=StrongBackupPasswordHere
   host=localhost
   EOF

   Keep /root/.my.cnf at mode 0600 because it stores the backup password in plaintext.

5. 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
   2026-04-09 23:20:12

   --defaults-extra-file is an option-file control flag, so keep it before other client options.

6. 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" >&2
     exit 1
   fi
   
   if command -v mariadb-dump >/dev/null 2>&1; then
     DUMP_CMD="$(command -v mariadb-dump)"
   elif command -v mysqldump >/dev/null 2>&1; then
     DUMP_CMD="$(command -v mysqldump)"
   else
     echo "No MariaDB or MySQL dump client found" >&2
     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" >&2
     exit 1
   fi
   
   extra_opts=()
   if "$DUMP_CMD" --help 2>/dev/null | grep -q -- '--no-tablespaces'; then
     extra_opts+=(--no-tablespaces)
   fi
   
   "$DUMP_CMD" \
     --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" -delete
   
   echo "Backup written: $outfile"
   EOF

   The script prefers mariadb-dump when it is installed and falls back to mysqldump for MySQL or older MariaDB environments.

   These steps assume a Linux host where flock is available. If the host does not provide flock, replace the lock section with the local equivalent before scheduling the job.

7. Make the backup script executable.

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

8. Run the backup script once before scheduling it.

   $ sudo /usr/local/sbin/mysql-backup.sh
   Backup written: /var/backups/mysql/mysql-2026-04-09_232011.sql.gz

9. Confirm that a new archive exists in /var/backups/mysql.

   $ sudo ls -lh /var/backups/mysql
   total 528K
   -rw------- 1 root root 527K Apr  9 23:20 mysql-2026-04-09_232011.sql.gz

10. Preview the start of the dump to confirm it is valid SQL and identify the dump client.

    $ sudo gzip --stdout --decompress /var/backups/mysql/mysql-2026-04-09_232011.sql.gz | head -n 12
    /*M!999999\- enable the sandbox mode */ 
    -- MariaDB dump 10.19-12.2.2-MariaDB, for Linux (x86_64)
    --
    -- Host: localhost    Database:
    -- ------------------------------------------------------
    -- Server version	12.2.2-MariaDB
    ##### snipped #####

    A recent MariaDB dump begins with a sandbox-mode line. Restore that type of dump with the mariadb client rather than an older mysql client.

11. Edit root's crontab.

    $ sudo crontab -e

12. Add a nightly cron entry for the backup script.

    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

    Use a full path in the cron entry because cron starts with a minimal environment.

13. 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

14. Check the log after the first scheduled run.

    $ sudo tail -n 20 /var/log/mysql-backup.log
    Backup written: /var/backups/mysql/mysql-2026-04-10_020000.sql.gz

    If the log stays empty, confirm that the host cron service is enabled and that the dump client and script paths match the local installation.