MySQL Backup and Restore with mysqldump
Back up and restore MySQL and MariaDB with mysqldump or mariadb-dump: gzip, ~/.my.cnf, large imports, daily cron backups, Docker and testing restores.

Contents
- mysqldump or mariadb-dump?
- Credentials without a password on the command line
- Backing up one, several or all databases
- Compressing with a timestamp
- Restoring a dump
- Large imports and max_allowed_packet
- A daily backup script with rotation
- Test your restores
- Databases in Docker
- phpMyAdmin and panel exports on shared hosting
- Physical backups for large databases
- Database backups on Nodesty servers
A mysqldump backup turns your MySQL or MariaDB database into a plain SQL file that you can compress, copy anywhere and load back with a single command. It's the simplest and most portable way to protect the data behind WordPress, Laravel, FiveM servers and most web applications.
This guide covers dumping one, several or all databases, safe options for InnoDB, keeping passwords off the command line, restoring (including into a different database name), large imports, a daily backup script with rotation, Docker, panel exports and when physical backups make more sense. For installing the database itself, see our How to install MariaDB guide; for the bigger picture of backups, see Server backups.
mysqldump or mariadb-dump?
Since MariaDB 10.5, the tools are called mariadb-dump and mariadb. On many systems the old names mysqldump and mysql still exist as links to the same programs, but newer MariaDB releases print a deprecation warning when you use them, and they may be removed in the future. On MySQL itself, the tools are still mysqldump and mysql.
The examples below use the MariaDB names. If you run MySQL, replace mariadb-dump with mysqldump and mariadb with mysql; the options are the same.
Credentials without a password on the command line
A password passed as -p'secret' ends up in your shell history and can be seen in the process list. There are two better ways.
On Ubuntu and Debian, the MariaDB root user uses unix_socket authentication by default: running the tools as root (or with sudo) needs no password at all. This also works in root's crontab.
For any other user, put the credentials in ~/.my.cnf:
[client]
user = backup
password = a-strong-password
chmod 600 ~/.my.cnf
The client tools read the [client] section automatically. A dedicated backup user only needs read rights; on MariaDB you can create one like this:
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'a-strong-password';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES ON *.* TO 'backup'@'localhost';
Backing up one, several or all databases
A single database:
mariadb-dump --single-transaction --routines --events app > app.sql
Several databases, or every database on the server:
mariadb-dump --single-transaction --routines --events --databases app shop > two.sql
mariadb-dump --single-transaction --routines --events --all-databases > all.sql
| Option | What it does |
|---|---|
--single-transaction | Consistent snapshot of InnoDB tables without locking them |
--quick | Streams rows instead of loading whole tables into memory; on by default |
--routines | Includes stored procedures and functions, which are left out by default |
--events | Includes scheduled events, which are left out by default |
--triggers | Includes triggers; on by default, listed here only so it's not turned off |
--databases | Adds CREATE DATABASE and USE lines so the dump restores into the same names |
--all-databases | Dumps every database, including the mysql system database with users |
--single-transaction only protects InnoDB tables. Older MyISAM tables can still change during the dump; avoid running schema changes such as ALTER TABLE while a dump is in progress.
To dump only some tables, list them after the database name. You can also skip a large log table, or dump the structure without data:
mariadb-dump --single-transaction app users orders > tables.sql
mariadb-dump --single-transaction --ignore-table=app.logs app > app-without-logs.sql
mariadb-dump --no-data app > app-schema.sql
Compressing with a timestamp
SQL dumps compress very well. Pipe the dump straight into gzip and put the date and time in the file name:
mariadb-dump --single-transaction --routines --events app | gzip > app-$(date +%F_%H%M).sql.gz
This creates a file such as app-2026-11-12_0315.sql.gz. A complete dump ends with a -- Dump completed line; checking for it is a quick way to catch a dump that stopped halfway:
zcat app-2026-11-12_0315.sql.gz | tail -n 1
Restoring a dump
A dump of a single database (taken without --databases) contains no CREATE DATABASE statement, so create the database first:
mariadb -e "CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
mariadb app < app.sql
For a compressed file:
gunzip < app-2026-11-12_0315.sql.gz | mariadb app
Because the file doesn't name the database, you can load it into a different name, for example app_test, without touching the live database. Dumps taken with --databases or --all-databases restore into their original names, so you run them without a database name: mariadb < all.sql.
Keep in mind that a restore overwrites tables with the same names: each table in the dump is dropped and created again.
Large imports and max_allowed_packet
Large dumps can fail with MySQL server has gone away or Got a packet bigger than 'max_allowed_packet' bytes. A single statement in the dump is bigger than the server allows. Raise the limit in the [mysqld] section (on Debian and Ubuntu in /etc/mysql/mariadb.conf.d/50-server.cnf, on AlmaLinux in /etc/my.cnf.d/mariadb-server.cnf):
[mysqld]
max_allowed_packet = 256M
Restart with sudo systemctl restart mariadb, then pass the same limit to the client:
gunzip < big.sql.gz | mariadb --max-allowed-packet=256M app
Run long imports inside tmux or screen so a dropped SSH connection doesn't stop them halfway. With the pv tool you also get a progress bar: pv big.sql.gz | gunzip | mariadb app.
A daily backup script with rotation
Save the following as /root/db-backup.sh. It dumps every database into its own file, keeps 7 days of backups and copies them to another server:
#!/bin/bash
set -eo pipefail
umask 077
DIR=/var/backups/mysql
STAMP=$(date +%F_%H%M)
mkdir -p "$DIR"
for DB in $(mariadb -N -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|sys)$'); do
mariadb-dump --single-transaction --routines --events "$DB" | gzip > "$DIR/$DB-$STAMP.sql.gz"
done
find "$DIR" -name '*.sql.gz' -mtime +7 -delete
rsync -a "$DIR/" backup@198.51.100.20:/srv/db-backups/
pipefail makes the script stop if mariadb-dump fails, not only if gzip fails, and umask 077 keeps the dumps readable only by root. Make it executable and add it to root's crontab with sudo crontab -e:
sudo chmod 700 /root/db-backup.sh
15 3 * * * /root/db-backup.sh >> /var/log/db-backup.log 2>&1
We explain the cron syntax in What is cron? and passwordless rsync over SSH in our rsync guide. Dumps that stay only on the same server are lost with it, and dumps that are never deleted fill the disk; see Linux disk full if that has already happened.
Test your restores
A dump you have never restored is a guess. Once a month, load the latest dump into a test database and check that the tables and recent rows are there:
mariadb -e "CREATE DATABASE app_test"
gunzip < /var/backups/mysql/app-2026-11-12_0315.sql.gz | mariadb app_test
mariadb -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'app_test'"
mariadb -e "DROP DATABASE app_test"
The same steps are how you move a database to a new server; our website migration guide covers the rest of the move.
Databases in Docker
If MariaDB runs in a container, run the dump tool inside it and write the output on the host. Using sh -c reads the password from the container's own environment, so it doesn't appear on the host command line:
docker exec db sh -c 'exec mariadb-dump --single-transaction --all-databases -uroot -p"$MARIADB_ROOT_PASSWORD"' | gzip > all.sql.gz
To restore, add -i so the file can be passed in:
gunzip < all.sql.gz | docker exec -i db sh -c 'exec mariadb -uroot -p"$MARIADB_ROOT_PASSWORD"'
Here db is the container name. For the official MySQL image, use mysqldump, mysql and $MYSQL_ROOT_PASSWORD.
phpMyAdmin and panel exports on shared hosting
On shared hosting with cPanel or DirectAdmin, the easiest way is phpMyAdmin: select the database, open Export, keep the SQL format and download the file. Both panels can also download a database backup from their own backup or MySQL management pages.
This works well for small databases. With larger ones, browser exports and especially imports run into PHP upload size and execution time limits and can stop halfway. If your hosting gives you SSH or a terminal in the panel, use the mysqldump commands above instead.
Physical backups for large databases
For databases of tens or hundreds of gigabytes, dumping and restoring SQL takes hours. Physical backup tools copy the data files of a running server consistently and restore much faster: mariadb-backup (Mariabackup) for MariaDB and Percona XtraBackup for MySQL. On Debian and Ubuntu, mariadb-backup is a separate package:
sudo apt install mariadb-backup
sudo mariadb-backup --backup --target-dir=/var/backups/mariadb-full
sudo mariadb-backup --prepare --target-dir=/var/backups/mariadb-full
Simply copying /var/lib/mysql while the server is running is not a backup: part of the data is still in memory and files change during the copy, so the copy may not start at all. A plain file copy is only safe when the service is stopped.
If losing even a few hours of data is too much, enable binary logs. Together with a full backup they let you replay changes up to a chosen moment (point-in-time recovery) with mariadb-binlog or mysqlbinlog.
Database backups on Nodesty servers
Nodesty AMD Ryzen 9 9950X VPS and AMD EPYC 7763 VPS plans come with NVMe disks and full root access, so you can run every command in this guide, schedule your own dumps and install mariadb-backup. Daily backups of the whole server are free on VPS plans and are kept for 7 days; your own database dumps, copied off the server, are the independent extra copy.
Frequently Asked Questions
Should I use mysqldump or mariadb-dump?
On MariaDB, use mariadb-dump and mariadb; these are the tool names since MariaDB 10.5. The old mysqldump and mysql names often still exist as links, but newer MariaDB versions warn that they are deprecated. On MySQL itself the tools are still called mysqldump and mysql. The options in this guide work the same way with both.
Does mysqldump lock the database?
With the --single-transaction option, InnoDB tables are backed up at one consistent point in time without locking, and the site keeps working during the dump. Tables that use the older MyISAM engine aren't covered by this and can change during the dump; converting them to InnoDB is the lasting fix.
How do I restore a dump into a database with a different name?
Take the dump without the --databases option so it contains no CREATE DATABASE or USE statements. Then create the new database and import the file into it with mariadb new_name < dump.sql. A dump taken with --databases or --all-databases always goes back into the original database names.
My import fails with MySQL server has gone away. What should I do?
This usually means a single statement in the dump is larger than max_allowed_packet. Raise max_allowed_packet in the [mysqld] section of the server configuration, restart the service and pass a matching --max-allowed-packet value to the mariadb client during the import.
Can I just copy the /var/lib/mysql folder as a backup?
Not while the database is running. Part of the data is still in memory and the files change during the copy, so the result often won't start or contains corrupted tables. Use a dump, or a physical backup tool such as mariadb-backup that is built to copy a running server consistently.
How often should I back up my database?
Base it on how much data you can afford to lose. A daily dump is enough for most sites and game servers; a busy shop may need dumps every few hours or binary logs for point-in-time recovery. Always keep a copy off the server and test a restore regularly.
About the author
Nodesty Team · Infrastructure & Support
The Nodesty team runs our servers, network and DDoS protection day to day and answers customer support requests around the clock. These guides come from that work.
Related articles

How to Install a LEMP Stack on Ubuntu and Debian
How to install a LEMP stack on Ubuntu and Debian: Nginx, MariaDB and PHP-FPM, the server block, the PHP socket, permissions, HTTPS and common errors.

How to Use tmux: Keep Programs Running After SSH Closes
Learn how to use tmux on a Linux server: keep programs running after SSH closes, detach and reattach sessions, split panes, scroll and fix common errors.

How to Deploy a Node.js App with PM2 and Nginx
How to deploy a Node.js app on a Linux VPS: Node.js LTS, npm ci, PM2 with autostart and reload, an Nginx reverse proxy, HTTPS, updates and common errors.