How to Back Up and Restore MySQL with Mysqldump

A reliable MySQL backup is only useful when you can restore it. In this guide, you will learn how to back up and restore a MySQL database with mysqldump, including safe options for an active InnoDB database, compressed backups, multiple databases, and restore testing.

The commands work with current MySQL releases on Linux. Replace the example database, user, and file names with your own values.

What is mysqldump?

mysqldump is a MySQL command-line backup program. It reads database objects and data, then writes SQL statements that can recreate them. This makes the dump portable and easy to inspect, but large logical backups can take longer to create and restore than physical backups.

Check that the client is installed:

mysqldump --version
mysql --version

On Ubuntu or Debian, the client can normally be installed with:

sudo apt update
sudo apt install mysql-client

Back up one MySQL database

The basic command is:

mysqldump -u backup_user -p database_name > database_name.sql

The -p option prompts for the password. Do not place the password directly after -p, because it can be exposed in shell history or the process list.

Confirm that the command succeeded before trusting the file:

test -s database_name.sql && echo "Backup file is not empty"
head -n 20 database_name.sql

An existing output file is overwritten by the shell before mysqldump begins. Use a new dated filename when keeping backup history.

Recommended command for an active InnoDB database

For a normal InnoDB application database, use:

mysqldump -u backup_user -p 
  --single-transaction 
  --quick 
  --routines 
  --events 
  --triggers 
  database_name > database_name.sql

--single-transaction creates a consistent snapshot for transactional tables such as InnoDB without locking them for the full dump. --quick reads rows one at a time, which helps with large tables.

Do not run schema-changing commands such as ALTER TABLE, DROP TABLE, RENAME TABLE, or TRUNCATE TABLE while this dump is running. Also remember that --single-transaction does not provide the same consistency for non-transactional MyISAM or MEMORY tables.

Triggers are included by default, but listing --triggers makes the intended backup scope clear. Stored routines and scheduled events require the explicit --routines and --events options.

Create a compressed MySQL backup

SQL dumps often compress well:

mysqldump -u backup_user -p 
  --single-transaction --quick --routines --events --triggers 
  database_name | gzip > database_name-$(date +%F).sql.gz

With a pipeline, check the exit status carefully. In Bash, enable pipefail in a backup script so a failed dump does not look successful just because gzip finished:

set -o pipefail
mysqldump -u backup_user -p 
  --single-transaction --quick --routines --events --triggers 
  database_name | gzip > database_name.sql.gz

Test the compressed file:

gzip -t database_name.sql.gz

Back up multiple databases

Use --databases when backing up selected databases:

mysqldump -u backup_user -p 
  --single-transaction --quick --routines --events --triggers 
  --databases app_db analytics_db > selected-databases.sql

This includes CREATE DATABASE and USE statements, so the database names are preserved during restoration.

Back up all MySQL databases

mysqldump -u backup_user -p 
  --single-transaction --quick --routines --events --triggers 
  --all-databases > all-databases.sql

Make sure the backup account has the privileges required for every object being dumped. MySQL 8.4 also requires --routines and --events when you want those objects included in an all-databases dump.

Back up one table

mysqldump -u backup_user -p database_name table_name > table_name.sql

List several table names after the database name to include more than one table.

Restore one MySQL database

If the dump does not contain CREATE DATABASE and USE statements, create the target first:

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

Choose the character set and collation that your application requires. Restoring into an empty test database is safer than overwriting production immediately.

If the dump was created with --databases or --all-databases, it normally contains its own database selection statements:

mysql -u root -p < selected-databases.sql

Restore a compressed backup

gunzip -c database_name.sql.gz | mysql -u root -p restored_db

For Bash scripts, use set -o pipefail here as well so a decompression or MySQL error fails the job.

Restore from inside the MySQL client

You can also load a dump interactively:

mysql -u root -p
USE restored_db;
SOURCE /absolute/path/database_name.sql;

An absolute path avoids confusion about the client’s current working directory.

Verify that the restore really works

Do not treat the existence of a backup file as proof that recovery will work. Perform a test restore to a separate database or server and check the result:

