.::: How to Check MySQL MariaDB Database Size, Total All Size Database And Table Size, Uptime Time, Create Time Table :::.

 1. type table
SELECT @@hostname as ServerName,
    table_schema,
    table_name,
    table_type,
    row_format,
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema = 'teguhth'     
ORDER BY TABLE_NAME,table_rows desc;
  

2. size table

## with round
SELECT @@hostname,table_schema,table_name AS "Table",table_type,
ROUND(((data_length + index_length) ), 0) AS "TableSize_(B)",  
ROUND(((data_length + index_length) / 1024 ), 0) AS "TableSize_(KB)" ,
ROUND(((data_length + index_length) / 1024 / 1024), 0) AS "TableSize_(MB)" ,
ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 0) AS "TableSize_(GB)"
FROM information_schema.TABLES  
##WHERE table_schema ='teguhth'
ORDER BY ((data_length + index_length) / 1024 / 1024) DESC;

## without round

SELECT @@hostname,table_schema,table_name AS "Table",table_type,
((data_length + index_length) ) AS "TableSize_(B)",  
((data_length + index_length) / 1024 ) AS "TableSize_(KB)" ,
((data_length + index_length) / 1024 / 1024) AS "TableSize_(MB)" ,
((data_length + index_length) / 1024 / 1024 / 1024) AS "TableSize_(GB)"
FROM information_schema.TABLES  
##WHERE table_schema ='teguhth'
ORDER BY ((data_length + index_length) / 1024 / 1024) DESC;
 

SELECT @@hostname as HostName,table_schema as DB_Name,
table_name AS "Table_Name",table_type as Table_Type,
table_rows as Table_Rows,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "TableSize_(MB)" ,
create_time as Create_Time,update_time as Update_Time
FROM information_schema.TABLES  
WHERE table_schema ='teguhth'
ORDER BY ((data_length + index_length) / 1024 / 1024) DESC;
 

 

3. dbsize

## with round

SELECT @@hostname, table_schema "database",
round(sum(data_length + index_length)/1024,0) "size in KB",
round(sum(data_length + index_length)/1024/1024,0) "size in MB",
round(sum(data_length + index_length)/1024/1024/1024,0) "size in GB"  
FROM information_schema.TABLES GROUP BY table_schema
ORDER BY round(sum(data_length + index_length)/1024/1024,0) desc ;

## without round
SELECT @@hostname, table_schema "database",
sum(data_length + index_length)/1024 "size in KB",
sum(data_length + index_length)/1024/1024 "size in MB",
sum(data_length + index_length)/1024/1024/1024 "size in GB"  
FROM information_schema.TABLES GROUP BY table_schema;
 

SELECT @@hostname as Hostname, table_schema "DBName",
round(sum(data_length + index_length)/1024/1024,2) "size in MB"
FROM information_schema.TABLES GROUP BY table_schema
ORDER BY round(sum(data_length + index_length)/1024/1024,0) desc ;

4. dbzise from os

du -h --max-depth=1 /var/lib/mysql/
[root@teguhth ~]# du -h --max-depth=1 /var/lib/mysql/
3.3M    /var/lib/mysql/mysql
4.0K    /var/lib/mysql/performance_schema
408K    /var/lib/mysql/teguhth
4.0K    /var/lib/mysql/secretdb
124M    /var/lib/mysql/
[root@teguhth ~]#

 

 

5. Total All DBSize

SELECT @@hostname,table_schema "database",
    SUM(data_length + index_length) / 1024 AS "Total Size (KB)",
    SUM(data_length + index_length) / 1024 / 1024 AS "Total Size (MB)",
    SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Total Size (GB)"
FROM information_schema.tables;