MySQL Backup Script

A Bash script that automates MySQL database backups across multiple servers using mysqldump. It reads server credentials from a JSON config file, backs up all non-system databases, organizes backups into daily and monthly directories, and enforces a configurable retention policy.

Requirements

• jq - JSON processor
• mysql-client - MySQL client tools (mysql and mysqldump)

Setup

1. Configure the Script

Copy the example config and fill in your server details:

1cp config.example.json config.json
1cp config.example.json config.json

Edit config.json with your servers:

 1{
 2    "backup_dir": "/backups/mysql",
 3    "retention_days": 7,
 4    "servers": [
 5        {
 6            "host": "db1.example.internal",
 7            "login_path": "db1-backup"
 8        }
 9    ]
10}
 1{
 2    "backup_dir": "/backups/mysql",
 3    "retention_days": 7,
 4    "servers": [
 5        {
 6            "host": "db1.example.internal",
 7            "login_path": "db1-backup"
 8        }
 9    ]
10}

Credentials are not stored in config.json. They live in an encrypted ~/.mylogin.cnf file managed by mysql_config_editor (see step 2).

login_path is the name of the credential entry in .mylogin.cnf. If omitted, the script falls back to using host as the login-path name.

2. The script

create mysql_backup.sh

  1#!/bin/bash
  2 
  3set -e
  4 
  5# Check if jq is installed
  6if ! command -v jq &> /dev/null; then
  7    echo "jq is required but not installed. Please install jq to use this script."
  8    exit 1
  9fi
 10 
 11# Check if mysqldump is installed
 12if ! command -v mysqldump &> /dev/null; then
 13    echo "mysqldump is required but not installed. Please install mysql-client to use this script."
 14    exit 1
 15fi
 16 
 17# Get the directory of the script
 18SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
 19 
 20# Set the config file path relative to the script directory
 21CONFIG_FILE="${SCRIPT_DIR}/config.json"
 22 
 23# Function to read configuration and check backup directory
 24read_config_and_check_dir() {
 25    if [ ! -f "$CONFIG_FILE" ]; then
 26        echo "Configuration file not found: $CONFIG_FILE"
 27        exit 1
 28    fi
 29 
 30    BACKUP_DIR=$(jq -r '.backup_dir' "$CONFIG_FILE")
 31    RETENTION_DAYS=$(jq -r '.retention_days' "$CONFIG_FILE")
 32 
 33    # Check if backup directory exists, create if it doesn't
 34    if [ ! -d "$BACKUP_DIR" ]; then
 35        echo "Backup directory does not exist. Creating: $BACKUP_DIR"
 36        mkdir -p "$BACKUP_DIR"
 37        if [ $? -ne 0 ]; then
 38            echo "Failed to create backup directory. Please check permissions and path."
 39            exit 1
 40        fi
 41    fi
 42 
 43    echo "Using backup directory: $BACKUP_DIR"
 44}
 45 
 46# Function to create backup
 47create_backup() {
 48    local HOST=$1
 49    local LOGIN_PATH=$2
 50    local DB=$3
 51    local BACKUP_PATH="$BACKUP_DIR/$HOST/$DB"
 52 
 53    mkdir -p "$BACKUP_PATH/daily"
 54    mkdir -p "$BACKUP_PATH/monthly"
 55 
 56    echo "Backing up database: $DB"
 57    mysqldump --login-path="$LOGIN_PATH" --single-transaction --routines --triggers "$DB" > "$BACKUP_PATH/daily/$DB-$DATE.sql"
 58 
 59    # Create monthly backup on the 1st of each month
 60    if [ "$(date +%d)" = "01" ]; then
 61        cp "$BACKUP_PATH/daily/$DB-$DATE.sql" "$BACKUP_PATH/monthly/$DB-$MONTH.sql"
 62    fi
 63}
 64 
 65# Function to remove old backups
 66cleanup_old_backups() {
 67    local HOST=$1
 68    local DB=$2
 69    local BACKUP_PATH="$BACKUP_DIR/$HOST/$DB/daily"
 70 
 71    find "$BACKUP_PATH" -name "*.sql" -type f -mtime +$RETENTION_DAYS -delete
 72}
 73 
 74# Function to list databases
 75list_databases() {
 76    local LOGIN_PATH=$1
 77 
 78    echo "Attempting to list databases using login-path $LOGIN_PATH" >&2
 79    mysql --login-path="$LOGIN_PATH" -N -e "SHOW DATABASES;" 2>/dev/null | grep -Ev "^(information_schema|performance_schema|mysql|sys)$"
 80}
 81 
 82# Main backup process
 83main() {
 84    read_config_and_check_dir
 85 
 86    DATE=$(date +"%Y-%m-%d")
 87    MONTH=$(date +"%Y-%m")
 88 
 89    echo "Starting backup process..."
 90 
 91    jq -c '.servers[]' "$CONFIG_FILE" | while read -r server; do
 92        HOST=$(echo $server | jq -r '.host')
 93        LOGIN_PATH=$(echo $server | jq -r '.login_path // .host')
 94 
 95        echo "Backing up server: $HOST (login-path: $LOGIN_PATH)"
 96 
 97        # Get list of databases
 98        echo "Listing databases..."
 99        DATABASES=$(list_databases "$LOGIN_PATH")
100 
101        if [ -z "$DATABASES" ]; then
102            echo "No databases found or unable to list databases on $HOST. Check your login-path and permissions."
103            continue
104        fi
105 
106        echo "Databases found:"
107        echo "$DATABASES"
108 
109        echo "$DATABASES" | while read -r DB; do
110            create_backup "$HOST" "$LOGIN_PATH" "$DB"
111            cleanup_old_backups "$HOST" "$DB"
112        done
113    done
114 
115    echo "Backup process completed."
116}
117 
118main
  1#!/bin/bash
  2 
  3set -e
  4 
  5# Check if jq is installed
  6if ! command -v jq &> /dev/null; then
  7    echo "jq is required but not installed. Please install jq to use this script."
  8    exit 1
  9fi
 10 
 11# Check if mysqldump is installed
 12if ! command -v mysqldump &> /dev/null; then
 13    echo "mysqldump is required but not installed. Please install mysql-client to use this script."
 14    exit 1
 15fi
 16 
 17# Get the directory of the script
 18SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
 19 
 20# Set the config file path relative to the script directory
 21CONFIG_FILE="${SCRIPT_DIR}/config.json"
 22 
 23# Function to read configuration and check backup directory
 24read_config_and_check_dir() {
 25    if [ ! -f "$CONFIG_FILE" ]; then
 26        echo "Configuration file not found: $CONFIG_FILE"
 27        exit 1
 28    fi
 29 
 30    BACKUP_DIR=$(jq -r '.backup_dir' "$CONFIG_FILE")
 31    RETENTION_DAYS=$(jq -r '.retention_days' "$CONFIG_FILE")
 32 
 33    # Check if backup directory exists, create if it doesn't
 34    if [ ! -d "$BACKUP_DIR" ]; then
 35        echo "Backup directory does not exist. Creating: $BACKUP_DIR"
 36        mkdir -p "$BACKUP_DIR"
 37        if [ $? -ne 0 ]; then
 38            echo "Failed to create backup directory. Please check permissions and path."
 39            exit 1
 40        fi
 41    fi
 42 
 43    echo "Using backup directory: $BACKUP_DIR"
 44}
 45 
 46# Function to create backup
 47create_backup() {
 48    local HOST=$1
 49    local LOGIN_PATH=$2
 50    local DB=$3
 51    local BACKUP_PATH="$BACKUP_DIR/$HOST/$DB"
 52 
 53    mkdir -p "$BACKUP_PATH/daily"
 54    mkdir -p "$BACKUP_PATH/monthly"
 55 
 56    echo "Backing up database: $DB"
 57    mysqldump --login-path="$LOGIN_PATH" --single-transaction --routines --triggers "$DB" > "$BACKUP_PATH/daily/$DB-$DATE.sql"
 58 
 59    # Create monthly backup on the 1st of each month
 60    if [ "$(date +%d)" = "01" ]; then
 61        cp "$BACKUP_PATH/daily/$DB-$DATE.sql" "$BACKUP_PATH/monthly/$DB-$MONTH.sql"
 62    fi
 63}
 64 
 65# Function to remove old backups
 66cleanup_old_backups() {
 67    local HOST=$1
 68    local DB=$2
 69    local BACKUP_PATH="$BACKUP_DIR/$HOST/$DB/daily"
 70 
 71    find "$BACKUP_PATH" -name "*.sql" -type f -mtime +$RETENTION_DAYS -delete
 72}
 73 
 74# Function to list databases
 75list_databases() {
 76    local LOGIN_PATH=$1
 77 
 78    echo "Attempting to list databases using login-path $LOGIN_PATH" >&2
 79    mysql --login-path="$LOGIN_PATH" -N -e "SHOW DATABASES;" 2>/dev/null | grep -Ev "^(information_schema|performance_schema|mysql|sys)$"
 80}
 81 
 82# Main backup process
 83main() {
 84    read_config_and_check_dir
 85 
 86    DATE=$(date +"%Y-%m-%d")
 87    MONTH=$(date +"%Y-%m")
 88 
 89    echo "Starting backup process..."
 90 
 91    jq -c '.servers[]' "$CONFIG_FILE" | while read -r server; do
 92        HOST=$(echo $server | jq -r '.host')
 93        LOGIN_PATH=$(echo $server | jq -r '.login_path // .host')
 94 
 95        echo "Backing up server: $HOST (login-path: $LOGIN_PATH)"
 96 
 97        # Get list of databases
 98        echo "Listing databases..."
 99        DATABASES=$(list_databases "$LOGIN_PATH")
100 
101        if [ -z "$DATABASES" ]; then
102            echo "No databases found or unable to list databases on $HOST. Check your login-path and permissions."
103            continue
104        fi
105 
106        echo "Databases found:"
107        echo "$DATABASES"
108 
109        echo "$DATABASES" | while read -r DB; do
110            create_backup "$HOST" "$LOGIN_PATH" "$DB"
111            cleanup_old_backups "$HOST" "$DB"
112        done
113    done
114 
115    echo "Backup process completed."
116}
117 
118main

