TK
Docs
Top Tags

Backup MySQL Databases on Linux using Cron

A guide on how to set up automated backups for MySQL databases on a Linux server using cron jobs.

MySQLBackupLinuxCronbashcron

Overview

This guide demonstrates how to implement automated MySQL database backups on Linux systems using cron jobs and bash scripting. The solution provides:

  • Automated scheduling - Unattended backups at specified intervals
  • Compression - Reduced storage footprint using gzip
  • Retention policy - Automatic cleanup of old backups
  • Secure authentication - Credentials stored in a protected MySQL options file
  • Transaction safety - Consistent backups using --single-transaction

Why Automated Backups Matter

Database backups are critical for:

  • Disaster recovery - Protection against hardware failures, data corruption
  • Human error mitigation - Recovery from accidental deletions or modifications
  • Compliance requirements - Many regulations mandate regular backup procedures
  • Development workflows - Safe testing environments with production data snapshots

Create bash script for backup

The backup script iterates through all user databases (excluding system databases) and creates compressed SQL dumps. Each backup is timestamped for easy identification and recovery.

bash
1#!/bin/bash
2

3BACKUP_DIR="/root/mysql-backup"
4DATE=$(date +"%Y-%m-%d_%H-%M")
5

6# Get list of databases (skip system DBs)
7DATABASES=$(mysql --defaults-file=/root/.my.cnf -e "SHOW DATABASES;" | grep -Ev "Database|information_schema|performance_schema|mysql|sys")
8

9# Create backup directory if missing
10mkdir -p "$BACKUP_DIR"
11

12for DB in $DATABASES; do
13    FILE="$BACKUP_DIR/${DB}-${DATE}.sql"
14

15    echo "Backing up $DB ..."
16

17    mysqldump --defaults-file=/root/.my.cnf \
18        --single-transaction \
19        --quick \
20        --routines \
21        "$DB" > "$FILE"
22

23    gzip "$FILE"
24done
25

26# Optional: remove backups older than 7 days
27find "$BACKUP_DIR" -type f -name "*.gz" -mtime +7 -delete

Script Breakdown

Key mysqldump Options Explained:

  • --defaults-file=/root/.my.cnf - Reads MySQL credentials from a secure configuration file
  • --single-transaction - Creates a consistent snapshot without locking tables (InnoDB only)
  • --quick - Retrieves rows one at a time instead of buffering entire tables in memory
  • --routines - Includes stored procedures and functions in the dump

Why Skip System Databases:

The script excludes:

  • information_schema - Virtual database containing metadata (read-only)
  • performance_schema - Performance monitoring data (transient)
  • mysql - System database (requires special handling)
  • sys - System views and procedures (can be regenerated)

Alternative: Backup with Additional Options

For advanced scenarios, you might want to include triggers, events, and generate different formats:

bash
1#!/bin/bash
2

3BACKUP_DIR="/root/mysql-backup"
4DATE=$(date +"%Y-%m-%d_%H-%M")
5DATABASES=$(mysql --defaults-file=/root/.my.cnf -e "SHOW DATABASES;" | grep -Ev "Database|information_schema|performance_schema|mysql|sys")
6

7mkdir -p "$BACKUP_DIR"
8

9for DB in $DATABASES; do
10    FILE="$BACKUP_DIR/${DB}-${DATE}.sql"
11    
12    echo "Backing up $DB with full options..."
13    
14    mysqldump --defaults-file=/root/.my.cnf \
15        --single-transaction \
16        --quick \
17        --routines \
18        --triggers \
19        --events \
20        --hex-blob \
21        --add-drop-database \
22        --add-drop-table \
23        "$DB" > "$FILE"
24    
25    # Create both compressed and checksum
26    gzip -c "$FILE" > "${FILE}.gz"
27    sha256sum "${FILE}.gz" > "${FILE}.gz.sha256"
28    rm "$FILE"  # Remove uncompressed file
29done
30

