Automatic Mysql Databases Backup using Shell Scripting

In this post we will see how we are going to take mysql databases backup using simple shell scripting with help of crontab.

root@linuxstorages:~# vi Mysql_backup_script.sh

#!/bin/bash

# Parent backup directory
backup_dir="/var/backups/mysql"

# MySQL settings
mysql_user="root"
mysql_password=""

# Create backup directory and set permissions
backup_date=`date +%Y_%m_%d`
backup_dir="${backup_dir}/${backup_date}"
echo "Backup directory: ${backup_dir}"
mkdir -p "${backup_dir}"
chmod 700 "${backup_dir}"

# Get MySQL databases
mysql_databases=`echo 'show databases' | mysql --user=${mysql_user} --password=${mysql_password} -B | sed /^Database$/d`

# Backup and compress each database
for database in $mysql_databases
do
  if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
        additional_mysqldump_params="--skip-lock-tables"
  else
        additional_mysqldump_params=""
  fi
  echo "Creating backup of \"${database}\" database"
  mysqldump ${additional_mysqldump_params} --user=${mysql_user} --password=${mysql_password} ${database} | gzip > "${backup_dir}/${database}.gz"
  chmod 600 "${backup_dir}/${database}.gz"

done

Change the permission for the script file to execute it.

root@linuxstorages:~#chmod +x Mysql_backup_script.sh

After changing the permission check the script is working properly by executing it in command prompt.

root@linuxstorages:~# ./Mysql_backup_script.sh

Now create a schedule timing for the script to execute and take the databases backup. This cron will run daily at 10 pm.

root@linuxstorages:~#crontab -e

* 22 * * *  /bin/Mysql_backup_script.sh

Browse More Articles

Tweet

Written by Unknown