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

One of the simplest ways to backup a mysql database is by creating a dump file. And that is what mysqldump is for. Please read the documentation for mysqldump.

In its simplest syntax, you can create a dump with the following command:

mysqldump [connection parameters] database_name > dump_file.sql

where the [connection parameters] are those you need to connect your local client to the MySQL server where the database resides.

mysqldump will create a dump file: a plain text file which contains the SQL instructions needed to create and populate the tables of a database. The > character will redirect the output of mysqldump to a file (in this example, dump_file.sql). You can, of course, compress this file to make it more easy to handle.

You can move that file wherever you want.

To restore a dump file:

1. Create an empty database (let's say restore) in the destination server
2. Load the dump:

   mysql [connection parameters] restore < dump_file.sql

---

There are, of course, some other "switches" you can use with mysqldump. I frequently use these:

• -d: this wil tell mysqldump to create an "empty" backup: the tables and views will be exported, but without data (useful if all you want is a database "template")
• -R: include the stored routines (procedures and functions) in the dump file
• --delayed-insert: uses insert delayed instead of insert for populating tables
• --disable-keys: Encloses the insert statements for each table between alter table ... disable keys and alter table ... enable keys; this can make inserts faster

---

You can include the mysqldump command and any other compression and copy / move command in a batch file.

Answer 2

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.