The Ultimate Guide to mysqldump: MySQL Database Backup, Restore, and Automation

mysqldump is a command-line utility bundled with MySQL and MariaDB that generates logical backups by serializing database objects and data as a sequence of SQL statements. The resulting dump file can recreate an identical database on any compatible server, making it the industry-standard tool for backups, cross-server migrations, version upgrades, and disaster recovery workflows.

Unlike physical backup tools such as Percona XtraBackup or MySQL Enterprise Backup, mysqldump operates at the SQL layer — it reads live data through the MySQL protocol and writes portable, human-readable SQL. This portability is its greatest strength and, at scale, its primary constraint.

What mysqldump Actually Does Under the Hood

When you invoke mysqldump, the client connects to the MySQL server, queries the information schema and data dictionary, and emits a stream of `CREATE DATABASE`, `CREATE TABLE`, `INSERT`, and DDL statements to standard output. You redirect that stream to a file, a pipe, or a compression utility.

For InnoDB tables with `–single-transaction`, mysqldump opens a repeatable-read transaction before reading any data. This gives you a consistent point-in-time snapshot without acquiring global read locks — the database remains fully writable during the dump. For MyISAM tables, no such mechanism exists; mysqldump falls back to `FLUSH TABLES WITH READ LOCK`, which briefly blocks writes.

Understanding this distinction is critical before you choose mysqldump for production workloads. If your schema mixes InnoDB and MyISAM tables, `–single-transaction` alone is insufficient — you will need `–lock-all-tables` or a maintenance window.

Prerequisites and Required Privileges

Before running any dump command, verify the following:

• MySQL or MariaDB is installed and accessible (local socket or TCP/IP).
• The backup user holds the minimum required privileges:
• `SELECT` on all target tables
• `LOCK TABLES` (required unless `–single-transaction` is used exclusively with InnoDB)
• `SHOW VIEW` to include views
• `TRIGGER` to include triggers
• `PROCESS` when using `–single-transaction` on MySQL 8+
• `RELOAD` for `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` if you need binary log coordinates for replication setup

Create a dedicated backup user rather than running dumps as root:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT

ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Running mysqldump as root with a password embedded in the shell command exposes credentials in process listings and shell history — a significant security risk on any shared or multi-user system.

Basic Syntax

“`

mysqldump [OPTIONS] database_name [table1 table2 …] > backup_file.sql

“`

Complete Option Reference

Connection Options

Scope Options

Consistency and Locking Options

Output and Content Options

mysqldump vs. Alternative Backup Methods

Choosing the right backup strategy depends on database size, RTO/RPO requirements, and infrastructure. Here is how mysqldump compares to the most common alternatives:

For databases under 10–20 GB on a VPS Hosting environment, mysqldump remains the most practical and portable solution. Beyond that threshold, physical backup tools offer dramatically faster backup and restore windows.

Practical Usage Examples

Example 1: Back Up a Single Database

“`bash

mysqldump -u backup_user -p database_name > /backups/database_name_$(date +%F).sql

“`

The `$(date +%F)` substitution appends the ISO date (e.g., `2025-07-15`) to the filename automatically, preventing overwrites.

Example 2: Back Up Multiple Specific Databases

“`bash

mysqldump -u backup_user -p –databases app_db analytics_db > /backups/multi_db_backup.sql

“`

The `–databases` flag causes mysqldump to emit `CREATE DATABASE` and `USE` statements, making the dump self-contained for restore.

Example 3: Back Up All Databases

“`bash

mysqldump -u backup_user -p –all-databases –events –routines –triggers

> /backups/full_server_$(date +%F).sql

“`

Always include `–events`, `–routines`, and `–triggers` in full-server dumps. These objects are silently omitted without explicit flags.

Example 4: Consistent InnoDB Backup (Production-Safe)

“`bash

mysqldump -u backup_user -p

–single-transaction

–flush-logs

–source-data=2

–routines –triggers –events

database_name > /backups/database_name_$(date +%F).sql

“`

`–flush-logs` rotates the binary log at the start of the dump. `–source-data=2` writes the current binary log filename and position as a SQL comment, enabling point-in-time recovery by replaying subsequent binlogs from that position.

