8 Best Ways to Back Up MySQL Databases

A safe MySQL database backup workflow creates a consistent dump, copies it away from the database server, and restores it into a clean test database. A scheduled command that produces files but never proves they can be restored is not a backup system. It is storage with good intentions.

I learned that after a client backup turned out to be an empty file. Since then, I have treated backup and restore as one job. For a small or medium InnoDB database, my default MySQL database backup uses mysqldump or mariadb-dump with --single-transaction, compression, an off-server copy, and a scheduled restore test.

Larger or recovery-critical systems need a different choice. MySQL Shell and MyDumper can parallelize logical backups. MariaDB Backup and provider snapshots can reduce recovery time for a full instance. WordPress plugins and phpMyAdmin are fallback options when shell access is unavailable.

The quick verdict: which MySQL database backup method should you use?

Choose by restore objective, data size, access level, and engine. The tool with the easiest backup command is not always the tool with the recovery path you need.

Method	Best fit	What it creates	Main limitation
mysqldump or mariadb-dump	Small to medium databases and portable restores	Logical SQL dump	Single-threaded and slower to restore as data grows
WP-CLI	WordPress servers with shell access	Logical SQL dump through site credentials	Still depends on the installed dump client
MySQL Shell	Modern MySQL instances and parallel logical migration	Directory of chunked dump files	MySQL-specific workflow and more moving parts
MyDumper and MyLoader	Large logical backups that need parallelism	Multiple schema and data files	Extra software and restore planning
MariaDB Backup	Fast full-instance MariaDB recovery	Physical data files	Version-sensitive and awkward for one-database restores
phpMyAdmin	Small databases without shell access	Browser-downloaded SQL or compressed export	HTTP limits and fragile large imports
WordPress plugin	Site owners who need files and database in one workflow	Plugin-specific backup archive	Runs inside WordPress and shares its resource limits
Managed snapshot	Fast whole-environment rollback	Provider-managed database or server snapshot	Provider lock-in and limited portability

What I measured in a MySQL database backup and restore test

I built a deterministic test database instead of quoting somebody else’s benchmark. It used MariaDB Server 12.3.2 on Apple Silicon, with 50,000 primary rows, 200,000 metadata rows, two InnoDB tables, six indexes including primary keys, one foreign key, one view, and one stored procedure.

• Table and index size after analysis: 66,256,896 bytes, or about 63.19 MiB.
• MariaDB 12.3.2 dump: 44,262,702 bytes in 0.31 seconds.
• Gzip level 9 output: 14,404,862 bytes in 1.01 seconds, a 67.46% size reduction.
• Clean MariaDB restore: 1.68 seconds.
• Restored row counts and two independent CRC32-based content signatures matched the source exactly.

Client against MariaDB 12.3.2	Dump time	SQL size	Gzip size	Restore time	Data match
mariadb-dump 12.3.2	0.31 s	42.21 MiB	13.74 MiB	1.68 s	Yes
mysqldump 9.7.1	0.31 s	41.97 MiB	13.72 MiB	1.96 s	Yes

The MySQL 9.7.1 client completed the dump and restore against the MariaDB server, but it emitted an event-terminology warning. That is a compatibility warning, not a result I would ignore in a production runbook. Match the dump tool to the server family and test cross-family restores before depending on them.

These timings are not a production promise. The test had no concurrent writes, network transfer, encryption, object storage, spinning disks, replication, or MySQL Server target. Its useful result is the method: record versions, dataset shape, dump size, checksum, restore time, row counts, and failure messages.

1. Backup a MySQL database with mysqldump or mariadb-dump

For a portable MySQL database backup, start here. MySQL’s mysqldump documentation describes a dump as SQL statements that recreate objects and data. MariaDB now calls its client mariadb-dump; the old mysqldump symlink is no longer present in the MariaDB 11+ official container image.

This is the MariaDB command I would use for a typical InnoDB application database:

mariadb-dump \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --default-character-set=utf8mb4 \
  app_database \
| gzip -9 > "app_database-$(date +%F-%H%M%S).sql.gz"

Use the matching MySQL client name on MySQL:

mysqldump \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --set-gtid-purged=OFF \
  app_database \
| gzip -9 > "app_database-$(date +%F-%H%M%S).sql.gz"

--single-transaction gives a consistent snapshot for transactional InnoDB tables without holding table locks for the whole dump. It does not make changing MyISAM tables transactionally consistent. --quick streams rows instead of buffering an entire large table in client memory. Routines and events need explicit attention because a default table dump is not a complete inventory of every database object.

Do not put a database password directly in shell history or a world-readable cron file. Use a restricted option file, a secrets manager, or the platform’s credential mechanism. Give the backup account only the privileges required by the objects and options you actually dump.

chmod 600 ~/.my.cnf

# ~/.my.cnf
[client]
user=backup_user
password=REPLACE_WITH_SECRET
host=127.0.0.1

Before you trust the result, test the compressed file and record a checksum:

test -s app_database-2026-07-28-020000.sql.gz
gzip -t app_database-2026-07-28-020000.sql.gz
sha256sum app_database-2026-07-28-020000.sql.gz

2. Backup a WordPress database with WP-CLI

WP-CLI is the cleanest WordPress-specific wrapper because it reads the database connection from wp-config.php. The official wp db export command calls the installed dump utility and accepts its flags.

wp db export - \
  --single-transaction \
  --quick \
| gzip -9 > "wordpress-$(date +%F-%H%M%S).sql.gz"

For a restore rehearsal, create an empty test database and point a disposable WordPress copy at it. The official wp db import command accepts SQL from standard input:

gunzip -c wordpress-2026-07-28-020000.sql.gz \
| wp db import - --path=/srv/www/restore-test

A database dump does not include wp-content/uploads, custom code, server configuration, environment variables, or external object storage. If a WordPress recovery depends on those assets, include them in the same recovery plan. That matters especially on media-heavy sites, even when you already compress images.

3. Use MySQL Shell for parallel logical backups

MySQL Shell is the better first-party choice when a single-threaded SQL stream becomes the bottleneck. Its dump and load utilities can split work across threads, compress output, dump an instance or selected schemas, and resume loads using progress state.

mysqlsh --js backup_user@127.0.0.1 -e '
util.dumpSchemas(
  ["app_database"],
  "/backups/app_database",
  {threads: 8, compression: "zstd"}
)'

mysqlsh --js restore_user@127.0.0.1 -e '
util.loadDump(
  "/backups/app_database",
  {threads: 8, showProgress: true}
)'

Do not build a new process around mysqlpump. Oracle deprecated it in MySQL 8.0.34 and removed it in MySQL 8.4. MySQL 8.0 also reached end of life in April 2026, so validate the runbook on MySQL 8.4 LTS or the supported series you actually operate.

4. Use MyDumper and MyLoader for large logical backups

MyDumper exists because traditional logical dumps are single-threaded. The project pairs mydumper for export with myloader for parallel restore and maintains a consistent snapshot across worker threads.

mydumper \
  --host 127.0.0.1 \
  --user backup_user \
  --password \
  --database app_database \
  --outputdir /backups/app_database \
  --threads 8 \
  --compress

myloader \
  --host 127.0.0.1 \
  --user restore_user \
  --password \
  --directory /backups/app_database \
  --threads 8 \
  --overwrite-tables

The MyDumper documentation is explicit about the tradeoff: parallelism makes large logical backups and restores practical, but you now manage a directory of files, thread count, lock mode, memory, disk throughput, and version compatibility. Benchmark the restore, not only the export.

5. Use MariaDB Backup for physical full-instance recovery

Logical dumps are portable and selective. Physical backups are usually faster for rebuilding a large instance because they copy database files instead of generating and replaying SQL statements. MariaDB Backup supports full and incremental physical backups for MariaDB.

mariadb-backup \
  --backup \
  --target-dir=/backups/full-2026-07-28 \
  --user=backup_user \
  --password

mariadb-backup \
  --prepare \
  --target-dir=/backups/full-2026-07-28

The prepare step is not optional. MariaDB’s full backup and restore guide explains that copied files are not initially point-in-time consistent. Restore also expects a stopped server and an empty data directory. Version compatibility matters, and restoring one database from a full physical backup is much less convenient than importing one logical dump.

6. Export and restore a small database with phpMyAdmin

phpMyAdmin is acceptable for a small one-off export when you have browser access but no shell. Select the database, choose Export, use SQL, include structure and data, and enable gzip compression. Then use Import against a separate test database.

The official phpMyAdmin import and export guide warns that large files can hit upload, memory, and HTTP timeout limits. That is why I do not use a browser workflow as the primary backup path for a growing production database.

7. Use a WordPress backup plugin when shell access is unavailable

A plugin can package the database and site files, schedule jobs, send archives to remote storage, and expose a guided restore flow. UpdraftPlus is the broad free starting point. Solid Backups is the paid option I would compare when you want scheduled off-site storage and a commercial recovery workflow.

The limitation is architectural: the backup code runs inside the WordPress and hosting environment it is trying to protect. PHP timeouts, disk limits, process killers, a broken plugin stack, or a compromised administrator can affect it. My guide on why every WordPress site needs a backup plugin explains the convenience case, while these WordPress maintenance habits help keep the restore path from becoming an annual surprise.

8. Use managed-host or cloud snapshots for fast rollback

A managed snapshot can restore a database or an entire environment much faster than replaying a large logical dump. That makes snapshots useful for a short recovery-time objective, release rollback, and server failure.

If you use Kinsta or Cloudways, check the retention, restore granularity, staging workflow, on-demand limits, and whether downloading an independent copy is included in the plan you pay for. Do not assume a provider snapshot replaces an off-platform backup.

Snapshots are often tied to the provider, region, account, and retention policy. Keep a portable logical dump for exits and migrations. That is especially useful when moving a WordPress site to a new host or moving between MySQL and MariaDB versions.

Turn the MySQL database backup command into a recovery system

A useful backup policy starts with two numbers: recovery point objective and recovery time objective. RPO is how much recent data you can lose. RTO is how long the service can remain unavailable. A daily dump gives a worst-case RPO close to 24 hours, even if the restore takes only five minutes.

