How to Backup MySQL Database Daily Automatically (September 2026)

An automated daily MySQL backup is a scheduled task, typically a cron job on Linux or a Task Scheduler entry on Windows, that runs the mysqldump command every day to export your database into a .sql file, then optionally compresses it, applies a retention policy, and ships it offsite. If you have ever lost a production database to a bad deploy or a hardware crash, you already know why this guide exists.

Our team has been running MySQL in production since the early 4.x days, and the single change that paid for itself the fastest was turning manual mysqldump runs into a fully automated pipeline. In this guide I will walk you through the exact process we use: a robust bash script, a one-line cron entry, a retention cleanup, a verification step, and a Windows path for teams that do not run Linux.

What Is a MySQL Scheduled Backup?

A MySQL scheduled backup is a job that runs without a human pressing Enter. You define what to back up (one database, several, or every database), where to write the output (a local folder, an S3 bucket, a NAS share), and when to run (midnight, every six hours, every Sunday). Your operating system scheduler then fires the job on that cadence.

The three pieces that work together are simple. The mysqldump command exports your tables and data as raw SQL statements. A small shell script wraps that command with timestamps, logging, compression, and cleanup. A scheduler, either cron on Linux or Task Scheduler on Windows, runs the script at the time you specify. When all three pieces are wired correctly, the database backs itself up while you sleep.

Why Automate MySQL Database Backups

Manual backups fail in ways automated ones do not. People forget, people go on vacation, people run the wrong command on the wrong server. An automated job removes every one of those failure modes and replaces them with a logged, repeatable, testable process.

The business case is stronger than the technical one. Real disasters have destroyed companies that thought they had backups. The 2019 OVH data center fire took racks offline with no offsite copies for some customers. Code Spaces collapsed in 2014 after an attacker deleted most of its AWS setup, including backups. The famous GitLab 2017 outage exposed a replication setup that was not actually replicating and a backup pipeline that had been failing silently for months. None of those teams woke up that morning expecting to lose data.

Beyond disasters, automation also satisfies compliance requirements. HIPAA, GDPR, PCI-DSS, and SOC 2 all require demonstrable, scheduled data protection. A cron job that writes timestamped .sql.gz files to encrypted storage gives auditors something concrete to look at. Your recovery time also drops from days (when someone has to find the right script and run it) to minutes (when the latest backup is already verified and waiting).

MySQL Backup Types You Can Schedule

Before you write any script, decide which kind of backup fits your workload. The choice affects script complexity, restore time, disk usage, and how much data you can lose if the worst happens.

Backup Type	What It Captures	Restore Speed	Storage Cost	Best For
Full (logical)	Entire database as SQL statements via mysqldump or mysqlpump	Slow on large DBs	Medium (compresses well)	Small to mid-size databases, simple restores
Full (physical)	Raw data files copied while server is stopped or via Percona XtraBackup	Fast	High	Large databases, fastest recovery time
Incremental	Only the binary log changes since the last full or incremental backup	Fast	Very low	Point-in-time recovery, large databases
Differential	Changes since the last full backup (cumulative)	Medium	Medium	Fewer restore steps than incremental
Snapshot	Filesystem or storage-level copy (LVM, ZFS, EBS)	Fast	High	Cloud VMs, large datasets

For most teams starting out, the right answer is a daily logical full backup produced by mysqldump, gzipped, retained for 14 days, and copied to offsite storage. That single setup prevents roughly 95 percent of the outages you will ever see. Once your database grows past a few hundred gigabytes, layer Percona XtraBackup on top for hot physical backups and the binary log for point-in-time recovery.

Prerequisites Before You Begin

Five things need to be in place before the first backup can run. Skipping any of them leads to silent failures, which are the most dangerous kind.

1. A dedicated backup MySQL user. Never reuse root. Create a user with SELECT, LOCK TABLES, SHOW VIEW, EVENT, and TRIGGER privileges on the databases you want to back up.
2. A .my.cnf credentials file. Storing the password in plaintext inside a cron job is a security smell. Putting it in a chmod 600 .my.cnf keeps cron clean and lets mysqldump read it automatically.
3. A backup directory with enough free space. Roughly 2x the size of your largest database, to leave room for compressed archives while retaining history.
4. Working mysqldump on PATH. Cron runs with a stripped PATH, so call the binary by full path (often /usr/bin/mysqldump) or set PATH inside the script.
5. gzip and the date command. Standard on every Linux box. Windows servers need 7-Zip or PowerShell’s Compress-Archive.

Run mysqldump --version on the same user that will own the cron job to confirm the binary is reachable. If you see a version string, you are clear to continue.

Step-by-Step: Automate Daily MySQL Backups on Linux with a Bash Script

The script below is the one we ship on every server we manage. It is opinionated: it logs to syslog, it gzips the output, it stamps the filename with the date and time, and it exits non-zero if mysqldump fails so cron can mail you about it.

1. Create the backup directory and lock it down.
   sudo mkdir -p /var/backups/mysql
