X
X
X
X

Knowledge Base

HomepageKnowledge BaseServer ManagementHow to Back Up and Restore a MySQL ...

How to Back Up and Restore a MySQL or MariaDB Database with mysqldump

In short: mysqldump is a command-line tool that dumps a MySQL or MariaDB database, tables and data included, into a single .sql file. You connect to the server over SSH, take a backup with mysqldump -u user -p database > backup.sql and restore it with mysql -u user -p database < backup.sql. This guide walks you step by step through the options needed for a consistent backup, compression, checking that the file is complete, restoring and the most common errors.

Before you start

  • You need SSH access to the server. If you are not sure how to connect, see our guide What is SSH and how to connect.
  • You need the database name, user name and password. On WordPress sites these are the DB_NAME, DB_USER and DB_PASSWORD lines in wp-config.php.
  • There must be enough free disk space for the backup. You can check it with df -h.
  • In MariaDB the tool's new name is mariadb-dump; the mysqldump name still works for compatibility and the options are the same (MariaDB documentation). For the differences between the two systems, see our MySQL vs MariaDB article.

Step 1: Connect without typing the password in the command

Writing the password as -pPassword stores it in your shell history. Type only -p instead and the tool will prompt for it. For scheduled backups, use a ~/.my.cnf file in your home directory that only you can read:

[client]
user=database_user
password=StrongPassword
chmod 600 ~/.my.cnf

When this file exists, mysqldump and mysql read the user name and password from it, so you do not need -u and -p in your commands.

Step 2: Back up the database

The basic command is:

mysqldump -u database_user -p --single-transaction --routines --default-character-set=utf8mb4 database_name > backup-$(date +%F).sql

What the options do:

  • --single-transaction: dumps InnoDB tables as a single consistent snapshot without locking them, so the site keeps working during the backup. This consistency is not provided for MyISAM tables.
  • --routines: also includes stored procedures and functions. Triggers are included by default.
  • --default-character-set=utf8mb4: keeps accented characters and emoji intact. See our utf8 vs utf8mb4 article for details.
  • $(date +%F): adds today's date to the file name (e.g. backup-2026-11-19.sql) so backups do not overwrite each other.

To back up several databases into one file, use --databases db1 db2; the file will then also contain CREATE DATABASE statements.

Diagram of the steps to back up with mysqldump, compress, verify and restore

Step 3: Compress the backup

SQL files are plain text and compress very well. Pipe the output straight into gzip to get a compressed backup without leaving an uncompressed copy on disk:

mysqldump -u database_user -p --single-transaction --routines --default-character-set=utf8mb4 database_name | gzip > backup-$(date +%F).sql.gz

Step 4: Check that the backup is complete

A command finishing without an error is not enough on its own; a dump that stops halfway still produces a file. Run these three checks:

ls -lh backup-*.sql.gz
gzip -t backup-2026-11-19.sql.gz
zcat backup-2026-11-19.sql.gz | tail -n 1

The file size should look reasonable, gzip -t should print nothing, and the last line should start with -- Dump completed on. If that line is missing, the dump was cut short.

Step 5: Copy the backup off the server

A backup that only lives on the same server can be lost together with the server. Download it from a terminal on your own computer (use -P if your SSH port is different):

scp -P 22 user@server_ip:~/backup-2026-11-19.sql.gz .

If you prefer a file transfer program to the command line, use the SFTP connection described in our FTP file transfer guide.

Step 6: Restore the backup

Restoring into an empty database is the safest option. After creating the database and its user in your control panel:

mysql -u database_user -p target_database < backup-2026-11-19.sql

For a compressed backup:

zcat backup-2026-11-19.sql.gz | mysql -u database_user -p target_database

Note: by default mysqldump writes a DROP TABLE statement before each table. If you restore into a database that already has data, tables with the same names are dropped and recreated from the backup. Back up the current state as well before restoring into a live database.

Common errors and fixes

Error Fix
Access denied for user The user name, password or database name is wrong, or the user has no privileges on this database. Verify the details in your control panel.
you need (at least one of) the PROCESS privilege(s) In MySQL 8 this privilege is required to dump tablespace information. Add --no-tablespaces to the command; website databases do not need that information.
Unknown collation: 'utf8mb4_0900_ai_ci' A backup taken from MySQL 8 is being loaded into a MariaDB version that does not know this collation. Replace it with sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' backup.sql and try again.
Unknown command '\-' on line 1 Recent MariaDB versions add a "sandbox mode" line at the top of the dump, which the MySQL client does not understand. Remove it with sed -i '1{/sandbox mode/d}' backup.sql.
Got a packet bigger than 'max_allowed_packet' The backup contains very large rows. Try mysql --max-allowed-packet=256M ... when restoring; if the server-side limit is also low, ask the support team for help.

Step 7: Automate the backup

Instead of running the backup by hand every day, automate it with a script and a scheduled task. Once ~/.my.cnf is in place, create a script called ~/db-backup.sh:

#!/bin/sh
mkdir -p ~/backups
mysqldump --single-transaction --routines --default-character-set=utf8mb4 database_name | gzip > ~/backups/backup-$(date +%F).sql.gz
find ~/backups -name 'backup-*.sql.gz' -mtime +7 -delete

The last line deletes backups older than 7 days. Make the script executable with chmod 700 ~/db-backup.sh and add it with crontab -e to run every night at 03:00:

0 3 * * * /home/user/db-backup.sh

If you put the command directly into a crontab line, the % character has a special meaning in crontab and must be written as \%; using a script avoids this problem. For the scheduling syntax, see our Linux cron job guide. Backups kept on the server are not enough on their own; copy them off the server regularly as well.

If your shared hosting account has no SSH access, or you hit an error during restore that you cannot solve, open a support ticket and include the error message.

 

Frequently Asked Questions

Does the site go down while mysqldump runs?

With --single-transaction, InnoDB tables are not locked and the site keeps working. On large databases disk usage can rise during the backup, so running it at low-traffic hours is a good habit.

Can I back up just one table?

Yes. List the table names after the database name: mysqldump -u user -p database_name wp_posts wp_postmeta > tables.sql.

The backup file will not open on my computer. Is it corrupted?

A .sql.gz file is compressed; open it with an archive tool first. Very large .sql files open slowly in ordinary text editors, which does not mean the file is damaged. Check integrity with gzip -t and the "Dump completed" line at the end.

Can I use phpMyAdmin instead of mysqldump?

For small databases, phpMyAdmin's export feature is enough. For large databases the command line is more reliable, because operations run through the browser can hit PHP time and size limits.

Can't find the information you are looking for?

Create a Support Ticket
Did you find it useful?
(3 times viewed / 0 people found it helpful)

Powered by WISECP
Top