Useful MySQL Commands

Using mysqladmin
The mysqladmin tool has various switches to simplify common commands, for example to determine basic server status:

mysqladmin -u root -p status

If run as root the mysql user account details need not be specified, making this is best suited to simple monitoring scripts and administrative tasks.

Verify the MySQL Server version

mysqladmin -u root -p version

or you can use the following SQL query:

SHOW GLOBAL VARIABLES LIKE 'version';

List server configuration

mysqladmin -u root -p variables

or you can use the following SQL query:

SHOW GLOBAL VARIABLES;

List databases

mysqlshow -u root -p

or you can use the following SQL query:

SHOW DATABASES;

Listing Tables

mysqlshow -u root -p mysql

or you can use the following SQL query:

USE mysql;
SHOW TABLES;

List MySQL users

SELECT Host,User FROM mysql.user;

To list users with specific database access:

SELECT Host,User,Db FROM mysql.db;

List SQL Errors and Warnings
If a SQL statement generates an Error, this will be returned to the MySQL client. If warnings are generated, you will be told how many, but these will not be returned.

To display SQL errors and warnings (for the current session only):

SHOW WARNINGS;

Note that the number of warnings recorded is limited by max_error_count:

SHOW VARIABLES LIKE 'max_error_count';

Check for fragmented Tables
To display fragmented tables run the following:

select TABLE_NAME, TABLE_SCHEMA, Data_free from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema', 'mysql') and Data_Free >0;

If there are any fragmented tables, this can be fixed by running the following:

use databasename;
OPTIMIZE TABLE tablename;

Check for running MySQL processes
To display the running MySQL processes run the following:

show full processlist;