I’ve been researching ways to automatically back up MariaDB databases on a remote server for my work. While individual cron jobs can handle each task, I found a simpler solution: a bash shell script. This script automates the process of backing up databases, compressing the backups, and transferring them to my Google Drive cloud storage. For those unfamiliar, a bash script is a series of shell commands written in the bash programming language, enabling interaction with Linux servers and execution of various system administration tasks.
To learn more about bash scripts, the Linux Documentation Project offers a helpful tutorial.
This forum provided an excellent backup script that I adapted for my needs, adding features like deleting older backups to manage server space. After testing on various hosting servers, I can confirm its effectiveness. Here’s a breakdown of the process:
Your backups will be zipped using gzip, allowing for easy extraction on Linux systems.
Here’s a summary of the steps involved:
1. Create a dedicated backup folder on the server.
2. Create a .sh file containing the bash shell script.
3. Schedule a daily cron job to execute the script.
4. Configure synchronization between the backed-up database and Google Drive.
5. Set up email notifications to the administrator upon completion.
Let’s delve into each step:
1: Creating the Backup Folder
Start by creating a backup folder on your server. This folder will store all your backups. You can place it within your server’s etc folder or create a new one within your home directory by following these steps:
Step 1: Log in to your server as the root user via SSH. I typically access the server using a Linux terminal. Windows users can utilize tools like Putty for server access.
Step 2: Navigate to your home directory using the command:
**
cd /home/username**
Step 3: Install a mail server.
Install your preferred mail server, such as Postfix or Exim, for sending email notifications. This example utilizes Exim. Instructions for installing Exim can be found here.
Step 4: Create a folder named ‘koha-backup’ within the home directory using:
sudo mkdir koha-backup
2: Adding the Shell Script
Now, let’s create a .sh file containing the bash shell script within our backup folder. Use your preferred text editor. This example uses vim. If you’re unfamiliar with vim, alternatives like nano, gedit, leafpad, mousepad, or kate can be used.
Step 1: Create a file named backup.sh and populate it with the following code. Ensure you modify the username and Koha database details according to your setup.
sudo vim /usr/local/bin/backup.sh
#!/bin/bash
# Your backups will use this filename format.
db_backup_name=“koha_library-$(date +%d-%m-%Y-%H.%M).sql.gz”
## 1: Database connection details. These can be obtained from your koha-conf.xml file.
db_name=“koha_library”
db_username=“koha_library”
db_password=“koha123”
## 2: Specify the path to your backup folder. Replace /home/username/ with the actual path to your home directory.
backup_folder_path="/home/username/koha-backup"
## 3: This line backs up the MYSQL/MariaDB database, compresses it using gzip, and sends it to the designated backup folder.
mysqldump –opt -u$db_username -p$db_password $db_name | gzip > $backup_folder_path/$db_backup_name
## 4: This line synchronizes the koha-backup folder with your Google Drive using rclone.
rclone sync $backup_folder_path/$db_backup_name Gdrive:KOHA-DBBACKUPS
## 5: This line manages storage by deleting all but the 5 most recent Koha database backups (identified by the .sql.gz extension) in the backup folder.
find $backup_folder_path -maxdepth 1 -name “*.sql.gz” -type f | xargs -x ls -t | awk ‘NR>5’ | xargs -L1 rm
## 6: Once the preceding tasks are complete, this line sends an email notification to the administrator.
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: Grant the necessary permissions to the shell script and schedule it in cron according to your preferred 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, compresses it using gzip, and saves it in the specified backup folder.
The backups will follow this naming pattern:
Database Backup: koha_library-day-month-year-hour-minute.sql.gz e.g., koha_library-30-08-2021-00.00.sql.gz
For instructions on configuring Gdrive using rclone, refer to this link: