MySQL full DB dump -shell script

#!/bin/bash
#Shell script to backup MySQL database
#To set permission to user which can take  backup of all the DB in
#MySQL server. plzsee query below
#mysql> grant SELECT, LOCK TABLES  on  *.*  to
#’backup_user’@’localhost’ identified  by  ‘pwd_of_user’;
#
#(c)arunbagul@indiangnu.org
###############################################################
#DB details
user_name=”user”
user_pwd=”password”
myhost=”localhost”

# Linux bin paths, change this if it can’t be autodetected via which command
mysql_cmd=”$(which mysql)”
dump_cmd=”$(which mysqldump)”
CHOWN=”$(which chown)”
CHMOD=”$(which chmod)”
zip_cmd=”$(which gzip)”

# Backup Dest directory, change this if you have someother location
dest_dir=”/var/mysql-backup/

# Get hostname
host_name=”$(hostname)”

# Get data in dd-mm-yyyy format
NOW=”$(date +”%d-%m-%Y”)”

# File to store current backup file
FILE=””
# Store list of databases
DB_list=””

# DO NOT BACKUP these databases
Ignore_db=”test”

[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $dest_dir
$CHMOD 0600 $dest_dir

# Get all database list first
DB_list=”$($mysql_cmd -u$user_name -h $myhost -p$user_pwd   -Bse ‘show
databases’)”

#send db list to file and clear previous  content
echo “” > $mail_file
echo “” > $database_file

for db in $DB_list
do
skipdb=-1
#send DB list to file
echo $db >> $database_file
echo $db
if [ “$Ignore_db” != “” ];
then
for i in $Ignore_db
do
[ “$db” == “$i” ] && skipdb=1 || :
done
fi

if [ “$skipdb” == “-1″ ] ; then
FILE=”$dest_dir/$db.$host_name.$NOW.gz”
my_file=”$dest_dir/${db}_${host_name}.sql”
# 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 🙂
# $dump_cmd -u $user_name -h $myhost -p$user_pwd $db | $zip_cmd -9 > $FILE
# DB dump start here
########################################
$dump_cmd -u $user_name -h $myhost -p$user_pwd $db > $my_file
if [ $? -eq 0 ]; then
echo $db >> $mail_file
fi
########################################
fi
done

#end

Similar Posts:

• Subversion (svn/cvs) repository backup script
• Rsync Client script
• Installing Zabbix (Server and Agent) On Ubuntu Gusty7.10
• Create Users And Change Passwords With A Bash Script
• Squid with MySQL – authentication
• How to smarthost on linux using sendmail
• Script for checking CPULOAD and send email to admin