How to back up and restore MySQL databases with mysqldump

mysqldump writes a database out as a plain SQL file that can rebuild it anywhere. It ships with MySQL and MariaDB, needs no extra software, and is the simplest way to back up a site's database or move it to a new server. This guide covers dumping one or all databases, compressing the output and restoring it.

Before you start

• MySQL or MariaDB running on your Linux VPS.
• An account that can read the databases. On a fresh install, sudo mysqldump uses the root account through socket login.
• Enough free disk space. Check with df -h. A compressed dump is often much smaller than the database, but plain SQL can be larger.

On MariaDB, the tool is also available as mariadb-dump, with the same options. The examples use mysqldump, which works on both.

Keep credentials out of the command line

If you back up with a password account rather than sudo, do not type the password after -p in a command. It ends up in shell history and process lists. Put it in a private option file instead:

nano ~/.my.cnf

[client]
user=backup
password=StrongPassword123!ReplaceMe

The password above is a placeholder; use the real password of your own backup account. Then lock the file down:

chmod 600 ~/.my.cnf

mysqldump and mysql read this file automatically for that Linux user. When you use it, drop sudo from the commands below, because sudo runs them as root and root has its own option file.

Back up one database

sudo mysqldump --single-transaction --routines --triggers --events \
  --databases shopdb > ~/shopdb-$(date +%F).sql

What each option does:

• --single-transaction: takes a consistent snapshot of InnoDB tables without locking them, so the site keeps running during the backup. It does not protect MyISAM tables.
• --routines: includes stored procedures and functions. They are left out by default.
• --triggers: includes triggers. This is on by default, but stating it makes the intent clear.
• --events: includes scheduled events, which are also left out by default.
• --databases shopdb: adds CREATE DATABASE and USE lines, so the file restores into a database of the same name.

Back up all databases

sudo mysqldump --single-transaction --routines --triggers --events \
  --all-databases > ~/all-databases-$(date +%F).sql

This includes the mysql system database, so user accounts and grants come along. That is useful for rebuilding the same server. When moving to a different MySQL or MariaDB version, dump only your app databases and recreate the users by hand, as system tables differ between versions.

Compress with gzip

SQL text compresses well. Pipe the dump straight into gzip so the uncompressed file never touches the disk:

sudo mysqldump --single-transaction --routines --triggers --events \
  --databases shopdb | gzip > ~/shopdb-$(date +%F).sql.gz

Check the file is not empty and ends properly:

ls -lh ~/shopdb-*.sql.gz
zcat ~/shopdb-$(date +%F).sql.gz | tail -n 1

The last line should be -- Dump completed on followed by a date. If it is missing, the dump stopped early.

Restore a dump

A dump made with --databases creates the database itself:

sudo mysql < ~/shopdb-2026-11-09.sql

For a compressed file:

gunzip < ~/shopdb-2026-11-09.sql.gz | sudo mysql

A dump made without --databases (for example mysqldump shopdb) has no CREATE DATABASE line. Create the target first, then name it on the command line:

sudo mysql -e "CREATE DATABASE shopdb_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
gunzip < ~/shopdb-2026-11-09.sql.gz | sudo mysql shopdb_restore

That second form is also the safe way to test a backup: restore into a new database, check it, then drop it.

Check it works

A backup is only proven once you have restored it. After a test restore, compare table counts between the original and the copy:

SELECT table_schema, COUNT(*) AS tables
FROM information_schema.tables
WHERE table_schema IN ('shopdb', 'shopdb_restore')
GROUP BY table_schema;

Both rows should show the same number. Spot-check a busy table with SELECT COUNT(*) on each side.

To run backups every night, add the dump command to cron as described in schedule cron jobs on Linux. Remove old files on the same schedule, for example those older than 14 days:

find ~/ -maxdepth 1 -name 'shopdb-*.sql.gz' -mtime +14 -delete

Troubleshooting

• Access denied; you need (at least one of) the PROCESS privilege(s): newer MySQL versions want PROCESS to dump tablespace details. Grant it to the backup user, or add --no-tablespaces.
• ERROR 1419... SUPER privilege and binary logging is enabled on restore: the dump has functions and the server logs binary changes. Restore as an admin user, or ask your DBA about log_bin_trust_function_creators.
• ERROR 1449 The user specified as a definer does not exist: a view or routine names a user missing on the new server. Create that user, or edit the DEFINER clauses in a copy of the dump.
• Unknown collation on import: the dump came from MySQL 8 and the target is MariaDB. Replace utf8mb4_0900_ai_ci with utf8mb4_unicode_ci in a copy of the file.
• Restore is very slow: large dumps take time. Run it inside screen or tmux so a dropped SSH session does not stop it.

Next steps

• Set up the user your app connects with: Create a database and user for a web app.