schema_wise_backup

STEP 1: Create config file

#<><><><><><><><><><><> CONFIG FILE <><><><><><><><><><><><>
DB_HOST1="source_ip"
DB_USER1="user"
DB_PASSWORD1="pass"


STEP 2: Create backup_process script for taking backup

<><><><><><><><><><><> backup_process.sh <><><><><><><><><><><><>

#!/bin/bash
echo "------------- Taking DUMP of tables --------------------"

HOST=`cat cred.txt | grep 'DB_HOST1' | cut -d '"' -f 2`
USR=`cat cred.txt | grep 'DB_USER1' | cut -d '"' -f 2`
PASS=`cat cred.txt | grep 'DB_PASSWORD1' | cut -d '"' -f 2`

echo "------------- Creating DB List --------------------"
rm -rf list.txt
/usr/local/mysql/bin/mysql -u$USR -h$HOST -p$PASS -e "select distinct table_schema from information_Schema.tables where table_schema in ('schema1','schema2');" > list.txt

echo "------------- Taking DB wise backup --------------------"
file=list.txt
for i in `awk 'NR>1' $file`; do echo "new list is" $i  ; done
for i in `awk 'NR>1' $file`;  do echo "removing old backup $i" && rm -rf $i.sql.gz ; done
for i in `awk 'NR>1' $file`;  do echo "taking dump $i" && /usr/local/mysql/bin/mysqldump -u$USR -h$HOST -p$PASS $i > $i.sql ; done
for i in *.sql; do echo "zipping $i" && gzip $i; done
#for i in *.sql.gz ; do echo "copying $i" && scp $i.sql.gz ssh_user@Destination_IP:<Path> ; done
#for i in `cat $file`; do echo Count for $i && mysql -u$USR -h$HOST -p$PASS -e "select count(1) from db.$i;" ; done