Example 5: Compressed Backup with gzip

“`bash

mysqldump -u backup_user -p database_name | gzip -9 > /backups/database_name_$(date +%F).sql.gz

“`

For CPU-constrained servers, substitute `pigz` (parallel gzip) to utilize multiple cores:

“`bash

mysqldump -u backup_user -p database_name | pigz -9 > /backups/database_name_$(date +%F).sql.gz

“`

Example 6: Schema-Only Backup (Structure Without Data)

“`bash

mysqldump -u backup_user -p –no-data database_name > /backups/schema_only.sql

“`

Useful for version-controlling your schema in Git or deploying to a staging environment without copying production data.

Example 7: Data-Only Backup (No Schema)

“`bash

mysqldump -u backup_user -p –no-create-info database_name > /backups/data_only.sql

“`

Use this when the target schema already exists and you only need to populate or refresh data.

Example 8: Back Up a Single Table

“`bash

mysqldump -u backup_user -p database_name orders > /backups/orders_table_$(date +%F).sql

“`

Example 9: Export a Filtered Subset of Rows

“`bash

mysqldump -u backup_user -p database_name orders

–where="created_at >= '2025-01-01' AND status='completed'"

> /backups/orders_2025_completed.sql

“`

The `–where` option is underused but extremely powerful for partial exports, data archiving, and debugging specific record sets.

Example 10: Exclude Specific Tables

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Excluding large, ephemeral tables (caches, session stores, log tables) can reduce dump size and duration by an order of magnitude.

Example 11: Including Stored Procedures, Functions, and Triggers

“`bash

mysqldump -u backup_user -p –routines –triggers –events database_name > /backups/full_backup.sql

“`

Example 12: Remote Database Backup

“`bash

mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 database_name

“`

When backing up a remote server, traffic traverses the network unencrypted by default. Add `–ssl-ca`, `–ssl-cert`, and `–ssl-key` flags or tunnel through SSH:

“`bash

ssh user@remote-server "mysqldump -u backup_user -p database_name | gzip"

> /backups/remote_db_$(date +%F).sql.gz

“`

Restoring a mysqldump Backup

Restore a Single Database

“`bash

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

If the target database does not yet exist, create it first:

“`bash

mysql -u root -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Restore All Databases from a Full-Server Dump

“`bash

mysql -u root -p < /backups/full_server_2025-07-15.sql

“`

Because `–all-databases` embeds `CREATE DATABASE` and `USE` statements, no target database argument is needed.

Restore from a Compressed Backup

“`bash

gunzip < /backups/database_name_2025-07-15.sql.gz | mysql -u root -p database_name

“`

Or using process substitution:

“`bash

mysql -u root -p database_name < <(gunzip -c /backups/database_name_2025-07-15.sql.gz)

“`

Restore a Single Table from a Full Database Dump

This is a common operational scenario that the original dump file makes non-trivial. Use `sed` or `grep` to extract the relevant section:

“`bash

sed -n '/^– Table structure for table `orders`/,/^– Table structure for table `/p'

backup_file.sql | head -n -1 | mysql -u root -p database_name

“`

Alternatively, use `mysql_extract_table.sh` or import into a temporary database and copy the table:

“`bash

mysql -u root -p temp_restore < backup_file.sql

mysql -u root -p -e "INSERT INTO database_name.orders SELECT * FROM temp_restore.orders;"

“`

Point-in-Time Recovery Using Binary Logs

If your dump was taken with `–source-data=2` and binary logging is enabled, you can recover to any point after the dump:

1. Identify the binary log position from the dump file header comment.
2. Restore the base dump.
3. Apply subsequent binary log events up to the desired timestamp:

“`bash

mysqlbinlog –start-position=154 –stop-datetime="2025-07-15 14:30:00"

/var/lib/mysql/binlog.000042 | mysql -u root -p database_name

“`

Automating Backups with Cron

Basic Daily Backup Job

Store credentials in `~/.my.cnf` rather than embedding them in cron commands:

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Set strict permissions:

“`bash

