DB/Mysql

This is another reference page to mysql database that lately free open source derivative is called MariaDB. So, in essence below commands should cover both although not always tested on both. Mainly these commands are used with Amazon RDS instances of mysql databases as my current focus in 2018 is right now. This may change in a future.

Check MySQL current connections

watch -n1 "mysql -u root -pPASSWORD -te \"select id, command, host, time, left(info,60) from information_schema.processlist where info is not null or time > 300 order by time asc;\" 2>/dev/null"

Mysql client - prompt

How to show the name of the current database in the MySQL prompt If you need the name of the currently selected database in your MySQL prompt, use the following lines in your MySQL my.cnf configuration file entry like this [mysql] prompt='mysql(\d)> ' Now, when you connect, the MySQL prompt will look like what’s shown below:

mysql((none))> use test;
#Database changed mysql(test)> 
mysql(test)> use mysql;
#Database changed mysql(mysql)>

Queries

Check size of all tables

SELECT 
     table_schema as `Database`, 
     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;