mysql-backup

A flexible MySQL backup script with automatic rotation and multiple backup modes.

Features

• Multiple backup modes: Individual databases or all-databases in a single file
• Automatic backup rotation: Configurable number of backup copies to retain
• Selective backups: Backup specific databases via command-line arguments
• System database filtering: Automatically skips system databases (information_schema, performance_schema, mysql)
• Environment variable support: Easy configuration for containerized environments
• Directory validation: Safety checks before executing backups
• Compression: All backups are gzip compressed to save space

Installation

1. Place the script in /usr/local/sbin (or your preferred location):

   sudo cp mysql-backup.sh /usr/local/sbin/
   sudo chmod 700 /usr/local/sbin/mysql-backup.sh

2. Create a MySQL user with appropriate permissions:

   CREATE USER 'backup'@'localhost' IDENTIFIED BY 'secure_password';
   GRANT SELECT, RELOAD, LOCK TABLES ON *.* TO 'backup'@'localhost';
   FLUSH PRIVILEGES;

3. Create the backup directory:

   sudo mkdir -p /var/myexport
   sudo chown mysql:mysql /var/myexport  # Or appropriate user

4. (Optional) Schedule with cron for automatic backups:

   # Edit crontab
   sudo crontab -e
   
   # Run backup 3 times a day (at 2am, 10am, and 6pm)
   0 2,10,18 * * * /usr/local/sbin/mysql-backup.sh

Configuration

Edit the configuration section at the top of the script:

USER="${MYSQL_USER:-root}"
PASS="${MYSQL_ROOT_PASSWORD:-}"
COPIES=2                              # Number of backup copies to keep
BASE="${BACKUP_PATH:-/var/myexport}"  # Backup directory

Environment Variables

• MYSQL_USER: MySQL username (default: root)
• MYSQL_ROOT_PASSWORD: MySQL password (optional if using socket authentication)
• BACKUP_PATH: Backup directory location (default: /var/myexport)

Docker/Container Usage

When running in a container, set environment variables:

docker run -e MYSQL_ROOT_PASSWORD=mypassword \
           -e BACKUP_PATH=/mnt \
           -v /host/backup:/mnt \
           mysql-container /path/to/mysql-backup.sh --all-databases

Usage

Backup all databases individually

./mysql-backup.sh

Creates separate compressed files for each database: dbname.sql.0.gz, dbname.sql.1.gz, etc.

Backup all databases in a single file

./mysql-backup.sh --all-databases

Creates a single file containing all databases: databases.sql.0.gz

Backup specific databases

./mysql-backup.sh myapp_db analytics_db

Only backs up the specified databases.

How Backup Rotation Works

The script maintains multiple copies of each backup using a rotation system:

• database.sql.0.gz - Most recent backup
• database.sql.1.gz - Previous backup
• database.sql.2.gz - Older backup (if COPIES=3)

Each time the script runs, it rotates existing backups:

1. Deletes the oldest backup (beyond COPIES limit)
2. Renames existing backups (0 → 1, 1 → 2, etc.)
3. Creates new backup as .0.gz

Examples

Basic setup with password

export MYSQL_USER=backup
export MYSQL_ROOT_PASSWORD=secure_password
export BACKUP_PATH=/var/myexport
./mysql-backup.sh

Restore a database

# Decompress and restore
gunzip < /var/myexport/mydb.sql.0.gz | mysql -u root -p mydb

# Or restore all databases
gunzip < /var/myexport/databases.sql.0.gz | mysql -u root -p

Check backup size

ls -lh /var/myexport/*.gz

Troubleshooting

Error: Backup directory not available

• Ensure the directory exists: mkdir -p /var/myexport
• Check permissions: The user running the script needs write access

Authentication errors

• Verify MySQL credentials
• For socket authentication, ensure the script runs as the appropriate user
• Check if password contains special characters (may need quoting)

No databases found

• Verify MySQL is running: systemctl status mysql
• Check user has appropriate permissions: SHOW GRANTS FOR 'backup'@'localhost';

Legacy Scripts

This repository previously contained three separate scripts that have been consolidated:

• container.sh - Container-focused all-databases backup
• extended.sh - Feature-rich individual database backups
• original.sh - Basic backup functionality

These have been combined into the unified mysql-backup.sh script.

License

Free to use and modify.