#!/bin/bash
#-------------------------------------------
#
# Backup Shell Script
#
# Developer : lhj1010
#
# date 2021-04-20
#-------------------------------------------
#===========================================
# Backup Host List
#-------------------------------------------
#
# /data/mysql_backup/shell_script/host.txt
#
#===========================================
# Variable Declaration
BAK_FILE_NM=_`date +"%Y%m%d"`.sql
BAK_LOG_FILE_NM=DB_compare_`date +"%Y%m%d"`.log
BAK_FILE_SAVE_PATH=/data/mysql_backup/database_schema_compare
BAK_FILE_DIRECTORY=`date +"%Y%m%d"`
WEEK_AGO=`date -d '1 week ago' +"%Y%m%d"`
MYSQLDUMP_CNF=/etc/my.cnf.d/mysql-clients.cnf
MYSQL_PORT=14306
# create backup directory
mkdir -p ${BAK_FILE_SAVE_PATH}/${BAK_FILE_DIRECTORY}
# backup start
for MYSQL_HOST in $(cat /data/mysql_backup/shell_script/host.txt);
do
echo `date +"%Y-%m-%d %H:%M:%S"`" <<<"$MYSQL_HOST" backup shell script start!>>>" >> ${BAK_FILE_SAVE_PATH}/${BAK_FILE_DIRECTORY}/${BAK_LOG_FILE_NM}
mysqldump --defaults-extra-file=$MYSQLDUMP_CNF --routines --skip-lock-tables --single-transaction --set-gtid-purged=OFF --skip-opt --all-databases --no-data --dump-date=FALSE --skip-comments -h ${MYSQL_HOST} -P $MYSQL_PORT > ${BAK_FILE_SAVE_PATH}/${BAK_FILE_DIRECTORY}/$MYSQL_HOST${BAK_FILE_NM} 2>&1 &&
echo `date +"%Y-%m-%d %H:%M:%S"`" <<<"$MYSQL_HOST" backup shell script end!>>>" >> ${BAK_FILE_SAVE_PATH}/${BAK_FILE_DIRECTORY}/${BAK_LOG_FILE_NM}
echo "" >> ${BAK_FILE_SAVE_PATH}/${BAK_FILE_DIRECTORY}/${BAK_LOG_FILE_NM} 2>&1
done
# 1 week ago backup delete
rm -rf $BAK_FILE_SAVE_PATH/$WEEK_AGO
<aside> 💡 mysqldump시 스키마 구조외 dumptime, auto_increment, DB Host 등 각종 변수를 가져오는데, 이 때문에 스키마 무결성 체크를 수행해도 다르다고 나옴. 그래서 추가된 옵션들 --skip-opt #auto_increment 등의 변수를 Skip —dump-date=FALSE #dump_time Skip --skip-comments #Host 등의 comments Skip
</aside>
diff -q --from-file *.sql; echo $? >> log.txt 2>&1
if 스키마 구조가 같다 :
else :
mysqldbcompare로 비교 (Windows. mysqldbcompare.exe) DB to DB 1:1을 비교하는 방법은 수십가지가 존재하므로, 편한대로 찾아서 하면된다. (ex. vimdiff file1 file2)