Mysql Backup Script

images

This is the Simple Shell Script to take the dump of Mysql Database either full or selective.
Requirements:
O.S:Linux
Editor:gedit vim according to our choice.
Software:mysql server installed in Linux.

Steps:
1. Write the shell script as Below.

#!/bin/bash
#shell script to take the backup of the mysql database dump
#first shell script will check for the user is root user or not,if not then it  exits.

user=`whoami`

if [ $user != ‘root’ ];then
echo ‘user $user  is not a root user this insidence will be reported ‘

exit 12
fi

host=’localhost’
mysql_user=’root’
mysql_pass=’root’
date=`date +%y-%m-%d`
#mysql dump used to create the dump of the database
echo “======================list of known databases==========================”

mysql -uroot -proot -e “show databases”

###########################################
#asking for the user which database you want to dump
echo “FULL or SELECTIVE”
echo  “Choose your database dump option”
read SEL
case $SEL in
FULL)
echo “Backup of all database”
mysqldump -uroot -proot –all-databases > /var/tmp/1.sql
if [ $? -eq 0 ];then
echo ‘database dump successful’
else
echo ‘database dump failed’
fi
exit 15
;;
SELECTIVE)
echo “Backup of selective database choose database you want to dump?”
read database
mysqldump -h$host -u$mysql_user -p$mysql_pass -B $database>$database.$date.sql
if [ $? -eq 0 ];then
echo ‘database dump successful’
else
echo ‘database dump failed’
fi
exit 15
;;
esce

2.save the shell script by <any name>.sh extension

3.We can run shell script either giving the executable permission to the file or by sh command as below
3.1 root@L595:~/shellscript#chmod 777 mysqldump.sh
root@L595:~/shellscript#./mysqldump.sh
or
root@L595:~/shellscript#sh mysqldump.sh

OUTPUT:
==================================================================================================
root@L595:~/shellscript# sh mysqldump.sh
======================list of known databases==========================
+——————–+
| Database           |
+——————–+
| information_schema |
| employee           |
| misal              |
| mysql              |
| performance_schema |
+——————–+
FULL or SELECTIVE
Choose your database dump option
FULL
Backup of all database
database dump successful
root@L595:~/shellscript#

*/backup of selective database */

root@L595:~/shellscript# sh mysqldump.sh
======================list of known databases==========================
+——————–+
| Database           |
+——————–+
| information_schema |
| employee           |
| misal              |
| mysql              |
| performance_schema |
+——————–+
FULL or SELECTIVE
Choose your database dump option
SELECTIVE
Backup of selective database choose database you want to dump?
employee
database dump successful
root@L595:~/shellscript#ls -l
employee.15-03-22.sql

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s