How to get size of mysql database?

Question

How to get size of a mysql database?
Suppose the target database is called "v3".

Answer 1

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.

Answer 2

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

Answer 3

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.

Screen-shot :

Answer 4

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";

Answer 5

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

Answer 6

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; 

Answer 7

mysqldiskusage  --server=root:MyPassword@localhost  pics

+----------+----------------+
| 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.

Update

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

Answer 8

First login 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;

Answer 9

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

Answer 10

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.

Hope this will help many of us.