MySql

Query

List sizes of all databases

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

List the sizes of all of the tables in a specific database

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

Kill all sleep connections

for proc in $(mysql --host IP -uroot -pPWD -e "SHOW PROCESSLIST" | grep Sleep | awk '{ print $1  }'); do mysql --host IP -uroot -pPWD -e "KILL $proc"; done

Show engine for all tables

SHOW TABLE STATUS WHERE Name = 'xxx'

Last updated