执行步骤：
1.备份mysql数据库
备份工具目录结构：
[ansible@java2 wz-temp]$ tree mysql_pg/
mysql_pg/
├── conf
│ └── common.sh
├── dump_mysql.sh
└── logs
├── mysql_bak_tables20180514.log
├── mysql_bak_tables20180517.log
├── mysql_bak_tables20180601.log
...
2 directories, 8 files
备份工具dump_mysql.sh：
#!/bin/bash
# 注意：执行该脚本一定要先进入到脚本的当前目录下
source ./conf/common.sh
set -o errexit
set -o nounset
DUMP_CMD="/usr/local/mysql/bin/mysqldump"
DB_CONN="-u rds_read -pxxxx -h 192.168.x.x"
ARGS=" --skip-opt --compatible=postgresql --compact --no-create-info --default-character-set=utf8 --skip-comments --skip-dump-date --set-gtid-purged=OFF --skip-tz-utc "
DB_NAME="xxx_db"
DISK_USAGE=3
BackPath="/web/share/backup_tables_pg"
LOG_PREFIX="mysql_bak_tables"
dat="`date +%F`"
# 判断执行返回结果
Execution_result(){
local method_name="$1"
local right_message="$2"
local error_message="$3"
if [[ $? -eq 0 ]]; then
LOG_INFO "${method_name}" "${right_message}"
else
LOG_ERR "${method_name}" "${error_message}"
exit 1
fi
}
# 磁盘空间检测
Check_disk(){
set +o errexit
local func_name="Check_disk"
DiskFree=`df -h | awk -F 'G' 'NR==2 {print $3}'`
c=$(echo " $DiskFree < $DISK_USAGE" | bc) # 小数点的运算
if [[ $c -eq 1 ]]; then
LOG_ERR "${func_name}" "there have no enough disk space."
exit 1
else
LOG_INFO "${func_name}" "Check disk spacess passed."
fi
set -o errexit
}
# 备份表
Backup_tables(){
local func_name="Backup_tables"
Check_disk
if [[ ! -d "${BackPath}" ]]; then
LOG_WARN "${func_name}" "Backup path is not exists, and create it now."
sudo /bin/mkdir -p "${BackPath}"
else
LOG_INFO "${func_name}" "Backup path is available"
fi
# Backup tables
for i in vip_level vip_level_benefit vip_user_level; do
sudo "${DUMP_CMD}" ${DB_CONN} ${ARGS} ${DB_NAME} "${i}" > "${BackPath}"/"${i}"_"${dat}".sql
Execution_result "${func_name}" "Backup table $i succeed." "Backup table $i failed."
done
}
# 对vip_level_benefit表处理成pg能接收的格式
Data_deal(){
local func_name="Data_deal"
if [[ ! -f ${BackPath}/vip_level_benefit_${dat}.sql ]]; then
LOG_ERR "${func_name}" "vip_level_benefit_${dat}.sql is not exists."
exit 1
else
sudo sed -i 's/\\//g' "${BackPath}"/vip_level_benefit_"${dat}".sql
Execution_result "${func_name}" "deal with backup data of vip_level_benefit succeed." "deal with data of vip_level_benefit failed."
fi
}
# 清理备份
Clean_oldback(){
local func_name="Clean_oldback"
sudo find "${BackPath}" -type f -mtime +5 -name "vip_*.sql" -exec rm -rf {} \;
Execution_result "${func_name}" "Clean old tables succeed." "Clean old tables failed."
}
main(){
Backup_tables
Data_deal
Clean_oldback
}
main
日志文件：common.sh , 代码见另一篇文章，Bash脚本的日志公用方法
2.将数据导入到pg数据库中
目录结构:
tree import_pg
import_pg
├── conf
│ └── common.sh
├── import_pg.sh
└── logs
├── import_pg_data20180517.log
├── import_pg_data20180601.log
├── import_pg_data20180612.log
....
2 directories, 7 files
备份恢复到pg中脚本，import_pg.sh
#!/bin/bash
# 注意：执行该脚本一定要先进入到脚本的当前目录下
source ./conf/common.sh
set -o errexit
set -o nounset
BackPath="/web/share/backup_tables_pg"
LOG_PREFIX="import_pg_data"
HOST="10.26.20.75"
USER="p2p_activity_rw"
DBNAME="p2p_activity"
PORT="8888"
NAME_suffix="$(date +%F.sql)"
Result_time="`date '+%Y-%m-%d %H:%M:%S'`"
TABLE_LIST="vip_level_${NAME_suffix} vip_level_benefit_${NAME_suffix} vip_user_level_${NAME_suffix}"
#TABLE_LIST="vip_level_${NAME_suffix} vip_level_benefit_${NAME_suffix} "
Execution_result(){
local method_name="$1"
local right_message="$2"
local error_message="$3"
if [[ $? -eq 0 ]]; then
LOG_INFO "${method_name}" "${right_message}"
else
LOG_ERR "${method_name}" "${error_message}"
exit 1
fi
}
Clean_old_tables(){
local func_name="Clean_old_tables"
sudo /usr/bin/psql -h "${HOST}" -p "${PORT}" -U "${USER}" -d "${DBNAME}" << EOF
truncate table vip_level;
truncate table vip_level_benefit;
truncate table vip_user_level;
EOF
# truncate table vip_user_level;
Execution_result "${func_name}" "truncate tables succeed." "truncate tables failed."
}
Import_tables(){
local func_name="Import_tables"
for i in ${TABLE_LIST} ; do
sudo /usr/bin/psql -h "${HOST}" -p "${PORT}" -U "${USER}" -d "${DBNAME}" < "${BackPath}"/$i > /dev/null
Execution_result "${func_name}" "import $i to p2p_activity db succeed." "import $i to p2p_activity db failed."
done
}
main(){
Clean_old_tables
Import_tables
}
main
备注：
- 导入到pg数据库的前提条件是需配置服务器本机免密码登陆pg数据库，具体参考简书上写的另一篇文章: 从阿里云上备份pg部分表到内网还原
- 日志文件：common.sh , 代码见另一篇文章：Bash脚本的日志公用方法
