如何使用Shell小脚本精准统计Mysql每张表的行数-创新互联
这篇文章主要介绍了如何使用Shell小脚本精准统计Mysql每张表的行数,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。
忻府ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景,ssl证书未来市场广阔!成为成都创新互联公司的ssl证书销售渠道,可以享受市场价格4-6折优惠!如果有意向欢迎电话联系或者加微信:18982081108(备注:SSL证书合作)期待与您的合作!循环获取数据库名
直接上Shell代码,show databases获取所有的库名。结果有一个我们不想要的,就是Database,这个grep -v掉,轻松获取所有数据库
[root@shijiangeit ~]# mysql -h 127.0.0.1 -uxxx -pxxx -e "show databases;" 2>/dev/null +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | shijiange | | test | | wordpress | +--------------------+
[root@shijiangeit ~]# mysql -h 127.0.0.1 -uxxx -pxxx -e "show databases;" 2>/dev/null |grep -v Database information_schema mysql performance_schema shijiange test wordpress
循环获取所有表
有了库信息,获取所有表就简单了,直接上Shell代码。show tables获取所有表名,其中Tables_in不需要,grep -v掉。
[root@shijiangeit ~]# for onedb in $(mysql -h 127.0.0.1 -uxxx -pxxx -e "show databases;" 2>/dev/null |grep -v Database);do > echo $onedb > mysql -h 127.0.0.1 -uxxx -pxxx $onedb -e "show tables" 2>/dev/null > done information_schema +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | | EVENTS | | FILES | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE |
循环统计每张表的行数
取出库名加表名,一个select count(1)统计表的行数,循环统计,直接上Shell代码。
[root@shijiangeit ~]# for onedb in $(mysql -h 127.0.0.1 -uxxx -pxxx -e "show databases;" 2>/dev/null |grep -v Database);do > for onetab in $(mysql -h 127.0.0.1 -uxxx -pxxx $onedb -e "show tables" 2>/dev/null |grep -v 'Tables_in_');do > onetablength=$(mysql -h 127.0.0.1 -uxxx -pxxx $onedb -e "select count(1) from $onetab" 2>/dev/null |grep -v 'count') > echo -e "$onedb.$onetab\t$onetablength" > done > done information_schema.CHARACTER_SETS 40 information_schema.COLLATIONS 219 information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 219 information_schema.COLUMNS 1789 information_schema.COLUMN_PRIVILEGES 0 shijiange.logincount 4 shijiange.member 0 shijiange.user 2097153 test.detect_servers 0 wordpress.wp_commentmeta 0 wordpress.wp_comments 0 wordpress.wp_links 0 wordpress.wp_options 156
变量化,脚本直接用
需要统计哪个Mysql,前面三个变量一改,立马就能统计所有表的大小了。
mysqlhost=127.0.0.1 mysqluser=xxx mysqlpassword=xxx for onedb in $(mysql -h $mysqlhost -u$mysqluser -p$mysqlpassword -e "show databases;" 2>/dev/null |grep -v Database);do for onetab in $(mysql -h $mysqlhost -u$mysqluser -p$mysqlpassword $onedb -e "show tables" 2>/dev/null |grep -v 'Tables_in_');do onetablength=$(mysql -h $mysqlhost -u$mysqluser -p$mysqlpassword $onedb -e "select count(1) from $onetab" 2>/dev/null |grep -v 'count') echo -e "$onedb.$onetab\t$onetablength" done done
想看哪张表的行数最多?
之前的脚本加个 |sort -nrk 2|less 搞定,超实用的小脚本就这样完成了
[root@shijiangeit ~]# for onedb in $(mysql -h $mysqlhost -u$mysqluser -p$mysqlpassword -e "show databases;" 2>/dev/null |grep -v Database);do > for onetab in $(mysql -h $mysqlhost -u$mysqluser -p$mysqlpassword $onedb -e "show tables" 2>/dev/null |grep -v 'Tables_in_');do > onetablength=$(mysql -h $mysqlhost -u$mysqluser -p$mysqlpassword $onedb -e "select count(1) from $onetab" 2>/dev/null |grep -v 'count') > echo -e "$onedb.$onetab\t$onetablength" > done > done | sort -nrk 2 shijiange.user 2097153 information_schema.INNODB_BUFFER_PAGE 8191 performance_schema.events_waits_summary_by_thread_by_event_name 5320 information_schema.INNODB_BUFFER_PAGE_LRU 3453
感谢你能够认真阅读完这篇文章,希望小编分享的“如何使用Shell小脚本精准统计Mysql每张表的行数”这篇文章对大家有帮助,同时也希望大家多多支持创新互联网站建设公司,,关注创新互联行业资讯频道,更多相关知识等着你来学习!
分享题目:如何使用Shell小脚本精准统计Mysql每张表的行数-创新互联
当前链接:http://ybzwz.com/article/cchpcd.html