mysql backup scripts : Auto Backups – Shell Scripts(A Best 2 Bash Script for MySQL Database Backup)

by
mysql backup scripts

mysql backup scripts : The great step by step Best tiny Way for mysql backup scripts described in this article work for any Linux distribution Like as a : Debian, Open SUSE, Ubuntu, CentOS, Red Hat, Oracle Linux etc. Anyway, the mysql script package step by step installation commands may differ.

mysql backup scripts | mysql auto backup

A Simple Bash Script for MySQL Database Backup – Question. How to schedule MySQL database backup? A bash script for MySQL database backup.

In this linux backup script, you will get a simple bash script as well as mysql running script, which takes the backup of MySQL database, archive the backup and my sql scripts save on the local linux operating system.

mysql backup script will also to delete older some package backups from disk to free space. You can particular the number of days(For Example Last 30 days) to keep the backup on local disk.

You can also use this article to backup MySQL database as well as save a copy on the remote FTP – File Transfer Protocol server (what is ftp?).

How to Automate MySQL Database Backups in Linux?

  1. Make a database backup
  2. Zip the backup
  3. Encrypt the compressed file
  4. Send the backup to Internet storage using Google Drive, FTP, AWS, Dropbox etc.
  5. Receive email notification concerning backup results
  6. Make a backup schedule
  7. Remove old backups

Create MySQL Backup Script

Bash scripts

Benefits: Ideally, you can customize a clarification that takes all of your specific requirements into account

Drawbacks: If you want to create a more complicated clarification, you required to have advanced knowledge of how to write bash scripts. and this is a difficult to settings the sending of backups to the cloud

Now, simple you can copy the Bellow data content in a php script file (For Example: /backup/mysql-backup.sh) and save on your Linux computer system. Use this simple click this link to free download script open source. And then update some configuration(settings) values in section “Update below values” in the fully script as per your environment.

#!/bin/bash
################################################################
##
##   MySQL Database Backup Script 
##   Written By: Pakainfo
##   URL: https://tecadmin.net/mysql-backup-scripts/
##   Last Update: July 07, 2021
##
################################################################

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

################## Update below values  ########################

DATABASE_FULL_BASE_PATH='/backup/dbbackup'
MYSQL_HOST='localhost'
MYSQL_PORT='3306'
MYSQL_USER='root'
MYSQL_PASSWORD='############'
SET_DB_NAME='mydb'
DATABASE_BKP_DAYS_REMAIN=20   ## total Number of days to keep local backup copy


mkdir -p ${DATABASE_FULL_BASE_PATH}/${TODAY}
echo "Backup started for database - ${SET_DB_NAME}"


mysqldump -h ${MYSQL_HOST} \
		  -P ${MYSQL_PORT} \
		  -u ${MYSQL_USER} \
		  -p${MYSQL_PASSWORD} \
		  ${SET_DB_NAME} | gzip > ${DATABASE_FULL_BASE_PATH}/${TODAY}/${SET_DB_NAME}-${TODAY}.sql.gz

if [ $? -eq 0 ]; then
  echo "Your Database backup successfully completed"
else
  echo "Error found during backup"
  exit 1
fi


##### Delete backups older than {DATABASE_BKP_DAYS_REMAIN} days  #####

DATABASEREMOVEDT=`date +"%d%b%Y" --date="${DATABASE_BKP_DAYS_REMAIN} days ago"`

if [ ! -z ${DATABASE_FULL_BASE_PATH} ]; then
      cd ${DATABASE_FULL_BASE_PATH}
      if [ ! -z ${DATABASEREMOVEDT} ] && [ -d ${DATABASEREMOVEDT} ]; then
            rm -rf ${DATABASEREMOVEDT}
      fi
fi

### End of script ####

And then simple step by step creating or fource downloading script make sure to set execute permission to run properly.

chmod +x /backup/mysql-backup.sh

AutoMySQLBackup
Benefits: Built-in incremental backups
Drawbacks: You may only particular a directory as the backup storage location & No integrated clarification for removing old backups

