一个表主键信息采集脚本
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
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2019-01-21，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。