How to Auto Backup MySQL Database to Google Drive

Introduction

Keeping your MySQL databases backed up is a critical job for any developer or database administrator. While there are many different strategies for backing up data, using cloud storage solutions like Google Drive can provide an easy and reliable method for automating backups. In this guide, you’ll learn how to set up an automated workflow to back up your MySQL database to Google Drive.

Prerequisites

• A MySQL Database
• Access to a server where MySQL is running
• A Google Drive account
• Access to Google API Console for creating credentials

Step 1: Preparing the Backup Script

Before you can upload backups to Google Drive, you’ll need to create them. These backups are commonly created using the mysqldump command.

mysqldump -u username -p database_name > backup.sql

Make this process automatic by writing a script that runs mysqldump on a schedule. For example, a simple shell script could look like this:

#!/bin/bash
USER='username'
PASSWORD='password'
DATABASE='database_name'

DATE=`/bin/date +%d-%m-%Y_%T`
BACKUP_DIR='/path/to/your/backup/directory'

mysqldump -u $USER -p$PASSWORD $DATABASE | gzip > "$BACKUP_DIR/backup-$DATE.sql.gz"

Step 2: Setting Up Google Drive API Access

To upload files to Google Drive from a script, you need to set up Google Drive API access:

1. Head to the Google API Console: https://console.developers.google.com/
2. Create a new project and enable the Drive API for it.
3. Configure the OAuth consent screen.
4. Create credentials (OAuth client ID).
5. Download the JSON file containing your credentials.

Ensure you follow Google’s up-to-date practices for safely handling API credentials.

Step 3: Installing Google Drive API Client Libraries

To interact with Google Drive through your script, you need a client library in your preferred programming language. Google provides libraries for several languages. For this tutorial, we will use Python and its library:

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

Step 4: Authenticating with Google Drive

Create a Python script to handle the OAuth 2.0 flow for accessing Google Drive:

from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
import os
import pickle

SCOPES = ['https://www.googleapis.com/auth/drive']

creds = None
if os.path.exists('token.pickle'):
    with open('token.pickle', 'rb') as token:
        creds = pickle.load(token)
if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file(
            'credentials.json', SCOPES)
        creds = flow.run_local_server(port=0)
    with open('token.pickle', 'wb') as token:
        pickle.dump(creds, token)

Step 5: Uploading the Backup to Google Drive

Now that your script has access to Google Drive, you can add functionality to upload your backups:

from googleapiclient.discovery import build

# Authenticate with Google Drive
# ... the previous authentication code ...

drive_service = build('drive', 'v3', credentials=creds)

file_metadata = {'name': f'backup-{DATE}.sql.gz'}
media = MediaFileUpload(f'path/to/your/backup/directory/backup-{DATE}.sql.gz',
                        mimetype='application/gzip')
file = drive_service.files().create(body=file_metadata,
                                    media_body=media,
                                    fields='id').execute()
print('File ID: %s' % file.get('id'))

Step 6: Automating the Backup Process

To automate your backup script, you can use cron jobs in Linux:

0 2 * * * /path/to/your/backup/script.sh

This cron job would run the backup script every day at 2 AM. Make sure the Python script is triggered within the shell script after the mysqldump command.

#!/bin/bash
# ... your mysqldump command ...

python3 /path/to/your/upload_script.py

Conclusion

By following the steps outlined in this tutorial, you now have an automated system for backing up your MySQL databases to Google Drive. This kind of automated backup strategy ensures data safety and eases the recovery process in case of any data loss incidents.