前言
對(duì)于開發(fā)或者運(yùn)維人員來說，Mysql數(shù)據(jù)庫每張表的數(shù)量肯定是要了解下，有助于我們清理無用數(shù)據(jù)或者了解哪張表比較占用空間。
另外多次統(tǒng)計(jì)表的行數(shù)，還能發(fā)現(xiàn)Mysql表的增量情況，能夠預(yù)測(cè)表未來會(huì)有多大的量。
廢話不多說，直接帶大家寫一個(gè)簡(jiǎn)單的Shell小腳本
循環(huán)獲取數(shù)據(jù)庫名
直接上Shell代碼，show databases獲取所有的庫名。結(jié)果有一個(gè)我們不想要的，就是Database，這個(gè)grep -v掉，輕松獲取所有數(shù)據(jù)庫
[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)獲取所有表
有了庫信息，獲取所有表就簡(jiǎ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)計(jì)每張表的行數(shù)
取出庫名加表名，一個(gè)select count(1)統(tǒng)計(jì)表的行數(shù)，循環(huán)統(tǒng)計(jì)，直接上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)計(jì)哪個(gè)Mysql，前面三個(gè)變量一改，立馬就能統(tǒng)計(jì)所有表的大小了。
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
想看哪張表的行數(shù)最多？
之前的腳本加個(gè) |sort -nrk 2|less 搞定，超實(shí)用的小腳本就這樣完成了
[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 2shijiange.user 2097153information_schema.INNODB_BUFFER_PAGE 8191performance_schema.events_waits_summary_by_thread_by_event_name 5320information_schema.INNODB_BUFFER_PAGE_LRU 3453
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持武林網(wǎng)。
新聞熱點(diǎn)
疑難解答
圖片精選
