To automatically back up a MySQL database, schedule mysqldump to run every night and save a dated, compressed copy, for example with a cron job on the server: mysqldump --single-transaction -u user -p dbname | gzip > backup.sql.gz. Then get those copies off the server, because a backup that only lives next to the database disappears with it. The simplest complete setup is a backup tool on your PC that pulls a database dump and your site files to your own drive on a schedule.
This guide covers the commands, three ways to automate them, how to keep copies safe and how to restore.
The basic mysqldump command
mysqldump is MySQL's standard export tool. It writes the database out as a .sql file of statements that can rebuild it.
mysqldump -u myuser -p mydatabase > mydatabase.sql
Useful options:
| Option | What it does |
|---|---|
--single-transaction | Takes a consistent snapshot of InnoDB tables without locking them, so the site keeps running during the dump. |
--routines --triggers | Includes stored procedures, functions and triggers. Triggers are included by default; routines are not. |
--no-tablespaces | Avoids a privilege error on many shared hosts where your user lacks the PROCESS privilege. |
--default-character-set=utf8mb4 | Keeps special characters and emoji intact. |
| gzip | Compresses the output. SQL text often shrinks to a fraction of its size. |
On MariaDB, which many hosts use instead of MySQL, the same command works; newer versions also provide it as mariadb-dump.
Keep the password out of the command
A password typed on the command line can show up in process lists and shell history, and -p on its own prompts for it, which does not work in an automated job. Instead, create a file called .my.cnf in your home directory on the server:
Put a [mysqldump] section heading on the first line, then two lines below it: user=myuser and password=yourpassword. Restrict the file so only you can read it with chmod 600 ~/.my.cnf. mysqldump reads it automatically, so the scheduled command no longer needs -u or -p.
Option 1: a cron job on the server
If you have SSH access, or your control panel has a Cron Jobs page (cPanel does), you can schedule the dump on the server.
- Create a folder for backups outside your public web folder, for example
~/db-backups. - Add a cron job that runs nightly at 2 a.m.:
0 2 * * * mysqldump --single-transaction --no-tablespaces mydatabase | gzip > ~/db-backups/mydatabase-$(date +\%F).sql.gz - Note the backslash before
%. In crontab, a bare%is treated as a new line and breaks the command. - Add a second job to delete old copies, for example files older than 14 days:
30 2 * * * find ~/db-backups -name "*.sql.gz" -mtime +14 -delete
This gives you dated copies, but they are still on the same server as the database. If the account is hacked, suspended or the server fails, they go too. You need to copy them off.
Option 2: phpMyAdmin export by hand
Without SSH or cron, phpMyAdmin is the fallback: select the database, click Export, choose SQL and download. It works, but it is manual, and manual backups stop happening. Fine for a one-off before an update; not a plan.
Option 3: pull backups to your own computer
The most robust setup creates the dump and stores it somewhere independent of your host. A Windows backup tool that understands websites can do both on a schedule.
Rocket Backups backs up MySQL databases and website files from your hosting account on a schedule and saves them to your own drive, NAS or other storage. You enter the connection details once and choose a time, such as 3 a.m. nightly. Each run saves a database dump alongside an incremental copy of the site files, so you always have a matching set of files and database for any night. Retention rules keep as many dated versions as you want and remove older ones automatically.
Because database dumps contain user details, order data and password hashes, encrypt them. Rocket Backups can protect each backup with AES-256 encryption and a password, and email you if a job fails, which is the part homemade cron scripts usually lack.
For backing up the rest of the site, see how to back up a website to your computer.
How to restore a MySQL backup
- If the file ends in .gz, extract it first to get the .sql file.
- Create an empty database, or empty the existing one if you are replacing it. Make sure your database user has access to it.
- Import it. In phpMyAdmin, select the database, click Import, choose the .sql file and click Import (or Go). From the command line:
mysql mydatabase < mydatabase.sql - Check your site's configuration file points to the right database name, user and password.
- Load the site and check recent content is there.
phpMyAdmin has an upload size limit set by the host. For larger databases, use the command line, ask your host, or import in parts.
Common mysqldump errors
| Error | Likely fix |
|---|---|
| Access denied; you need the PROCESS privilege | Add --no-tablespaces, which is common on shared hosting. |
| Access denied for user | Check the user name and password in .my.cnf, and that the user has rights on that database. |
| Got packet bigger than max_allowed_packet | Add --max-allowed-packet=512M or ask your host to raise the server limit. |
| Empty or tiny .sql file | The command failed. Run it by hand over SSH to see the error, and check cron's email or log. |
| Command not found in cron | Use the full path, such as /usr/bin/mysqldump, because cron has a minimal PATH. |
Best practices
- Back up daily for any site where content, orders or users change. Hourly for busy stores is worth considering.
- Keep at least 14 to 30 days of versions. Problems such as a hack or a bad plugin are often noticed late.
- Store copies off the server, ideally in two places.
- Back up files and database together so they match when you restore.
- Test a restore to a local or staging database every few months.
- Back up before every update of your CMS, plugins or theme.
If something has already gone wrong, our guide to restoring a website after a hack explains how to choose a clean backup.
Frequently asked questions
What is the command to back up a MySQL database?
The standard tool is mysqldump, for example: mysqldump -u username -p database_name > backup.sql. It writes the database out as a SQL file you can import later.
Can I back up a MySQL database while the site is live?
Yes. For InnoDB tables, the mysqldump --single-transaction option takes a consistent copy without locking the site for the whole dump.
How do I restore a MySQL backup?
Import the .sql file into an empty database with phpMyAdmin's Import tab or the mysql command line, then make sure your site's configuration points to that database.
