一个表主键信息采集脚本
- 2019 年 11 月 6 日
- 筆記
01 如何查询目前哪些表有主键？
查询目前哪些表有主键，可以通过information_schema.key_column_usage表来确定哪些列使用了主键约束，这个表中包含如下列，每个列的含义如下： CONSTRAINT_CATALOG ：约束所属目录的名称。 该值始终为def。 CONSTRAINT_SCHEMA ：约束所属schema（database）名称 CONSTRAINT_NAME ：约束名称 TABLE_CATALOG ：表所属目录的名称。 该值始终为def。 TABLE_SCHEMA ：表所属schema（database）名称 TABLE_NAME ：具有约束的表的名称 COLUMN_NAME ：具有约束的列的名称。 如果约束是外键，则这是外键的列，而不是外键引用的列。 ORDINAL_POSITION ：列在约束内的位置，而不是列在表中的位置。列位置从1开始编号。 POSITION_IN_UNIQUE_CONSTRAINT：NULL对于唯一和主键约束。对于外键约束，此列是正在引用的表的键中的序号位置。 REFERENCED_TABLE_SCHEMA ：约束引用的schema（数据库）的名称。 REFERENCED_TABLE_NAME ：约束引用的表的名称。 REFERENCED_COLUMN_NAME ：约束引用的列的名称。 我们来看看这个表中的记录吧：
mysql> select table_schema,table_name,column_name from information_schema.key_column_usage; +--------------+---------------------------+--------------------+ | table_schema | table_name | column_name | +--------------+---------------------------+--------------------+ | infra | chk_masterha | key | | mysql | columns_priv | Host | | mysql | columns_priv | Db | | mysql | user | User | | sys | sys_config | variable | | tkXXXXXdb | Dic_UserXXXXXXXXX | AppID | | tkXXXXXXdb | Dic_UserXXXXXXXXX | AppID | +--------------+---------------------------+--------------------+ rows in set (. sec)
这个表里面给出了相关的记录，包含数据库名称，表名称以及列名称。但是我们可以看到，它里面包含了mysql数据库中的3条记录(还有一些我已经删除了)，我们在计算业务数据库的时候，应该把这些库先剔除掉，所以我们最后的SQL就变成了：
select table_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t where t.table_schema not in ('information_schema','performance_schema','mysql','test','sys','infra')
这里我们排除掉了系统的数据库。
02
如何获得当前数据库中没有主键的表？
要想获得没有主键的表，需要使用全库的所有表去掉包含主键的表，那么问题就先转化为如何获取全库的所有表？
这个问题可能算是比较简单的问题了，我们都知道information_schema中的tables表上面有数据库中的所有表的信息，所以我们用一条简单的SQL就能获得数据库中的所有表：
mysql> select table_schema,table_name from information_schema.tables; +--------------------+----------------------------------------------+ | table_schema | table_name | +--------------------+----------------------------------------------+ | information_schema | CHARACTER_SETS | | information_schema | COLLATIONS | | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | | celery | djcelery_workerstate | | celery | test_celery_adhoc | | celery | test_celery_ansible | | dms_metadata | metadata_app | | dms_metadata | metadata_app_cluster | | dms_metadata | metadata_app_instance | | mysql | time_zone_transition | | mysql | time_zone_transition_type | | mysql | user | | performance_schema | cond_instances | | performance_schema | events_waits_current | | performance_schema | events_waits_history | | yeyz | yeyz | +--------------------+----------------------------------------------+ 126 rows in set (0.02 sec)
从结果中我们可以看出，这条命令输出了数据库中的所有表和视图的信息，其中information_schema,mysql以及performance_schema表都做了删减，我们用这个结果减掉刚才我们查出来的包含主键约束的表，就是剩余的那些没有包含主键的表。于是写成的SQL如下：
select table_schema,table_name from information_schema.tables where table_schema not in ('information_schema','performance_schema','mysql','test','sys','infra') and table_name not in (select table_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t where t.table_schema not in ('information_schema','performance_schema','mysql','test','sys','infra'));
03
如何处理单机多实例？
它的逻辑大概分为两步：
- ps -ef查看当前IP上的实例，然后通过脚本中的awk命令过滤，将这些实例的端口拿到；
- 分别连接这些数据库实例，然后在实例上跑前面的SQL语句来得到没有主键的表。
大体的思路就是上面那样，详细的脚本如下，里面给出了每个函数的注释：
##获取所有的实例信息，保存在一个info_from_sys.tmp的文件中 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}'` ##从info_from_sys.tmp中拿到所有的实例端口，并保存到一个文件info_from_sys.lst中 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}'` connection_num=`/usr/local/mysql/bin/mysql -udba_admin -p$dec_passwd -h127.0.0.1 -P${port} -e "show processlist" |wc -l` mysqlsize=`sudo du -sk $mysqldir|awk '{print $1}'` databasearray=` /usr/local/mysql/bin/mysql -udba_admin -p$dec_passwd -h127.0.0.1 -P${port} -N -e "select schema_name from information_schema.schemata where schema_name not in('information_schema','test','performance_schema','sys','mysql');" 2>/dev/null ` #echo $databasearray for db_inst in $databasearray do /usr/local/mysql/bin/mysql -udba_admin -p$dec_passwd -h127.0.0.1 -P${port} -N -e "select @@port,table_schema,table_name from information_schema.tables where table_schema not in ('information_schema','performance_schema','mysql','test','sys','infra') and table_name not in (select table_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t where t.table_schema not in ('information_schema','performance_schema','mysql','test','sys','infra'));" >> info_from_db.lst >/dev/null done done < info_from_sys.lst } ##密码解密 function decrypt_passwd { tmp_passwd=$ dec_passwd=`echo $tmp_passwd|base64 -d` } ##MAIN get_info_from_sys sec_password='XXXXXXXXXX' dec_passwd='' decrypt_passwd $sec_password get_info_from_db sort info_from_db.lst|uniq > info_from_db.tmp sort info_from_sys.lst|uniq > info_from_sys.tmp cat info_from_db.tmp rm info_from_db.lst info_from_sys.lst rm info_from_sys.tmp info_from_db.tmp