3. Store Credentials with mysql_config_editor

Since the cron job runs as root, credentials must live in root's home (/root/.mylogin.cnf). For each server in config.json, create a matching login-path:

1sudo mysql_config_editor set --login-path=db1-backup --host=db1.example.internal --user=backups --password
1sudo mysql_config_editor set --login-path=db1-backup --host=db1.example.internal --user=backups --password

You'll be prompted for the password, which is then stored in /root/.mylogin.cnf (obfuscated, mode 0600).

Verify entries:

1sudo mysql_config_editor print --all
1sudo mysql_config_editor print --all

Remove an entry if needed:

1sudo mysql_config_editor remove --login-path=db1-backup
1sudo mysql_config_editor remove --login-path=db1-backup

Test a connection:

1sudo mysql --login-path=db1-backup -e "SHOW DATABASES;"
1sudo mysql --login-path=db1-backup -e "SHOW DATABASES;"

4. Create a MySQL Backup User on the Remote Server

Connect to MySQL on the database server:

1mysql -u root -p
1mysql -u root -p

Create the backup user. Replace 198.51.100.25 with the IP of the server running this script, or use % to allow connections from any host:

1CREATE USER 'backups'@'198.51.100.25' IDENTIFIED BY 'strong_password_here';
2 
3GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backups'@'198.51.100.25';
4 
5FLUSH PRIVILEGES;
1CREATE USER 'backups'@'198.51.100.25' IDENTIFIED BY 'strong_password_here';
2 
3GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backups'@'198.51.100.25';
4 
5FLUSH PRIVILEGES;

