基于shell自动完成mysql格式审计的项目

1，对于mysql字段长度，表名长度等规范问题，完成shell自动校验，结果存入数据库中进行查看。

项目目录结构:

├── config
│   ├── config_audit.sh
│   └── config_db.sh
├── lib
│   ├── function.sh
│   └── main_in.sh
└── main.sh

#规则参考

url:http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=28437434&id=4776846

http://blog.csdn.net/JeffreyNicole/article/details/48198587

https://zhidao.baidu.com/question/812502421126049892.html

http://blog.chinaunix.net/uid-20785090-id-5031014.html

具体脚本内容 :

1，数据库脚本:

create database explaindb character set 'utf8'; #version 5.7

CREATE TABLE `audit_tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `db_name` varchar(100) NOT NULL COMMENT '库名',
  `tb_name` varchar(100) NOT NULL COMMENT '表名',
  `col_name` varchar(100) NOT NULL COMMENT '列名',
  `audit_type` varchar(50) NOT NULL COMMENT '审核类型:schema,table,column',
  `audit_name` varchar(100) NOT NULL COMMENT '审计的具体库名、表名、列名',
  `audit_title` varchar(100) NOT NULL COMMENT '审计类目标题,如table_name_length',
  `audit_form` int(11) NOT NULL COMMENT '审计方式：1，比较大小；2，字符串比较；',
  `audit_logic` varchar(100) DEFAULT NULL COMMENT '基于给定的审计方式确定的具体逻辑',
  `cur_value` varchar(1000) DEFAULT NULL COMMENT '当前值',
  `threshold` varchar(1000) DEFAULT NULL COMMENT '阈值',
  `comment` varchar(1000) NOT NULL COMMENT '备注',
  `status` int(11) NOT NULL COMMENT '状态:0:未通过；1:通过。2:异常',
  `updated` datetime DEFAULT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `audit_type` (`audit_type`,`audit_name`,`audit_title`,`db_name`,`tb_name`,`col_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1021 DEFAULT CHARSET=utf8 COMMENT='审计结果表';

2,审计规则脚本:config_audit.sh

:<<!规则定义:rule="审计项目名称,审计方式,审计逻辑,阈值,中文解释";rule:有table_rule01,column_rule01,index_rule01分别表示表，列，索引等的规则，表名当前条目适应的范围审计项目名称:table_name_length_limit，可以直观理解为表名长度限制的校验审计方式:1,表示数值大小比较，对应逻辑有 gt,ge,e,le,lt,ne2,表示文本内容比较，对应逻辑有 ==,!=  。比较内容忽略大小写。阈值：如果审计结果与该阈值比较为真，则审计结果插入数据库。中文解释:对“审计项目名称”的中文解释规则参考url:http://blog.csdn.net/JeffreyNicole/article/details/48198587https://zhidao.baidu.com/question/812502421126049892.htmlhttp://blog.chinaunix.net/uid-20785090-id-5031014.html!table_rule01="table_name_length_limit,1,gt,10,表名超过长度限制table_engine_innodb_check,2,!=,INNODB,表的存储引擎须是innodb";column_rules="column_name_length_limit,1,gt,9,列名超过长度限制column_coment_is_null,2,==,null,列的备注不能为空";

3，数据库连接脚本config_db.sh

#被审计数据库配置user=rootpassword=123123database=uei #被审计的具体库port=3306host=127.0.0.1#查询结果存放数据库配置touser=roottopassword=123123todatabase=explaindb#需提前建好totable=audit_tb#需提前建好toport=3306tohost=127.0.0.1

4，通用方法脚本function.sh

