Auto Backup your Mysql Database to Google Drive using Cron- 3 mins
Let’s assume that you have an instance running Ubuntu and mysql is installed in it. We will also assume that a
ubuntu user is setup and there is a database called
products, for which we need to create backup and upload to Google Drive. We will assume taking hourly backups. This can be updated as per the requirements.
First, we will install gdrive which will be used to access google drive from command line
# find installation file version specifiv to your version wget -O gdrive "https://drive.google.com/uc?id=1Ej8VgsW5RgK66Btb9p74tSdHMH3p4UNb&export=download" chmod +x gdrive sudo mv gdrive /usr/local/bin/ # authenticate gdrive about # enter verification code here to authenticate # on success, we will get the following as output User: Taranjeet Singh, [email protected] Used: 190.5 MB Free: 15.9 GB Total: 16.1 GB Max upload size: 5.2 TB
Just to be sure that gdrive is working we will run
gdrive list to see the list of folders present in drive.
We will now create a new folder named
dbbackups using gdrive
gdrive mkdir dbbackups Directory 3EDlQZRuPdyAG91lhVhsl5gLFeX68PG3n created.
We will copy the id (here
3EDlQZRuPdyAG91lhVhsl5gLFeX68PG3n) and here in refer to as
We will create a folder named
mysqlbackup in the home directory of
ubuntu user. We will also make sure that
ubuntu user ownws this directory.
pwd # /home/ubuntu mkdir mysqlbackup # just to be sure chown -R ubuntu:ubuntu mysqlbackup/
Next we will create a script called
uploadbackup.sh and place it in the home directory of ubuntu user.
pwd # /home/ubuntu vim uploadbackup.sh
We will write the following content in
#!/bin/sh set -e set -x DUMP_DIR="/home/ubuntu/mysqlbackup" DB_NAME=products DB_USER=DB_USER_NAME_HERE DB_PASSWORD=DB_PASSWORD_HERE DATE_FORMAT="%Y-%m-%d_%H" GOOGLE_DRIVE_FOLDER=ID_OF_GOOGLE_FOLDER_COPIED_ABOVE file_name="$(date +$DATE_FORMAT)" sql_file="$file_name.sql" gz_file="$file_name.tar.gz" # make sure that the folder exists mkdir -p $DUMP_DIR cd $DUMP_DIR # run mysqlbackup, tar gz and delete sql file mysqldump -u$DB_USER -p$DB_PASSWORD $DB_NAME > $sql_file tar -zvcf $gz_file $sql_file rm $sql_file /usr/local/bin/gdrive upload --parent $GOOGLE_DRIVE_FOLDER $gz_file rm $gz_file
Now we will test this script by running it once.
.tar.gz file should be created in the
dbbackups google drive folder.
Now, we will perform the last step, which is adding this script on crontab. We will use the ubuntu user to add an hourly crontab.
In the editor, that open, we will add the following line
* * * * * sh /home/ubuntu/uploadbackup.sh >> /var/log/cron/mysqlbackup.log 2>&1
Here we have
/var/log/cron/mysqlbackup.log as the output of log file. In case we want to take some other folder, we need to make sure that the directory is owned and write-able by
ubuntu user, since crontab is being run via
ubuntu user. To own a directory(lets say
/var/log/cron), we will execute the following steps
cd /var/log sudo mkdir cron sudo chown -R ubuntu:ubuntu cron
- This tutorial helped me a lot in writing this blog post. Thanks