jenkins staging db newst sehll

작업은 매주 한번 실행한다.

보안 조건

조건은 접근제어 서버는 비밀번호가 없이 접근 가능하거나, sshpass 를 이용해서 비밀번호를 자동입력이 가능해야 한다.

작업 순서

1. 접근제어 서버에서 backup 명령어를 밀어넣어서 백업 실행
2. scp 를 이용 파일을 라우팅해서 복사
3. 스테이징 서버에서 db를 새롭게 구성

Production db backup

# backup, 필요시 실행
time=`date +%Y%m%d`
mysqlhost="username@db.doamin.com"
backuphost="username@dev.domain.com:mysqlBackup/"
scheme="${time}.database.sql.scheme.gz"
file="${time}.database.sql.data.gz"

#echo "mysqldump -uusername -ppassword -d -q --opt database_name | bzip2 -9 > ${scheme}" | ssh -T ${mysqlhost}
#echo "mysqldump -uusername -ppassword -t -q --opt --extended-insert database_name --ignore-table=database_name.table_name1 --ignore-table=database_name.table_name2 --ignore-table=database_name.table_name3 | bzip2 -9 > ${file}" | ssh -T ${mysqlhost}
echo "mysqldump -uusername -ppassword -d -q --opt database_name | gzip -9 > ${scheme}" | ssh -T ${mysqlhost}"
echo "mysqldump -uusername -ppassword -t -q --opt --extended-insert database_name --ignore-table=database_name.table_name1 --ignore-table=database_name.table_name2 --ignore-table=database_name.table_name3 | gzip -9 > ${file}" | ssh -T ${mysqlhost}"
mysqldump -uusername -ppassword -d -q --opt database_name | gzip -9 > ${scheme}" | ssh -T ${mysqlhost}
mysqldump -uusername -ppassword -t -q --opt --extended-insert database_name --ignore-table=database_name.table_name1 --ignore-table=database_name.table_name2 --ignore-table=database_name.table_name3 | gzip -9 > ${file}" | ssh -T ${mysqlhost}

scp -3 ${mysqlhost}:${scheme} ${backuphost}
scp -3 ${mysqlhost}:${file} ${backuphost}

echo "ls -al *.gz" | ssh -T ${mysqlhost}
echo "rm -f ${scheme}" | ssh -T ${mysqlhost}
echo "rm -f ${file}" | ssh -T ${mysqlhost}

echo "ls -al *.gz" | ssh -T ${mysqlhost}

exit 0;

staging db renew

cd mysqlBackup

# 1week over delete
find . -name '*.tgz'  -mtime +7 -delete

# restor
latest1=$(ls *.scheme.gz | tail -1)
#bunzip2 < ${latest1} | mysql -uusername -ppassword  database_stg_name
gunzip < ${latest1} | mysql -uusername -ppassword  database_stg_name

latest2=$(ls *.data.gz | tail -1)
#bunzip2 < ${latest2} | mysql -uusername -ppassword -f database_stg_name
gunzip < ${latest2} | mysql -uusername -ppassword -f database_stg_name

curl -X POST --data-urlencode 'payload={"channel": "#jenkins", "username": "jenkins", "icon_emoji": ":hear_no_evil:", "text": "stg.db.newst staging database updated!."}' https://hooks.slack.com/services/key1/key2/key3

exit 0;