• Schedule: run more often than the maximum acceptable data loss.
• Isolation: copy backups away from the database server and hosting account.
• Encryption: encrypt archives containing customer, order, member, credential, or regulated data.
• Immutability: use object lock, write-once storage, or a disconnected copy where the threat model justifies it.
• Retention: keep enough daily, weekly, and monthly points to recover from a problem discovered late.
• Monitoring: alert on missing, tiny, stale, slow, or failed backup jobs.
• Restore drills: restore into a separate environment and record the time, errors, row counts, and application checks.

CISA recommends offline, encrypted backups and regular availability and integrity tests in a disaster-recovery scenario. Its ransomware guidance is a useful reason not to leave the only backup mounted beside production.

Security matters on both sides of the copy. Restrict the backup account, protect the destination, rotate credentials, and keep recovery keys outside the system they unlock. Review your broader site security and store human-managed secrets in a proper password manager.

How to restore a MySQL backup safely

Never make the first restore attempt against production. Create an empty database on a compatible test server, import the dump, record every warning, and run application checks before you consider replacing live data.

mariadb -e \
  "CREATE DATABASE restore_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"

gunzip -c app_database-2026-07-28-020000.sql.gz \
| mariadb restore_test

Then verify what the business depends on:

• Expected schemas, tables, views, triggers, routines, and events exist.
• Critical table row counts match the source snapshot or backup manifest.
• Representative orders, users, posts, settings, relationships, Unicode text, and binary values read correctly.
• The application can authenticate, read, write, schedule jobs, and complete a critical transaction.
• The measured restore time fits the stated RTO.

For a migration, a successful import is only the database half. URLs, serialized WordPress data, file paths, upload assets, caches, DNS, TLS, email, cron, and external services can still fail. Treat the database restore as one checkpoint in the migration plan.

Common backup failures I would test

A restore rehearsal should try to expose predictable failures, not merely celebrate a green command exit.

Failure	How to detect it	Practical control
Empty or truncated file	Nonzero size, gzip -t, checksum and SQL tail inspection	Fail the job and alert before retention cleanup
Inconsistent tables	Restore under write load and compare related records	Use a supported consistent-snapshot or physical-backup method
Missing routines or events	Inventory object types before and after restore	Include explicit flags and verify privileges
No files or uploads	Application pages reference missing assets	Back up the database and filesystem as one recovery set
Backup stored beside production	Deleting or encrypting the server removes both copies	Maintain off-server and offline or immutable copies
Version incompatibility	Restore warnings, invalid collations, SQL mode or authentication errors	Test on the target version before the emergency
Restore exceeds RTO	Timed clean restore and application checks	Move to parallel logical or physical recovery

If the backup job makes the site slow, measure server CPU, disk I/O, query latency, and application response during the dump. My guide to why a WordPress site is slow covers the wider performance diagnosis. Schedule heavy work off-peak, but do not hide a backup process that saturates production every night.

Frequently asked questions

These answers cover the choices that change the command or the recovery design.

Does mysqldump lock the database?

With InnoDB and --single-transaction, mysqldump uses a consistent read and does not hold table locks for the full data dump. Some metadata operations and options can still require locks, and changing nontransactional tables are not covered by InnoDB transaction consistency.

Does a MySQL dump include users and permissions?

A single application-database dump does not automatically recreate all server accounts and privileges. An all-databases or instance-level plan has different security and portability consequences. Document the users, grants, authentication plugins, TLS requirements and secret rotation separately.

How often should I run a MySQL database backup?

Back up more frequently than the maximum data loss you can accept. If losing four hours of orders is unacceptable, a nightly dump is already too infrequent. Use binary logs or provider point-in-time recovery when full dumps alone cannot meet the RPO.

Is a hosting snapshot enough?

No single snapshot is enough. It may be fast, but it shares provider, account, retention and access risks. Keep a portable copy outside that failure boundary and rehearse both recovery paths.

Which tool should I use for a large database?

Use MySQL Shell or MyDumper when parallel logical dump and restore solve the bottleneck. Use a compatible physical backup tool or provider snapshot when whole-instance recovery time matters more than portability. Benchmark with your data shape and storage, because one large table behaves differently from thousands of small tables.

Can I use a MySQL client to back up MariaDB?

Sometimes, but treat it as a tested compatibility path, not a guarantee. My MySQL 9.7.1 client successfully dumped and restored the MariaDB 12.3.2 test dataset, with matching signatures, but it emitted an event-related warning. Use the matching vendor client by default.

The backup is finished only after the restore works

For most WordPress and small application databases, use a scheduled mysqldump or mariadb-dump, compress it, copy it off the server, and restore it into a clean database on a schedule. Move to parallel or physical tooling when the measured backup window or restore time stops meeting the business target.

The wider choice between MySQL, MariaDB, PostgreSQL and other systems belongs in my guide to the best open-source database software. Whatever engine you choose, keep the rule: a backup file is evidence of an attempted backup; a verified restore is evidence of recovery.