A small shell script to accurately count the number of rows in each table of MySQL

Time:2020-10-27

preface

For development or operation and maintenance personnel, the number of each table in MySQL database must be known, which helps us clean up useless data or know which table takes up more space.

In addition, by counting the number of rows in the table many times, you can also find the increment of the MySQL table, and predict how large the table will be in the future.

I don’t want to say much nonsense, but I will write a simple shell script directly

Loop to get database name

Go directly to the shell code and show databases to get all the library names. As a result, there is a database that we don’t want. This grep – V drop can easily get all databases


			[[email protected] ~]# mysql -h 127.0.0.1 -uxxx -pxxx -e "show databases;" 2>/dev/null
			+--------------------+
			| Database      |
			+--------------------+
			| information_schema |
			| mysql       |
			| performance_schema |
			| shijiange     |
			| test        |
			| wordpress     |
			+--------------------+
	

			[[email protected] ~]# 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
	

Get all loop tables

With the library information, it is easy to get all the tables, and go directly to the shell code. Show tables gets all table names, where tables_ In no need, grep – V drop.


			[[email protected] ~]# 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           |

Loop count the number of rows per table

Take out the database name and add the table name, oneselect count(1)Statistics table row number, loop statistics, shell code directly.


			[[email protected] ~]# 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
	

Variable, script directly used

If you need to count which mysql, you can immediately count the size of all tables as soon as the first three variables are changed.


			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

	

Which table has the most rows?

The previous script is completed by adding a | sort – NRK 2 | less, and the super practical small script is completed in this way


			[[email protected] ~]# 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
	

The above is the whole content of this article, I hope to help you in your study, and I hope you can support developeppaer more.