How can I get a rowcount for all tables in MySQL. I can do this databases by database if that is easier.
The purpose is to do a cursory check on my database replication that was just setup.
Add a comment
|
5 Answers
try mk-table-checksum instead. docs might be scary but together with mk-checksum-filter it's very useful tool.
If you are want to check on your replication setup, have you considered using:
- SHOW SLAVE STATUS check the Seconds_Behind_Master value
- SHOW MASTER STATUS not as useful as the above
./mysql -utest -ptest dbname -e 'show table status\G' | awk '/Name/ {name = $2}; /Rows/ {print $2"\t"name}' | sort -gr
I found this article to be a very crisp and clean way to retrieve all the tables along with their row counts.
-
This answer would be more useful if it went into more detail about the solution in the linked article. Links often go away; if this one does, this answer becomes useless.– pjmorseDec 14, 2012 at 2:04
Also useful could be:
show table status;
Must be executed for each database. Just sho it for you current used database. Alternate but not fast way: Use the information schema:
select TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS from information_schema.tables;
-> ENGINE could be important, maybe you want the filter the information about performance_schema or information_schema, etc. which will not be replicated.