#editplus中寻找关键字： (.*)\n   替换关键字: (case when \1 is null or trim(\1) = '' then 'null' else \1 end),\"reqreq\",#查询information_schema中tables表的sql。分割符： reqreqsql_table="select concat((case when TABLE_CATALOG is null or trim(TABLE_CATALOG) = '' then 'null' else TABLE_CATALOG end),\"reqreq\",(case when TABLE_SCHEMA is null or trim(TABLE_SCHEMA) = '' then 'null' else TABLE_SCHEMA end),\"reqreq\",(case when TABLE_NAME is null or trim(TABLE_NAME) = '' then 'null' else TABLE_NAME end),\"reqreq\",(case when TABLE_TYPE is null or trim(TABLE_TYPE) = '' then 'null' else TABLE_TYPE end),\"reqreq\",(case when ENGINE is null or trim(ENGINE) = '' then 'null' else ENGINE end),\"reqreq\",(case when VERSION is null or trim(VERSION) = '' then 'null' else VERSION end),\"reqreq\",(case when ROW_FORMAT is null or trim(ROW_FORMAT) = '' then 'null' else ROW_FORMAT end),\"reqreq\",(case when TABLE_ROWS is null or trim(TABLE_ROWS) = '' then 'null' else TABLE_ROWS end),\"reqreq\",(case when AVG_ROW_LENGTH is null or trim(AVG_ROW_LENGTH) = '' then 'null' else AVG_ROW_LENGTH end),\"reqreq\",(case when DATA_LENGTH is null or trim(DATA_LENGTH) = '' then 'null' else DATA_LENGTH end),\"reqreq\",(case when MAX_DATA_LENGTH is null or trim(MAX_DATA_LENGTH) = '' then 'null' else MAX_DATA_LENGTH end),\"reqreq\",(case when INDEX_LENGTH is null or trim(INDEX_LENGTH) = '' then 'null' else INDEX_LENGTH end),\"reqreq\",(case when DATA_FREE is null or trim(DATA_FREE) = '' then 'null' else DATA_FREE end),\"reqreq\",(case when AUTO_INCREMENT is null or trim(AUTO_INCREMENT) = '' then 'null' else AUTO_INCREMENT end),\"reqreq\",(case when CREATE_TIME is null or trim(CREATE_TIME) = '' then 'null' else CREATE_TIME end),\"reqreq\",(case when UPDATE_TIME is null or trim(UPDATE_TIME) = '' then 'null' else UPDATE_TIME end),\"reqreq\",(case when CHECK_TIME is null or trim(CHECK_TIME) = '' then 'null' else CHECK_TIME end),\"reqreq\",(case when TABLE_COLLATION is null or trim(TABLE_COLLATION) = '' then 'null' else TABLE_COLLATION end),\"reqreq\",(case when CHECKSUM is null or trim(CHECKSUM) = '' then 'null' else CHECKSUM end),\"reqreq\",(case when CREATE_OPTIONS is null or trim(CREATE_OPTIONS) = '' then 'null' else CREATE_OPTIONS end),\"reqreq\",(case when TABLE_COMMENT is null or trim(TABLE_COMMENT) = '' then 'null' else TABLE_COMMENT end)) from tables where table_schema='${database}'   "#editplus中寻找关键字： (.*)\n   替换关键字: ["\1"]="xxxx" #获取information_schema中tables表中数据的方法function get_tb_val(){declare -A map=(["TABLE_CATALOG"]="0" ["TABLE_SCHEMA"]="1" ["TABLE_NAME"]="2" ["TABLE_TYPE"]="3" ["ENGINE"]="4" ["VERSION"]="5" ["ROW_FORMAT"]="6" ["TABLE_ROWS"]="7" ["AVG_ROW_LENGTH"]="8" ["DATA_LENGTH"]="9" ["MAX_DATA_LENGTH"]="10" ["INDEX_LENGTH"]="11" ["DATA_FREE"]="12" ["AUTO_INCREMENT"]="13" ["CREATE_TIME"]="14" ["UPDATE_TIME"]="15" ["CHECK_TIME"]="16" ["TABLE_COLLATION"]="17" ["CHECKSUM"]="18" ["CREATE_OPTIONS"]="19" ["TABLE_COMMENT"]="20") local tmp_val2=$1local talbe_row_val=(${tmp_val2//reqreq/ }); #分割符： reqreqlocal map_index=${map["$2"]}echo  ${talbe_row_val[$map_index]} | awk '{gsub("qpzmxxqpzm"," "); print $0 }'}#查询information_schema中column表的sql。sql_column="select concat((case when TABLE_CATALOG is null or trim(TABLE_CATALOG) = '' then 'null' else TABLE_CATALOG end),\"reqreq\",(case when TABLE_SCHEMA is null or trim(TABLE_SCHEMA) = '' then 'null' else TABLE_SCHEMA end),\"reqreq\",(case when TABLE_NAME is null or trim(TABLE_NAME) = '' then 'null' else TABLE_NAME end),\"reqreq\",(case when COLUMN_NAME is null or trim(COLUMN_NAME) = '' then 'null' else COLUMN_NAME end),\"reqreq\",(case when ORDINAL_POSITION is null or trim(ORDINAL_POSITION) = '' then 'null' else ORDINAL_POSITION end),\"reqreq\",(case when COLUMN_DEFAULT is null or trim(COLUMN_DEFAULT) = '' then 'null' else COLUMN_DEFAULT end),\"reqreq\",(case when IS_NULLABLE is null or trim(IS_NULLABLE) = '' then 'null' else IS_NULLABLE end),\"reqreq\",(case when DATA_TYPE is null or trim(DATA_TYPE) = '' then 'null' else DATA_TYPE end),\"reqreq\",(case when CHARACTER_MAXIMUM_LENGTH is null or trim(CHARACTER_MAXIMUM_LENGTH) = '' then 'null' else CHARACTER_MAXIMUM_LENGTH end),\"reqreq\",(case when CHARACTER_OCTET_LENGTH is null or trim(CHARACTER_OCTET_LENGTH) = '' then 'null' else CHARACTER_OCTET_LENGTH end),\"reqreq\",(case when NUMERIC_PRECISION is null or trim(NUMERIC_PRECISION) = '' then 'null' else NUMERIC_PRECISION end),\"reqreq\",(case when NUMERIC_SCALE is null or trim(NUMERIC_SCALE) = '' then 'null' else NUMERIC_SCALE end),\"reqreq\",(case when DATETIME_PRECISION is null or trim(DATETIME_PRECISION) = '' then 'null' else DATETIME_PRECISION end),\"reqreq\",(case when CHARACTER_SET_NAME is null or trim(CHARACTER_SET_NAME) = '' then 'null' else CHARACTER_SET_NAME end),\"reqreq\",(case when COLLATION_NAME is null or trim(COLLATION_NAME) = '' then 'null' else COLLATION_NAME end),\"reqreq\",(case when COLUMN_TYPE is null or trim(COLUMN_TYPE) = '' then 'null' else COLUMN_TYPE end),\"reqreq\",(case when COLUMN_KEY is null or trim(COLUMN_KEY) = '' then 'null' else COLUMN_KEY end),\"reqreq\",(case when EXTRA is null or trim(EXTRA) = '' then 'null' else EXTRA end),\"reqreq\",(case when PRIVILEGES is null or trim(PRIVILEGES) = '' then 'null' else PRIVILEGES end),\"reqreq\",(case when COLUMN_COMMENT is null or trim(COLUMN_COMMENT) = '' then 'null' else COLUMN_COMMENT end)) from columns  where table_schema='${database}' and table_name = \"table_name_variable\"    "#获取information_schema中columns表中数据的方法function get_col_val(){declare -A map2=(["TABLE_CATALOG"]="0" ["TABLE_SCHEMA"]="1" ["TABLE_NAME"]="2" ["COLUMN_NAME"]="3" ["ORDINAL_POSITION"]="4" ["COLUMN_DEFAULT"]="5" ["IS_NULLABLE"]="6" ["DATA_TYPE"]="7" ["CHARACTER_MAXIMUM_LENGTH"]="8" ["CHARACTER_OCTET_LENGTH"]="9" ["NUMERIC_PRECISION"]="10" ["NUMERIC_SCALE"]="11" ["DATETIME_PRECISION"]="12" ["CHARACTER_SET_NAME"]="13" ["COLLATION_NAME"]="14" ["COLUMN_TYPE"]="15" ["COLUMN_KEY"]="16" ["EXTRA"]="17" ["PRIVILEGES"]="18" ["COLUMN_COMMENT"]="19" ) local tmp_val2=$1local col_row_val=(${tmp_val2//reqreq/ }); #分割符： reqreqlocal col_row_val2=${tmp_val2//reqreq/ };local map_index=${map2["$2"]}echo  ${col_row_val[$map_index]} | awk '{gsub("qpzmxxqpzm"," "); print $0 }'#echo  ${col_row_val[$map_index]} }#执行特定的sql语句,只返回某一列第二行以后的数据function sql_ext(){local e_result2=`mysql -u${user} -p${password} -h${host} -P${port} -N -e   "$1"  `echo $e_result2;}function to_sql_ext(){local e_result2=`mysql -u${touser} -p${topassword} -h${tohost} -P${toport} -e  "$1"  `echo $e_result2;}#通用方法function general(){local dataTime=`date '+%Y-%m-%d %H:%M:%S'`;local flage=0;if [ $2 == '1' ]thenlocal cur_value=`expr length $6`case $3 in'ge')if [ ${cur_value} -ge $4 ]    then    flage=1;fi;;'gt')if [ ${cur_value} -gt $4 ]    then    flage=1;fi;;'eq')if [ ${cur_value} -eq $4 ]    then    flage=1;fi;;'le')if [ ${cur_value} -le $4 ]    then    flage=1;fi;;'lt')if [ ${cur_value} -lt $4 ]    then    flage=1;fi;;'ne')if [ ${cur_value} -ne $4 ]    then    flage=1;fi;;*)echo "case error";;esacfiif [ $2 == '2' ]thenlocal cur_value=$6LOWERCASE1=$(echo $6 | tr '[A-Z]' '[a-z]')LOWERCASE2=$(echo $4 | tr '[A-Z]' '[a-z]')case $3 in'==')if [ ${LOWERCASE1} == $LOWERCASE2 ]    then    flage=1;fi;;'!=')if [ ${LOWERCASE1} != $LOWERCASE2 ]    then    flage=1;fi;;*)echo "case error";;esacfiif [ ${flage} -eq 1 ]thentosql_tmp="use ${todatabase} ;insert into ${totable}(db_name,tb_name,col_name,audit_type,audit_name,audit_title,audit_form,audit_logic,cur_value,threshold,comment,status,updated) values('$8','$9','${10}','$7','$6','$1',$2,'$3','${cur_value}','$4','$5',0,'${dataTime}')";to_sql_ext "${tosql_tmp}"fi}

