Automate MySQL Database Backup to Google Drive with Python

You might think your data is safe because you have backups on your server. But ask yourself: What happens if the server disk gets corrupted? What if a hacker deletes everything, including your backups? Or worse, ransomware locks your files?

You already know how to create local Bash backups. But relying on local storage is a single point of failure. The 3-2-1 Backup Rule says you must keep at least one copy off-site.

In this tutorial, we will automate sending your MySQL database dumps directly to Google Drive using Python.

Don't rely on local backups. Learn how to script a Python tool that dumps your MySQL database and uploads it securely to Google Drive API automatically

Step 1: Enable Google Drive API

To let Python talk to your Drive, you need to go to the Google Cloud Console, enable "Google Drive API", and create an OAuth Client ID. Download the credentials.json file and place it in your script folder.

Step 2: The Upload Script (Using PyDrive)

We use the PyDrive wrapper because it handles the complex authentication process automatically.

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
import os

# 1. Dump Database
# We use mysqldump to create the raw SQL file first
os.system("mysqldump -u root -pPASSWORD db_name > backup.sql")

# 2. Authenticate Drive
gauth = GoogleAuth()
gauth.LocalWebserverAuth() # Creates a settings.yaml for auto-login next time
drive = GoogleDrive(gauth)

# 3. Upload to Cloud
file1 = drive.CreateFile({'title': 'backup_db_auto.sql'})
file1.SetContentFile('backup.sql')
file1.Upload()

print("Success: Backup uploaded to Google Drive safely!")

Conclusion

Automation brings peace of mind. By adding this script to your daily Cron Job, you can sleep soundly knowing that even if your server explodes tomorrow, your data is safe in the cloud. Don't forget to check your Drive folder occasionally to verify the files are arriving!

Author: Marg | Daily Innovate Tech