Sometimes you may need to check the size of each MySQL/MariaDB database and table to optimize or repair them due to some reason.
So, what to do? is there any options to check these from Linux command line?
Yes, everybody knows that everything is possible in Linux.
In other words i can say Nothing is Impossible.
Since MariaDB database has kept all the data’s in the filesystem so, we can easily check size of the databases and tables by navigating to the following folder var/lib/mysql/.
It won’t give you an accurate size but no major difference in the size when you comparing with the MySQL/MariaDB query.
Make sure, you should have root privilege to perform this action.
It stores all the database information in the information_schema database so, we can easily gather all the database information from information_schema database.
First login to MariaDB database using the following command and perform the below commands.
# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 43 Server version: 10.3.13-MariaDB Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
1) How To View The MySQL/MariaDB Database Size In MB Format In Linux?
Use the below command to get the database size in MB format.
MariaDB [(none)]> SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema; +--------------------+--------------+ | database | size in MB | +--------------------+--------------+ | information_schema | 0.17187500 | | mysql | 0.71849060 | | performance_schema | 0.00000000 | | testdb | 107.89165115 | | testdb1 | 1.67250538 | +--------------------+--------------+ 5 rows in set (0.013 sec)
2) How To View The MySQL/MariaDB Database Size In GB Format In Linux?
Use the below command to get the database size in GB format.
MariaDB [(none)]> SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES GROUP BY table_schema; +--------------------+----------------+ | database | size in GB | +--------------------+----------------+ | information_schema | 0.000167846680 | | mysql | 0.000701650977 | | performance_schema | 0.000000000000 | | testdb | 0.105362940580 | | testdb1 | 0.001633306034 | +--------------------+----------------+ 5 rows in set (0.012 sec)
3) How To View The Particular MySQL/MariaDB Database Size In MB Format In Linux?
Use the below command to get the given database size in MB format.
MariaDB [(none)]> SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES WHERE table_schema='testdb' GROUP BY table_schema; +----------+--------------+ | database | size in MB | +----------+--------------+ | testdb | 107.89165115 | +----------+--------------+ 1 row in set (0.002 sec)
4) How To View The MySQL/MariaDB Database Table Size In MB Format In Linux?
Use the below command to get the database table size in MB format.
MariaDB [(none)]> SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS TABLE_SIZE_in_MB FROM information_schema.TABLES; +--------------------+----------------------------------------------------+------------------+ | DB_NAME | TABLE_NAME | TABLE_SIZE_in_MB | +--------------------+----------------------------------------------------+------------------+ | information_schema | ALL_PLUGINS | 0.01562500 | | information_schema | APPLICABLE_ROLES | 0.00000000 | | . | | . | | information_schema | INNODB_SYS_VIRTUAL | 0.00000000 | | information_schema | INNODB_MUTEXES | 0.00000000 | | mysql | help_topic | 0.40622330 | | mysql | help_keyword | 0.10279846 | | . | | . | | mysql | column_stats | 0.00390625 | | mysql | time_zone | 0.00097656 | | testdb1 | wp_posts | 0.71291733 | | testdb1 | wp_term_taxonomy | 0.02214050 | | . | | . | | testdb1 | wp_comments | 0.01002884 | | testdb1 | wp_options | 0.62099075 | | testdb | wp_posts | 94.74848175 | | testdb | wp_term_taxonomy | 0.43618011 | | . | | . | | testdb | wp_options | 2.27652359 | | testdb | wp_jtrt_tables | 0.00097656 | | performance_schema | cond_instances | 0.00000000 | | performance_schema | events_waits_current | 0.00000000 | | . | | . | | performance_schema | session_connect_attrs | 0.00000000 | | performance_schema | session_account_connect_attrs | 0.00000000 | +--------------------+----------------------------------------------------+------------------+ 197 rows in set (0.012 sec)
5) How To View The MySQL/MariaDB Database Table Size In GB Format In Linux?
Use the below command to get the database table size in GB format.
MariaDB [(none)]> SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 AS TABLE_SIZE_in_MB FROM information_schema.TABLES;
6) How To View The Particular MySQL/MariaDB Database Table Size In MB Format In Linux?
Use the below command to get the given database table size in MB format.
In this example we have checked testdb database tables so, you have to add your database name instead of us.
MariaDB [(none)]> SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "testdb" ORDER BY (data_length + index_length) DESC; +--------------------------+-----------+ | Table | Size (MB) | +--------------------------+-----------+ | wp_posts | 94.75 | | wp_postmeta | 8.11 | | wp_options | 2.28 | | wp_terms | 0.83 | | wp_comments | 0.58 | | wp_term_taxonomy | 0.44 | | wp_commentmeta | 0.41 | | wp_term_relationships | 0.40 | | wp_usermeta | 0.04 | | wp_gglcptch_whitelist | 0.03 | | wp_users | 0.01 | | wp_bpspro_login_security | 0.01 | | wp_termmeta | 0.00 | | wp_ultimatetables | 0.00 | | wp_wct_fields | 0.00 | | wp_links | 0.00 | | wp_bpspro_mscan | 0.00 | | wp_wct_cron | 0.00 | | wp_jtrt_tables | 0.00 | | wp_wct_relations | 0.00 | | wp_wct_setup | 0.00 | | wp_bpspro_seclog_ignore | 0.00 | | wp_bpspro_db_backup | 0.00 | +--------------------------+-----------+ 23 rows in set (0.002 sec)
7) How To View The MySQL/MariaDB Database Size In Linux?
Use the below command to get the database size in MB format.
# du -h --max-depth=1 /var/lib/mysql/ 1.3M /var/lib/mysql/mysql 1.9M /var/lib/mysql/testdb1 109M /var/lib/mysql/testdb 8.0K /var/lib/mysql/performance_schema 232M /var/lib/mysql/
8) How To View The MySQL/MariaDB Database Table Size In Linux?
Use the below command to get the database table size in MB format.
MySQL uses a number of different file formats for the storage of information.
If it’s MyISAM Storage Engine and it will create a following file when the new database is created.
MySQL/MariaDB creates files named Table1.MYD MySQL Data, Table1.MYI MySQL Index, and Table1.frm Format. These files will be in the directory.
# ls -ltrhS /var/lib/mysql/testdb total 109M -rw-rw---- 1 mysql mysql 111K Apr 6 10:11 wp_comments.MYI -rw-rw---- 1 mysql mysql 112K Apr 6 10:11 wp_gglcptch_whitelist.ibd -rw-rw---- 1 mysql mysql 118K Apr 6 10:11 wp_commentmeta.MYI -rw-rw---- 1 mysql mysql 135K Apr 6 10:11 wp_term_relationships.MYD -rw-rw---- 1 mysql mysql 220K Apr 6 10:11 wp_term_taxonomy.MYD -rw-rw---- 1 mysql mysql 227K Apr 6 10:11 wp_term_taxonomy.MYI -rw-rw---- 1 mysql mysql 271K Apr 6 10:11 wp_term_relationships.MYI -rw-rw---- 1 mysql mysql 304K Apr 6 10:11 wp_commentmeta.MYD -rw-rw---- 1 mysql mysql 376K Apr 6 10:11 wp_terms.MYD -rw-rw---- 1 mysql mysql 470K Apr 6 10:11 wp_terms.MYI -rw-rw---- 1 mysql mysql 486K Apr 6 10:11 wp_comments.MYD -rw-rw---- 1 mysql mysql 600K Apr 6 10:11 wp_postmeta.MYI -rw-rw---- 1 mysql mysql 1.2M Apr 6 10:11 wp_posts.MYI -rw-rw---- 1 mysql mysql 2.3M Apr 6 10:11 wp_options.MYD -rw-rw---- 1 mysql mysql 7.6M Apr 6 10:11 wp_postmeta.MYD -rw-rw---- 1 mysql mysql 94M Apr 6 10:11 wp_posts.MYD
One Comment on “How to Check MySQL/MariaDB Database And Table Size In Linux?”