31find "$BACKUP_DIR" -type f -name "*.gz" -mtime +7 -delete
32find "$BACKUP_DIR" -type f -name "*.sha256" -mtime +7 -delete

Additional options explained:

  • --triggers - Includes table triggers
  • --events - Includes scheduled events
  • --hex-blob - Dumps binary columns in hex format (more reliable)
  • --add-drop-database - Adds DROP DATABASE IF EXISTS statements
  • --add-drop-table - Adds DROP TABLE IF EXISTS statements

Logging and Error Handling

Enhanced version with logging:

bash
1#!/bin/bash
2

3BACKUP_DIR="/root/mysql-backup"
4LOG_FILE="/var/log/mysql-backup.log"
5DATE=$(date +"%Y-%m-%d_%H-%M")
6TIMESTAMP=$(date +"%Y-%m-%d %H:%M:%S")
7

8# Redirect stdout and stderr to log file
9exec 1>>"$LOG_FILE"
10exec 2>&1
11

12echo "[$TIMESTAMP] Starting MySQL backup process"
13

14DATABASES=$(mysql --defaults-file=/root/.my.cnf -e "SHOW DATABASES;" 2>/dev/null | grep -Ev "Database|information_schema|performance_schema|mysql|sys")
15

16if [ -z "$DATABASES" ]; then
17    echo "[$TIMESTAMP] ERROR: No databases found or MySQL connection failed"
18    exit 1
19fi
20

21mkdir -p "$BACKUP_DIR"
22

23SUCCESS_COUNT=0
24FAIL_COUNT=0
25

26for DB in $DATABASES; do
27    FILE="$BACKUP_DIR/${DB}-${DATE}.sql"
28    
29    echo "[$TIMESTAMP] Backing up database: $DB"
30    
31    if mysqldump --defaults-file=/root/.my.cnf \
32        --single-transaction \
33        --quick \
34        --routines \
35        "$DB" > "$FILE" 2>/dev/null; then
36        
37        gzip "$FILE"
38        SIZE=$(du -h "${FILE}.gz" | cut -f1)
39        echo "[$TIMESTAMP] SUCCESS: $DB backed up successfully (Size: $SIZE)"
40        ((SUCCESS_COUNT++))
41    else
42        echo "[$TIMESTAMP] ERROR: Failed to backup $DB"
43        ((FAIL_COUNT++))
44        rm -f "$FILE"  # Clean up partial backup
45    fi
46done
47

48# Cleanup old backups
49OLD_COUNT=$(find "$BACKUP_DIR" -type f -name "*.gz" -mtime +7 | wc -l)
50find "$BACKUP_DIR" -type f -name "*.gz" -mtime +7 -delete
51

52echo "[$TIMESTAMP] Backup summary: $SUCCESS_COUNT succeeded, $FAIL_COUNT failed, $OLD_COUNT old backups removed"
53echo "[$TIMESTAMP] MySQL backup process completed"
54echo "----------------------------------------"

Create MySQL credentials file

bash
1cat <<EOF > /root/.my.cnf
2[client]
3user=your_mysql_username
4password=your_mysql_password
5EOF
6chmod 600 /root/.my.cnf

Security Considerations

The .my.cnf file stores sensitive credentials. Important security practices:

  1. File permissions - chmod 600 ensures only the owner can read/write
  2. Root ownership - Store in /root/ which is only accessible to root
  3. Never commit to version control - Add .my.cnf to .gitignore
  4. Dedicated backup user - Create a MySQL user with only necessary privileges

Creating a Dedicated Backup User

For enhanced security, create a MySQL user specifically for backups with minimal privileges:

sql
1-- Connect to MySQL as root
2mysql -u root -p
3

4-- Create backup user
5CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password_here';
6

7-- Grant necessary privileges for backups
8GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
9

10-- For single-transaction backups (InnoDB)
11GRANT RELOAD ON *.* TO 'backup_user'@'localhost';
12

