MySQL - Troubleshooting

mysql

How can we get a concise report of the server's load from within the mysql client program?:

mysql> STATUS;

How can we see what SQL statements are currently being executed?

SHOW PROCESSLIST

The SHOW PROCESSLIST statement displays information about the activity of each currently connected client. For example, the presence of a large number of blocked queries might indicate that another connection is running a query that is inefficient and should be examined to see if it can be optimized. If you have the PROCESS privilege, it also shows queries being run by other users / accounts.

How can I discover which database schema I am using?

SELECT DATABASE();

How can we display the number of tables in each database:

SELECT TABLE_SCHEMA, COUNT(*) FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA;

Basic commands:

SHOW DATABASES;
SHOW DATABASES LIKE 'm%';
SHOW TABLES;
SHOW TABLES FROM mysql;
SHOW COLUMNS FROM CountryLanguage;
SHOW FULL COLUMNS FROM CountryLanguage\G
SHOW KEYS FROM City\G
SHOW INDEX FROM City\G
SHOW CREATE TABLE tableName;
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;

How can we discover information about available character sets?

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

How can we discover information about collations for each character set?

SELECT * FROM INFORMATION_SCHEMA.COLLATIONS;

How can we discover information about which character set applies to each collation?

SELECT * FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY;

How can we display the default collation for each character set:

SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
 WHERE IS_DEFAULT = 'Yes';

How can we discover information about constraints on key columns?

DESC INFORMATION_SCHEMA.KEY_COLUMN_USAGE;

How can we list available sub-routines, stored procedures, triggers, and functions?

DESC INFORMATION_SCHEMA.ROUTINES;

How can we list available databases?

DESC INFORMATION_SCHEMA.SCHEMATA;
SHOW DATABASES;

How can we display the storage engines used for the tables in a given database:

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world';

How can we find all the tables that contain columns of type SET:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE = 'set';

What information is stored in INFORMATION_SCHEMA.COLUMNS table?

The INFORMATION_SCHEMA.COLUMNS contains information about tables and columns.

DESC INFORMATION_SCHEMA.COLUMNS;
SHOW CREATE TABLE INFORMATION_SCHEMA.COLUMNS;

What information is stored in the INFORMATION_SCHEMA.COLUMN_PRIVILEGES table?

The INFORMATION_SCHEMA.COLUMN_PRIVILEGES contains information about column privileges held by your user accounts.

What information is stored in the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES table?

It contains information about database privileges held by your user accounts.

What information is stored in the INFORMATION_SCHEMA.STATISTICS table?

It contains information about table indexes.

What information is stored in the INFORMATION_SCHEMA.TABLES table?

It contains information about tables in databases.

What information is stored in the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table?

It contains information about constraints on tables.

**What information is stored in the INFORMATION_SCHEMA.TABLE_PRIVILEGES table?

It contains information about table privileges held by MySQL user accounts.

What information is stored in the INFORMATION_SCHEMA.TRIGGERS table?

It contains information about triggers in databases.

What information is stored in the INFORMATION_SCHEMA.USER_PRIVILEGES?

It contains information about global privileges held by user accounts.

What information is stored in the INFORMATION_SCHEMA.VIEWS?

It contains information about views in databases.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License