mysql -u root -p -e "SHOW TABLES FROM restored_db;"
mysql -u root -p -e "SELECT COUNT(*) FROM restored_db.important_table;"

Also verify views, triggers, routines, scheduled events, application login, and a few important records. Record the restore time so you know whether the recovery process meets your needs.

Keep MySQL credentials out of scripts

A password on the command line may be visible to other users. For automated backups, MySQL’s login-path feature can store connection details in an obfuscated local file:

mysql_config_editor set --login-path=backup 
  --host=localhost --user=backup_user --password

mysqldump --login-path=backup 
  --single-transaction --quick --routines --events --triggers 
  database_name > database_name.sql

Protect the operating-system account that runs backups and give the MySQL backup account only the required privileges. The exact privileges depend on the objects and options being dumped.

Simple automated backup script

#!/usr/bin/env bash
set -euo pipefail

backup_dir="/var/backups/mysql"
database="database_name"
stamp="$(date +%F-%H%M%S)"
output="$backup_dir/$database-$stamp.sql.gz"

install -d -m 700 "$backup_dir"

mysqldump --login-path=backup 
  --single-transaction --quick --routines --events --triggers 
  "$database" | gzip > "$output"

gzip -t "$output"
echo "Created $output"

Store a copy away from the database server. A disk failure, compromised root account, or ransomware incident can destroy backups kept only on the same machine. Encrypt backups that contain private data and test your retention cleanup before automating deletion.

Common mysqldump errors

Access denied

Check the username, host, password, and grants. The account may need privileges such as SELECT, SHOW VIEW, TRIGGER, and others depending on the selected options.

PROCESS privilege error

Some environments report a tablespace-related PROCESS privilege error. If tablespace information is not required, --no-tablespaces may be appropriate. Review the dump and restore requirements before changing privileges.

Unknown database during restore

Create the target database first, or use a dump made with --databases. A simple single-database dump does not include CREATE DATABASE by default.

GTID error on another server

GTID-enabled servers may add SET @@GLOBAL.GTID_PURGED to the dump. For partial dumps or migrations, review MySQL’s --set-gtid-purged option and the target replication design instead of deleting statements blindly.

Restore stops because an object already exists

Restore into an empty test database when possible. Dropping a live database or adding destructive drop options can permanently remove data, so confirm the target and keep a separate verified backup first.

MySQL backup checklist

• Use a dated, protected backup file.
• Use --single-transaction --quick for an active InnoDB database.
• Include routines, events, and triggers when the application uses them.
• Check command exit codes and compressed-file integrity.
• Copy backups to a separate protected location.
• Restore regularly into a test environment.
• Document recovery steps and expected recovery time.

Database protection is only one part of server safety. Review our Linux server hardening guide and configure a UFW firewall on Ubuntu as well.

Frequently asked questions

Does mysqldump back up users and permissions?

A normal application-database dump does not recreate every server account and global grant. Plan account and privilege recovery separately, especially when moving to another MySQL server.

Can I run mysqldump while the website is online?

Yes, for InnoDB databases --single-transaction is commonly used to obtain a consistent snapshot without locking tables for the entire dump. Avoid schema changes during the operation.

How often should I back up MySQL?

Base the schedule on how much data the business can afford to lose. A busy store may need frequent backups and binary-log point-in-time recovery, while a small read-mostly site may accept daily backups.

Is a successful mysqldump enough?

No. Check the exit status, protect and copy the file, then perform regular test restores. A backup becomes trustworthy only after recovery has been verified.

Official MySQL references

• MySQL 8.4: mysqldump backup program
• MySQL 8.4: Dumping data in SQL format
• MySQL 8.4: Reloading SQL-format backups
• MySQL 8.4: Dumping stored programs

Before updating a self-hosted collaboration server, pair database backups with application and file backups. Our Nextcloud 34.0.3 maintenance update guide includes a practical pre-update and verification checklist.

You now have a practical way to back up and restore MySQL databases with mysqldump. Keep the command appropriate for your storage engines, protect the credentials and files, and make restore testing a normal part of the backup routine.