Trending News

Blog

How To Use A Simple Bash Script To Backup MySQL Database
Database

How To Use A Simple Bash Script To Backup MySQL Database 

Q. How do I back up my MySQL database on a regular basis? A bash script for backing up MySQL databases. In this tutorial, you will learn how to use a simple bash script to take a MySQL database backup, archive it, and store it locally.

Table of Contents

Bash script to backup MySQL database

This script would also free up storage space by removing older backups. You can decide how long the backup should be held on a local disk. This tutorial can also be used to back up a MySQL database and store a copy on a remote FTP server.

Create a backup script for MySQL

Copy the following content into a script file (for example, /backup/mysql-backup.sh) and save it to your Linux system. This link will take you to a page where you can download the script. Then, according to your environment, adjust any configuration values in the script’s section “Update below values.”

				
					#!/bin/bash
################################################################
##
## MySQL Database Backup Script
## Written By: Yehiweb
## URL: https://yehiweb.com/wp-content/uploads/2021/05/mysql-backup.sh
## Last Update: May 17, 2021
##
################################################################

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

Be sure to set execute permission after creating or downloading a script in order for it to run properly.

				
					chmod +x /backup/mysql-backup.sh
				
			

Now Schedule The Script In Crontab

Now, in crontab, schedule the script to run every day and perform a backup on a regular basis. With the crontab -e command, you can edit your system’s crontab. To allow backup at 2 a.m., add the following settings.

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

Now save the crontab file. After enabling cron, the script can take backups automatically. But, you can double-check backups on a weekly or monthly basis to ensure that they are working.

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Share on reddit
Share on vk
Share on tumblr
Share on skype
Share on stumbleupon
Share on whatsapp

Related posts

Leave a Reply

Required fields are marked *