lundi 26 décembre 2011

Free MySQL backup with Dropbox

Recently, I've been working on creating an automatic service for backing up our RedmineMySQL database on the Debian server.

So I've decide to share there how I made a free backup service for the MySQL database.
I set several goals that should be attempted in order to consider that backup service is useful

  1. Only free & open source applications.
  2. Remote disk storage also for free.
  3. Backup must be automatic.
  4. Backup encryption.
To accomplish these requirements finally I have selected the next tools:
  1. AutoMysqlBackup - - the core of theservice.
  2. DropBox free account (2 Gb for free) -
  3. crontab - a standard Linux job automation tool
  4. openssl - for encryptionHow does this it works together.A little description.
First of all you will be need a DropBox account - there your MySQL backups will arrive and willbe stored. You have to download the DropBox client application or a DropBox daemon. I suggest you to use a dropboxd (daemon) for the server use: - install DropBox as a daemon

Downlaod & install AutoMysqlBackup tool. After the installation you have to modify the script /etc/automysqlbackup/automysqlbackup.conf

You should uncomment the line you need and put the appropriate values. Each line in this script is well documented and you will find all the options you need. Just a brief description of what can you configure with it : mysql server address, mysql credentials for the command mysqldump, location where the dump will be stored, the regularity of the backuping, etc.

Next step, is creating your own bash script that will be called by cron, make a backup with autoMysqlBackup, copy the backup files into the local dropBox folder.

Download my example :

Make sure that your own is runnable (don't forget chmod u+x

The next step is to configure your crontab. In the crontab I've defined the period of time the script will be called. I set up the backup everyday at 3:00 AM.
Make sure you have the root privileges and run the following command :
crontab -e
and at the end of the file add the line :
00 03 * * * /root/

And the last step is about the encryption. The openSSL encryption is made by the AutoMysqlBackup, for this you have to uncomment in your /etc/automysqlbackup/automysqlbackup.conf the following line:
don't forget to set the password to encrypt!

That's all, make a test that prove that your backup is working fine before moving all this stuff in the production environment.