PROCESS must be granted globally (*.*) and is required to avoid tablespace errors during mysqldump.

Verify the user was created:

1SELECT user, host FROM mysql.user WHERE user = 'backups';
1SELECT user, host FROM mysql.user WHERE user = 'backups';

If you need to fix the host IP after creation:

1RENAME USER 'backups'@'wrong_host' TO 'backups'@'198.51.100.25';
2FLUSH PRIVILEGES;
1RENAME USER 'backups'@'wrong_host' TO 'backups'@'198.51.100.25';
2FLUSH PRIVILEGES;

5. Ensure MySQL Accepts Remote Connections

On the database server, check the bind address:

1sudo grep -E "bind.address|bind-address" /etc/mysql/mysql.conf.d/mysqld.cnf
1sudo grep -E "bind.address|bind-address" /etc/mysql/mysql.conf.d/mysqld.cnf

If it shows 127.0.0.1, change it to listen on the network:

1bind-address = 0.0.0.0
1bind-address = 0.0.0.0

Restart MySQL:

1sudo systemctl restart mysql
1sudo systemctl restart mysql

6. Open the Firewall

Allow the backup server to reach MySQL:

1sudo ufw allow from 198.51.100.25 to any port 3306
1sudo ufw allow from 198.51.100.25 to any port 3306

Avoid opening port 3306 to the world. Lock it to specific source IPs.

7. Make the Script Executable and Run

1chmod +x mysql_backup.sh
2sudo ./mysql_backup.sh
1chmod +x mysql_backup.sh
2sudo ./mysql_backup.sh

Must run as root — /root/.mylogin.cnf is only readable by root, and cron runs backups as root too.

Cron Job

Run backups daily at 2 AM:

1sudo crontab -e
1sudo crontab -e

10 2 * * * /path/to/mysqlbackup/mysql_backup.sh
10 2 * * * /path/to/mysqlbackup/mysql_backup.sh

Backup Structure

1backup_dir/
2└── hostname/
3    └── database_name/
4        ├── daily/
5        │   └── database_name-YYYY-MM-DD.sql
6        └── monthly/
7            └── database_name-YYYY-MM.sql
1backup_dir/
2└── hostname/
3    └── database_name/
4        ├── daily/
5        │   └── database_name-YYYY-MM-DD.sql
6        └── monthly/
7            └── database_name-YYYY-MM.sql

• Daily backups are created on every run.
• Monthly backups are created on the 1st of each month.
• Daily backups older than the configured retention_days are automatically deleted.

Excluded Databases

The following system databases are automatically excluded:

• information_schema
• performance_schema
• mysql
• sys

Required MySQL Privileges

Restoring a Backup

To restore a dump using a configured login-path:

1mysql --login-path=db1-backup < /path/to/backup.sql
1mysql --login-path=db1-backup < /path/to/backup.sql

Because the dump is created with --databases, it includes the CREATE DATABASE and USE statements needed to recreate and select the database during restore.

To restore with a different host or user:

1mysql -h db1.example.internal -u your-user -p < /path/to/backup.sql
1mysql -h db1.example.internal -u your-user -p < /path/to/backup.sql

Notes

A few practical details worth calling out:

• --single-transaction works best for InnoDB tables and avoids locking during most backups.
• This script writes plain .sql dumps, which keeps restores simple.
• If storage becomes a concern, piping the output through gzip is an easy next step.

It’s not fancy, but it’s easy to understand, easy to schedule, and easy to extend.