MySQL Database Information and Commands

We can get all the information related to the MySQL Database using functions provided by MySQL. We generally need to get information related to:

• MySQL version
• What Database is selected
• User details
• Server Stats
• What sort of Queries that the user has triggered etc

To get each of this information, MYSQL provides in-built functions.

How to check the MySQL version?

To get the MySQL Version, we use the function 

SELECT VERSION();

How to check the current MySQL database name?

To get the database currently being used, we use the 

SELECT DATABASE();

How to show current MySQL user?

To get the current user who is connected, we use 

SELECT USER();

How to show current logged users?

To list all users that are currently logged in the MySQL database server, we use,

SELECT 
    user, 
    host, 
    db, 
    command 
FROM 
    information_schema.processlist;

How to check MySQL Status?

To get general Statistics about the session and user, we the following command. The statistics we get are things like bytes sent and received. Types of queries the user has triggered. For example, the root user has altered a table in this session. These are all session-specific details.

SHOW STATUS;

How to show MySQL variables?

The variables show usage information like what collation is used, default storage engine, cache sizes, connection timeouts etc. The list of information available is very big.

SHOW VARIABLES;