Skip to main content
To back up a MySQL database, run sudo mysqldump --single-transaction --routines --triggers shop > shop.sql (on MariaDB the command is mariadb-dump). To restore it, create an empty database and run sudo mysql shop < shop.sql. The .sql file holds every table and row as SQL statements, so you can restore it on the same or a new server.

​
Before you start

  • Check you have enough free space for the dump. See check disk space.
  • On Ubuntu and Debian, sudo lets the dump tool log in as database root without a password. On other setups add -u root -p (or another admin user) to each command.
  • On MariaDB, mysqldump and mysql still work as older names for mariadb-dump and mariadb.

​
How to back up one database

1

Make a backup folder

2

Dump the database

This writes a compressed file such as shop-2026-11-04.sql.gz.
3

Check the file

OK means the archive is complete.
What the options do:

​
How to back up all databases

--all-databases includes the mysql system database, so it also keeps your users and passwords. A single-database dump does not. Red Hat notes that a dump also does not include configuration files such as /etc/my.cnf, so copy those separately.

​
How to restore a MySQL database

Restoring into a database that already has tables overwrites tables with the same names. Restore into a new, empty database first if you are not sure, then switch your app over.
1

Create an empty database

Skip this if the database already exists and you want to overwrite it.
2

Import the dump

For a plain .sql file:
For a compressed .sql.gz file:
3

Check the data

To restore a full --all-databases dump, leave out the database name: gunzip < all-2026-11-04.sql.gz | sudo mysql.

​
How to schedule daily MySQL backups with cron

1

Open root's crontab

2

Add two lines

The first line dumps everything at 03:30 every day. The second deletes dumps older than 7 days. In a crontab, % must be written as \%.
3

Save and check

Save and close the editor, then run sudo crontab -l to confirm. Learn more in schedule tasks with cron.
A backup on the same disk is lost if the server is lost. Copy the files off the server with rclone or restic. On a VPS you can also schedule panel backups of the whole server.

​
How to check a backup works

The only real test is a restore. Once a month, restore the latest dump into a test database:
Replace orders with one of your own tables.

​
Frequently asked questions

​
Does mysqldump lock my website while it runs?

No. --single-transaction turns off table locking and dumps everything from one consistent snapshot. That snapshot is only guaranteed for InnoDB tables (the default engine). Avoid ALTER TABLE or DROP TABLE while the dump runs.

​
How do I move a database to a new server?

Dump it on the old server, copy the file with scp or rsync, create the database and user on the new server, and import the file.

​
Why do I get “Unknown database” when restoring?

A single-database dump has no CREATE DATABASE line, so the database you name in mysql shop < shop.sql must already exist. Create it first, then import.

​
How do I back up PostgreSQL?

Use pg_dump instead. See install PostgreSQL.

VPS and dedicated server guides

Every guide in this section.

Back up a Linux server

Files, configs and databases.

Schedule tasks with cron

Run backups automatically.

Encrypted backups with restic

Store dumps off the server.

Create a database and user

Recreate users after a restore.
Checked against the official MySQL mysqldump documentation and Red Hat MariaDB backup guide on 4 November 2026; dump, gzip and restore commands tested on Ubuntu 24.04 LTS with MariaDB 10.11. Commands can differ on other distributions or releases.

​
Need Extra Help?

If you encounter any issues, our support team is ready to assist:

​
Save on Your Hosting

Ready to upgrade your hosting? Use code KB20 at checkout for 20% off your first order!
Last Updated: November 2026