Backing up MySQL databases using cron jobs

Backing up MySQL databases using cron jobs
This article explains how to set up a cron job to automatically back up a MySQL database at scheduled intervals.

Automated database backups using cron jobs
There are two ways to run cron jobs for MySQL backups: including login information in the command or using a configuration file.

Method 1: Include MySQL login information in the cron job command
Create a cron job with the following command:

/usr/bin/mysqldump --routines -u dbusername -p'dbpassword' dbname > ${HOME}/path/backup.sql

Replace dbusername with the database user, dbpassword with the password, dbname with the database name, and path with the backup directory. You can choose any filename for the backup.

Tip
Enclosing the password in single quotes ensures special characters are processed correctly.

To suppress email notifications when the cron job fails, add:

2>/dev/null

Most users, however, prefer to receive notifications if a cron job fails.

For guidance on creating cron jobs in cPanel, see this article.

Method 2: Use a configuration file to store MySQL login information

1. Create a file named .my.cnf in your /home/username directory (replace username with your account username). Ensure the filename starts with a dot.

2. Add the following, replacing dbusername and dbpassword with your credentials:

   [client]
   user = dbusername
   password = "dbpassword"
   host = localhost

3. Create a cron job with this command (replace dbname and path as needed):

   /usr/bin/mysqldump --routines dbname > /path/backup.sql

Tip
Successful commands produce no output. Errors may trigger an email depending on your configuration. To suppress these emails, append 2>/dev/null to the command.

For guidance on using cPanel to create cron jobs, see this article.

More information
For details on mysqldump, visit MySQL documentation.