Information Archives | Bravehartk2's Blog

Get table or DB size im Megabyte

11th August 201510th October 2016 SheldonNo Comments

Sometimes it is really useful to know how large a certain database table has become. With the following SQL statement you get a list of all tables of the currently connected database with their rounded size in MB:

SELECT table_name AS "Table",
  round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

SELECT table_name AS "Table",

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

FROM information_schema.TABLES

ORDER BY (data_length + index_length) DESC;

This query selects all tables from all connected databases. If you want to select a certain […]

Get current user, mysql version and other status information

15th July 2015 SheldonNo Comments

To get information about the currently connected user and more MySQL status information, you can call the following command if you are already logged in:

mysql> STATUS;

1	mysql> STATUS;

This will give you an output like that:

--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:		1044395
Current database:	
Current user:		niceguy@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.1.73-log Source distribution
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			2 days 6 hours 26 min 44 sec

Threads: 8  Questions: 399680642  Slow queries: 20292  Opens: 9697  Flush tables: 1  Open tables: 4096  Queries per second avg: 2039.145
--------------

--------------

mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: 1044395

Current database:

Current user: niceguy@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.1.73-log Source distribution

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: latin1

Conn. characterset: latin1

UNIX socket: /var/lib/mysql/mysql.sock

Uptime: 2 days 6 hours 26 min 44 sec

Threads: 8 Questions: 399680642 Slow queries: 20292 Opens: 9697 Flush tables: 1 Open tables: 4096 Queries per second avg: 2039.145

--------------

As you can see, you got a lot more information like operation system data, MysSQL version or the number of slow […]

Check and optimize tables in MySQL

26th November 201312th January 2017 SheldonNo Comments

To check and optimize tables of a MySQL database:

$> mysqlcheck -os <db_name> -u<username> -p

1	$> mysqlcheck -os <db_name> -u<username> -p

To achieve the same for all databases of the respective MySQL server you can run the following command:

$> mysqlcheck -Aos -u<username> -p

1	$> mysqlcheck -Aos -u<username> -p

Further information about check and optimize tables in MySQL To get a few more facts about how to check and optimize tables in MySQL, you should […]

Find out where my.cnf is located

11th September 201220th September 2015 SheldonNo Comments

To find out where the MySQL configuration (my.cnf) is stored, you can use the strace command:

$> strace mysql ";" 2>&1 | grep my.cnf

1	$> strace mysql ";" 2>&1 \| grep my.cnf

You will get an output showing all the paths the MySQL deamon searches for the my.cnf upon startup like that:

stat("/etc/my.cnf", 0x7fffecb75210)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=3505, ...}) = 0
open("/etc/mysql/my.cnf", O_RDONLY)     = 3
stat("/usr/etc/my.cnf", 0x7fffecb75210) = -1 ENOENT (No such file or directory)
stat("/home/sheldon/.my.cnf", 0x7fffecb75210) = -1 ENOENT (No such file or directory)

stat("/etc/my.cnf", 0x7fffecb75210) = -1 ENOENT (No such file or directory)

stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=3505, ...}) = 0

open("/etc/mysql/my.cnf", O_RDONLY) = 3

stat("/usr/etc/my.cnf", 0x7fffecb75210) = -1 ENOENT (No such file or directory)

stat("/home/sheldon/.my.cnf", 0x7fffecb75210) = -1 ENOENT (No such file or directory)

As you can see, the service searches the following files: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf /home/sheldon/.my.cnf But only the /etc/mysql/my.cnf is found […]

Bravehartk2's Blog

- next level braining -

Category: Information

Get table or DB size im Megabyte

Get current user, mysql version and other status information

Check and optimize tables in MySQL

Find out where my.cnf is located

Share this:

Share this:

Share this:

Share this: