Backing Up a MySQL Database to Google Drive

As I’ve built projects for clients and myself, I’ve set up a few interesting cronjobs (or at least I think so) and pieced together shell commands which I have found useful.

I’ve included a one in this article which I’ve found myself frequently using and, in the case of cronjobs, have been running consistently for months and years on end

The Situation

I needed to create a backup of a MySQL database once a week of weather data, also, it defeats the purpose of a backup a bit if the backup is stored on the same system as the database you’re backing up. Therefore I decided to upload it to Google Drive. Google Drive was my personal choice at the time but you could choose Amazon S3, Google Cloud Storage (GCS), etc, you would just have to set up the credentials.

Note: As far as I know, an EC2 instance already has the AWS S3 command-line interface installed and the credentials require no setup (and same situation with Google Compute Instances and GCS), I’d probably use this approach if I did it again.

Requirements

• Skicka

You may have to include PATH=/usr/bin:/bin:/usr/local/bin at the top of your crontab file otherwise, it may not be able to locate skicka (if you used Skicka)

The Cronjob:

0 12 * * 0 cd [dumps_location] && mysqldump -u [db_user ] — password="[user_pwd]" [db_name] > dump_$(date '+\%Y-\%m-\%d').sql && skicka upload dump_$(date '+\%Y-\%m-\%d').sql /[upload_location]

Note : The “\” escapes e.g. ‘+\%Y-\%m-\%d’, are necessary for crontab, but are not necessary if you run the command yourself in shell

Like all cronjobs, the code-block follows the expected format i.e.

[schedule definition] [command to be run]

The command is long but if you break it down it is just 3 different commands separated by &&. It is also important to break it down each individual command e.g.

• “Okay, how do I create a backup with MySQL”.
• “Okay, now how do I get the current date as a string”.
• “Okay, now how do I insert that date command’s output in another command”
• Etc.

The cronjob is going to;

• Run every Sunday at 12, shoutout to crontab guru (a website I’ve found very useful to define schedules)
• First changes to the location where I want to create the backup
• Runs the MySQL mysqldump and creates a backup named with the current date, $(date '+\%Y-%m-\%d')
• Finally uploads the backup my Google Drive

Considerations

• Including your password for MySQL in the command may be icky security-wise, but someone unauthorized being able to SSH into your server is also a big issue.
• We could just be using a hosted database, but maybe you have a situation where a hosted database is not possible and the server only has connectivity at a handful of points during the week
• Skicka having full access to your Google Drive may also be icky, another reason an EC2 instance or Google Cloud Instance would be my approach now

My goal for this article is not, “It must be done like this”, but more to indicate the issue I had a while back and how I built up a longer cronjob definition by breaking it into smaller pieces.

Sign-up here to receive automation tips, case studies, and guides on the mistakes you maybe making on your automation journey.

And follow me on Twitter where I post about my journey, automation, and business growth.