Category Archives: MySQL

Check MySQL database size

This is an example on how to check mysql database size of cacti database.

mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| cacti              |
| mysql              |
| test               |
+——————–+
6 rows in set (0.00 sec)

The the command below.

mysql> SELECT table_schema cacti, sum( data_length + index_length ) / 1024 / 1024 “Database Size in MB” FROM information_schema.TABLES GROUP BY table_schema;

Output.

+——————–+———————+
| cacti              | Database Size in MB |
+——————–+———————+
| cacti              |       1620.85510254 |
| information_schema |          0.00000000 |
| mysql              |          0.64040279 |
| test               |          0.00208855 |
+——————–+———————+
4 rows in set, 8 warnings (0.00 sec)

That’s all!

How to check the max_connections in MySQL database.

rpaco@choi:~$mysql -uroot -h localhost -p
Enter Password: mypassword

Type the command below.
mysql> show variables like ‘max_connections’;
+—————–+——-+
| Variable_name   | Value |
+—————–+——-+
| max_connections | 100   |
+—————–+——-+

Set temporary values. This values will be reset when mysql service restart.

mysql>set global max_connections = 200;

To set it permanently.

vi /etc/mysql/my.cnf
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
max_connections        = 250    <— uncommented then I have changed the value to 250