YeCoder
PHP, Laravel, Javascript
Mysql auto backup to Dropbox

Mysql auto backup to Dropbox

14 Feb 2022
Saeed Vaziry

In this article, I'm gonna show you how you can easily configure an auto backup Mysql databases to a Dropbox account using Mysqldump and Cron jobs on an Ubuntu server.

Set up the Dropbox

To store the backup files into Dropbox, You'll need, of course, a Dropbox account and an API Key associated with it.

Go to your Dropbox account and generate an API Key and save it.

Backup

Let's make a backup with the mysqldump command:

sudo mysqldump -u root db_name > backup_name.sql

Very easy hah? 😁

Make sure that you've installed the zip app on your server. Here is the installation for Ubuntu:

sudo apt install zip

Then let's zip it:

zip backup.zip backup_name.sql

Upload to Dropbox

To upload the backup file to Dropbox we need curl. Here is the installation for Ubuntu:

sudo apt install curl

Relace the parameters to the below command and execute it.

curl --location --request POST 'https://content.dropboxapi.com/2/files/upload' \
--header 'Accept: application/json' \
--header 'Dropbox-API-Arg: {"path":"{PATH-ON-DROPBOX}"}' \
--header 'Content-Type: text/plain; charset=dropbox-cors-hack' \
--header 'Authorization: Bearer {YOUR-DROPBOX-API-TOKEN}' \
--data-binary '@{PATH-TO-BACKUP-FILE}'

You should receive a response like this

{
    "name": "backup.zip",
    "path_lower": "/path/to/backup.zip",
    "path_display": "/path/to/backup.zip",
    "id": "id:XXXXXXXXX",
    "client_modified": "XXXXX",
    "server_modified": "XXXXX",
    "rev": "XXXXXXX",
    "size": 111,
    "is_downloadable": true,
    "content_hash": "XXXXXXXX"
}

Automation

To automate this process we'll use Cron jobs but first, we need to gather all steps together and create an executable file.

Create a file named autobackup.sh with the below content and don't forget to replace the variables.

sudo mysqldump -u root db_name > backup_name.sql

zip backup.zip backup_name.sql

curl --location --request POST 'https://content.dropboxapi.com/2/files/upload' \
--header 'Accept: application/json' \
--header 'Dropbox-API-Arg: {"path":"{PATH-ON-DROPBOX}"}' \
--header 'Content-Type: text/plain; charset=dropbox-cors-hack' \
--header 'Authorization: Bearer {YOUR-DROPBOX-API-TOKEN}' \
--data-binary '@{PATH-TO-BACKUP-FILE}'

Then make the file executable:

sudo chmod +x autobackup.sh

Now let's set up the Cron job.

Run the following command to open the crontab file (My favorite editor is nano)

EDITOR=nano crontab -e

And then append the following line to the end of it with your preferred cronjob interval

This example is making a backup hourly:

0 * * * * /path/to/autobackup.sh

That's it! 🎉

I'll be happy to hear your thoughts about this article.

Share on
Submit your link now
Submit