Hackonology Forums
Bash script for MySql Auto Backup - Printable Version

+- Hackonology Forums (https://hackonology.com/forum)
+-- Forum: Technology & Configuration (https://hackonology.com/forum/forumdisplay.php?fid=3)
+--- Forum: Configuration Scripts (https://hackonology.com/forum/forumdisplay.php?fid=6)
+--- Thread: Bash script for MySql Auto Backup (/showthread.php?tid=135)



Bash script for MySql Auto Backup - SysAdmin - 12-15-2020

In most cases a bash script is a good solution. However, not all storage locations allow you to upload files using shell commands. But if all that you want to do is save backups to a directory, then you can accomplish this using a simple script.

To set up this solution, we need to install postfix mailutils. In Ubuntu we can do this as follows:

sudo apt-get update
sudo apt-get install postfix mailutils

The following is the simplest implementation of a bash script for performing regular backups. At the beginning of the script, we specify the backup storage directory, the notification email address, and the backup storage period.

# Backup storage directory
backupfolder=/var/backups

# Notification email address
recipient_email=<username@mail.com>

# MySQL user
user=<user_name>

# MySQL password
password=<password>

# Number of days to store the backup
keep_day=30

sqlfile=$backupfolder/all-database-$(date +%d-%m-%Y_%H-%M-%S).sql
zipfile=$backupfolder/all-database-$(date +%d-%m-%Y_%H-%M-%S).zip

# Create a backup
sudo mysqldump -u $user -p$password --all-databases > $sqlfile

if [ $? == 0 ]; then
  echo 'Sql dump created'
else
  echo 'mysqldump return non-zero code' | mailx -s 'No backup was created!' $recipient_email 
  exit
fi

# Compress backup
zip $zipfile $sqlfile
if [ $? == 0 ]; then
  echo 'The backup was successfully compressed'
else
  echo 'Error compressing backup' | mailx -s 'Backup was not created!' $recipient_email
  exit
fi
rm $sqlfile
echo $zipfile | mailx -s 'Backup was successfully created' $recipient_email

# Delete old backups
find $backupfolder -mtime +$keep_day -delete

cron allows you to schedule this script to run regularly.Carry out the following steps to do this
sudo crontab -e

And add the script path to the end of the string
30 22 * * * /home/user/script/mysql_backup.sh

Thus, your script will be executed every day at 10:30 PM.

Summary

Creating a bash script is a good and simple solution if you know bash and how to support it. However, this solution also entails certain disadvantages. You will find that it is difficult to configure the saving of backups to Google Drive or DropBox. Cloud storage rarely supports the CLI interface. In addition, it can be a daunting task to implement the automatic removal of old backups.