Regular database backups are non-negotiable for any production system. mysqldump is the standard MySQL/MariaDB logical backup tool — it exports database contents as SQL statements (CREATE TABLE, INSERT) that can be restored on any compatible server. While mysqldump is not the fastest solution for very large databases (where Percona XtraBackup or MySQL Enterprise Backup are preferred), it is universally available, requires no additional software, produces human-readable output, and works perfectly for databases up to tens of gigabytes. This guide covers backing up individual databases, all databases, specific tables, and using options for consistent backups (--single-transaction), including stored routines and triggers, restoring from backups, and automating daily backups with a cron job and retention policy.

Prerequisites

  • MySQL or MariaDB installed on RHEL 9
  • A dedicated backup user with appropriate privileges

Step 1 — Create a Dedicated Backup User

mysql -u root -p <<'SQL'
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPassword123!';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, RELOAD, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
SQL

Step 2 — Back Up a Single Database

# --single-transaction: consistent InnoDB backup without locking tables
# --routines: includes stored procedures and functions
# --triggers: includes triggers
# --events: includes scheduled events
mysqldump 
    -u backup_user -p 
    --single-transaction 
    --routines 
    --triggers 
    --events 
    myapp > /var/backups/myapp-$(date +%Y%m%d-%H%M%S).sql

# Compress immediately
mysqldump -u backup_user -p --single-transaction --routines myapp | gzip > /var/backups/myapp-$(date +%Y%m%d).sql.gz

Step 3 — Back Up All Databases

# --all-databases: dumps all databases
# --master-data=2: includes SHOW MASTER STATUS as a comment (for replication setup)
mysqldump 
    -u backup_user -p 
    --all-databases 
    --single-transaction 
    --routines 
    --triggers 
    --events 
    --master-data=2 
    | gzip > /var/backups/all-databases-$(date +%Y%m%d).sql.gz

Step 4 — Back Up Specific Tables

# Backup specific tables from a database
mysqldump -u backup_user -p myapp users orders products > /var/backups/myapp-tables.sql

Step 5 — Restore from Backup

# Restore an uncompressed dump
mysql -u root -p myapp < /var/backups/myapp-20241201.sql

# Restore a gzip-compressed dump
gunzip < /var/backups/myapp-20241201.sql.gz | mysql -u root -p myapp

# Restore all databases
gunzip < /var/backups/all-databases-20241201.sql.gz | mysql -u root -p

Step 6 — Automate Daily Backups with Retention

# /usr/local/bin/mysql-backup.sh
#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
MYSQL_USER="backup_user"
MYSQL_PASS="BackupPassword123!"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d)

mkdir -p "$BACKUP_DIR"

# Backup each database separately for targeted restores
mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW DATABASES;" --skip-column-names | 
grep -Ev "^(information_schema|performance_schema|sys|mysql)$" | 
while read db; do
    mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASS" --single-transaction --routines "$db" 
        | gzip > "$BACKUP_DIR/${db}-${DATE}.sql.gz"
    echo "Backed up: $db"
done

# Delete backups older than retention period
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "Backup complete: $(date)"
chmod +x /usr/local/bin/mysql-backup.sh
echo "0 2 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1" > /etc/cron.d/mysql-backup

Conclusion

Automated mysqldump backups on RHEL 9 protect against accidental data deletion, corruption, and hardware failure. Using --single-transaction for InnoDB tables ensures consistent snapshots without table locks, while a separate backup user with minimal privileges reduces security risk. Backing up each database to a separate file allows targeted restores without importing an entire multi-database dump.

Next steps: How to Use Percona XtraBackup for MySQL Hot Backups on RHEL 9, How to Configure MySQL Primary-Replica Replication on RHEL 9, and How to Back Up PostgreSQL with pg_dump on RHEL 9.