How to Count Rows in All MySQL Tables Using a Bash Script

When managing a MySQL database, it’s often useful to get the row counts of all tables to monitor the size and growth of your database. While MySQL doesn’t provide a built-in command to directly count rows across all tables in a database, you can easily achieve this with a simple Bash script.

In this article, we will walk through how to create and run a Bash script that queries each table in a MySQL database and returns the row count (COUNT(1)) for each table.

Prerequisites

• MySQL Server: You must have a running MySQL server with access to the database.
• Bash: The script will be written in Bash, so make sure you’re running it on a Unix-like system (Linux/macOS) with Bash available.

Step-by-Step Guide

1. Create the Bash Script

First, you need to create a Bash script that will connect to your MySQL server, retrieve all the tables, and execute a SELECT COUNT(1) for each table to count the rows. Here's the full script:

#!/bin/bash

# MySQL credentials
USER="your_username"
PASSWORD="your_password"
DATABASE="your_database"

# Get list of all tables in the database
TABLES=$(mysql -u $USER -p$PASSWORD -D $DATABASE -e 'SHOW TABLES;' | tail -n +2)

# Loop through each table and get the count
for TABLE in $TABLES; do
  COUNT=$(mysql -u $USER -p$PASSWORD -D $DATABASE -e "SELECT COUNT(1) FROM $TABLE;" | tail -n 1)
  echo "Table: $TABLE, Count: $COUNT"
done

2. Script Breakdown

Let’s break down the components of this script:

• MySQL Login Credentials: The script requires your MySQL username, password, and database name. Replace the placeholders (your_username, your_password, your_database) with your actual credentials.
• Get Tables: The SHOW TABLES; query retrieves all the table names in the specified database.
• Loop Through Tables: The script then loops over each table and runs SELECT COUNT(1) FROM <table> to count the number of rows in the table.
• Output: The result is printed as Table: <table_name>, Count: <row_count>.

3. Make the Script Executable

To make the script executable, save the content to a file, for example, count_tables.sh. Then, give it executable permissions:

chmod +x count_tables.sh

4. Run the Script

You can now run the script by typing:

./count_tables.sh

5. Sample Output

When you run the script, you will get output similar to this:

Table: users, Count: 1250
Table: orders, Count: 890
Table: products, Count: 150
Table: transactions, Count: 2043
Table: logs, Count: 5632

Each line shows the table name followed by the row count.

6. Handling Large Databases

For databases with many tables, running this script can take some time since it performs a COUNT(1) on each table individually. If you have a very large number of tables or large tables, consider running the script during off-peak hours to avoid putting unnecessary load on the MySQL server.

Conclusion

This simple Bash script is a great way to quickly check the row counts of all tables in your MySQL database. It can be used for monitoring purposes, optimization, or any time you need an overview of the size of your tables.

By modifying this script, you can add more functionality, such as filtering certain tables or exporting the results to a file for later analysis.

With just a few lines of code, you now have a powerful tool to help you manage your MySQL database more efficiently.

---