How to back up MySQL databases using cron jobs

Automated database backups using cron jobs

There are two methods for scheduling cron jobs to do MySQL database backups. You have two options: either use a configuration file to hold the MySQL login credentials, or provide the information directly in the cron job command.

Method #1: 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:

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

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.

• The single quote (‘) characters around the password ensure that any special characters are processed correctly.
• When the command runs successfully, no output is generated. However, if there is an error or misconfiguration, depending on your account configuration you may receive an e-mail message that contains the command output. To suppress e-mail messages when the command fails, add the following text to the end of the cron job command:Copy2>/dev/nullMost people, however, want to be notified when a cron job fails.

For information about how to use cPanel to create a cron job, please see this article.

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

Alternatively, you can create a configuration file in your home directory that stores MySQL login information. Using this method, you do not have to include login information in your cron job commands. To do this, follow these steps:

1. Create a file named .my.cnf in your /home/username directory, replacing username with your account username.Make sure you include the leading . in the filename.
2. Copy and paste the following text into the .my.cnf file. Replace dbusername with the database user and replace dbpassword with the database user’s password:[client] user = dbusername password = “dbpassword” host = localhost
3. Create a cron job that runs the following command. Replace dbname with the name of the database to back up, and replace path with the path to store the backup file. This example uses backup.sql for the backup’s filename, but you can use any filename you want:
   Copy/usr/bin/mysqldump –routines dbname > /path/backup.sqlWhen the command runs successfully, no output is generated. However, if there is an error or misconfiguration, depending on your account configuration you may receive an e-mail message that contains the command output. To suppress e-mail messages when the command fails, add the following text to the end of the cron job command:
   Copy2>/dev/nullMost people, however, want to be notified when a cron job fails.For information about how to use cPanel to create a cron job, please see this article.

Also Read

How to use .htaccess files

How to change the default directory index page using an .htaccess file

How to enable and disable directory index listings using an .htaccess file