Jun 272012
 
Article MySQL

As part of the common administration tasks of a server, it is important to implement a procedure to perform the daily backup of the existing databases.

Our recommmendation is to use an open source bash script named automysqlbackup. This script writes into a compressed files a backup of the database given as argument, and perfoms a weekly rotation of the backup files.

This script can be run daily by creating a cron entry like this (the following is a single line in the crontab file, although it has been split in to in this post to make it easier to read):

In this sample cron entry, the script is run daily at 3:05 hours. The arguments to the script are:

  • rootuser: mysql user (not unix user) with read access to all the databases we want to back up.
  • rootpass: The password of the mysql user specified as first argument.
  • localhost: The name/ip address of the server where the databases are located.
  • “openalfa postfix”: The names of the databases we want to back up. They are surrounded by double quotes because they must be passed to the script as a single argument.
  • /home/openalfa/backup_db: The directory that will hold the backup files to be created.
  • openalfa@openalfa.com: The email address to be sent notifications in cases there are issues while running the script.

When the script is executed for the first time, it creates under the directory ‘backup_db’ the subdirectories ‘daily’, ‘weekly’ y ‘monthly’. In each of these directories it creates a subdirectory for each database specified, with the same name as the database name.

The backup file generated is compressed with gzip, and is given a name that includes the database name, and the creation date and time.

In our sample, the backup file for the openalfa database is named:

and is located under the directory /home/openalfa/backup_db/daily/openalfa.

The automysqlbackup.sh script can be downloaded from sourceforge:

http://sourceforge.net/projects/automysqlbackup/

 

 Posted by at 2:36 pm

 Leave a Reply

(required)

(required)