Backup DB to Google Cloud Storage with Python and cronjob

I have a database on a GCP VM, Ubuntu 18.04, that will be updated everyday. I want to backup the data on a daily-basis and save it in Google Cloud Storage, which is a safer option in comparison with merely having a local sql file in VM. To accomplish our goal, we will later go through these steps:

1. Set up Google Cloud Storage
2. Write our python script
3. Set up cronjob

Set up Google Cloud Storage

As I already have a bucket on Google Cloud Storage, namely taipei-medical, I will upload the backup files to this bucket. /* Here is a tutorial guiding you to create a bucket */

In order to use the GCS API, let’s follow the official document to get an authentication json file 👇🏼👇🏼👇🏼

Cloud Storage Client Libraries | Google Cloud

Our json file will look like this:

Write our python script

pip install --upgrade google-cloud-storage

What our script needs to do is: dump the database ➕ upload the sql file to GCS. I also want the backup file to be named after its created date 😉

My project is in the folder /var/www/sophie. The structure of sophie is shown below:

-- backup.py
|- credentials_gcs.json   # our GCS authentication json file
|_ backup                 # Dumped files are saved in this folder
 |- 2021-02-01.sql
 |- 2021-02-02.sql
 |_ 2021-02-xx.sql

Set up cronjob

Cronjob is a time-based job scheduler in Unix-like computer operating systems. As a result, it is a perfect tool to help us run the backup script regularly.

In the VM’s console, we can type crontab -e

hall-labeling is the name of my GCP VM

At the bottom, we can specify how often the script needs to be executed. In my case, I want to backup my database everyday at 18:00, so I put:

0 18 * * * python3 /var/www/sophie/backup.py

You can use cronjob guru to find out more cronjob expressions. BTW, if you are using a GCP VM like me, you may need to be aware that the VM’s time may not be the same as your local time (You can type date to check the machine’s time ⏰)

Now our script should work 🥳 🥳 🥳 p.s. The created time is 2:00 a.m. instead of 18:00 p.m. is because there is a 8 hour time difference between the VM and my local time 😅

Happy Coding ☕️