How do I run this script?

Configure ~/.my.cnf with credentials, save as mysql-backup.sh, run chmod +x mysql-backup.sh, then execute ./mysql-backup.sh.

Does this work on macOS?

Yes with MySQL or MariaDB installed via Homebrew. mysqldump and gzip are available after brew install mysql.

MySQL Database Backup Script

Q: How do I backup a MySQL database with bash?

Run mysqldump dbname | gzip > backup.sql.gz. Add a timestamp to the filename so each backup is unique.

Q: Should I compress MySQL backups?

Yes. gzip reduces a 500 MB dump to 50–100 MB. Always verify restores with mysql dbname < backup.sql after compressing.

The Script

Copy this into mysql-backup.sh, edit the configuration block, then make it executable.

bash

#!/bin/bash

CHECK="✓"
CROSS="✗"

# --- Configuration ---
DB_USER="root"
DB_PASS="your_password"          # Or use ~/.my.cnf for security
DB_NAME="your_database"          # Change to your DB name
BACKUP_DIR="/var/backups/mysql"
KEEP_DAYS=7                       # Delete backups older than this
DATE=$(date '+%Y-%m-%d_%H-%M-%S')
FILENAME="${DB_NAME}_${DATE}.sql.gz"

# --- Create backup directory if it doesn't exist ---
mkdir -p "$BACKUP_DIR"

# --- Run the backup ---
echo "Backing up database: $DB_NAME..."

if mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "${BACKUP_DIR}/${FILENAME}"; then
  echo "$CHECK Backup created: ${BACKUP_DIR}/${FILENAME}"
  echo "$CHECK Size: $(du -sh "${BACKUP_DIR}/${FILENAME}" | cut -f1)"
else
  echo "$CROSS Backup FAILED for $DB_NAME — check credentials and database name"
  exit 1
fi

# --- Delete old backups ---
echo "Removing backups older than $KEEP_DAYS days..."
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$KEEP_DAYS" -delete
echo "$CHECK Cleanup complete"

