Shulou(Shulou.com)06/02 Report--

Preface

For developers or operators, the number of tables in the Mysql database must be known to help us clean up useless data or know which tables take up more space.

In addition, the number of rows of the statistical table can also be found in the increment of the Mysql table, which can predict how large the table will be in the future.

No more nonsense, just take you to write a simple Shell script

Loop to get the database name

Go directly to the Shell code, and show databases gets all the library names. As a result, one thing we don't want is Database. The grep-v is dropped, which makes it easy to get all the databases.

[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 loop to get all tables

With the library information, it's easy to get all the tables and go directly to the Shell code. Show tables gets all the table names, of which Tables_in is not needed and grep-v is dropped.

[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 | count the number of rows in each table on a circular basis |

Take out the library name plus the table name, the number of rows of a select count (1) statistical table, circular statistics, and directly add the Shell code.

[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 156variational The script is used directly

Which Mysql needs to be counted? as soon as the first three variables are changed, you can immediately count the size of all tables.

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 want to see which table has the most rows?

Add to the previous script | sort-nrk 2 | less is done, so the super practical mini script is completed.

[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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.