How to Back Up and Restore MySQL Databases with mysqldump on RHEL 7

January 3, 2026
Linux
Comment off

How to Back Up and Restore MySQL Databases with mysqldump on RHEL 7

Regular database backups are the single most important operational task for any MySQL administrator. mysqldump is the built-in logical backup utility that ships with every MySQL and MariaDB installation — it produces plain-text SQL files that can be compressed, transferred off-site, and restored with a single command. While it does not offer the raw speed of physical backup tools for very large databases, its simplicity, universality, and portability make it the right choice for most workloads on RHEL 7. This tutorial covers every aspect of using mysqldump effectively: backing up single databases, all databases, compressing output, restoring, scheduling automated backups with cron, and briefly introduces mydumper as a multi-threaded alternative for larger datasets.

Prerequisites

RHEL 7 server with MySQL or MariaDB installed and running
A MySQL user account with sufficient privileges (at minimum SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT on the target databases)
Enough disk space for the dump files (check with df -h)
Root or sudo access for scheduling and file management

Step 1: Basic mysqldump Syntax

The core syntax for mysqldump is:

mysqldump -u <user> -p [options] [database_name] [table1 table2 ...] > output.sql

To back up a single database named myapp:

mysqldump -u root -p myapp > /var/backups/mysql/myapp_$(date +%F).sql

You will be prompted for the MySQL password. The $(date +%F) substitution appends the current date (e.g. 2026-05-17) to the filename, creating a new file each day.

Step 2: Backing Up All Databases

The --all-databases flag dumps every database on the server, including the system databases (mysql, information_schema, performance_schema). This is the most complete backup option:

mysqldump -u root -p 
  --all-databases 
  --single-transaction 
  --routines 
  --triggers 
  --events 
  --flush-logs 
  --master-data=2 
  > /var/backups/mysql/all_databases_$(date +%F_%H%M).sql

Explanation of each flag:

--all-databases: dump every database
--single-transaction: for InnoDB tables, starts a consistent read transaction instead of locking tables; avoids blocking application writes during backup
--routines: include stored procedures and functions
--triggers: include triggers (default is on, but explicit is cleaner)
--events: include scheduled events from the event scheduler
--flush-logs: rotate binary logs at the start of the backup, useful for point-in-time recovery
--master-data=2: embed the binary log position as a comment (value 1 makes it an active CHANGE MASTER TO statement)

Important: --single-transaction only works correctly for InnoDB tables. MyISAM tables still require table locks; for mixed engines, add --lock-all-tables instead (but this will briefly block writes).

Step 3: Compressing Dump Files with gzip

SQL dump files compress very well because they are plain text. Pipe the output directly through gzip to save significant disk space:

mysqldump -u root -p 
  --all-databases 
  --single-transaction 
  --routines 
  --triggers 
  | gzip > /var/backups/mysql/all_databases_$(date +%F_%H%M).sql.gz

For faster compression at lower CPU cost, use gzip -1 (fastest, less compression) or the multi-threaded pigz if installed:

mysqldump -u root -p --all-databases --single-transaction 
  | pigz -p 4 > /var/backups/mysql/all_databases_$(date +%F).sql.gz

Step 4: Backing Up Specific Tables

To back up only specific tables within a database:

# Backup only the 'orders' and 'products' tables from 'myapp'
mysqldump -u root -p myapp orders products 
  > /var/backups/mysql/myapp_orders_products_$(date +%F).sql

Step 5: Storing Credentials Securely in ~/.my.cnf

Typing the password on the command line is inconvenient for scripted backups and leaves credentials visible in shell history. Store credentials in a user-specific MySQL options file instead:

vi ~/.my.cnf

[mysqldump]
user     = backup_user
password = BackupPassw0rd!

[mysql]
user     = backup_user
password = BackupPassw0rd!

chmod 600 ~/.my.cnf

Now you can run mysqldump without the -u and -p flags and credentials will be read from the file automatically. Create a dedicated MySQL backup user with minimal privileges:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPassw0rd!';
GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Step 6: Restoring a Database with mysql

Restoring from a mysqldump file is done by piping the SQL file into the mysql client. For an uncompressed file:

# Restore a specific database (must exist first, or use --all-databases dump)
mysql -u root -p myapp < /var/backups/mysql/myapp_2026-05-17.sql

If you need to create the database first:

mysql -u root -p -e "CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p myapp < /var/backups/mysql/myapp_2026-05-17.sql

For a compressed backup, decompress on the fly:

gunzip < /var/backups/mysql/all_databases_2026-05-17_0200.sql.gz | mysql -u root -p

Step 7: Scheduling Automated Backups with Cron

Automate nightly backups using cron. Edit the root crontab:

crontab -e

Add a nightly backup at 2:00 AM with automatic cleanup of backups older than 14 days:

# MySQL full backup every night at 2:00 AM
0 2 * * * /usr/bin/mysqldump --all-databases --single-transaction --routines --triggers 
  | gzip > /var/backups/mysql/all_databases_$(date +%F).sql.gz 2>> /var/log/mysql_backup.log

# Remove backups older than 14 days
10 2 * * * find /var/backups/mysql/ -name "*.sql.gz" -mtime +14 -delete

Note that % characters in crontab must be escaped as %. Ensure the backup directory exists:

sudo mkdir -p /var/backups/mysql
sudo chown root:root /var/backups/mysql
sudo chmod 700 /var/backups/mysql

Step 8: mydumper — A Faster Multi-Threaded Alternative

For large databases, mysqldump is single-threaded and can be slow. mydumper is an open-source tool that uses multiple threads to dump and restore databases in parallel. Install it from EPEL:

sudo yum install -y mydumper

Dump with 4 threads:

mydumper 
  --host 127.0.0.1 
  --user backup_user 
  --password 'BackupPassw0rd!' 
  --outputdir /var/backups/mysql/mydumper_$(date +%F) 
  --threads 4 
  --compress 
  --trx-consistency-only 
  --routines 
  --triggers 
  --events

Restore with myloader:

myloader 
  --host 127.0.0.1 
  --user root 
  --password 'RootPass!' 
  --directory /var/backups/mysql/mydumper_2026-05-17 
  --threads 4 
  --overwrite-tables

Step 9: Testing Restores Regularly

A backup you have never restored is not a backup — it is a hope. Regularly test your backup files by restoring them to a separate test server or a temporary local database:

# Create a test database and restore the latest backup into it
mysql -u root -p -e "CREATE DATABASE restore_test;"
gunzip < /var/backups/mysql/all_databases_$(date +%F).sql.gz 
  | mysql -u root -p restore_test 2>&1 | head -50

# Spot-check a table
mysql -u root -p restore_test -e "SHOW TABLES; SELECT COUNT(*) FROM orders;"

# Clean up test database
mysql -u root -p -e "DROP DATABASE restore_test;"

Automate restore testing in a staging environment and alert on failure. Document your expected restore time (RTO) so you know how long a production restore will take before you need to do it under pressure.

Mastering mysqldump on RHEL 7 gives you a reliable, portable, and human-readable backup solution for MySQL and MariaDB databases. By combining --single-transaction for non-blocking InnoDB backups, gzip compression to minimize storage, secure credential storage in ~/.my.cnf, automated cron scheduling, and a systematic restore-testing process, you have all the building blocks of a sound backup strategy. For databases growing beyond a few hundred gigabytes, graduate to mydumper for parallel dumps or to physical backup tools like Percona XtraBackup for truly large-scale environments.

How to Back Up and Restore MySQL Databases with mysqldump on RHEL 7