Databases, MySQL

Retrieve useful information from MySQL

Today we will see how can we retrieve various information from mysql server using SQL queries.

Get all users:

SELECT * FROM mysql.user;

Get top 10 tables in size:

SELECT concat(table_schema,'.',TABLE_NAME) TABLE_NAME, 
concat(round(data_length/(1024*1024),2),'M') 
data_length FROM information_schema.TABLES ORDER BY data_length DESC LIMIT 10;

Get activated plugins:

SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE 
FROM information_schema.`PLUGINS` WHERE PLUGIN_STATUS='ACTIVE';

Get user privilleges:

SELECT * FROM information_schema.`USER_PRIVILEGES` WHERE grantee LIKE '%root%';

Get available engines:

SELECT * FROM information_schema.`ENGINES` WHERE SUPPORT='YES';

Get available global variables:

SELECT * FROM information_schema.`GLOBAL_VARIABLES` ORDER BY VARIABLE_NAME;

Get available schemas:

SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME 
FROM information_schema.`SCHEMATA`;