13-- Apply changes
14FLUSH PRIVILEGES;
15

16-- Verify privileges
17SHOW GRANTS FOR 'backup_user'@'localhost';

Then update your .my.cnf with these credentials:

bash
1cat <<EOF > /root/.my.cnf
2[client]
3user=backup_user
4password=strong_password_here
5host=localhost
6EOF
7chmod 600 /root/.my.cnf

Make the backup script executable

bash
1chmod +x /root/mysql-backup.sh

This command makes the script executable by the owner (root). Verify with:

bash
1ls -lh /root/mysql-backup.sh
2# Should show: -rwxr--r-- (executable bit set)

Test the backup script manually before scheduling:

bash
1# Run the script
2/root/mysql-backup.sh
3

4# Check if backups were created
5ls -lh /root/mysql-backup/
6

7# Verify a backup by checking its contents
8zcat /root/mysql-backup/your_database-*.sql.gz | head -n 20

Set up cron job for automated backups

bash
1crontab -e
20 20 * * * /root/mysql-backup.sh

Understanding Cron Syntax

The cron expression 0 20 * * * breaks down into five fields:

bash
10 20 * * * /root/mysql-backup.sh
2│ │  │ │ │
3│ │  │ │ └─── Day of week (0-6, Sunday=0)
4│ │  │ └───── Month (1-12)
5│ │  └─────── Day of month (1-31)
6│ └────────── Hour (0-23)
7└──────────── Minute (0-59)

Cron Field Reference:

FieldValuesSpecial Characters
Minute0-59* , - /
Hour0-23* , - /
Day of Month1-31* , - / ? L W
Month1-12 or JAN-DEC* , - /
Day of Week0-6 or SUN-SAT* , - / ? L #

Special Characters:

  • * = Any value (every minute, hour, day, etc.)
  • , = List of values (e.g., 1,15,30)
  • - = Range of values (e.g., 1-5 for Mon-Fri)
  • / = Step values (e.g., */5 for every 5 units)

This example runs the backup daily at 8:00 PM (20:00).

Common Cron Schedule Examples

bash
1# Every day at 2:30 AM
230 2 * * * /root/mysql-backup.sh
3

4# Every 6 hours
50 */6 * * * /root/mysql-backup.sh
6

7# Every Sunday at midnight
80 0 * * 0 /root/mysql-backup.sh
9

10# Every weekday at 11 PM
110 23 * * 1-5 /root/mysql-backup.sh
12

13# First day of every month at 1 AM
140 1 1 * * /root/mysql-backup.sh
15

16# Every 12 hours (midnight and noon)
170 0,12 * * * /root/mysql-backup.sh

Verify Cron Job Setup

bash
1# List current crontab
2crontab -l
3

4# Check cron service status
5systemctl status cron    # Debian/Ubuntu
6systemctl status crond   # RHEL/CentOS
7

8# View cron logs
9grep CRON /var/log/syslog    # Debian/Ubuntu
10grep CRON /var/log/cron      # RHEL/CentOS

Cron Best Practices

  1. Use absolute paths - Cron runs with limited PATH
  2. Add email notifications - Set [email protected] at top of crontab
  3. Redirect output - Capture stdout/stderr for debugging:
bash
1# Crontab with email on errors only
2[email protected]
3SHELL=/bin/bash
4PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
5

60 20 * * * /root/mysql-backup.sh >> /var/log/mysql-backup-cron.log 2>&1

Download backups

bash
10 20 * * * rsync -avz -e ssh root@IP-HOST:/root/mysql-backup /mnt/c/backup2025/sql

Remote Backup Transfer

Rsync is an efficient tool for transferring backups to a remote location for off-site storage.

