8 ways to backup MySQL databases with commands & tools

I lost a client database once because a “backup” turned out to be an empty 0-byte file nobody had ever tested. That single mistake is why I now treat every backup MySQL database job as two jobs: take the dump, then prove you can restore it. The fastest reliable method for most people is a one-line mysqldump piped through gzip, run on a schedule. Reach for a plugin only when you do not have shell access, and reach for managed-host snapshots when you want zero-maintenance recovery points.

I have run MySQL behind WordPress, Magento, Drupal, and custom portals for 18 years, and the database is the one asset you genuinely cannot rebuild from memory. Lose the files and you re-upload them. Lose the database and you lose every order, comment, user, and post. Below are the methods I actually use, the exact commands, and an honest verdict on which one fits your situation.

Proof I’ve done this: I’ve run MySQL behind WordPress, Magento, Drupal, and custom portals for 18 years and across 800+ client projects. I migrated a 30GB database with mydumper last year, I run nightly mysqldump jobs on most of my own servers, and I test a restore into a throwaway database every month. Every command below is one I actually run, not one I copied from the manual.

Who this is for: developers and site owners who want a backup MySQL database routine they can trust. Who can skip the manual route: if you’re on a managed host like Kinsta or Cloudways that already takes automatic daily snapshots, you can lean on that and only reach for mysqldump when you migrate. Everyone else should automate one of the methods below.

The quick verdict: which method to use

If you have SSH access, use mysqldump with gzip on a cron schedule. It ships with MySQL, costs nothing, and produces a portable SQL file you can restore anywhere. If you only have a control panel, export through phpMyAdmin or a WordPress backup plugin. If you run a big database (tens of GB or more) and care about backup speed, switch to a parallel tool like mydumper. And if you would rather never think about it again, let your managed host take automatic snapshots. Most of my sites use two of these at once: a nightly mysqldump for portability plus host snapshots as a safety net.

Method	How it works	Best for	Automated?
mysqldump	CLI utility that writes a logical SQL dump	Most sites, migrations, portable backups	Yes, via cron
mysqlpump	Parallel logical dump (MySQL 5.7+)	Faster dumps on multi-DB servers	Yes, via cron
mydumper	Multi-threaded dump and restore	Large databases needing speed	Yes, via cron
phpMyAdmin	Browser-based Export tab	No SSH, small databases	No, manual
WordPress plugin	UpdraftPlus or similar, dashboard-driven	WordPress users, off-site copies	Yes, scheduled
Managed host snapshot	Host-level automatic backups	Hands-off recovery points	Yes, built-in

1. Backup a MySQL database with mysqldump

mysqldump is the utility I reach for nine times out of ten. It ships with MySQL, runs from the command line, and writes a plain SQL file containing the statements needed to rebuild your data on any other MySQL or MariaDB server. You will find it in /usr/bin/mysqldump on most Linux installs, or under the bin folder of your MySQL install on Windows.

The basic form is straightforward. Back up a single database to a file:

mysqldump -u USERNAME -p DATABASE_NAME > backup.sql

You will be prompted for the password, which keeps it out of your shell history. To back up every database on the server at once, swap the database name for the all-databases flag:

mysqldump -u root -p --all-databases > all-databases.sql

A raw SQL dump for a busy WordPress site can run into hundreds of megabytes, so I always compress on the fly by piping straight into gzip. This is the exact command I run on most servers:

mysqldump -u USERNAME -p DATABASE_NAME | gzip > backup-$(date +%F).sql.gz

That $(date +%F) stamps the filename with the date (like backup-2026-06-25.sql.gz) so each run is a separate, dated file instead of overwriting yesterday’s copy. One detail people miss: stored procedures, triggers, and events are not always included by default. If your database uses them, add the relevant flags so the dump is genuinely complete:

mysqldump -u USERNAME -p --routines --triggers --events \
  --single-transaction DATABASE_NAME | gzip > backup.sql.gz

The --single-transaction flag is the one I never skip on InnoDB tables. It takes a consistent snapshot without locking the tables, so your site keeps serving traffic while the backup runs. For a WordPress database, that means no visitor sees an error mid-dump.

2. Automate MySQL backups with cron

A backup you have to remember to run is a backup that eventually does not happen. The fix is cron. The cleanest way to script it is a small shell file that holds the credentials, dumps with compression, and deletes anything older than 14 days so the disk does not fill up.

Save this as /usr/local/bin/db-backup.sh:

#!/bin/bash
DB_NAME="your_database"
DB_USER="your_user"
DB_PASS="your_password"
DEST="/home/backups"
STAMP=$(date +%F-%H%M)

mkdir -p "$DEST"
mysqldump -u "$DB_USER" -p"$DB_PASS" --single-transaction \
  --routines --triggers "$DB_NAME" | gzip > "$DEST/$DB_NAME-$STAMP.sql.gz"

# keep 14 days of backups
find "$DEST" -name "*.sql.gz" -mtime +14 -delete

Make it executable with chmod +x /usr/local/bin/db-backup.sh, then add a cron entry with crontab -e to run it every night at 2am:

0 2 * * * /usr/local/bin/db-backup.sh

One honest warning: putting the password in the script means the file must be locked down with chmod 600 so only its owner can read it. A cleaner approach is a ~/.my.cnf file with the credentials, which lets you drop the password from the command entirely. I treat the backup destination as off-site too, since a backup sitting on the same disk as the database dies with the server. I sync the folder to object storage or a second box nightly.

