Auto Backup Mysql Database From Ubuntu Server to Google Drive 2021 With 2 Easy steps

December 22, 2020

We are going to see how to auto backup MySQL Database from server to Google Drive with 2 steps

Connect Google drive to your server.
Upload Mysql Database to Google Drive.

Let’s dive right into steps of auto backup MySQL Database to google drive

Connect Google drive to your server

Install rclone

curl https://rclone.org/install.sh | sudo bash

once rclone the installation has finished, you will get a success message.

Now we are going to configure rclone. Type below command in terminal

rclone config

Press “n” and press “Enter” to create a new remote. It will prompt you for a name. We’re going to call it “drive.” you can use any name.

Now choose the storage type

In the list we found google drive is numbered with 13. Type number 13 in the terminal and click enter.

It will ask google drive client id and client secret. Just press enter to accept default

Now it will request permission to access google drive.Type 1 and press enter

It will ask google drive root folder and Service Account Credentials. Just press enter to accept default

At the “Edit advanced config” prompt, just press “Enter.” At the “Use auto config” menu, press “n” and then press “Enter.”

Copy the link provided in the terminal & google it

Copy the verification Code & put it in Terminal

At the “Configure this as a team drive” prompt, type “n” and then press “Enter.”

At the “Yes, Edit, Delete” menu type “y” and then press “Enter.”

At the final menu, type “q” and press “Enter.”

Now Google Drive connected to your server. Next we are going to write script for auto backup mysql database to google drive

Auto Backup Mysql Database to the server

Go to the terminal type sudo su to become an super user

Sudo su

Go to the root directory, in this tutorial I did all things from root directory so I recommend you to do it from the root directory

cd /

Create shell script file in root directory using your favourite editor

nano backup.sh

Copy the below code and paste it into your backup.sh file and save it. Click here to see the same code in the Github gist file.

#!/bin/bash
##this script will backup mysql and upload it to google drive
##directory name
dirname=$1;
##database name
database=$2;
##database username
dbuser=$3;
##database password
dbpass=$4;
## rclone remote name
rcloneRemoteName=$5;
##google drive folder name
gdrivefoldername=$6;
##condition to check folder exist or not
if [ ! -d "$dirname" ]
then
    ##create directory
    mkdir ./$dirname
    ##dump mysql database on server
    mysqldump -u $dbuser -p$dbpass $database | gzip>"./$dirname/$database($(date +\%Y_\%m_\%d_\%H)).sql.gz"
    ##wait for 10 seconds
    sleep 10
    ##upload it to google drive
    rclone copy "./$dirname/$database($(date +\%Y_\%m_\%d_\%H)).sql.gz" $rcloneRemoteName:$gdrivefoldername
    ##if folder already exist
else
    ##dump mysql database on server
    mysqldump -u $dbuser -p$dbpass $database | gzip>"./$dirname/$database($(date +\%Y-\%m-\%d-\%H)).sql.gz"
     ##wait for 10 seconds
    sleep 10
    ##upload it to google drive
    rclone copy "./$dirname/$database($(date +\%Y-\%m-\%d-\%H)).sql.gz" $rcloneRemoteName:$gdrivefoldername
    ##delete 10 days older file on server to save disk space(this command is optional)
    find ./$dirname -mtime +10 -type f -delete
fi
exit 0;

Give permission to backup.sh file by using below command

chmod +X backup.sh

The final command will be like the following, Type it in the terminal then click enter

./backup.sh mysqlbackup test_db root pass123 drive BackupFolder

After executing the command type “ls” in Terminal you will find mysqlbackup folder which you passed as a parameter.
Now go to mysqlbackup folder by typing “cd mysqlbackup” then type “ls”you will find a dumped Database file
Now go to Google drive Check your folder you will find a Backedup Database
Now we are going to automate our script using crontab

nano /etc/crontab

Add the below line in your crontab file

  0 11 * * * root /backup.sh mysqlbackup test_db root pass123 drive BackupFolder

In the above command 0 11 * * _ refers to the time when you want to backup sql file to the server….Here 0 11 _ * * means everyday backup will be done at 11 AM.

You have successfully completed the auto backup MySQL database to google drive

If you want to take a ckup at 11.00 PM, the command will be

0 23 * * * root /backup.sh mysqlbackup test_db root pass123 drive BackupFolder