sudo chown backup:backup /var/backups/mysql
sudo chmod 700 /var/backups/mysql
2. Store credentials in /home/backup/.my.cnf.
   [mysqldump]
user=backup_user
password=YOUR_STRONG_PASSWORD
   Then chmod 600 /home/backup/.my.cnf and chown backup:backup /home/backup/.my.cnf. Now mysqldump picks up the credentials without you ever typing them.
3. Create the script at /usr/local/bin/mysql-backup.sh.

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
TIMESTAMP="$(date +%Y-%m-%d_%H%M%S)"
LOG_TAG="mysql-backup"
MYSQLDUMP="$(command -v mysqldump)"
GZIP="$(command -v gzip)"

DATABASES=("app_prod" "app_staging")

for DB in "${DATABASES[@]}"; do
  FILENAME="${BACKUP_DIR}/${DB}-${TIMESTAMP}.sql.gz"
  logger -t "${LOG_TAG}" "Starting backup of ${DB}"

  if "${MYSQLDUMP}" --defaults-extra-file=/home/backup/.my.cnf 
                    --single-transaction --quick --routines --triggers 
                    "${DB}" | "${GZIP}" > "${FILENAME}"; then
    logger -t "${LOG_TAG}" "Backup of ${DB} completed: ${FILENAME}"
  else
    logger -t "${LOG_TAG}" "Backup of ${DB} FAILED"
    exit 1
  fi
done

4. Make the script executable and owned by the backup user.
   sudo chown backup:backup /usr/local/bin/mysql-backup.sh
sudo chmod 750 /usr/local/bin/mysql-backup.sh
5. Test it manually before scheduling it.
   sudo -u backup /usr/local/bin/mysql-backup.sh
ls -lh /var/backups/mysql/
   You should see one or more timestamped .sql.gz files. If the file is empty or the script exits with an error, fix it now, not at 2 a.m.

Step-by-Step: Schedule the Script with Cron

Once the script works on its own, schedule it. Cron reads a per-user schedule from a file you open with crontab -e. Add a single line at the bottom and save.

0 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

That line means: at minute 0 of hour 2 (2:00 AM), every day, every month, every weekday, run the backup script and append stdout and stderr to a log. Common alternatives for the time field:

• 0 2 * * * — every night at 02:00 (the safest default)
• */30 * * * * — every 30 minutes (only for high-churn databases)
• 0 2 * * 0 — every Sunday at 02:00 (weekly full backup)
• 0 3 * * 1-5 — weekdays at 03:00

After saving, verify cron picked it up with crontab -l. If the listing is empty, cron did not load the file. If the job does not appear to run, check /var/log/syslog on Debian/Ubuntu or /var/log/cron on CentOS/RHEL for lines mentioning your command.

Step-by-Step: Automate MySQL Backups on Windows with Task Scheduler

Windows servers do not have cron, but Task Scheduler does the same job with a GUI. The pattern is identical: a script that runs mysqldump, and a scheduled task that fires it every day.

1. Create a backup folder. For example, C:BackupsMySQL. Right-click the folder, Properties, Security, and grant only your backup user write access.
2. Store credentials in a .my.cnf file at C:BackupsMySQL.my.cnf with the same [mysqldump] section as the Linux example. Restrict the NTFS permissions so only the backup user can read it.
3. Create C:BackupsMySQLbackup.bat.

@echo off
setlocal

set BACKUP_DIR=C:BackupsMySQL
set TIMESTAMP=%date:~-4%-%date:~3,2%-%date:~0,2%_%time:~0,2%%time:~3,2%
set TIMESTAMP=%TIMESTAMP: =0%
set MYCNF=C:BackupsMySQL.my.cnf

"C:Program FilesMySQLMySQL Server 8.0binmysqldump.exe" ^
  --defaults-extra-file="%MYCNF%" ^
  --single-transaction --routines --triggers ^
  app_prod | "C:Program Files7-Zip7z.exe" a -tgzip -si -so "%BACKUP_DIR%app_prod-%TIMESTAMP%.sql.gz"

if errorlevel 1 (
  echo Backup failed at %date% %time% >> %BACKUP_DIR%backup.log
  exit /b 1
)

echo Backup completed at %date% %time% >> %BACKUP_DIR%backup.log

4. Open Task Scheduler (Start, search “Task Scheduler”). Click Create Task. Give it a name like MySQL Daily Backup, choose Run whether user is logged on or not, and tick Run with highest privileges.
5. On the Triggers tab, click New, set it to Daily, and pick a start time (02:00 works well). On the Actions tab, click New, set Action to Start a program, and browse to C:BackupsMySQLbackup.bat. Save, then right-click the task and choose Run to verify it works end-to-end.

PowerShell users can do the same job with Compress-Archive instead of 7-Zip, and with Register-ScheduledTask instead of the GUI. The logic does not change.

Limiting Old Backups: Retention and Cleanup

