SAMPLE SCRIPT FOR BACKUP OF MYSQL DB DUMP DAILY BASIS WITH TABLE DECLARATION

Hi everyone,

Before going to  start some sample script on Linux, i would like to tell you why i have created this blog. When i  newly joined in  company as a fresher, my work is to monitor our existing server. I have struggled a lot to find out a way to monitor system automatic script for backup and for alert. I have searched in internet , but i didn't find out exact script for my self with basic command script.

SAMPLE SCRIPT FOR BACKUP OF MYSQL DB DUMP DAILY BASIS WITH TABLE DECLARATION

Description:
This script will be  need of two running server in same LAN, as keeping the backup in a  same  server is not useful because of server crash fear. If we are keeping the backup in a same server, if server got crash we will lost the backup also.

create a file  using below command
 vi "filename".sh

#!/bin/bash
DATE=`(date --date='today' '+%Y-%m-%d')`
REMOVED=`(date --date='1 day ago' '+%Y-%m-%d')`

SRCPATH="/home/samplepath"
DEST_USERNAME="sampleuser"
DEST_PASSWORD="sampleuser"

DEST_IP="10.0.0.1"

DEST_PATH="/home/sampleuser/BACKUP"
`mkdir -p $SRCPATH/DailyDBDump/PROJECTX/$DATE/`


echo "Backup started"

echo "========================samplePROJECTX======================="
mysqldump -uroot -proot --routines --lock-tables=FALSE  "DATABASENAME"  "TABLENAMES"   > /home/sampleuser/DailyDBDump/PROJECTX/$DATE/PROJECTX_$DATE.sql
        echo `top -b -n1 |awk 'NR>0 && NR<2 {print $0}' | awk -F',' '{print $4","$5","$5}' | awk -F':' '{print $2}'`

cd $SRCPATH/DailyDBDump/PROJECTX/$DATE/
tar -czf samplePROJECTX_$DATE.tar PROJECTX_$DATE.sql
lftp -e "cd $DEST_PATH; mput samplePROJECTX_$DATE.tar ; quit" -u $DEST_USERNAME,$DEST_PASSWORD sftp://$DEST_IP
rm PROJECTX_$DATE.sql
cd $SRCPATH/DailyDBDump/PROJECTX/
rm -rf $REMOVED



        echo "Completed the Backup Process."

Note: Once the file saved you have to follow below point
1) change the file permission using "chmode +x filename.sh". Once changing the permission of the file you will be able to run that script.
2)Before running the script SRCPATH u have to give current path of the server. it will be helpfull if u give separate for this location other the default location of user.
i)DEST_USERNAME : destination username
ii)DEST_PASSWORD: destination password
iii)DEST_IP :destination IP
iv)DEST_PATH: destination path, u have to create this destination path in  destination server
v)DATABASENAME : name of database which you have to take
vi) TABLENAMES: which all table you want u can give with space separated. If u want whole database, u can ignore this table list column.
3) Mysql dump will take default  all procedures for above command
4)lftp command will send the file based on destination path and will use the username and password of destination ip from script.