How to back up MySQL databases using cron jobs

This article describes how to set up a cron job that automatically backs up a MySQL database to a file at specific intervals.

Automated database backups using cron jobs

There are two ways to run cron jobs that back up a MySQL database. You can either include MySQL login information in the cron job command itself, or you can use a configuration file to store the MySQL login information.

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:

/usr/bin/mysqldump -u dbusername -p'dbpassword' dbname > /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.

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:
   

   mysqldump dbname > /path/backup.sql

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

More Information

For more information about the mysqldump program, please visit http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html.

Related Articles

• Cron jobs

  You can use cPanel to configure cron jobs so commands run at user-defined intervals.

• Connecting to MySQL from the command line

  You use the mysql program to connect directly to MySQL from the command line. Learn how here.

• Import and export a MySQL database

  Learn how to import and export a MySQL database. If you already have a database that you want to install on an Webhost.Berlin server, or you want to back up your database, this article shows you how.