How to check database and table size in MariaDB

To check database and table size in MariaDB

MariaDB is a community developed fork of MySQL. It is designed to maintain high compatibility with MySQL. It includes the XtraDB feature for replacing the innoDB. In this tutorial we are going cover the topic on how to check database size and table size in Mariadb.

Checking the database and table size

To check the various database and table size in MariaDB check whether the MySQL is running or not. enter the mysql command to call the MariaDB.

[root@linuxhelp1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with   or g.
Your MariaDB connection id is 8
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ' help '  or ' h'  for help. Type ' c'  to clear the current input statement.
MariaDB [(none)]> 

After invoking the MariaDB in the terminal, enter the following command to check a particular database size.

MariaDB [(none)]>  SELECT table_schema " Database Name" , SUM( data_length + index_length)/1024/1024 " Database Size (MB)"  FROM information_schema.TABLES where table_schema = ' mysql'  
+---------------+--------------------+
| Database Name | Database Size (MB) |
+---------------+--------------------+
| mysql         |         0.62618065 |
+---------------+--------------------+
1 row in set (0.00 sec)

To check the size of all the database stored in MariaDB, enter the following command to check the size.

MariaDB [(none)]>  SELECT table_schema " Database Name" , SUM(data_length+index_length)/1024/1024 " Database Size (MB)"   FROM information_schema.TABLES GROUP BY table_schema 
+--------------------+--------------------+
| Database Name      | Database Size (MB) |
+--------------------+--------------------+
| information_schema |         0.07031250 |
| mysql              |         0.62618065 |
| performance_schema |         0.00000000 |
+--------------------+--------------------+
3 rows in set (0.01 sec)

After checking a particular database size, now you can check the particular table size from the database by entering the following command.

MariaDB [(none)]>  SELECT table_name " Table Name" , table_rows " Rows Count" , round(((data_length + index_length)/1024/1024),2) " Table Size (MB)"  FROM information_schema.TABLES WHERE table_schema = " mysql"  AND table_name =" user"  
+------------+------------+-----------------+
| Table Name | Rows Count | Table Size (MB) |
+------------+------------+-----------------+
| user       |          3 |            0.00 |
+------------+------------+-----------------+
1 row in set (0.00 sec)

To check all the table size from the databases enter the following command.

MariaDB [(none)]>  SELECT table_name " Table Name" , table_rows " Rows Count" , round(((data_length + index_length)/1024/1024),2) " Table Size (MB)"  FROM information_schema.TABLES WHERE table_schema = " mysql"  
+---------------------------+------------+-----------------+
| Table Name                | Rows Count | Table Size (MB) 
+---------------------------+------------+-----------------+
| columns_priv              |          0 |            0.00 |
| db                        |          0 |            0.01 |
| event                     |          0 |            0.00 |
| func                      |          0 |            0.00 |
| general_log               |          2 |            0.00 |
| help_category             |         39 |            0.00 |
| help_keyword              |        464 |            0.10 |
| help_relation             |       1028 |            0.03 |
| help_topic                |        508 |            0.45 |
| host                      |          0 |            0.00 |
| ndb_binlog_index          |          0 |            0.00 |

To quit from the MariaDB, enter the following command.

MariaDB [(none)]>  q
Bye