Bash Script for MySQL Backup: How to Write One

A Bash script for MySQL backup automates the process of dumping one or more databases to a file using the mysqldump utility, typically compressing the output and optionally rotating old backups. You write a script that runs mysqldump with the right options, stores the dump in a dated file, and cleans up files older than a set number of days. The result is a repeatable, scheduled backup you can run manually or via cron.

Why Automate MySQL Backups with Bash

Running mysqldump by hand works for a one-off dump, but databases change constantly. A scheduled, scripted backup ensures you have a recent copy without relying on memory. Bash is the natural choice on Linux servers because it is already present, integrates with cron, and gives you full control over file naming, compression, and retention. A script also lets you log successes and failures, send alerts, and copy backups off-site—things a single command cannot do.

What a Basic Backup Script Does

At its core, the script does four things:

• Sets variables for database name, user, password, and backup directory.
• Runs mysqldump with options like --single-transaction for InnoDB consistency.
• Compresses the dump with gzip to save disk space.
• Deletes backups older than a defined number of days.

A minimal working example looks like this:

#!/bin/bash
DB_NAME="mydb"
DB_USER="backup_user"
DB_PASS="secret"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M-%S)

mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +7 -delete

This script creates a timestamped, compressed dump and removes any dump older than seven days. It is a solid starting point, but production scripts usually add error handling and logging.

Choosing the Right mysqldump Options

The options you pass to mysqldump determine whether the backup is consistent, portable, and safe to restore. Key choices:

• –single-transaction: For InnoDB tables, this gives a consistent snapshot without locking tables. It is the recommended default for most setups.
• –lock-tables=false: Avoids global table locks; used with --single-transaction to prevent disruption.
• –routines and –triggers: Include stored procedures, functions, and triggers. Without these, restores lose application logic.
• –events: Backs up scheduled events if your database uses them.
• –databases: Use this when dumping multiple databases, so the dump includes CREATE DATABASE statements.

For a typical InnoDB database, a good command is:

mysqldump --single-transaction --routines --triggers --events -u user -p pass dbname

If you use MyISAM tables, --single-transaction is ignored; you would need --lock-tables for consistency, which briefly blocks writes.

Handling Authentication and Security

Storing a plaintext password in a script is a security risk. The script file should be readable only by root or the backup user. Set permissions with chmod 700 or chmod 600. Better still, use a MySQL option file (~/.my.cnf) with the password, and ensure that file is also protected. For example:

[mysqldump]
user=backup_user
password=secret

Then in the script, just call mysqldump --defaults-extra-file=/path/to/.my.cnf. This keeps the password out of the script itself. Avoid using the MySQL root account; create a dedicated backup user with only SELECT, SHOW VIEW, and LOCK TABLES privileges.

Adding Logging and Error Checking

A silent script that fails is worse than no script—you do not know until it is too late. Add a check on the exit status of mysqldump and write to a log file. A simple pattern:

if mysqldump ... | gzip > "$BACKUP_DIR/$FILE"; then
    echo "$(date): Backup succeeded" >> "$LOG"
else
    echo "$(date): Backup FAILED" >> "$LOG"
    exit 1
fi

You can also send the failure to an email or a messaging webhook, but that requires additional configuration. At minimum, log the outcome so you can review it.

Scheduling with Cron

Once the script works, schedule it with cron. Edit the crontab with crontab -e and add a line like:

0 2 * * * /usr/local/bin/mysql_backup.sh

This runs the script every day at 2:00 AM. Make sure the script is executable (chmod +x) and that the cron environment has the correct PATH. Cron uses a minimal PATH, so either use absolute paths to mysqldump and gzip or set PATH at the top of the script.

Common Pitfalls and How to Avoid Them

• Password on command line: Visible in process lists. Use an option file.
• No error handling: A failed dump looks like a success. Always check the exit code.
• Disk full: If the backup directory fills up, the dump fails. Monitor disk space or set a retention policy.
• Inconsistent backups: Forgetting --single-transaction on InnoDB can produce a dump with mixed data from different points in time.
• Restoring without testing: A backup you never restore is not a backup. Test restores regularly.

Alternatives to a Custom Bash Script

Bash is not the only option. Tools like automysqlbackup, xtrabackup (for physical backups), or database management panels (phpMyAdmin, Adminer) offer pre-built solutions. However, a custom script gives you full control and no extra dependencies. For very large databases, mysqldump may be too slow; xtrabackup performs physical file copies and is faster, but it is more complex to set up.

Frequently Asked Questions

How do I restore a backup made by this script?
Decompress the file with gunzip, then pipe it into mysql: gunzip < backup.sql.gz | mysql -u user -p dbname. If the dump was made with --databases, you can omit the database name on the command line.

Can I back up all databases at once?
Yes, use mysqldump --all-databases instead of specifying a single database. This includes the system tables, which is useful for full-server recovery.

Why is my backup file empty?
Usually because mysqldump failed (wrong credentials, missing database) but the error was not captured. Check the log and run the command manually to see the error message.

How often should I run backups?
It depends on how much data loss you can tolerate. Daily is common for small to medium databases. For critical data, consider hourly or use binary log replication for point-in-time recovery.

Is it safe to store backups on the same server?
It is convenient, but if the server fails (disk crash, ransomware), you lose everything. Copy backups to another machine or cloud storage regularly.

Do I need to stop MySQL to back it up?
No, mysqldump with --single-transaction creates a consistent backup without stopping the server. For MyISAM tables, you may need to lock tables briefly, but you do not need to stop MySQL.

Related

• Best Hi-Res Music Players: Top Picks & Buying Guide
• On-Call Runbook for AI Agents: Build One
• Send Photos Echo Show: How to Do It