Monthly Archives: December 2011

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

STRESS TEST ON ASTERISK


sip.conf

[sipp]
type=friend
context=sipp
host=dynamic
port=6000
user=sipp
canreinvite=no
disallow=all
allow=ulaw

[6666]
context=stresstool
type=friend
callerid=”6666″ <6666>
username=6666
secret=6666
host=dynamic ; This device needs to register
disallow=all
allow=gsm ; GSM consumes far less bandwidth than ulaw
allow=ulaw
allow=alaw

extension.conf

[stresstool]
exten => 3000,1,NoOp(STRESS TOOL ASTERISK)
exten => 3000,2,Set(GROUP()=counter)
exten => 3000,3,NoOp(TOTAL COUNTER = ${GROUP_COUNT(${counter})})
exten => 3000,4,Playback(vm-record-prepend)
exten => 3000,5,SetMusicOnHold(default)
exten => 3000,6,WaitMusicOnHold(30)
exten => 3000,7,Hangup()

[sipp]
exten => 2500,1,Goto(stresstool,3000,1)

extension 6666 di daftarkan sebagai nomor extension xlite yang akan digunakan untuk melakukan call ke 3000 pada saat script ini dijalankanĀ sipp -sn uac -d 60000 -s 2500 192.168.0.187 -l 60 -mp 5606 -trace_err.

-d 60000 = 60 detik = 1menit

-l 60 jumlah call

-mp 5606 = codec ulaw

==================================================================================

http://sourceforge.net/projects/sipp/files/sipp/3.2/sipp.svn.tar.gz/download

tar sipp.svn.tar.gz -C /usr/src/

cd /usr/src/

make

cp sipp /usr/bin

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: