Introduction to a simple bash script for MySQL database backup

How to implement MySQL database backup? It is possible to use a bash script for MySQL database backup. In this article, we will introduce a simple bash script for backing up a MySQL database, archiving and storing the backup on the local system. This bash script will also delete old backups from disk to free up space. You can also specify the number of days to keep backups on local disk.

Create MySQL backup script

Now, copy the following content into the script file (such as /backup/mysql backup.sh ) and saved on the Linux system. After that, change some configuration values ​​in the "Update below values" section of the script according to 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"
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 ####

After creating or downloading the script, make sure to set execution permissions to run properly.

$ chmod + x /backup/mysql-backup.sh

Scheduling the script in Crontab

Now, schedule the script in crontab to run every day and complete the backup regularly. Use the crontab -e command to edit crontab on your system. Add the following setting to enable backup at 2 AM.

Scheduling the script in crontab

Now schedule the script in crontab to run every day and complete the backup regularly. Use the crontab -e command to edit crontab on your system. Add the following setting to enable backup at 2 AM.

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

Save your crontab file. When cron is enabled, the script will automatically perform backups, but please check weekly or monthly to make sure it has been backed up.

This article has ended here. For more other exciting content, you can pay attention to the Linux Video Tutorial column on the PHP Chinese website!

The above is the detailed content of Introduction to a simple bash script for MySQL database backup. For more information, please follow other related articles on the PHP Chinese website!