Rsync Options Explained:

  • -a (archive mode) - Preserves permissions, timestamps, symbolic links
  • -v (verbose) - Shows detailed progress
  • -z (compress) - Compresses data during transfer (already gzipped files won't compress much more)
  • -e ssh - Uses SSH for secure transfer
  • --delete - Remove files in destination that no longer exist in source (use with caution)

Setting Up SSH Key Authentication

To avoid password prompts in cron jobs, use SSH key authentication:

bash
1# On the local machine (where cron downloads backups)
2ssh-keygen -t ed25519 -C "mysql-backup-key"
3

4# Copy public key to remote server
5ssh-copy-id -i ~/.ssh/id_ed25519.pub root@IP-HOST
6

7# Test passwordless SSH
8ssh root@IP-HOST "echo SSH connection successful"
9

10# Add to crontab for automated downloads
11crontab -e

Advanced Rsync Examples

Incremental backup with bandwidth limit:

bash
1# Limit bandwidth to 5MB/s
2rsync -avz --bwlimit=5000 -e ssh root@IP-HOST:/root/mysql-backup/ /mnt/c/backup2025/sql/

With deletion of old files and logging:

bash
1rsync -avz --delete -e ssh \
2  --log-file=/var/log/rsync-mysql-backup.log \
3  root@IP-HOST:/root/mysql-backup/ \
4  /mnt/c/backup2025/sql/

Copy only recent backups (last 24 hours):

bash
1# On remote server, sync only recent files
2ssh root@IP-HOST "find /root/mysql-backup -type f -name '*.gz' -mtime -1" | \
3  while read file; do
4    rsync -avz -e ssh "root@IP-HOST:$file" /mnt/c/backup2025/sql/
5  done

Alternative: SCP for Simple Transfers

For simple one-time transfers:

bash
1# Copy all .gz files from today
2scp root@IP-HOST:/root/mysql-backup/*$(date +%Y-%m-%d)*.sql.gz /mnt/c/backup2025/sql/
3

4# Copy specific database backup
5scp root@IP-HOST:/root/mysql-backup/mydb-2026-01-14*.sql.gz ./

Restoring from Backups

Restore a Single Database

bash
1# Decompress and restore
2gunzip < /root/mysql-backup/database_name-2026-01-14_20-00.sql.gz | \
3  mysql --defaults-file=/root/.my.cnf database_name
4

5# Or in two steps
6gunzip /root/mysql-backup/database_name-2026-01-14_20-00.sql.gz
7mysql --defaults-file=/root/.my.cnf database_name < database_name-2026-01-14_20-00.sql

Restore All Databases

bash
1for backup in /root/mysql-backup/*.sql.gz; do
2    DB=$(basename "$backup" | cut -d'-' -f1)
3    echo "Restoring $DB..."
4    gunzip < "$backup" | mysql --defaults-file=/root/.my.cnf "$DB"
5done

Verify Database After Restore

bash
1# Check tables in database
2mysql --defaults-file=/root/.my.cnf -e "USE database_name; SHOW TABLES;"
3

4# Check table row counts
5mysql --defaults-file=/root/.my.cnf database_name -e "
6  SELECT 
7    TABLE_NAME, 
8    TABLE_ROWS 
9  FROM information_schema.TABLES 
10  WHERE TABLE_SCHEMA = 'database_name';"
11

12# Check database size
13mysql --defaults-file=/root/.my.cnf -e "
14  SELECT 
15    table_schema AS 'Database',
16    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
17  FROM information_schema.tables
18  WHERE table_schema = 'database_name'
19  GROUP BY table_schema;"

Monitoring and Maintenance

Check Backup Disk Usage

bash
1# Check backup directory size
2du -sh /root/mysql-backup
3

4# List backups sorted by size
5du -h /root/mysql-backup/*.gz | sort -h
6

7# Show oldest and newest backups
8ls -lt /root/mysql-backup/*.gz | tail -n 5  # oldest
9ls -lt /root/mysql-backup/*.gz | head -n 5  # newest

Automated Monitoring Script

Create a monitoring script to alert when backups fail:

bash
1#!/bin/bash
2# /root/check-mysql-backup.sh
3

4BACKUP_DIR="/root/mysql-backup"
5ALERT_EMAIL="[email protected]"
6HOURS_THRESHOLD=25  # Alert if no backup in last 25 hours
7

8# Find most recent backup
9LATEST_BACKUP=$(find "$BACKUP_DIR" -type f -name "*.gz" -printf '%T@ %p\n' | sort -n | tail -1 | cut -d' ' -f2-)
10

11if [ -z "$LATEST_BACKUP" ]; then
12    echo "ERROR: No backups found in $BACKUP_DIR" | mail -s "MySQL Backup Alert: No backups found" "$ALERT_EMAIL"
13    exit 1
14fi
15

16# Check age of latest backup
17LATEST_TIME=$(stat -c %Y "$LATEST_BACKUP")
18CURRENT_TIME=$(date +%s)
19AGE_HOURS=$(( (CURRENT_TIME - LATEST_TIME) / 3600 ))
20

21if [ "$AGE_HOURS" -gt "$HOURS_THRESHOLD" ]; then
22    echo "WARNING: Latest backup is $AGE_HOURS hours old (threshold: $HOURS_THRESHOLD hours)" | \
23      mail -s "MySQL Backup Alert: Backup too old" "$ALERT_EMAIL"
24    exit 1
25fi
26

27echo "OK: Latest backup is $AGE_HOURS hours old"

Schedule the monitoring script:

bash
1# Run every 6 hours
20 */6 * * * /root/check-mysql-backup.sh

Troubleshooting

Common Issues and Solutions

Issue: mysqldump fails with "Access denied"

bash
1# Check MySQL credentials
2mysql --defaults-file=/root/.my.cnf -e "SELECT 1"
3

4# Verify user privileges
5mysql -u root -p -e "SHOW GRANTS FOR 'backup_user'@'localhost';"

Issue: Backups are too large

bash
1# Check which databases are largest
2mysql --defaults-file=/root/.my.cnf -e "
3  SELECT 
4    table_schema,
5    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
6  FROM information_schema.tables
7  GROUP BY table_schema
8  ORDER BY SUM(data_length + index_length) DESC;"
9

10# Consider using compression level for gzip (1=fast, 9=best compression)
11gzip -9 "$FILE"  # Maximum compression

Issue: Cron job doesn't run

bash
1# Check if cron is running
2systemctl status cron
3

4# Check cron logs for errors
5tail -f /var/log/syslog | grep CRON
6

7# Test script directly
8sudo -u root /root/mysql-backup.sh

Issue: Running out of disk space

bash
1# Adjust retention period (reduce from 7 to 3 days)
2find "$BACKUP_DIR" -type f -name "*.gz" -mtime +3 -delete
3

4# Or keep only last N backups per database
5for db in $(mysql --defaults-file=/root/.my.cnf -e "SHOW DATABASES;" | grep -Ev "Database|information_schema|performance_schema|mysql|sys"); do
6  ls -t /root/mysql-backup/${db}-*.gz | tail -n +6 | xargs rm -f
7done

Best Practices Summary

  1. 3-2-1 Backup Rule - 3 copies, 2 different media types, 1 off-site
  2. Test restores regularly - Verify backups can actually be restored
  3. Monitor backup status - Automated alerts for failures
  4. Secure credentials - Use dedicated users with minimal privileges
  5. Document procedures - Keep restoration steps accessible
  6. Consider replication - Use MySQL replication for high availability
  7. Encrypt sensitive backups - Use GPG for additional security layer

Optional: Encrypt Backups with GPG

bash
1# Generate GPG key (one-time)
2gpg --gen-key
3

4# Encrypt backup
5gzip "$FILE"
6gpg --encrypt --recipient [email protected] "${FILE}.gz"
7rm "${FILE}.gz"  # Keep only encrypted version
8

9# Decrypt when restoring
10gpg --decrypt database-2026-01-14_20-00.sql.gz.gpg | gunzip | mysql database_name