根据字符串与所有库相匹配输出包含该字符串的库,表,字段
背景:
要求在所有库下面查找出包含有域名(www.xxxx.com)字符串的库,表,字段
#!/bin/bash
# line: V1.3
# mail: gczheng@139.com
# data: 2018-07-06
# script_name:
#=======================================================================
# MySQL 连接选项
#=======================================================================
MY_USER="root"
MY_PASSWORD="iforgot"
MY_IP=192.168.49.251
MYSQL_CONN_OPTION=" -u$MY_USER -p$MY_PASSWORD -h$MY_IP -N"
#=======================================================================
# 5.5文件和日志目录
#=======================================================================
CHK_FILE=check_table-$MY_IP.log
if [ -d $CHK_FILE ];then
echo "文件存在"
else
echo "文件不存在"
touch $CHK_FILE
fi
#=======================================================================
# echo添加颜色
#=======================================================================
echo_color(){
color=${1} && shift
case ${color} in
black)
echo -e "\e[0;30m${@}\e[0m"
;;
red)
echo -e "\e[0;31m${@}\e[0m"
;;
green)
echo -e "\e[0;32m${@}\e[0m"
;;
yellow)
echo -e "\e[0;33m${@}\e[0m"
;;
blue)
echo -e "\e[0;34m${@}\e[0m"
;;
purple)
echo -e "\e[0;35m${@}\e[0m"
;;
cyan)
echo -e "\e[0;36m${@}\e[0m"
;;
*)
echo -e "\e[0;37m${@}\e[0m"
;;
esac # --- end of case ---
}
#=======================================================================
# 行号自增和判断执行情况
#=======================================================================
rownum=0
rownum=`expr $rownum + 1`
function run_status()
{
if [ $? -eq 0 ];then
echo_color green "$(date +'%Y-%m-%d %H:%M:%S') Command execution successful..."
else
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') Command execution failed, please check..."
exit 1
fi
}
function auto_increment()
{
rownum=`expr $rownum + 1`
}
#=======================================================================
#判断帐号和IP是否异常
#=======================================================================
function conn_check()
{
echo_color purple "$rownum,检查mysql连接是否正常"
mysql $MYSQL_CONN_OPTION -e 'select @@hostname as Update_Host;'
if [ "$?" -ne 0 ];then
echo_color red "$rownum,mysql连接异常,请检查帐号密码和主机名/IP......"
exit 1
fi
auto_increment
}
#=======================================================================
#检查帐号、表、磁盘空间、触发器
#=======================================================================
function run_check()
{
#3检查所有库里面的表
echo_color purple "$rownum,检查所有库里面的表"
TABLE_SCHEMAS=`mysql $MYSQL_CONN_OPTION -e "SELECT DISTINCT TABLE_SCHEMA FROM information_schema.COLUMNS where TABLE_SCHEMA not in ('sys','mysql','information_schema','performance_schema','test')"`
for schema in $TABLE_SCHEMAS
do
TABLE_NAMES=`mysql $MYSQL_CONN_OPTION -e "SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMNS where TABLE_SCHEMA='$schema'"`
echo_color yellow "$TABLE_NAMES"
for table in $TABLE_NAMES
do
COLUMN_NAMES=`mysql $MYSQL_CONN_OPTION -e "SELECT DISTINCT COLUMN_NAME FROM information_schema.COLUMNS where TABLE_SCHEMA='$schema' and TABLE_NAME='$table'"`
echo_color red "$COLUMN_NAMES"
for column in $COLUMN_NAMES
do
nums=`mysql $MYSQL_CONN_OPTION -e "SELECT $column FROM $schema.$table where $column like '%www.xxxx.com%'"|grep -w 'www.xxxx.com' |wc -l`
if [ $nums -gt '0' ];then
echo $schema.$table.$column >> $CHK_FILE
fi
sleep 0.1
done
done
done
}
conn_check
run_check
echo_color blue "$(date +'%Y-%m-%d %H:%M:%S') \033[42;37m 脚本执行完成 \033[0m" >>$CHK_FILE
执行结果
iusm.account_icon.icon_url
iusm.operator_info.iconurl
iusm.t_da_anchor_role.f_idphoto
iusm.t_shopping_main_product.f_postlist
iusm.t_user_suggestion.f_pic_url
2018-07-12 09:26:19 脚本执行完成