A Simple Bash Script for MySQL Database Backup

Q. How to schedule MySQL database backup? A bash script for MySQL database backup. In this tutorial, you will find a simple bash script, which takes the backup of MySQL database, archive the backup and store on the local system. This script will also to remove older backups from disk to free space. You can specify the number of days to keep the backup on local disk. You can also use this tutorial to backup MySQL database and store a copy on the remote FTP server.

Create MySQL Backup Script

Now, copy the following content in a script file (like: /backup/mysql-backup.sh) and save on your Linux system. Use this link to download script. After than change some configuration values in section 「Update below values」 in the script as per your environment.

#!/bin/bash ################################################################ ## ## MySQL Database Backup Script ## Written By: Rahul Kumar ## URL: https://tecadmin.net/bash-script-mysql-database-backup/ ## Last Update: Jan 05, 2019 ## ################################################################ export PATH=/bin:/usr/bin:/usr/local/bin TODAY=`date +"%d%b%Y"` ################################################################ ################## Update below values ######################## DB_BACKUP_PATH='/backup/dbbackup' MYSQL_HOST='localhost' MYSQL_PORT='3306' MYSQL_USER='root' MYSQL_PASSWORD='mysecret' DATABASE_NAME='mydb' BACKUP_RETAIN_DAYS=30 ## Number of days to keep local backup copy ################################################################# mkdir -p ${DB_BACKUP_PATH}/${TODAY} echo "Backup started for database - ${DATABASE_NAME}" mysqldump -h ${MYSQL_HOST} \ -P ${MYSQL_PORT} \ -u ${MYSQL_USER} \ -p${MYSQL_PASSWORD} \ ${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz if [ $? -eq 0 ]; then echo "Database backup successfully completed" else echo "Error found during backup" exit 1 fi ##### Remove backups older than {BACKUP_RETAIN_DAYS} days ##### DBDELDATE=`date +"%d%b%Y" --date="${BACKUP_RETAIN_DAYS} days ago"` if [ ! -z ${DB_BACKUP_PATH} ]; then cd ${DB_BACKUP_PATH} if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then rm -rf ${DBDELDATE} fi fi ### End of script ####123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657#!/bin/bash ####################################################################   MySQL Database Backup Script ##   Written By: Rahul Kumar##   URL: https://tecadmin.net/bash-script-mysql-database-backup/##   Last Update: Jan 05, 2019################################################################## export PATH=/bin:/usr/bin:/usr/local/binTODAY=`date +"%d%b%Y"` ################################################################################## Update below values  ######################## DB_BACKUP_PATH='/backup/dbbackup'MYSQL_HOST='localhost'MYSQL_PORT='3306'MYSQL_USER='root'MYSQL_PASSWORD='mysecret'DATABASE_NAME='mydb'BACKUP_RETAIN_DAYS=30   ## Number of days to keep local backup copy ################################################################# mkdir -p ${DB_BACKUP_PATH}/${TODAY}echo "Backup started for database - ${DATABASE_NAME}"  mysqldump -h ${MYSQL_HOST} \   -P ${MYSQL_PORT} \   -u ${MYSQL_USER} \   -p${MYSQL_PASSWORD} \   ${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz if [ $? -eq 0 ]; then  echo "Database backup successfully completed"else  echo "Error found during backup"  exit 1fi  ##### Remove backups older than {BACKUP_RETAIN_DAYS} days  ##### DBDELDATE=`date +"%d%b%Y" --date="${BACKUP_RETAIN_DAYS} days ago"` if [ ! -z ${DB_BACKUP_PATH} ]; then      cd ${DB_BACKUP_PATH}      if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then            rm -rf ${DBDELDATE}      fifi ### End of script ####

After creating or downloading script make sure to set execute permission to run properly.

chmod +x /backup/mysql-backup.sh

Schedule Script in Crontab

Now schedule the script in crontab to run on a daily basis and complete backup on regular basis. Edit crontab on your system with crontab -e command. Add following settings to enable backup at 2 in the morning.

0 2 * * * root /backup/mysql-backup.sh

Save your crontab file. After enabling cron, the script will take backup automatically, But keep check backups on a weekly or monthly basis to make sure.