CODEWITHSUNDEEP
mysqldatabase
Newbie
Newbie

Reputation: 8605

How can I get the size of a MySQL database?

How can I get the size of a MySQL database?

Suppose the target database is called "v3".

Upvotes: 830

Views: 897978

Answers (12)

Rajib
Rajib

Reputation: 592

If you are using MySQL Workbench

If you use MySQL Workbench, you can check the database size as follows:

  • Open MySQL Workbench and connect to your MySQL server.
  • Go to the "Navigator" pane on the left.
  • Right-click on the database you want to check.
  • Select "Schema Inspector."
  • The "Schema Inspector" window will show various details, including the estimated size of the database. Data may be showed GiB. 1 GB = 0.93132257461548 GiB

Using ubuntu file system. Usually mysql resides inside /var/lib du -sh var/lib/mysql

-s: Display only total. If you do not use s it will show breakdown of subfolder.
-h: Print sizes in human-readable format (e.g., 1K, 234M, 2G).

Upvotes: 0

Wasid Hossain
Wasid Hossain

Reputation: 65

In addition: If someone wants to get the size of a single table please use the following codes:

SELECT
  TABLE_NAME AS `Table Name`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size ( in MB)`
FROM
  information_schema.TABLES
WHERE
    TABLE_SCHEMA = "your_db_name"
  AND
    TABLE_NAME = "your_single_table_name"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Note: It won't show the fraction numbers for using the ROUND() method.

Upvotes: 2

Jörg Asmussen
Jörg Asmussen

Reputation: 369

If you want the list of all database sizes sorted, you can use:

SELECT * 
FROM   (SELECT table_schema AS `DB Name`, 
           ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS `DB Size in MB`
        FROM   information_schema.tables 
        GROUP  BY `DB Name`) AS tmp_table 
ORDER  BY `DB Size in MB` DESC;

Upvotes: 24

Hiren Parghi
Hiren Parghi

Reputation: 1895

First log in to MySQL using:

mysql -u username -p

Command to Display the size of a single Database along with its table in MB.

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

Change database_name to your Database

Command to Display all the Databases with its size in MB.

SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

Upvotes: 19

Nadeem0035
Nadeem0035

Reputation: 3957

It can be determined by using following MySQL command

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

Result

Database  Size (MB)
db1       11.75678253
db2        9.53125000
test      50.78547382

Get result in GB

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema

Upvotes: 211

Rick James
Rick James

Reputation: 142188

mysqldiskusage  --server=root:MyPassword@localhost  pics

Output:

+----------+----------------+
| db_name  |         total  |
+----------+----------------+
| pics     | 1,179,131,029  |
+----------+----------------+

If not installed, this can be installed by installing the mysql-utils package which should be packaged by most major distributions.

Alas, they got rid of that package. If you are on Linux (or similar),

du -m /var/lib/mysql/*

will list the size, in megabytes, for each database you have. (Caveats: You probably need to be root to run the command, and the path may not be what I provided there.) This lists the 20 biggest:

du -m /var/lib/mysql/* | sort -nb | tail

Upvotes: 21

Joel
Joel

Reputation: 2824

Alternatively, if you are using phpMyAdmin, you can take a look at the sum of the table sizes in the footer of your database structure tab. The actual database size may be slightly over this size. However, it appears to be consistent with the table_schema method mentioned above.

Screenshot:

Enter image description here

Upvotes: 46

kedar
kedar

Reputation:

Alternatively, you can directly jump into the data directory and check for the combined size of v3.myd, v3.myi and v3.frm files (for MyISAM), or v3.idb & v3.frm (for InnoDB).

Upvotes: 26

Brian Willis
Brian Willis

Reputation: 23854

Run this query, and you'll probably get what you're looking for:

SELECT table_schema "DB Name",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;

This query comes from the MySQL forums, where there are more comprehensive instructions available.

Upvotes: 1774

prem
prem

Reputation: 3538

To get the list of all the databases sorted by their size in descending order without using any sub-query, you can use the below query:

SELECT 
    table_schema AS Database_Name,
    SUM(data_length + index_length) Size_In_Bytes,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) Size_In_MB,
    ROUND(SUM(data_length + index_length) / 1024 / 1024/ 1024, 2) Size_In_GB
FROM information_schema.tables 
GROUP BY table_schema ORDER BY Size_In_Bytes DESC;

You will get the size of all the databases in Bytes, MB and GB as shown in the example below:

enter image description here

As asked by OP, to get the size on any particular database like "v3" WHERE can be used in the query as given below:

SELECT 
    table_schema AS Database_Name,
    SUM(data_length + index_length) Size_In_Bytes,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) Size_In_MB,
    ROUND(SUM(data_length + index_length) / 1024 / 1024/ 1024, 2) Size_In_GB
FROM information_schema.tables WHERE table_schema = 'v3'
GROUP BY table_schema ORDER BY Size_In_Bytes DESC;

Upvotes: 8

Evan Haston
Evan Haston

Reputation: 85

Go into the mysql data directory and run du -h --max-depth=1 | grep databasename

Upvotes: 6

williambarau
williambarau

Reputation: 511

To get a result in MB:

SELECT
SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2)) AS "SIZE IN MB"
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = "SCHEMA-NAME";

To get a result in GB:

SELECT
SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024), 2)) AS "SIZE IN GB"
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = "SCHEMA-NAME";

Upvotes: 31

Related Questions