一个Shell小脚本精准统计Mysql每张表的行数实现
时间:2020-03-09 17:19 来源:linux.it.net.cn 作者:IT
前言
对于开发或者运维人员来说，Mysql数据库每张表的数量肯定是要了解下，有助于我们清理无用数据或者了解哪张表比较占用空间。
另外多次统计表的行数，还能发现Mysql表的增量情况，能够预测表未来会有多大的量。
废话不多说，直接带大家写一个简单的Shell小脚本
循环获取数据库名
直接上Shell代码，show databases获取所有的库名。结果有一个我们不想要的，就是Database，这个grep -v掉，轻松获取所有数据库
1
2
3
4
5
6
7
8
9
10
11
[root@it.net.cn ~]# mysql -h 127.0.0.1 -uxxx -pxxx -e "show databases;" 2>/dev/null
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shijiange |
| test |
| wordpress |
+--------------------+
1
2
3
4
5
6
7
[root@it.net.cn ~]# 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@it.net.cn ~]# 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@it.net.cn ~]# 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@it.net.cn ~]# 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
(责任编辑：IT)
| 前言 对于开发或者运维人员来说，Mysql数据库每张表的数量肯定是要了解下，有助于我们清理无用数据或者了解哪张表比较占用空间。 另外多次统计表的行数，还能发现Mysql表的增量情况，能够预测表未来会有多大的量。 废话不多说，直接带大家写一个简单的Shell小脚本 循环获取数据库名 直接上Shell代码，show databases获取所有的库名。结果有一个我们不想要的，就是Database，这个grep -v掉，轻松获取所有数据库 1 2 3 4 5 6 7 8 9 10 11 [root@it.net.cn ~]# mysql -h 127.0.0.1 -uxxx -pxxx -e "show databases;" 2>/dev/null +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | shijiange | | test | | wordpress | +--------------------+ 1 2 3 4 5 6 7 [root@it.net.cn ~]# 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@it.net.cn ~]# 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@it.net.cn ~]# 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@it.net.cn ~]# 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 (责任编辑：IT) |