How to know mysql database size

How to know mysql database size

There are two ways to know the mysql database size

1 : BY SQL Script

Sum of data_length + index_length is equal to the total table size

       data_length – store the real data.

       index_length – store the table index.

Here’s the SQL script to list out the entire databases size

1 : SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 

"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

2 : SELECT TABLE_NAME, table_rows, data_length, index_length, 

ROUND(((data_length + index_length) / 1024 / 1024),2) "Size in MB"

FROM information_schema.TABLES WHERE table_schema = "msplash";

Table counts for a database the query is :

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test';

2. Locate the MySQL stored data

Windows

1) Locate the my.ini, which store in the MySQL installation folder

for example :

C:\Program Files\MySQL\MySQL Server 5.2\my.ini

Open it any text editor

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.2/"
 
#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.2/Data/"

Find the “datadir”, this is the where  MySQL stored the data in Windows.

Linux

1) Locate the my.cnf with the find / -name my.cnf command.

cat /etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
[mysqld]
#
# * Basic Settings
#
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket  = /var/run/mysqld/mysqld.sock
port   = 3306
basedir  = /usr
datadir  = /var/lib/mysql
tmpdir  = /tmp
language = /usr/share/mysql/english
skip-external-locking

3) Find the “datadir”, this is where  MySQL stored the data in Linux system.