how to find size of the database via mysql console

There are a bunch of different tools with a graphical interface for managing and maintaining databases (mysqladmin, navicat and others), plus the capabilities of server control panels, where you can get information about databases, tables, users. However, sometimes, when working in the server console via ssh, you need to quickly get the sizes of the existing mysql databases. To do this, just go to the mysql console client and execute an sql query.

root@localhost:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43764664
Server version: 5.6.46-0+deb9u2 (Debian)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

In this case, we entered from under the root. We execute the request:

 SELECT table_schema "database_name", sum( data_length + index_length )/1024/1024 "database size in MB" FROM information_schema.TABLES GROUP BY table_schema;

mysql> SELECT table_schema "database_name", sum( data_length + index_length )/1024/1024 "database size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+---------------------+
| database_name      | database size in MB |
+--------------------+---------------------+
| database1         |         21.73606873 |
| database2        |          0.79687500 |
| information_schema |          0.00878906 |
| mysql              |          0.68143845 |
| roundcube          |          0.40625000 |
+--------------------+---------------------+
7 rows in set (0.14 sec)

The second column shows the size of each database. To find out the size of a specific database, we slightly modify the sql query and get:

mysql> SELECT table_schema "database_name", sum( data_length + index_length )/1024/1024 "database size in MB" FROM information_schema.TABLES WHERE table_schema="festivalse";
+---------------+---------------------+
| database_name | database size in MB |
+---------------+---------------------+
| database1   |         21.73606873 |
+---------------+---------------------+
1 row in set (0.01 sec)

Similar Posts: