#!/bin/bash
#program:HTS-A数据库和插件检查
#author:sundz
#version 20220531 v1 创建脚本 生成sql的表和字段汇总;aboss5版本
#version 20220805 v2 添加备份文件夹;添加关于资金汇总 持仓汇总至sql_file;添加文件时间戳;每个库都添加日期判断是否恢复成功
#version 20220816 v3 检查是否恢复成功也加入进程判断；添加总行数汇总
#ip_0和ip_1获取IP后缀 以作区分
ip_0=`ip a | grep inet | grep -v inet6 | awk 'NR==2{print $2}' | cut -d '.' -f 4`
ip_1=${ip_0%%/*}
date=$(date +%Y%m%d)
#log_time=$(date +%H:%M:%S)
backup_floder=/home/aboss/backup/${date}
file_time=$(date +%Y%m%d%H%M%S)
sql_file=sqlchk_${file_time}.txt
#axtool_file=aboss5_ver_${file_time}_${ip_1}.txt
axtool_file=aboss5_ver_${file_time}.txt
#检测当天文件夹是否存在
[ ! -d ${backup_floder} ] && mkdir -p ${backup_floder}
echo 'excuting...'
#mysql数据备份检查
mysql_chk=`ps x | grep 3306 | grep -v 'grep' | wc -l`
if [ "${mysql_chk}" == 1 ]; then
#资金汇总
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e \
'select sum(ZHYE) ZHYE from a5_fund.tfc_zjzh;' > ${sql_file} 2>/dev/null
#持仓汇总
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e \
'select sum(ZQSL) ZQSL from a5_sectrade.tsc_zqgl;' >> ${sql_file} 2>/dev/null
#总行数汇总
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e \
'select sum(TABLE_ROWS) ALL_ROWS from information_schema.TABLES t
where
t.TABLE_SCHEMA in ("a5_dtx","a5_fund","a5_manage","a5_secinit","a5_sectrade","a5_sysinfo") and t.TABLE_NAME not like "%copy%";' >> ${sql_file} 2>/dev/null
#检查表名
echo "开始所有表汇总..." >> ${sql_file}
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e \
'select t.TABLE_SCHEMA , t.TABLE_NAME ,t.TABLE_COLLATION from information_schema.TABLES t
where
t.TABLE_SCHEMA in ("a5_dtx","a5_fund","a5_manage","a5_secinit","a5_sectrade","a5_sysinfo") and t.TABLE_NAME not like "%copy%";' >> ${sql_file} 2>&1
#检查字段
echo "开始表字段汇总..." >> ${sql_file}
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e \
'select c.TABLE_SCHEMA ,c.TABLE_NAME ,c.COLUMN_NAME ,c.COLUMN_TYPE ,c.COLUMN_KEY from information_schema.COLUMNS c
where c.TABLE_SCHEMA in ("a5_dtx","a5_fund","a5_manage","a5_secinit","a5_sectrade","a5_sysinfo")
and c.TABLE_NAME not like "%copy%";' >> ${sql_file} 2>&1
mv ${sql_file} ${backup_floder}
#检查是否恢复成功
temp=temp.txt
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e 'select * from a5_dtx.tq2_work where jyrq=curdate();' > ${temp} 2>/dev/null
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e 'SELECT * FROM a5_odeliver11.tod_hostchk where date=curdate();' >> ${temp} 2>/dev/null
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e 'SELECT * FROM a5_odeliver12.tod_hostchk where date=curdate();' >> ${temp} 2>/dev/null
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e 'select * from a5_fund.tfc_xtzt where JYRQ=curdate();' >> ${temp} 2>/dev/null
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e 'select * from a5_manage.tmc_xtzt where JYRQ=curdate();' >> ${temp} 2>/dev/null
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e 'select * from a5_sectrade.tsc_xtzt where JYRQ=curdate();' >> ${temp} 2>/dev/null
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e 'select * from a5_secinit.tsc_xtzt where JYRQ=curdate();' >> ${temp} 2>/dev/null
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e 'SELECT * from a5_sc_syncnode1_11.tnodeinfo where TradeDate=curdate();' >> ${temp} 2>/dev/null
mysql -uroot -pP@ssw0rd123.abc -h127.0.0.1 -P3306 -e 'SELECT * from a5_sc_syncnode1_12.tnodeinfo where TradeDate=curdate();' >> ${temp} 2>/dev/null
echo "检查恢复结果...周五请忽略"
[ -s ${temp} ] && echo "检查不通过" || echo "检查通过"
else
echo "[ERROR] mysql process not running,please check"
echo "[ERROR] $(date +%Y%m%d%H%M%S) mysql process not running,please check" > ${sql_file}
mv ${sql_file} ${backup_floder}
fi
#aboss程序检查
cd /home/aboss/aboss5/bin
./axtool hotfix -b all > ${axtool_file} 2>&1
mv ${axtool_file} ${backup_floder}
echo "excuted"
#压缩删除生成文件，按照IP获取后缀 看现场需求，如有可启用
#cd
#tar zcvf chk_A_${date}_${ip_1}.tgz ${sql_file} ${axtool_file} > /dev/null
#rm ${axtool_file} ${sql_file} > /dev/null
#echo 'excuted...'
