How to schedule a MySQL database backup in a remote Ubuntu server to a Dropbox folder in Windows PC?

Question

I have a MySQL database that I want to daily backup to my Dropbox folder on my Windows PC.

How can I do that automatically from Windows 7?

Answer 1

My solution to extract a backup and push it onto Dropbox is as below.

A sample of Ubuntu batch file can be downloaded here.

In brief

1. Prepare a batch script backup.sh
2. Run backup.sh to create a backup version e.g. backup.sql
3. Copy backup.sql to Dropbox folder
4. Schedule Ubuntu/Windows task to run backup.sh task e.g. every day at night

Detail steps

1. All about backing up and restoring an MySQL database can be found here.

Back up to compressed file

mysqldump -u [uname] -p [dbname] | gzip -9 > [backupfile.sql.gz]

2. How to remote from Windows to execute the 'backup' command can be found here.

plink.exe -ssh -pw -i "Path\to\private-key\key.ppk" -noagent username@server-ip

3. How to bring the file to Dropbox can be found here

Create a app https://www2.dropbox.com/developers/apps

Add an app and choose Dropbox API App. Note the created app key and app secret

Install Dropbox API in Ubuntu; use app key and app secret above

$ wget https://raw.github.com/andreafabrizi/Dropbox-Uploader/master/dropbox_uploader.sh
$ chmod +x dropbox_uploader.sh

Follow the instruction to authorize access for the app e.g.

http://www2.dropbox.com/1/oauth/authorize?oauth_token=XXXXXXX

Test the app if it is working right - should be ok

$ ./dropbox_uploader.sh info

The app is created and a folder associating with it is YourDropbox\Apps\<app name>

Commands to use

List files

$ ./dropbox_uploader.sh list

Upload file

$ ./dropbox_uploader.sh upload <filename> <dropbox location>
e.g.
$ ./dropbox_uploader.sh upload backup.sql .

This will store file backup.sql to YourDropbox\Apps\<app name>\backup.sql

Done

4. How to schedule a Ubuntu can be view here using crontab

Call command

sudo crontab -e

Insert a line to run backup.sh script everyday as below

0 0 * * * /home/userName/pathTo/backup.sh

Explaination:

minute (0-59), hour (0-23, 0 = midnight), day (1-31), month (1-12), weekday (0-6, 0 = Sunday), command

Or simply we can use

@daily /home/userName/pathTo/backup.sh

Note:

• To mornitor crontab tasks, here is a very good guide.