Shell Script To Backup MySql Database Server

#!/bin/bash
# Shell script to backup MySql database 
# To backup Nysql databases file to /backup dir and later pick up by your 
# script. You can skip few databases from backup too.
# For more info please see (Installation info):
# https://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html 
# Last updated: Aug - 2005
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------
 
MyUSER="SET-MYSQL-USER-NAME"     # USERNAME
MyPASS="SET-PASSWORD"       # PASSWORD 
MyHOST="localhost"          # Hostname
 
# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
 
# Backup Dest directory, change this if you have someother location
DEST="/backup"
 
# Main directory where backup will be stored
MBD="$DEST/mysql"
 
# Get hostname
HOST="$(hostname)"
 
# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"
 
# File to store current backup file
FILE=""
# Store list of databases 
DBS=""
 
# DO NOT BACKUP these databases
IGGY="test"
 
[ ! -d $MBD ] && mkdir -p $MBD || :
 
# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST
 
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
 
for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
	for i in $IGGY
	do
	    [ "$db" == "$i" ] && skipdb=1 || :
	done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
	FILE="$MBD/$db.$HOST.$NOW.gz"
	# do all inone job in pipe,
	# connect to mysql using mysqldump for select mysql database
	# and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done

Save the script and run it as a cron job:
@daily /path/to/yourmysql.sh

Get the latest tutorials on SysAdmin, Linux/Unix, Open Source, and DevOps topics:
CategoryList of Unix and Linux commands
File Management cat
Firewall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilities dig host ip nmap
OpenVPN CentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Manager apk apt
Processes Management bg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searching grep whereis which
User Information groups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPN CentOS 8 Debian 10 Firewall Ubuntu 20.04
55 comments… add one
  • Luis Anaya Oct 17, 2013 @ 20:33

    This is a really nice script although I did some changes for it, nothing major:

    – Commented the CHOWN. For some reason is was doing a root wide CHOWN, being that I am not running the backups as root, I do not need to CHOWN the files anyway, they’ll be owned by the administrative account.
    – I had to add the list of databases that I wanted not to back up, I though that just adding the ones that I wanted would exclude the ones that I did not wanted to.

    But, it is a very handy script and I appreciate you posting it. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.