前言

對于開發(fā)或者運維人員來說，Mysql數(shù)據庫每張表的數(shù)量肯定是要了解下，有助于我們清理無用數(shù)據或者了解哪張表比較占用空間。

另外多次統(tǒng)計表的行數(shù)，還能發(fā)現(xiàn)Mysql表的增量情況，能夠預測表未來會有多大的量。

廢話不多說，直接帶大家寫一個簡單的Shell小腳本

循環(huán)獲取數(shù)據庫名

直接上Shell代碼，show databases獲取所有的庫名。結果有一個我們不想要的，就是Database，這個grep -v掉，輕松獲取所有數(shù)據庫

[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 Databaseinformation_schemamysqlperformance_schemashijiangetestwordpress

循環(huán)獲取所有表

有了庫信息，獲取所有表就簡單了，直接上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> doneinformation_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           |

循環(huán)統(tǒng)計每張表的行數(shù)

取出庫名加表名，一個select count(1)統(tǒng)計表的行數(shù)，循環(huán)統(tǒng)計，直接上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> doneinformation_schema.CHARACTER_SETS  40information_schema.COLLATIONS  219information_schema.COLLATION_CHARACTER_SET_APPLICABILITY  219information_schema.COLUMNS 1789information_schema.COLUMN_PRIVILEGES  0shijiange.logincount  4shijiange.member  0shijiange.user 2097153test.detect_servers 0wordpress.wp_commentmeta  0wordpress.wp_comments  0wordpress.wp_links 0wordpress.wp_options  156

變量化，腳本直接用

需要統(tǒng)計哪個Mysql，前面三個變量一改，立馬就能統(tǒng)計所有表的大小了。

mysqlhost=127.0.0.1mysqluser=xxxmysqlpassword=xxxfor 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" donedone