chmod 600 ~/.my.cnf

“`

Then create the cron job:

“`bash

crontab -e

“`

“`

Daily compressed backup at 02:00, retained for 30 days

0 2 * * * mysqldump –single-transaction –routines –triggers –events database_name

Delete backups older than 30 days

10 2 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

“`

Production-Grade Backup Script

For Dedicated Servers hosting multiple databases, a more robust script handles error logging, disk space checks, and remote offloading:

“`bash

#!/bin/bash

BACKUP_DIR="/backups/mysql"

RETENTION_DAYS=30

LOG_FILE="/var/log/mysql_backup.log"

DATE=$(date +%F_%H-%M)

DATABASES=$(mysql –defaults-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do

OUTPUT="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

mysqldump –defaults-file=/etc/mysql/backup.cnf

–single-transaction –routines –triggers –events

"$DB" | gzip -9 > "$OUTPUT"

if [ $? -eq 0 ]; then

echo "$(date): SUCCESS – $DB -> $OUTPUT" >> "$LOG_FILE"

else

echo "$(date): FAILURE – $DB" >> "$LOG_FILE"

fi

done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

“`

Security Hardening for mysqldump Operations

Credential management is the most commonly neglected aspect of backup security. Never pass `-pYourPassword` directly on the command line — it is visible in `ps aux` output and shell history. Use one of these approaches instead:

• `~/.my.cnf` with `chmod 600` (per-user)
• `/etc/mysql/backup.cnf` with `chmod 640`, owned by root, readable by the backup group
• Environment variable `MYSQL_PWD` (visible in `/proc`, use only in isolated containers)
• MySQL Vault or HashiCorp Vault for enterprise environments

Backup file permissions must be restrictive:

“`bash

chmod 640 /backups/database_name_2025-07-15.sql.gz

chown root:backup_group /backups/database_name_2025-07-15.sql.gz

“`

Encryption at rest: For sensitive data, encrypt backup files before storing or transferring them:

“`bash

mysqldump –single-transaction database_name

> /backups/database_name_$(date +%F).sql.gz.enc

“`

Transport encryption: When dumping from a remote server, always use SSL/TLS or an SSH tunnel. On a VPS with cPanel environment, cPanel's backup interface handles this automatically, but manual mysqldump operations require explicit SSL flags.

Common Pitfalls and How to Avoid Them

Character set mismatches are the most frequent cause of corrupted restores. Always specify the character set explicitly:

“`bash

mysqldump –default-character-set=utf8mb4 database_name > backup.sql

mysql –default-character-set=utf8mb4 database_name < backup.sql

“`

Missing `–column-statistics=0` causes failures when a MySQL 8.0 client dumps from a MySQL 5.7 or MariaDB server. The MySQL 8 client attempts to dump column statistics that older servers do not support:

“`bash

mysqldump –column-statistics=0 -u backup_user -p database_name > backup.sql

“`

Forgetting `–routines`, `–triggers`, and `–events` silently omits critical database objects. These flags are not enabled by default (except `–triggers`) and are frequently forgotten in ad-hoc dumps.

Large table dumps causing OOM: mysqldump buffers entire result sets in memory by default. For very large tables, add `–quick` (enabled by default in most versions, but worth verifying) to stream rows one at a time rather than buffering:

“`bash

mysqldump –quick –single-transaction database_name > backup.sql

“`

Restoring to a different MySQL version: Dumps from MySQL 8.0 may contain syntax not supported in MySQL 5.7 (e.g., functional indexes, invisible columns). Always test restores in a version-matched environment before relying on cross-version migrations.

Auto-increment value drift: If you restore a table into an existing schema that already has rows, `INSERT` statements will fail on primary key conflicts unless you include `–add-drop-table` or manually truncate the target table first.

Using mysqldump for Database Migrations

mysqldump is the standard approach for migrating databases between servers — for example, when moving a WordPress site from Shared Web Hosting to a VPS, or replatforming to a VPS Control Panels environment with more resources.

The recommended migration workflow:

1. Dump the source database with full options:

“`bash

mysqldump –single-transaction –routines –triggers –events

–default-character-set=utf8mb4 source_db | gzip > migration.sql.gz

“`

1. Transfer securely using rsync over SSH:

“`bash

rsync -avz -e ssh migration.sql.gz user@target-server:/tmp/

“`

1. Create the target database with matching character set:

“`bash

mysql -u root -p -e "CREATE DATABASE target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

1. Restore and verify:

“`bash

gunzip < /tmp/migration.sql.gz | mysql -u root -p target_db

mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM critical_table;"

“`

1. Update application configuration to point to the new database host.

For applications that also rely on email infrastructure, ensure DNS records and Email Hosting configurations are updated in parallel with the database migration to avoid service disruption.

Verifying Backup Integrity

A backup that has never been tested is not a backup — it is an untested assumption. Implement a verification routine:

“`bash

#!/bin/bash

Restore backup to a test database and verify row counts

TEST_DB="backup_verify_$(date +%s)"

BACKUP_FILE="/backups/database_name_$(date +%F).sql.gz"

mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

PROD_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM database_name.orders;")

TEST_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM $TEST_DB.orders;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then

echo "Backup verified: row counts match ($PROD_COUNT rows)"

else

echo "BACKUP VERIFICATION FAILED: prod=$PROD_COUNT, test=$TEST_COUNT"

fi

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

“`

Run this verification script weekly via cron and alert on failure.

Decision Matrix: When to Use mysqldump

Technical Key-Takeaway Checklist

• Always use `–single-transaction` for InnoDB-only databases to avoid write locks during backup.
• Always include `–routines –triggers –events` in any dump intended as a complete backup.
• Store credentials in `~/.my.cnf` or `/etc/mysql/backup.cnf` with `chmod 600/640` — never inline in scripts or cron commands.
• Add `–column-statistics=0` when using a MySQL 8.0 client against a MySQL 5.7 or MariaDB server.
• Always specify `–default-character-set=utf8mb4` on both dump and restore to prevent character encoding corruption.
• Compress all backups with gzip or pigz; encrypt sensitive dumps with AES-256 before offsite transfer.
• Include `–flush-logs –source-data=2` in production dumps to enable point-in-time recovery via binary logs.
• Automate retention cleanup with `find … -mtime +N -delete` to prevent disk exhaustion.
• Test restores on a schedule — verify row counts and spot-check data integrity against production.
• For mixed-engine schemas, use `–lock-all-tables` instead of `–single-transaction` to guarantee consistency.

Frequently Asked Questions

Does mysqldump lock tables during backup?

With `–single-transaction` on a pure InnoDB database, no table locks are acquired beyond a brief initial flush. MyISAM tables always require a read lock (`LOCK TABLES`) because they lack transaction support. Mixed-engine schemas require `–lock-all-tables` for a consistent snapshot, which blocks writes for the duration of the dump.

How do I back up only the database schema without any data?

Use the `–no-data` flag: `mysqldump -u backup_user -p –no-data database_name > schema.sql`. This exports all `CREATE TABLE`, `CREATE VIEW`, stored procedures, and triggers without any `INSERT` statements.

Why does my mysqldump fail with "column statistics" errors?

This occurs when a MySQL 8.0 client connects to a MySQL 5.7 or MariaDB server. Add `–column-statistics=0` to your command. Alternatively, update the server to MySQL 8.0 or use a client binary that matches the server version.

Can mysqldump perform incremental backups?

No. mysqldump always produces a full logical dump of the specified scope. Incremental backup capability requires binary log archiving (`mysqlbinlog`) combined with a base mysqldump taken with `–flush-logs –source-data=2`. True incremental physical backups require Percona XtraBackup or MySQL Enterprise Backup.

What is the safest way to automate mysqldump without exposing passwords?

Create a dedicated MySQL backup user with minimal required privileges, store its credentials in a `[mysqldump]` section of `~/.my.cnf` or a separate options file with `chmod 600`, and reference it with `–defaults-file=/path/to/backup.cnf`. This approach keeps credentials out of process listings, shell history, and cron job definitions entirely.