Automating Daily Database Backups to Google Drive Free of Cost

In today’s data-driven world, ensuring the safety and availability of your database is crucial. One way to safeguard your data is by creating regular backups. In this article, we will walk you through the process of automating daily database backups to Google Drive, using a combination of bash and Python scripts. This method is not only effective but also cost-efficient as it leverages a free Google Cloud service account.

Automating Daily Database Backups to Google Drive: Step-by-Step Guide

Prerequisites

Before we dive into the details, here’s what you’ll need to get started:

1. Google Cloud Service Account: Create a service account on the Google Cloud Console. This account will be used to interact with Google Drive. Make sure to download the JSON key file for this service account.
2. Google Drive Account: Access to a Google Drive account where you want to store your database backups. You will create a dedicated folder for these backups.
3. MySQL Database: Ensure you have a MySQL database that you want to back up.
4. Python: Python should be installed on your system to run the Python script.

Setting Up Automated Backups

Let’s break down the process into manageable steps:

1. Create a Google Drive Folder

The first step is to log in to your Google Drive account and create a new folder for your database backups. You can name it “DatabaseBackups” or choose a name that suits your preference.

2. Share the Folder with the Service Account

To enable your service account to access this folder, follow these steps:

1. Right-click on the “DatabaseBackups” folder and select “Share.”
2. In the sharing dialog, enter the email address associated with your Google Cloud service account.
3. Set the service account’s access level to “Editor” or “Viewer,” depending on your preference.
4. Click “Send” to send an invitation to the service account’s email address.

3. Clone or Download the Scripts

Next, clone the repository containing the necessary scripts or download them individually:

• backup_script.sh (bash script)
• upload_to_drive.py (Python script)

Github: https://github.com/Gurinder-Batth/auto-database-backup-google-drive

4. Configure the Scripts

Open backup_script.sh and set the MySQL database credentials (username and password) and adjust the backup file name and location as needed:

DB_USER="your_db_username" DB_PASS="your_db_password" BACKUP_DIR="/path/to/backup/directory"

Then, open upload_to_drive.py and configure the following variables:

SERVICE_ACCOUNT_KEY_FILE = 'path/to/your/service-account-key.json' DRIVE_FOLDER_ID = 'your-drive-folder-id' BACKUP_DIR = '/path/to/backup/directory'

5. Test the Scripts

Before setting up automation, it’s essential to verify that the scripts work correctly. You can test them manually:

./backup_script.sh python upload_to_drive.py

6. Set Up a Cron Job

Now, let’s automate the backup process. To do this, set up a cron job to schedule the backup and upload tasks to run every 24 hours. Open your terminal and type:

crontab -e

Then, add the following lines to schedule the tasks:

0 0 * * * /path/to/database-backup/backup_script.sh 30 0 * * * /usr/bin/python /path/to/database-backup/upload_to_drive.py

These lines will execute the bash script to create a database backup at midnight and run the Python script to upload it to Google Drive at 12:30 AM every day.

7. Save and Exit

Save the changes to the cron job file and exit the text editor.

Automation in Action

With these steps completed, your database will be automatically backed up to your Google Drive folder every day. You can check the Google Drive “DatabaseBackups” folder for the backups.

It’s important to note that you should monitor your backups and adjust the retention policy as needed to manage storage space efficiently.

Let’s Wrap It Up!

Automating your database backups to Google Drive is a reliable and cost-effective way to ensure the safety of your data. By following this step-by-step guide, you can set up this automation quickly and efficiently. So, don’t wait; start protecting your valuable data today!

LN Webworks expert team is ready to tailor solutions that meet your every need. Reach out to us now for Drupal project excellence and discover how we can help you with your web development projects.