5,主入口方法脚本:main_in.sh

function main(){local e_result=`mysql -u${user} -p${password} -h${host} -P${port} information_schema -N -e "$sql_table" | awk '{gsub(" ","qpzmxxqpzm"); print $0 }' `;  for table_row_val_pas in $e_result do table_name=`get_tb_val ${table_row_val_pas} TABLE_NAME`local schema_name=`get_tb_val ${table_row_val_pas} TABLE_SCHEMA`local create_time=`get_tb_val ${table_row_val_pas} CREATE_TIME `#对表规则进行校验arr=(${table_rule01})  for table_rules in ${arr[@]} do     local target_rule=(${table_rules//,/" "});    #echo "target_rule" ${target_rule[@]}    #根据具体校验规则定义方法case ${target_rule[0]} in                "table_name_length_limit")general   ${target_rule[@]} `get_tb_val ${table_row_val_pas} TABLE_NAME` "table" `get_tb_val ${table_row_val_pas} TABLE_SCHEMA` `get_tb_val ${table_row_val_pas} TABLE_NAME` "";;                "table_engine_innodb_check")general   ${target_rule[@]} `get_tb_val ${table_row_val_pas} ENGINE` "table" `get_tb_val ${table_row_val_pas} TABLE_SCHEMA` `get_tb_val ${table_row_val_pas} TABLE_NAME` "" ;; *) echo "general 4 default table"; ;; esacdonelocal sql_column2=${sql_column//"table_name_variable"/"$table_name"};local col_results=`mysql -u${user} -p${password} -h${host} -P${port} information_schema -N -e "$sql_column2" | awk '{gsub(" ","qpzmxxqpzm"); print $0 }' `; for col_row_val_pas in $col_results do get_col_val ${col_row_val_pas} COLUMN_COMMENTlocal column_name=`get_col_val ${col_row_val_pas} COLUMN_COMMENT`col_arr=(${column_rules})  for col_rule in ${col_arr[@]}do     local cur_col_rule=(${col_rule//,/" "});    #根据具体校验规则定义方法case ${cur_col_rule[0]} in                "column_name_length_limit")general   ${cur_col_rule[@]} `get_col_val ${col_row_val_pas} COLUMN_NAME` "column" `get_tb_val ${col_row_val_pas} TABLE_SCHEMA` `get_tb_val ${col_row_val_pas} TABLE_NAME` `get_col_val ${col_row_val_pas} COLUMN_NAME`;;"column_coment_is_null")general   ${cur_col_rule[@]} `get_col_val ${col_row_val_pas} COLUMN_COMMENT` "column" `get_tb_val ${col_row_val_pas} TABLE_SCHEMA` `get_tb_val ${col_row_val_pas} TABLE_NAME` `get_col_val ${col_row_val_pas} COLUMN_NAME`;;*) echo "general 4 default column"; ;; esacdonedonedone}

6,启动方法脚本:main.sh

#!/bin/sh#coding=utf-8realpath=$(readlink -f "$0")basedir=$(dirname "$realpath")export PATH=$PATH:$basedir/configexport PATH=$PATH:$basedir/lib. config_db.sh. config_audit.sh. function.sh. main_in.sh:<<!1,加密取出2,结果分组，拆分单行数据为数组(解密)3,业务处理!mainecho "successful!";