Checking MySQL Database Size in Linux

MySQL is the most popular open-source database among developers. It was developed and managed by Oracle Corporation and initially released on 23 May 1995. It is compatible with each operating system, Like(Linux, Windows, and macOS) which is major support for web-based applications. In this article, we are going to see how to get the size of a database in MySQL in Linux. Every operating system follows the same approach(Syntax) to check the size of a database. 

Prerequisites: Before proceeding, ensure that MySQL is installed on your Linux system.

You can verify this by running:

Step 1: Check whether your MySql is installed or not.

mysql --version

Explanation: This command will display the MySQL version installed on your system, confirming its presence.

Step 2: Access MySQL Command Line.

First, access the MySQL command line interface. You can do this with:

sudo mysql

Explanation: This command starts the MySQL server interface where you can execute SQL commands.

Step 3: Check the databases.

SHOW databases;

Explanation: This command will list all the databases managed by MySQL on your server.

Step 4: Check the Size of all databases.

Here we define the table_schema and show the size of our database in a new column by following commands:

SELECT table_schema "Database_Name"
      SUM(data_length + index_length) / (1024 * 1024) AS "Database Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema;

Output:

Explanation: This command displays each database along with its size in megabytes, providing a clear overview of space usage.

Step 5: Check the Size of a Specific Database.

Here we define the table_schema and show the size of any one database (students) in a new column by following commands:

SELECT table_schema "Database_Name",
        SUM(data_length + index_length) / (1024 * 1024) AS "Database Size in MB"
FROM information_schema.TABLES
WHERE table_schema = 'students'
ORDER BY (data_length + index_length) DESC;

Explanation: This query will return the size of the 'students' database in descending order based on data and index length.

Tips:

• Regularly monitor database sizes to manage storage effectively.
• Consider setting up alerts for when database sizes reach a threshold to prevent potential issues.

Conclusion

Monitoring the size of your MySQL databases is vital for effective database management, particularly in environments requiring optimal performance and resource allocation. By following these steps, administrators and developers can efficiently manage database storage and plan for necessary upgrades or maintenance.