On WordPress? Skip the credentials entirely. If you have WP-CLI installed, wp db export reads the database name, user, and password straight from wp-config.php and wraps mysqldump for you, so there’s no password to hard-code. It’s the cleanest way to back up a WordPress database from the shell.

# exports to {dbname}-{date}-{hash}.sql by default
wp db export - | gzip > wp-db-$(date +%F).sql.gz

3. Faster dumps with mysqlpump and mydumper

mysqldump runs on a single thread, which is fine until your database grows past a few gigabytes and the nightly dump starts taking 40 minutes. Two parallel tools fix that. mysqlpump ships with MySQL 5.7 and later and dumps multiple databases at once:

mysqlpump -u root -p --default-parallelism=4 \
  --exclude-databases=performance_schema,sys > dump.sql

Note that mysqlpump skips performance_schema, sys, and ndbinfo by default, and it does not dump user accounts unless you ask. For a genuinely large database, I prefer mydumper, which writes each table to its own file and restores in parallel too. That table-per-file layout makes it easy to restore a single table without touching the rest:

mydumper \
  --database=DB_NAME \
  --host=127.0.0.1 \
  --user=DB_USER \
  --password=DB_PASS \
  --rows=500000 \
  --compress \
  --outputdir=/home/backups/mydumper

The --rows=500000 flag chunks big tables so threads can split the work, and --compress gzips each file as it writes. On a 30GB database I migrated last year, mydumper finished in about a third of the time mysqldump needed.

4. Backup MySQL with phpMyAdmin

No SSH access? phpMyAdmin is the answer, and most shared hosts ship it inside cPanel. It is a browser tool, so this is the route I point non-technical clients to. Open phpMyAdmin, click the database name in the left sidebar, then open the Export tab at the top.

Pick the Custom export method rather than Quick, set the format to SQL, and under Output choose “Save output to a file” with gzip compression. That gives you a single compressed .sql.gz download you can store anywhere. The one limit to watch is size: phpMyAdmin runs inside PHP, so a database larger than your host’s upload and execution limits can time out mid-export. For anything past a few hundred megabytes, the command-line tools above are more reliable.

5. Backup a WordPress database with a plugin

If your MySQL database powers a WordPress site, a backup plugin is the lowest-friction option. UpdraftPlus (3 million-plus active installs), BlogVault, and Duplicator all run from the dashboard, schedule themselves, and push copies off-site to Google Drive, Dropbox, or S3. The premium plugin I trust on client sites is Solid Backups, which handles scheduled off-site database and file backups together. That off-site step matters more than the backup itself, because a copy on the same server is worthless when the server dies.

I have written separately on why every WordPress site needs a backup plugin, and the short version is that plugins back up your files and database together, which command-line dumps do not. The tradeoff is that plugin backups run inside PHP and can struggle on huge sites or cheap hosting. Pair a plugin with solid WordPress maintenance habits and you have covered both halves of the problem.

6. Managed host and cloud snapshots

The most hands-off method is to let infrastructure do it. Managed WordPress hosts like Kinsta and Cloudways take automatic daily backups and let you restore with one click. Cloud platforms go further: Amazon RDS, DigitalOcean Managed Databases, and Google Cloud SQL take point-in-time snapshots you can roll back to any moment in the retention window.

I still keep my own mysqldump running even on managed databases. Host snapshots are convenient, but they live inside that host’s ecosystem. The day you move providers, a portable SQL file is what actually travels with you. If a migration is on your roadmap, my guide on moving a WordPress site to a new host walks through using these dumps to land cleanly on the new server. For choosing the database engine itself, I compared the best open-source database software in a separate piece.

How to restore a MySQL backup (the part that matters)

A backup you have never restored is a guess, not a backup. Restoring a mysqldump file is the mirror image of creating one. For a plain SQL file, pipe it back in:

mysql -u USERNAME -p DATABASE_NAME < backup.sql

If you compressed with gzip, decompress on the way in so you never have to unzip the file to disk first:

gunzip < backup.sql.gz | mysql -u USERNAME -p DATABASE_NAME

The database has to exist before you restore into it, so create an empty one first with CREATE DATABASE DATABASE_NAME; if you are rebuilding from scratch. My rule, learned the hard way from that 0-byte file, is to test a restore into a throwaway database once a month. It takes five minutes and it is the only thing that turns a hopeful file into a backup you can trust. Pair good backups with strong site security and a sensible password manager, and your data is genuinely protected rather than just copied.

The bottom line

Start with a nightly mysqldump piped through gzip on a cron job. It is free, portable, and covers the vast majority of sites. Add a WordPress plugin if you want files and database together, switch to mydumper once your database gets big, and lean on managed-host snapshots as a hands-off safety net. A lean database also keeps the rest of your stack quick, the same way you'd compress images or work through why a WordPress site is slow to keep load times down. Whatever you choose, schedule it, send a copy off-site, and test a restore. The backup is the easy half. The restore is the half that saves you.

Disclaimer: This site is reader-supported. If you buy through some links, I may earn a small commission at no extra cost to you. I only recommend tools I trust and would use myself. Your support helps keep gauravtiwari.org free and focused on real-world advice. Thanks. - Gaurav Tiwari