mysql dba queries

this post is small refcard of useful queries to understand your database and find some problems. list will be updated constantly with new ones.

--- mysql-dba-queries.sql --------------------------------

SHOW DATABASES;

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
SELECT VERSION();

/* http://dev.mysql.com/doc/refman/5.0/en/mysqld-server.html */
SHOW VARIABLES;
SHOW VARIABLES LIKE '%innodb%';

/* http://dev.mysql.com/doc/refman/5.0/en/show.html */
SHOW STATUS;
SHOW GLOBAL STATUS;

SHOW GRANTS;
SHOW GRANTS FOR 'viewer';

SHOW ENGINES;
SHOW CHARSET;
SHOW COLLATION;

/* Find total number of tables, rows, total data in index size for given MySQL Instance */

SELECT
 COUNT(*) `tables`
, CONCAT(ROUND(SUM(`table_rows`)/1024, 2), ' K') `rows`
, CONCAT(ROUND(SUM(`data_length`)/1048576, 2), ' M') `data`
, CONCAT(ROUND(SUM(`index_length`)/1048576,2), ' M') `idx`
, CONCAT(ROUND(SUM(`data_length`+`index_length`)/1048576, 2), ' M') `total_size`
, ROUND(SUM(`index_length`)/SUM(`data_length`), 2) `idx_frac`
FROM
 `information_schema`.`TABLES`;

/* Find biggest databases */

SELECT
 `table_schema`
, COUNT(*) `tables`
, CONCAT(ROUND(SUM(`table_rows`)/1024, 2), ' K') `rows`
, CONCAT(ROUND(SUM(`data_length`)/1048576, 2), ' M') `data`
, CONCAT(ROUND(SUM(`index_length`)/1048576, 2), ' M') `idx`
, CONCAT(ROUND(SUM(`data_length`+`index_length`)/1048576, 2), ' M') `total_size`
, ROUND(SUM(`index_length`)/SUM(`data_length`), 2) `idx_frac`
FROM
 `information_schema`.`TABLES`
GROUP BY
 `table_schema`
ORDER BY
 SUM(`data_length`+`index_length`) DESC
LIMIT 10;

/* Data Distribution by Storage Engines */

SELECT
 `engine`
, COUNT(*) `tables`
, CONCAT(ROUND(SUM(`table_rows`)/1024, 2), ' K') `rows`
, CONCAT(ROUND(SUM(`data_length`)/1048576, 2), ' M') `data`
, CONCAT(ROUND(SUM(`index_length`)/1048576, 2), ' M') `idx`
, CONCAT(ROUND(SUM(`data_length`+`index_length`)/1048576, 2), ' M') `total_size`
, ROUND(SUM(`index_length`)/SUM(`data_length`), 2) `idx_frac`
FROM
 `information_schema`.`TABLES`
GROUP BY
 `engine`
ORDER BY
 SUM(`data_length`+`index_length`) DESC
LIMIT 10;

--- mysql-dba-queries.sql ----------------------------EOF-

Thanks to:
— Peter Zaitsev and his MySQL Performance Blog
— MySQL Documentation