Auto BackUp Koha Database Using a Simple Bash Script

Recently, I was doing some research on automatically backing up MariaDB databases installed on a remote server for my production purposes. Although you can put each task statement one by one in the cronjobs, I found a simple bash shell script for the purpose which takes backups, compresses, and syncs to my Google Drive cloud storage. A bash script is a list of shell commands written in a bash programming language that lets you interact with your Linux server and perform a variety of system admin tasks.

To learn more about bash scripts, check out this tutorial on the Linux Documentation Project site.  

I found a really good backup script on this forum, made a few modifications to it, and also added a few extra lines to delete older backups so the backups do not eat up the server space. I tested this script on a couple of hosting servers and it works great. So here's the tutorial on how this can be done:

Your backups will be compressed using gzip and you will be able to open them using archive extractor in Linux.

Before beginning, here are the steps involved in a nutshell:

    1. Create a backup folder in the server to save the backups.
    2. Create a .sh file with the bash shell script 
    3. Create a cron job to call and run this script on a daily basis.
    4. Sync the backed-up database and save it in Google Drive.

    5. Sent Email notification to the admin 

So let's look at these steps in detail:

1: Creating the Backup Folder

The first step is to create a backup folder on your server. This is the folder where all your backup files will be saved. You can create this folder under your server's etc folder or simply create a new folder in your home directory as follows:

Step 1: SSH to your server as root user. I usually access the server using a terminal in Linux. You can use Putty in windows for accessing the server.

Step 2: Go to your home directory. 

 
cd /home/username

Step 3: Install a Mail server

Install any mail server for sending an email like Postfix/Exim etc, I am here using Exim, see how to install Exim

Step 4: Create a folder named 'koha-backup' in the home directory.
 

sudo mkdir koha-backup

2: Adding the Shell Script 

We will now create a sh file with the bash shell script inside our backup folder using any text editor, I am here using vim, if you are not familiar with vim, use nano/gedit/leafpad/mousepad/kate, etc.

Step 1: Create a file named backup.sh and add the following code to it modify with your username and koha database details 

sudo vim /usr/local/bin/backup.sh

#!/bin/bash
 
# your backups will use this filename.
db_backup_name="koha_library-$(date +%d-%m-%Y-%H.%M).sql.gz"

## 1: Database connection info. You can get these details from your koha-conf.xml file.
db_name="koha_library"
db_username="koha_library"
db_password="koha123"
 
## 2: Path to your backup folder. Replace /home/username/ with path to your home directory.
backup_folder_path="/home/username/koha-backup"

## 3: Backup MYSQL/MariaDB database, gzip it and send to backup folder.
mysqldump --opt -u$db_username -p$db_password $db_name | gzip > $backup_folder_path/$db_backup_name

## 4: Sync the koha-backup folder to Google Drive using rclone
rclone sync $backup_folder_path/$db_backup_name Gdrive:KOHA-DBBACKUPS

## 5: Delete all but 5 recent Koha database back-ups (files having .sql.gz extension) in backup folder.
find $backup_folder_path -maxdepth 1 -name "*.sql.gz" -type f | xargs -x ls -t | awk 'NR>5' | xargs -L1 rm

## 6: Sent Email notification to admin when the above tasks completes

echo 'filename $db_backup_name on GEMS-DBBACKUPS folder on Google Drive of gemsasc@gmail.com Backup synced at $(date +%d-%m-%Y-%H.%M)' | mail -s KOHA_GEMS-db-backed-up-on-GDrive maheshpalamuttath@gmail.com

Step 2: Give the necessary permission to the shell script and assign the task in cron according to your time 

sudo chmod +x /usr/local/bin/backup.sh

crontab -e

#Koha backup 

0 0 * * * /usr/local/bin/backup.sh

This script backs up the koha database and then gzips it and saves it in the backup folder.

The backups will follow this naming convention:

Database Backup:  koha_library-day-month-year-hour-minute.sql.gz eg: koha_library-30-08-2021-00.00.sql.gz

How to configure Gdrive using rclone

https://libtechnophile.blogspot.com/search/label/Rclone