MYSQL Database Backups Using Dropbox

There are a lot of ways to keep files backed up and safe. Today we will explore the option of using dropbox with its API functions. For the purpouse of this tutorial we will asume you already have a dropbox account however, if you do not then sign up here.

To start off we will download the dropbox-uploader created by andreafabrizi to our server.

git clone https://github.com/andreafabrizi/Dropbox-Uploader.git

You should now have a folder called Dropbox-Uploader in your working directory. It is probably best to keep this in your /root/ directory.

Update the script permissions so that you can execute the bash file:

chmod 744 /pathToDropbox-Uploader/dropbox_uploader.sh

To initiate your dropbox API key and secret as well as tokens simply run the bash file and follow the instructions on screen:

./pathToDropbox-Uploader/dropbox_uploader.sh

Once all that is done you are ready to test the upload function. Create a file called hi.txt and then try uploading it to dropbox:

touch hi.txt

./pathToDropbox-Uploader/dropbox_uploader.sh upload hi.txt /

Check out your dropbox folder and see if hi.txt is floating around in the app directory. Provided the above has worked you can delete hi.txt and we are now going to create the script to backup the database. We will use mysqldump to create a compressed archive of our databases so create a file called databaseBackup and put the following code in it:

#!/bin/bash
DAY=`/bin/date +%Y%m%d`
mysqldump -u USER -pPASSWORD databaseName | gzip -9 > /tmp/$DAY.sql.gz
./pathToDropbox-Uploader/dropbox_uploader.sh upload /tmp/$DAY.sql.gz
rm /tmp/$DAY.sql.gz

Be sure to change the mysql USER and PASSWORD variables as well as the name of the database that you would like to backup databaseName. Make sure there is no space between -p and PASSWORD!

Move the databaseBackup file to /etc/cron.monthly/ (or any other frequency that you prefer) to backup your database automatically every month.

Finally, ensure the owner of databaseBackup is root and that it is executable.

chown root /etc/cron.monthly/databaseBackup
chmod 744 /etc/cron.monthly/databaseBackup

As a last note, if you would like toupdate multiple databases at once then simply change the third line of databaseBackup to:

mysqldump -u USER -pPASSWORD --databases databaseName1 databaseName2 | gzip -9 > /tmp/$DAY.sql.gz

That’s it. You are done. Check out how to monitor your www directory for changes here.

 

Comments are closed.