Back up and restore MySQL or MariaDB with mysqldump

A MySQL backup with mysqldump writes a database out as SQL statements you can replay to rebuild it anywhere. It works the same on MySQL and MariaDB, needs no extra software and is the standard way to back up and move databases on a VPS.

Before you start

• SSH access to the server and a MySQL user with SELECT, LOCK TABLES, SHOW VIEW, TRIGGER and EVENT privileges (root works).
• Free disk space roughly equal to the database size (check with df -h).

Store credentials safely

Avoid typing the password on the command line, where it appears in shell history and the process list. Create ~/.my.cnf:

[client]
user=backup
password=YourStrongPassword

Then lock it down with chmod 600 ~/.my.cnf. mysqldump and mysql read it automatically.

Dump a single database

mysqldump --single-transaction --routines --triggers --events shopdb > shopdb.sql

• --single-transaction takes a consistent snapshot of InnoDB tables without locking the site. (MyISAM tables are not covered; convert them to InnoDB.)
• --routines --triggers --events include stored procedures, triggers and scheduled events, which are easy to forget.

Compress on the fly to save space:

mysqldump --single-transaction --routines --triggers shopdb | gzip > shopdb-$(date +%F).sql.gz

Dump all databases

mysqldump --single-transaction --routines --triggers --events --all-databases | gzip > all-$(date +%F).sql.gz

For migrations, per-database files are easier to restore selectively than one large dump.

Restore a backup

1. Create the empty database if it does not exist:
   Copy

   mysql -e "CREATE DATABASE shopdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

2. Import the dump:
   Copy

   mysql shopdb < shopdb.sql

   or from a compressed file:
   Copy

   gunzip < shopdb-2025-01-01.sql.gz | mysql shopdb

3. Grant the application user access again if this is a new server, and update the app's config.

Automate daily backups

Save this as /usr/local/bin/mysql-backup.sh:

#!/bin/bash
DIR=/backup/mysql
mkdir -p "$DIR"
for db in $(mysql -N -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|sys)$'); do
  mysqldump --single-transaction --routines --triggers --events "$db" | gzip > "$DIR/$db-$(date +%F).sql.gz"
done
find "$DIR" -name '*.sql.gz' -mtime +7 -delete

Make it executable and add a cron job (as root, so /root/.my.cnf is used):

0 3 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Copy the folder off the server too, for example with rsync, so a disk failure does not take the backups with it.

Check the backup

• Look at the end of the file: zcat file.sql.gz | tail -1 should show -- Dump completed.
• Restore into a test database occasionally and compare row counts.

Troubleshooting

• Access denied ... when using LOCK TABLES: grant LOCK TABLES or use --single-transaction on InnoDB.
• ERROR 2006 MySQL server has gone away on import: raise max_allowed_packet (e.g. 256M) on the server and client.
• Unknown collation utf8mb4_0900_ai_ci: the dump came from MySQL 8 and the target is MariaDB or older MySQL. Replace it with utf8mb4_unicode_ci using sed before importing.
• Definer errors: views or triggers reference a user that does not exist on the new server. Create the user or edit the DEFINER clauses.

mysqldump FAQ

Is mysqldump fine for large databases?

Up to tens of gigabytes, yes. Beyond that, restores get slow; physical tools such as Percona XtraBackup or Mariabackup are faster.

Does mysqldump lock my website?

Not with --single-transaction on InnoDB tables. MyISAM tables are locked during their dump.

How do I back up a database on cPanel hosting?

Use cPanel → Files → Backup to download a database, or phpMyAdmin's Export tab. MdmHost hosting also takes daily JetBackup backups.

Next steps

• Manage MySQL databases and users in cPanel
• Copy backups off-site with rsync
• Schedule tasks with cron jobs