Backups you never delete eventually fill the disk, which in turn breaks the next backup, which in turn means you lose data right when you need it most. Retention rules prevent that. A simple approach is to delete any backup older than 14 days using find.

0 3 * * * find /var/backups/mysql -type f -name "*.sql.gz" -mtime +14 -delete

Add that line as a separate cron job at 03:00, an hour after the backup itself. The -mtime +14 flag matches files modified more than 14 days ago. Windows equivalents use ForFiles with the /d -14 switch inside a second scheduled task.

For compliance-driven workloads, run multiple retention tiers: daily files kept for 14 days, weekly files kept for 12 weeks, monthly files kept for 12 months. The easiest way is to organize the backup directory by cadence and run a separate cleanup script per folder.

Verifying the Backup and Restoring from a .sql File

A backup you have never restored is not really a backup, it is a guess. The single most important habit you can build is testing your restore at least once a month. It takes five minutes and it is the only way to catch a silently broken job before a real outage.

To verify a backup file is valid, confirm it is non-empty and gzip can decompress it cleanly.

gunzip -t /var/backups/mysql/app_prod-2026-09-11_020000.sql.gz && echo "OK"

Then restore into a throwaway database. Create an empty database, feed the dump back through mysql, and spot-check the row counts.

mysql -u root -p -e "CREATE DATABASE app_prod_restore_test;"
gunzip -c /var/backups/mysql/app_prod-2026-09-11_020000.sql.gz | mysql -u root -p app_prod_restore_test
mysql -u root -p -e "SELECT COUNT(*) FROM app_prod_restore_test.users;"

If the row counts match production, the backup is sound. Drop the test database and you are done. If they do not match, your backup is broken, and finding that out at 02:00 on a Tuesday beats finding it out during a real incident.

MySQL Backup Best Practices: 3-2-1, Encryption, and Alerting

Once the basic pipeline runs, layer these habits on top. Each one protects against a different failure mode.

• Follow the 3-2-1 rule. Keep at least three copies of your data, on two different media, with one copy offsite. The local copy protects against logical mistakes, the offsite copy protects against fire, flood, or a stolen server.
• Encrypt backups at rest. Use gpg --symmetric or age to wrap the gzip archive before uploading. Most cloud breaches in the last decade involved unencrypted backups sitting in a misconfigured bucket.
• Push to offsite storage every night. The AWS CLI, rclone, and restic all do this well. A one-liner like aws s3 sync /var/backups/mysql s3://my-backups/mysql/ keeps an immutable second copy in another region.
• Alert on failure. Wrap the cron command with || to mail or webhook on failure. Even simpler: pipe the cron output to a monitoring agent like Prometheus node_exporter textfile collector or Healthchecks.io.
• Log size and duration. If today’s backup is 40 percent smaller than yesterday’s, something is wrong. Trend the size and runtime so you notice the drop before you need the file.
• Document the restore procedure. A runbook in the team wiki that names the backup location, the restore command, and the on-call contact pays for itself the first time someone new is paged.

Troubleshooting Common MySQL Backup Failures

Every team we have onboarded hits at least one of these issues in the first month. Knowing the symptoms saves hours of debugging at midnight.

The .sql file is empty or suspiciously small. Almost always a credentials or PATH problem. Cron runs with a stripped environment, so mysqldump either is not found or fails to authenticate silently. Add the full path to mysqldump inside the script and verify the .my.cnf file is readable by the cron user.

Cron runs but the backup never appears. Check /var/log/syslog (Debian/Ubuntu) or /var/log/cron (RHEL/CentOS) for the CRON entry. If you see (backup) CMD (...) but no subsequent log line, the script itself is failing. Add set -x at the top of the bash script temporarily to see every command it tries to run.

mysqldump locks tables and the application stalls. You are running a non-transactional storage engine, or you forgot --single-transaction. For InnoDB, --single-transaction gives you a consistent snapshot without locking writes. For MyISAM, accept the lock or migrate the tables to InnoDB.

Permission denied on the .my.cnf file. MySQL refuses to use a credentials file that is world-readable. It must be exactly chmod 600 and owned by the user running the script. Cron runs as the user from crontab -u USER -e, not as root by default.

Backups succeed but restores fail with charset errors. Add --default-character-set=utf8mb4 to your mysqldump command. Skipping it makes the dump fall back to the server default, which on older MySQL installs is the 3-byte utf8 that truncates emoji and many Asian characters.

Frequently Asked Questions

Final Thoughts

Learning how to backup MySQL database daily automatically is less about memorizing commands and more about building a small, repeatable pipeline: a script that exports the data, a scheduler that fires the script, a retention rule that prunes old files, an offsite copy that survives a disaster, and a monthly restore test that proves the whole thing works. Set those five pieces up once and you can stop worrying about backups forever.

Your next step is straightforward. Copy the bash script from this guide, swap in your own database names and credentials, run it once by hand, then drop the one-line cron entry at the top of your crontab. Schedule the monthly restore test on your calendar tonight so it does not slip. Within an hour you will have the most reliable part of your whole stack in place.