How to Backup MySQL Databases Using Cron Jobs

This article describes how to configure a cron job to back up a MySQL database to a file at predefined intervals.

There are two methods for running cron jobs to backup a MySQL database. You can either include MySQL login information in the cron job command itself, or you can store the MySQL login information in a configuration file.

How to Back Up MySQL Databases Using Cron Jobs

• Include MySQL login information in the cron job command
  
  You can run scheduled backups of a MySQL database by creating a cron job that runs the following command:

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

  Click here to find the step-by-step process of configuring a Cron job in your cPanel interface

  Replace dbusername with the database user, dbpassword with the database user’s password,  dbname with the database to back up, and path with the path where you want to store the backup file. This example uses backup.sql for the backup’s filename, but you can use any filename you want.

  Note: The single quotation marks (‘) around the password ensure that any special characters are correctly processed.

  When the command is executed successfully, no output is produced. If there is an error or misconfiguration, you may receive an e-mail message containing the command output, depending on your account configuration. Add the following text to the end of the cron job command to suppress e-mail messages when the command fails:

  2>/dev/null

  Most people, however, want to be notified when a cron job fails.

   

  

  Cron Jobs in cPanel

• Contents hide

  1 Use a configuration file to store MySQL login information

  2 Our Guiding Principles

  Use a configuration file to store MySQL login information

  Alternatively, you can store MySQL login information in a configuration file in your home directory. You do not need to include login information in your cron job commands if you use this method. To do this, follow these steps:

In your /home/username directory, create a file called .my.cnf, replacing username with your account username.

Copy and paste the text below into the .my.cnf file. Replace dbusername with the name of the database user and dbpassword with the password of the database user:

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

Create a cron job that runs the command below. Replace dbname with the name of the backup database, and path with the location of the backup file. The backup filename in this example is backup.sql, but you can use any filename you want:

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

Cron Jobs in cPanel

Note: As mentioned before you can choose whether to add the following text to the end of the cron job command to suppress e-mail messages:

2>/dev/null

Conclusion

Congratulations! You have learned how to back up MySQL databases using cron jobs.

Our Guiding Principles

• Provide consistent, stable, and reliable web hosting services.
• Ensure rapid ticket response and quick resolutions to issues.
• Never saturate or over-provision servers to ensure stability and speed for our customers.
• Use only high-quality enterprise-class hardware to ensure minimal downtime from hardware failures.
• Provide clear pricing with no hidden fees or gotchas.