Rocket Backups
Windows 10 & 11 Buy license · $69.95
Websites

How to Automatically Back Up a MySQL Database Every Night

Automatically back up a MySQL database every night: mysqldump commands, cron and phpMyAdmin options, and how to keep dated copies on your own drive.

Updated 5 minute read The Rocket Backups team

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:

OptionWhat it does
--single-transactionTakes a consistent snapshot of InnoDB tables without locking them, so the site keeps running during the dump.
--routines --triggersIncludes stored procedures, functions and triggers. Triggers are included by default; routines are not.
--no-tablespacesAvoids a privilege error on many shared hosts where your user lacks the PROCESS privilege.
--default-character-set=utf8mb4Keeps special characters and emoji intact.
| gzipCompresses 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.

  1. Create a folder for backups outside your public web folder, for example ~/db-backups.
  2. 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
  3. Note the backslash before %. In crontab, a bare % is treated as a new line and breaks the command.
  4. 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

  1. If the file ends in .gz, extract it first to get the .sql file.
  2. Create an empty database, or empty the existing one if you are replacing it. Make sure your database user has access to it.
  3. 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
  4. Check your site's configuration file points to the right database name, user and password.
  5. 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

ErrorLikely fix
Access denied; you need the PROCESS privilegeAdd --no-tablespaces, which is common on shared hosting.
Access denied for userCheck the user name and password in .my.cnf, and that the user has rights on that database.
Got packet bigger than max_allowed_packetAdd --max-allowed-packet=512M or ask your host to raise the server limit.
Empty or tiny .sql fileThe command failed. Run it by hand over SSH to see the error, and check cron's email or log.
Command not found in cronUse 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.

Get three guides like this every month, plus software update notices.Subscribe →