# Backing up MySQL databases automatically with a cron job

> Make a compressed copy of your MySQL databases every day with a cron job in cPanel, and delete old copies automatically.

Source: https://helpcenter.ruachost.com/cpanel/databases/backing-up-mysql-databases-automatically/

Your Ruachost hosting is already backed up automatically with **JetBackup**. See [Restoring files, email and databases using JetBackup](https://helpcenter.ruachost.com/cpanel/files/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.

> **Caution:**
Backups use disk space in your account. Keep only a few days' copies, and check your disk usage. See [My hosting account is out of disk space](https://helpcenter.ruachost.com/getting-started/website-troubleshooting/hosting-account-out-of-disk-space/).

## Step 1: Save your database login safely

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

1. In cPanel, open **File Manager** and go to your home folder (not `public_html`).
2. Click **Settings** and tick **Show Hidden Files (dotfiles)**.
3. Create a file named `.my.cnf` with:

   ```ini
   [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](https://helpcenter.ruachost.com/cpanel/databases/finding-your-database-details/).
4. 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

1. In cPanel, open **Cron Jobs**. See [Setting up cron jobs in cPanel](https://helpcenter.ruachost.com/cpanel/advanced/setting-up-cron-jobs-in-cpanel/).
2. Choose **Once Per Day** (or another schedule).
3. In **Command**, enter (replace `cpaneluser_dbname` with your database name):

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

4. 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:

```bash
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:

```bash
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](https://helpcenter.ruachost.com/cpanel/databases/importing-a-large-database/).

> **Tip:**
Copy important backups off the server from time to time, for example to your computer or cloud storage. See [Using Google Drive to back up and restore data](https://helpcenter.ruachost.com/cpanel/files/using-google-drive-to-backup-and-restore-data-in-ruachost/).

## Related articles

- [Managing MySQL databases and users](https://helpcenter.ruachost.com/cpanel/databases/managing-mysql-databases-and-users/)
- [Using cPanel API tokens to automate your hosting](https://helpcenter.ruachost.com/cpanel/advanced/using-cpanel-api-tokens/)