Backing up MySQL databases automatically with a cron job

Your Ruachost hosting is already backed up automatically with JetBackup. See Restoring files, email and databases using JetBackup. If you also want your own copies, for example every few hours on a busy shop, or to download them elsewhere, a cron job can make them for you.

Putting a password in a cron job is risky, so save it in a private settings file instead.

In cPanel, open File Manager and go to your home folder (not public_html).
Click Settings and tick Show Hidden Files (dotfiles).
Create a file named .my.cnf with:
```
[client]
user=cpaneluser_dbuser
password="your-database-password"
```
Use a database user that has access to the databases you want to back up. See Finding your database details.
Right-click the file, choose Change Permissions, and set it to 600, so only your account can read it.

Step 2: Create a backup folder

In File Manager, create a folder named db-backups in your home folder. Keep it outside public_html, so the backups can’t be downloaded from your website.

Step 3: Add the cron job

In cPanel, open Cron Jobs. See Setting up cron jobs in cPanel.
Choose Once Per Day (or another schedule).

In Command, enter (replace cpaneluser_dbname with your database name):

/usr/bin/mysqldump --single-transaction --quick cpaneluser_dbname | gzip > $HOME/db-backups/cpaneluser_dbname-$(date +\%F-\%H\%M).sql.gz

Click Add New Cron Job.

The % signs must be written as \% in cron jobs, as shown.

Step 4: Delete old copies automatically

Add a second cron job, once per day, to delete backups older than 7 days:

find $HOME/db-backups -name "*.sql.gz" -mtime +7 -delete

Back up every database

To back up each database to its own file, use this command in Step 3 instead:

for db in $(mysql -N -e "SHOW DATABASES" | grep -v -E "^(information_schema|performance_schema)$"); do /usr/bin/mysqldump --single-transaction --quick "$db" | gzip > $HOME/db-backups/$db-$(date +\%F).sql.gz; done

The database user in .my.cnf needs access to every database.

Check it works

The next day, open db-backups in File Manager and check a file is there and isn’t 0 bytes. To restore one, unzip it and import it with phpMyAdmin. See Importing a large database.

Was this page helpful?