Schedule Script in Crontab

Now schedule the script in crontab to run on a daily basis as well as fully all backup on regular basis. and then Edit crontab on your system with crontab -e command.

Include Bellow settings to enable backup at 2 in the morning.

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

Save your main crontab file.
Now, enabling cron, the script will take backup automatically, But keep check full backups on a weekly (Last 7 days) or monthly(Last 30Days or Last Month) basis to make sure.

Example 2: mysql backup scripts

mysql scripting examples

#!/bin/bash
# mysql-backup.sh
# linux backup script - use mysqldump to Dump DB as well as your file data compress it on the fly to a mounted partition
#
LIVE_DIR_AUTOBUP="/data/db-backups"
mkdir -p $LIVE_DIR_AUTOBUP
chmod 777 $LIVE_DIR_AUTOBUP
#
#
SERIAL="`date +%Y%m%d-%H%M%S`"
 
#=====================================
# FIRST Log Functions
#
function textLogCreate
{
	echo "********* User Log Start *********" >> $LOGFOLDER
	echo "Time: `date`" >> $LOGFOLDER
	echo " " >> $LOGFOLDER
}
function LastLogLine
{
	echo " " >> $LOGFOLDER
	echo "Time: `date`" >> $LOGFOLDER
	echo "********* User Log End   *********" >> $LOGFOLDER
}
 
#=====================================
#
#
function GetDBList
{
	echo "Init Here Service Call GetDBList()" >> $LOGFOLDER
	mysqlshow |grep "|"| tr -d ' '|tr -d '|'| egrep -v Databases > $DBLIST
}
 
#=====================================
#
#
function GetFullBackup
{
echo "Calling GetFullBackup()" >> $LOGFOLDER
 
DBFILE=$LIVE_DIR_AUTOBUP/db-$DB-$SERIAL.sql
echo "Host [$H]" >> $LOGFOLDER
echo "DB File [$DBFILE]" >> $LOGFOLDER
if [ -a  $DBFILE ]
then
mv $DBFILE $DBFILE.`date '+%M%S'`
fi
echo "Dumping ${DB}" >> $LOGFOLDER
mysqldump -B ${DB}  --add-drop-database --add-drop-table >> ${DBFILE}
echo "Zipping up file!" >> $LOGFOLDER
gzip ${DBFILE}
echo "Done!" >> $LOGFOLDER
}
 
CURRENT_DT_LIVE=`date '+%Y-%m-%d'`
LOGFOLDER_DIR=/logs/db-backup
LOGFOLDER=$LOGFOLDER_DIR/db-backup-$CURRENT_DT_LIVE.log
mkdir -p $LOGFOLDER_DIR
chmod 777 $LOGFOLDER_DIR
touch $LOGFOLDER
chmod 664 $LOGFOLDER
 
DBLIST=/tmp/dblist-$CURRENT_DT_LIVE.list
 
textLogCreate
#=====================================
#
#                     MAIN MySQL DB Source Code Start
 
GetDBList
while read line
do
echo "Backuping up: $line"
H="localhost"
DB=$line
GetFullBackup
done < $DBLIST
echo "Good Luck, All DB backups done" >> $LOGFOLDER
LastLogLine
#
# EOF

Testing the Backup

/usr/local/bin/mysql-backups.sh

Automating the Script

"chmod 744 /usr/local/bin/mysql-backup.sh"

//you can use to crontab using the command "crontab -e"

15 21 * * * /usr/local/bin/mysql-backups.sh > /dev/null 2>&1

Recovery – mysql scripting examples

cd /data/db-backups
gunzip db-mybigdatabase-202204552-985654.sql.gz
mysql < db-mybigdatabase-20220455-985654.sql

I hope you get an idea about mysql backup scripts.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

Related posts:

  1. how to add two decimal numbers in shell script?
  2. How To Create Image Hover Overlay Effects?
  3. how to show an image in php from mysql database example?

Leave a Comment