Backup MySQL Database using a Bash script

A helpful bash script tutorial to help you back up your MySQL database, archive it and store it in your local system using a simple bash script. And not only that, the bash script helps with space by deleting older backups from your disk space. You can specify how many days to store the backup in your local space.

Create a MySQL Backup Script

Copy the script-code in a script file and save it on your Linux system. Change the configuration values in the part ‘Update Values’ to fit your environment.

#!/bin/bash
 
################################################################
##
##  MySQL Database Backup Script 
##  Written By: You
##  URL: https:youdomain
##  Last Update:the time now
##
################################################################

export PATH=/bin:/usr/bin:/usr/local/bin
TODAY=`date +"%d%b%Y"`

################################################################
################## Update 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 a 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 ####

Set Execute function to run correctly. Make sure you remember your MySQL password, if you forget, click here to reset your root password.

Backup MySQL Database schedule in Crontab

Schedule your script in crontab to run according to your specifications and to do it automatically. Edit crontab on your system by running this command crontab –e. For example to run the back up at 3 in the morning, run this: 03 *** root /backup/mysql-backup.sh

Save your crontab file by typing [esc key] :wq [enter]