# --- Show current backups ---
echo "Current backups:"
ls -lh "$BACKUP_DIR"/*.sql.gz 2>/dev/null || echo "No backups found"

Never hardcode database passwords

Use ~/.my.cnf so the password never appears in process listings or script files. Create the file:

ini

[client]
user=root
password=your_password

Then chmod 600 ~/.my.cnf and drop the -u and -p flags from the mysqldump command — credentials are read automatically.

Step-by-Step Setup

Step 1 — Create the script file

bash

nano mysql-backup.sh

Paste the script above, then press Ctrl+X → Y → Enter to save.

Step 2 — Configure your credentials securely

Never hardcode the database password in the script file — it appears in process listings when the script runs. Use ~/.my.cnf instead:

bash

nano ~/.my.cnf

Paste this (replace with your actual credentials):

ini

[client]
user=root
password=your_database_password

Save, then lock down the file so only your user can read it:

bash

chmod 600 ~/.my.cnf

Now remove the -u"$DB_USER" -p"$DB_PASS" flags from the mysqldump line in the script — MySQL reads credentials from ~/.my.cnf automatically.

Step 3 — Set your database and backup directory

Edit these four variables at the top of the script:

Variable	Example	What it means
DB_NAME	myapp_production	The MySQL database to back up
BACKUP_DIR	/var/backups/mysql	Where backup files are stored
KEEP_DAYS	7	Delete backups older than this many days
DATE	(auto)	Timestamp — never change this

Step 4 — Make it executable and run a test

bash

chmod +x mysql-backup.sh
./mysql-backup.sh

A successful run shows:

text

Backing up database: myapp_production...
✓ Backup created: /var/backups/mysql/myapp_production_2026-06-03_02-00-00.sql.gz
✓ Size: 12M
Removing backups older than 7 days...
✓ Cleanup complete
Current backups:
-rw-r--r-- 1 user user 12M Jun  3 02:00 myapp_production_2026-06-03_02-00-00.sql.gz

Step 5 — Verify the backup is restorable

A backup you have never tested is a backup you cannot trust. Test a restore to a scratch database:

bash

# Create a test database
mysql -e "CREATE DATABASE restore_test;"

# Decompress and restore
gunzip < /var/backups/mysql/myapp_production_2026-06-03_02-00-00.sql.gz \
  | mysql restore_test

# Verify it has your tables
mysql restore_test -e "SHOW TABLES;"

# Clean up
mysql -e "DROP DATABASE restore_test;"

Do this once after your first backup. Do it again after any major schema change.

How It Works

-u"$DB_USER" and -p"$DB_PASS" — These pass the MySQL username and password to mysqldump. Putting the flags adjacent to the quoted variables (-u"root") avoids a space between -p and the password, which mysqldump expects when you supply the password on the command line. Prefer ~/.my.cnf in production so the password never appears in process listings or the script file.

Gzip pipe — mysqldump writes SQL to standard output. The pipe sends that stream into gzip, which compresses it before the shell redirects the result into ${BACKUP_DIR}/${FILENAME}. You get a single .sql.gz file without an intermediate uncompressed dump on disk.

find … -mtime +"$KEEP_DAYS" -delete — -mtime +N matches files last modified more than N days ago. Combined with -name '*.sql.gz', only compressed dumps in your backup directory are removed. This keeps disk usage bounded while still honoring roughly “last seven daily backups” when you run the script once per day.

mkdir -p "$BACKUP_DIR" — Creates the backup directory (and any missing parents) without error if it already exists, so the script can run on a fresh path or under cron without manual setup.

Schedule It with Cron

Open your crontab and point it at the full path of the script:

bash

crontab -e

bash

# Every night at 2:00
0 2 * * * /home/user/mysql-backup.sh

Cron PATH

If the job fails silently, set an explicit PATH at the top of the crontab or use absolute paths to mysqldump and gzip. Cron runs with a minimal environment.

Variations

Use case	What to change
Backup all databases	Run mysqldump --all-databases (and adjust FILENAME if you want a fixed name like all-databases_DATE.sql.gz). Omit the single-database argument.
Backup to a remote server	After a successful local dump, copy the file with scp or rsync to another host or object storage. Keeps data off the machine you are backing up.
MariaDB	The flow is the same. Use the MariaDB client tools: often mariadb-dump instead of mysqldump, and mariadb for restores — options align closely with MySQL.

Frequently Asked Questions

How do I backup a MySQL database with bash?

Use mysqldump: mysqldump -u username -p dbname > backup.sql. In a script, pass the password with -p'password' or use a .my.cnf credentials file.

How do I automate MySQL backups with cron?

Write a mysqldump script, chmod +x it, then add to crontab: 0 2 * * * /home/user/mysql-backup.sh to run every night at 2am.

How do I keep only the last 7 MySQL backups?

Use find to delete old files: find /backup/ -name '*.sql.gz' -mtime +7 -delete. This removes any backup files older than 7 days.

Should I compress MySQL backups?

Yes. Pipe mysqldump output through gzip: mysqldump ... | gzip > backup.sql.gz. A typical database compresses 80–90%, saving significant disk space.

How do I backup all MySQL databases at once?

Use mysqldump --all-databases instead of specifying one database name. This creates a single dump of all databases.

MySQL Database Backup Script

The Script

Step-by-Step Setup

Step 1 — Create the script file

Step 2 — Configure your credentials securely

Step 3 — Set your database and backup directory

Step 4 — Make it executable and run a test

Step 5 — Verify the backup is restorable

How It Works

Schedule It with Cron

Variations

Frequently Asked Questions

How do I backup a MySQL database with bash?

How do I automate MySQL backups with cron?

How do I keep only the last 7 MySQL backups?

Should I compress MySQL backups?

How do I backup all MySQL databases at once?

Related Snippets

Frequently Asked Questions