Back up and restore MariaDB with mysqldump

A backup is only useful if you can restore it. This guide dumps MariaDB databases into compressed SQL files with mysqldump, then restores one into a fresh server. Ubuntu 24.04 ships MariaDB 10.11, which uses the same tools as MySQL.

Before you begin

An Ubuntu 24.04 VPS with MariaDB installed. If it is not, run sudo apt install -y mariadb-server mariadb-client first.
SSH access as a sudo user, such as alex.
Free disk space at least equal to the size of your databases. Check it with df -h ~.
A second machine, or at least a separate folder, to store copies. A backup on the same disk does not protect you from disk failure.

Step 1: Check which databases exist

On Ubuntu, sudo mariadb logs in as the database root user through the operating system's socket, so no password is needed. That is convenient on the server itself. The -e option runs one statement and exits.

sudo mariadb -e "SHOW DATABASES;"

+--------------------+
| Database           |
+--------------------+
| appdb              |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

The three names beginning with information_schema, mysql and performance_schema are system databases. The mysql one holds user accounts, so treat its dumps as sensitive.

Step 2: Create a backup folder

Keep backups in one place so they are easy to find and copy later.

mkdir -p ~/backups/mariadb

Step 3: Dump one database and compress it

This command writes a complete, compressed copy of the appdb database. Each option has a job:

--single-transaction takes a consistent snapshot of InnoDB tables without locking them, so the site keeps running.
--routines and --events include stored procedures and scheduled events.
--databases adds a CREATE DATABASE line, so the restore does not need the name typed again.
| gzip > compresses the output and saves it to the file.

sudo mysqldump --single-transaction --routines --events --databases appdb | gzip > ~/backups/mariadb/appdb-2026-10-31.sql.gz
ls -lh ~/backups/mariadb

-rw-r--r-- 1 alex alex 2.1M Oct 31 09:14 appdb-2026-10-31.sql.gz

Because the file is written by your own shell, it belongs to your user. Restrict it to you, since it contains your data:

chmod 600 ~/backups/mariadb/appdb-2026-10-31.sql.gz

Step 4: Dump every database (optional)

To capture everything, including the user accounts, use --all-databases instead of a single name:

sudo mysqldump --single-transaction --routines --events --all-databases | gzip > ~/backups/mariadb/all-2026-10-31.sql.gz
chmod 600 ~/backups/mariadb/all-2026-10-31.sql.gz

Tip: Name files with the date, as shown. Old backups then sort correctly, and you can delete the oldest ones on a schedule without guessing.

Step 5: Copy the backup off the server

From your own computer or a second server, copy the file with scp, which uses SSH. Replace the address with your backup machine's address.

scp alex@203.0.113.10:~/backups/mariadb/appdb-2026-10-31.sql.gz ~/Downloads/

Run this on the machine that will hold the copy. The source path is on the server, and the destination is local.

Step 6: Restore into a fresh server

On a new VPS, install MariaDB, copy the file across, and restore it. Because the dump includes CREATE DATABASE, you do not need to create the database yourself. The gunzip -c command decompresses the file to the screen, and the pipe feeds it to MariaDB.

sudo apt update
sudo apt install -y mariadb-server mariadb-client
gunzip -c ~/appdb-2026-10-31.sql.gz | sudo mariadb
sudo mariadb -e "SHOW TABLES;" appdb

The last command lists the tables in the restored appdb database. If you see your tables, the restore worked.

Careful: Restoring over a live database replaces its contents. Never run a restore against a production database without a fresh backup first, and never run DROP DATABASE unless you are sure of the name.

Troubleshooting

Access denied when you run as a normal user: use sudo mariadb on the server, or pass -u and -p with a database account that has the needed privileges.
mysqldump: command not found: install the client tools with sudo apt install -y mariadb-client. On Ubuntu, mariadb-dump is the same program under its newer name.
Table already exists during restore: the target already holds that data. Restore into an empty server, or use a separate test database.
Restore finishes instantly with nothing in it: test the archive with gzip -t on the file. No output means it is intact.

Next steps

Schedule the dump with the cron guide so it runs every night, and follow the restore testing guide to prove that the copies really work. Keep at least one copy somewhere other than this server.