HOW BACKUP EVERY TABLE USING SHELL SCRIPT


Berikut adalah sedikit shell script untuk melakukan backup per table dari suatu database. Cocoknya script ini dijalankan di single server (tidak memiliki server backup), namun pada script ini juga dibuat satu parameter sehingga bisa di backup di ke server backup dalam bentuk text file (.sql) dan dimport ke server backup.

Kelemahan dari backup ini adalah : Backup tidak dilakukan secara realtime, tapi H-1. Jadi ketika database corrupt & hardisk server crash data terakhir adalah H-1

#!/bin/bash
#A UNIX / Linux shell script to backup per table!!!!!
# ————————————————————————-
# Copyright (c) 2011 Mustafa Tambunan <mustafa@intelix.co.id>
# Created : 2011-12-23 03:00:00
# ————————————————————————-
# Tested under RHEL / CentOS 5.5
# note :
# Jalankan di crontab setiap jam 1 pagi : contoh : 0 1 * * * /shell/filename.sh > /dev/null

#IP DARI SERVER UTAMA
_SRC_HOST=”192.168.137.100″
_SRC_USER=”ecentrix”
_SRC_PASSWD=”mosespassword”
_SRC_OPT=” –no-create-info –compact –skip-quote-names”
_SRC_DBNAME=”ecentrix_addons” # nama datbase di server utama

#IP/LOCALHOST DARI SERVER BACKUP
_DEST_HOST=”localhost”
_DEST_USER=”ecentrix”
_DEST_PASSWD=”mosespassword”
_DEST_DBNAME=”ecentrix_addons” #nama database di server backup

_BACKUP_IS_AVAILABLE=”N”

_CMD_MYSQLDUMP=”$(which mysqldump)”
_CMD_MYSQL=”$(which mysql)”

if [ $1 != “” ]; then
_DATE=$1
else
_DATE=$(date –date=”1 days ago” +”%Y-%m-%d”)
fi

exit
_DIR_SQL_DATA=”/home/ecentrix/backup_db/${_SRC_DBNAME}/${_DATE}”
_LOG_DIR=”/home/ecentrix/backup_db/${_SRC_DBNAME}/log/”

mkdir -p ${_DIR_SQL_DATA}
mkdir -p ${_LOG_DIR}

#cat > ${_LOG_DIR}”${_DATE}.log”

_logger(){
LOG_FORMAT=” [ “$( date +’%H:%M:%S’)”] ”
echo $LOG_FORMAT $1
echo $LOG_FORMAT $1 >> ${_LOG_DIR}”${_DATE}.log”
}

_ARR_TABLE=(
“table_name” “field_param”
“dashboard_user” “created_time”
“dashboard_user_privilege” “created_time”
“ecentrix_sms_in” “receive_date”
“ecentrix_sms_out” “send_date”
“ecentrix_sms_call” “created_time”
“ecentrix_fax” “created_time”
“ecentrix_voice_mail” “created_time”
“ecentrix_blocked_caller” “created_time”
“ecentrix_fax” “created_time”
“pp_fax” “created_time”
“pz_fax” “created_time”
“pz_sms_out” “created_time”
)

_COUNT_ARR_TABLE=13
_LIMIT_TBL_NAME=$((($_COUNT_ARR_TABLE*2)-4))

_TBL_NAME=0
_TBL_PARAM=1
_C=2

while [ ${_TBL_NAME} -le ${_LIMIT_TBL_NAME} ] ; do
_TBL_NAME=$( expr ${_TBL_NAME} + ${_C} )
_TBL_PARAM=$( expr ${_TBL_PARAM} + ${_C} )

if [ “${_ARR_TABLE[${_TBL_PARAM}]}” == “” ]; then
_WHERE=” ”
else
_WHERE=” –where=date(${_ARR_TABLE[${_TBL_PARAM}]})=’${_DATE}'”
fi

#_logger ${_WHERE}
#dump to text file then import to database backup every day
${_CMD_MYSQLDUMP} -h ${_SRC_HOST} -u${_SRC_USER} -p${_SRC_PASSWD} ${_SRC_OPT} ${_SRC_DBNAME} ${_ARR_TABLE[${_TBL_NAME}]} ${_WHERE} | replace “INSERT
INTO” “INSERT IGNORE INTO “> ${_DIR_SQL_DATA}/${_ARR_TABLE[${_TBL_NAME}]}”.sql”
if [ “$?” -eq 0 ];then
_TBL_SIZE=$(ls -lah ${_DIR_SQL_DATA}/${_ARR_TABLE[${_TBL_NAME}]}.sql | awk ‘{ print $5}’)
_logger “Success, dump table ${_ARR_TABLE[${_TBL_NAME}]} with file size = ${_TBL_SIZE}”
if [ “${_BACKUP_IS_AVAILABLE}” == “Y” ]; then
if [[ -f ${_DIR_SQL_DATA}/${_ARR_TABLE[${_TBL_NAME}]}.sql && ${_TBL_SIZE} > 0 ]]; then
#start import to database backup
${_CMD_MYSQL} -h ${_DEST_HOST} -u${_DEST_USER} -p${_DEST_PASSWD} –database ${_DEST_DBNAME} < ${_DIR_SQL_DATA}/${_ARR_TABLE[$
{_TBL_NAME}]}”.sql”
if [ “$?” -eq 0 ];then
_logger “Success, import table ${_ARR_TABLE[${_TBL_NAME}]}”
else
_logger “Fail, import table ${_ARR_TABLE[${_TBL_NAME}]}”
fi
#edn import to backup
else
_logger “Not import table ${_ARR_TABLE[${_TBL_NAME}]} bcz size = 0 or file not exits”
fi
fi
else
_logger “Fail, dump table ${_ARR_TABLE[${_TBL_NAME}]}”
fi
done

#dump lates schema database ecentrix every month
_DATE_LAST_MONTH=”$( date +%Y-%m)-“`cal $( date +%m) $( date +%Y) |tail -2|awk ‘{print $NF;exit}’`
if [ “$( date +%F )” == “${_DATE_LAST_MONTH}” ]; then
${_CMD_MYSQLDUMP} -h ${_SRC_HOST} -u${_SRC_USER} -p${_SRC_PASSWD} -d –quick –database ${_SRC_DBNAME} | gzip -c > ${_DIR_SQL_DATA}/${_SRC_DBNAME}-d
b-schema.sql.gz
fi

cat > ${_LOG_DIR}fail.log
egrep -wRZ “Fail” ${_LOG_DIR} >> ${_LOG_DIR}fail.log

Jika harus dibackup ke server backup maka rubah parameter:

_BACKUP_IS_AVAILABLE=”Y”

dan sesuaikan parameter

#IP DARI SERVER UTAMA
_SRC_HOST=”192.168.137.100″
_SRC_USER=”ecentrix”
_SRC_PASSWD=”mosespassword”
_SRC_OPT=” –no-create-info –compact –skip-quote-names”
_SRC_DBNAME=”ecentrix_addons” # nama datbase di server utama

#IP/LOCALHOST DARI SERVER BACKUP
_DEST_HOST=”localhost”
_DEST_USER=”ecentrix”
_DEST_PASSWD=”mosespassword”
_DEST_DBNAME=”ecentrix_addons” #nama database di server backup

 

Terimakasih

 

 

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

shisdew

Listens until think alike

moses.spaceku@yahoo.com / voip ipbx

Hosted PBX, IP-PBX SOHO/ CALL CENTER, VOICE GATEWAY, VOICE CARD, COST EFECTIVE SOLUTIONS (LCR), GSM/CDMA GATEWAY

%d bloggers like this: