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:
- Changing column collation and character set in mysql database / schema
- Howto Set Up Named Time Zones in MySQL
- How to install phpMyAdmin with nginx on Debian 11
- How to disable MySQL Strict Mode?
- change / reset root password in mysql
680