Getting the row count from mysql tables are not a big deal and even there is no need for a blog for this. Simply go and query the INFORMATION_SCHEMAand get the row count for the tables. But this is not your actual row counts. It’ll show the row count of the tables during the last statistics update. So if you want to track your tables growth then you should doselect count(*) from table_name for all the tables and insert the results to somewhere. There are a lot of ways available. Im just make this as a blog post. So others can benefit from it.
Row Count - From Stored Procedure:
We’ll get the list of table names from the information_schema and use cursor to run select count(*) on that table and save the row count value to a table.
In this example, Im going to collect the row count of the tables from the database called prod_db. And this procedure and tracking table will be saved on the database called dbadmin.
Row Count - From Shell Script
If your tables are having a huge amount of data and running with one or two read replica, then use replica’s IP address for doing the select count(*).