使用shell脚本抽取MySQL表属性信息

这是学习笔记的第 1815篇文章

在最近抽取了数据库层级的信息之后，我们可以基于已有的数据做一些分析，比如那些业务属于僵尸业务，可以通过分析binlog的偏移量来得到一个初版的信息，如果在一个周期之后偏移量未发生任何变化，则可以断定没有任何数据的写入，很可能是一个空跑的业务。如果某些业务的日增长数据在1000M，结果有一天突然爆发增长到了4000M,则这种情况我们可以基于建立的模型来做出响应，而这些信息在系统层面是无法感知的。这是对于业务探索的第一步。

在这个基础上，如果某些表数据量太大，某些表数据增长过于频繁，某些表中的碎片率很高，表中的索引过度设计等，这些对于业务来说是很欢迎的，如果能够及时发现，从设计上就可以改进和完善，为后期的问题排查也提供一种参考思路。

所以简而言之，表属性的收集是一个很细粒度的工作，虽然琐碎，但是尤其重要，而这个很可能是我们DBA同学目前容易忽视的。

我写了一个初版的采集脚本。会基于数据字典information_schema.tables采集一些基础信息，对于表中的碎片分析，则是通过和系统层结合来得到的。

为了减少采集到的表数量过多，目前是优先采集数据量在100M以上的表，然后分析碎片率等。

完整的脚本如下，供参考。

ps -ef|grep mysql |grep -w mysqld|grep -v grep |grep -v infobright|awk -F'--' '{for (i=2;i<=NF;i++) {printf $i" "}printf "\n"}' > info_from_sys.tmp

memtotal=`cat /proc/meminfo |grep MemTotal|awk '{print $2}'`

#echo $memtotal

function get_info_from_sys()

{

while read line

do

array=$line

port_str='port='

socket_str='socket='

port_str='port='

socket_str='socket='

port_str='port='

socket_str='socket='

for arr_tmp in ${array[*]}; do

if [[ $arr_tmp =~ $port_str ]];then

port_tmp=`echo $arr_tmp|sed 's/port=//g'`

fi

if [[ $arr_tmp =~ $socket_str ]];then

socket_tmp=`echo $arr_tmp|sed 's/socket=//g'`

fi

done

if [ -z "$port_tmp" ];then

port_tmp=3306

fi

echo $port_tmp >> info_from_sys.lst

# echo $port_tmp $socket_tmp >> info_from_sys.lst

done < info_from_sys.tmp

}

function get_info_from_db()

{

while read line

do

port=`echo $line|awk '{print $1}'`

version=` /usr/local/mysql/bin/mysql -udba_admin -p$dec_passwd -h127.0.0.1 -P${port} -N -e "select substr(version(),1,3);" 2>/dev/null `

datadir=` /usr/local/mysql/bin/mysql -udba_admin -p$dec_passwd -h127.0.0.1 -P${port} -N -e "select @@datadir;" 2>/dev/null `

mysqldir=`cd ${datadir};cd ..;pwd`

#echo 'mysqldir=' $mysqldir

#echo 'datadir=' $datadir

datasize=`sudo du -sk $datadir|awk '{print $1}'`

mysqlsize=`sudo du -sk $mysqldir|awk '{print $1}'`

/usr/local/mysql/bin/mysql -udba_admin -p$dec_passwd -h127.0.0.1 -P${port} -N -e "select table_schema,table_name,engine,TABLE_COLLATION,table_rows,data_length,index_length,DATE_FORMAT(create_time,'%Y-%m-%d-%H-%i-%s') create_time,DATE_FORMAT(update_time,'%Y-%m-%d-%H-%i-%s')update_time from information_schema.tables where table_schema not in ('sys','infra','test','mysql','information_schema') and ENGINE='InnoDB' and data_length>1024*1024*10 ;" 1>/tmp/table.lst 2>/dev/null

while read table_info

do

#echo $table_info

database_name=`echo $table_info|awk '{print $1}'`

#echo ${database_name}

table_name=`echo $table_info|awk '{print $2}'`

#echo $table_name

tablesize=`sudo du -sk ${datadir}/${database_name}/${table_name}.ibd|awk '{print $1}'`

echo $table_info ' ' $tablesize

done </tmp/table.lst

done < info_from_sys.lst

}

function decrypt_passwd

{

tmp_passwd=$1

dec_passwd=`echo $tmp_passwd|base64 -d`

}

##MAIN

get_info_from_sys

sec_password='RHB6WUFtesttestfasdfGIwSgo='

dec_passwd=''

decrypt_passwd $sec_password

get_info_from_db

rm info_from_sys.lst

rm info_from_sys.tmp