BACKUPS
June 24, 2026

How to Back Up a MySQL Database from the Command Line

9 min read
Author
CloudStick Team
Backend Developer
Share this article
MySQL Database Backup Command Line
CloudStick
MySQL CLI Backup Guide

mysqldump: The Standard MySQL Backup Tool

mysqldump is a command-line utility included with every MySQL and MariaDB installation. It exports one or more databases as a plain SQL file — a series of CREATE TABLE and INSERT INTO statements that reconstruct the database from scratch when run against a fresh MySQL instance.

PREREQUISITE

You need MySQL credentials with at least SELECT, LOCK TABLES, and SHOW VIEW privileges on the databases you want to back up. For a dedicated backup user, run: GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup_user'@'localhost' IDENTIFIED BY 'password';

Backing Up a Single Database

# Plain SQL dump
mysqldump -u backup_user -p my_database > backup.sql
# Compressed — much smaller, always use this
mysqldump -u backup_user -p my_database | gzip > backup.sql.gz
# With --single-transaction for InnoDB (no table locks)
mysqldump -u backup_user -p \
--single-transaction \
--quick \
my_database | gzip > backup-$(date +%Y%m%d).sql.gz

The --single-transaction flag is critical for WordPress and other InnoDB databases — it takes a consistent snapshot without locking any tables, meaning the site stays responsive during backup. The --quick flag writes rows one at a time instead of buffering them in memory, which prevents crashes on large databases.

Backing Up All Databases at Once

# Dump every database including system schemas
mysqldump -u root -p \
--all-databases \
--single-transaction \
--events --routines --triggers \
| gzip > all-databases-$(date +%Y%m%d).sql.gz

The --events, --routines, and --triggers flags include stored procedures, functions, and event scheduler definitions that are otherwise omitted from the default dump.

Restoring from a mysqldump Backup

# Create the target database first (if restoring single DB)
mysql -u root -p -e "CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
# Restore from compressed backup
gunzip -c backup-20260624.sql.gz | mysql -u root -p my_database
# Restore all databases from --all-databases dump
gunzip -c all-databases-20260624.sql.gz | mysql -u root -p

Storing Credentials Safely for Automated Backups

Avoid putting passwords directly in cron command lines or scripts. Use a MySQL option file instead:

# /root/.my.cnf — readable only by root
[client]
user = backup_user
password = your_password
chmod 600 /root/.my.cnf
# Now mysqldump reads credentials automatically
mysqldump my_database | gzip > backup.sql.gz
TIP

For a cron line that runs at 3am daily and keeps 14 days of backups: 0 3 * * * mysqldump my_db | gzip > /backups/$(date +\%Y\%m\%d).sql.gz && find /backups -mtime +14 -delete

CloudStick Makes Database Backups Point-and-Click

CloudStick's Database Backups section handles mysqldump, compression, storage, and retention automatically — you enable backup per-database, set the schedule and retention period, and CloudStick runs the dump on schedule and stores it in managed offsite storage.

See the CloudStick knowledge base: How to Download a Database Backup to Localhost for the step-by-step walkthrough of downloading a specific backup file from the dashboard for local restore testing.

Leave a comment
Full Name
Email Address
Message
Contents