MySQL Database Backup Automatic Script

MySQL is an open source relational database management system (DBMS) which is much of the time conveyed in a wide grouping of settings. Most every now and again it is conveyed as a feature of the LAMP Stack. The database system is additionally simple to utilize and exceedingly convenient and is, with regards to numerous applications, amazingly productive. As MySQL is frequently a unified information store for a lot of mission basic information, making general backups of your MySQL database is a standout amongst the most essential fiasco recuperation assignments a system administrator can perform.

In this article I will provide a simple MySQL bash script.

Creating MySQL Backup Script

First you have to create a script with below content called “mysql-bkp.sh” and save it on your system. Please change the below value as per your requirements.

# mkdir /root/scripts/
# cd /root/scripts/
# vim mysql-bkp.sh

#!/bin/bash
 
################################################################
##
##   MySQL Database backup Script 
##   Written By: Santosh
##   Website: http://www.looklinux.com
##
################################################################
 
export PATH=/bin:/usr/bin:/usr/local/bin
TODAY=`date +"%d%b%Y"`
 
################################################################
################## Update below values  ########################
 
BACKUPPATH='/opt/db'
MYSQL_HOST='localhost'
MYSQL_PORT='3306'
MYSQL_USER='root'
MYSQL_PASSWORD='password'
DATABASE_NAME='mydb'
BACKUPRETAINDAYS=7   ## Number of days to keep local backup copy
 
#################################################################
 
mkdir -p ${BACKUPPATH}/${TODAY}
echo "Backup started for database - ${DATABASE_NAME}"
 
 
mysqldump -h ${MYSQL_HOST} \
   -P ${MYSQL_PORT} \
   -u ${MYSQL_USER} \
   -p${MYSQL_PASSWORD} \
   ${DATABASE_NAME} | gzip > ${BACKUPPATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz
 
if [ $? -eq 0 ]; then
  echo "Database backup successfully completed"
else
  echo "Error found during backup"
fi
 
## Remove backups older than {BACKUPRETAINDAYS} days
 
DBDELDATE=`date +"%d%b%Y" --date="${DBRETAINDAYS} days ago"`
 
rm -rf ${BACKUPPATH}/${DBDELDATE}
 
### Script ends here #######

Save and close the file. Now set the executable permission on mysql-bkp.sh file.

# chmod +x /root/scripts/mysql-bkp.sh

Schedule MySQL Backup

You can scheduled this script to run on daily, weekly, monthly basis as per your database backup requirement. Edit crontab entry on your system typing below command and set cron job for mysql-bkp.sh script.

# crontab -e
@daily root /backup/scripts/mysql-bkp.sh

Save crontab file. In above cron I have scheduled MySQL backup on daily basis.

Suggested Read:

• 20 Crontab Examples To Schedule Tasks

Thanks:)