Getting server info in MySQL

Once you are connected to MySQL, you can execute help command to view all available commands in MySQL. To know what all options are supported by show command, you can use below command.

 
help show

There are many show commands that can give all important information of MySQL server. For example – You can view all databases as shown in below command.

 
show databases;

Here is the sample output of above command.

 
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |

Information_schema is the database that stores meta data of the user tables, triggers, routines etc. If you can not see information_schema in workbench, you will have to edit the preferences to show the metadata and internal schemas. To view which database is currently selected, you can use below command.

 
select database();

Above command returns null if no database is selected. If you execute any command without selecting the database, you will get error saying no Database is selected. To select the database, you have to use below command.

 
use databaseName;

To view all tables in the database, you can use below command.

 
Show tables;

To view table schema, you can use below command.

 
Describe tableName;

You can use below commands to view procedures

 
SHOW PROCEDURE STATUS;

You can use below commands to view functions

 
SHOW FUNCTION STATUS;

MySQL supports many storage engines like InnoDB and MyISAM. To view which storage engine is being used for each table, you need to use below command.

 
SHOW TABLE STATUS;
Show table status where name like ‘%routines%’G

Note that \G (instead of ; semicolon ) is used when you want to display the records in key-value pairs.

information_schema database in MySQL

MySQL stores metadata in the database called as information_schema. You can view all tables in information_schema using below command.

 
SHOW TABLES;

You can use SQL queries to get the information of all tables in MySQL Server. Below query will return all table and schema names in MySQL Server.

 
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables

Below query will return all views and schema names in MySQL Server.

 
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables 
WHERE TABLE_TYPE LIKE ‘VIEW’;

Getting server date and time

Below commands can be used to get the current date and time of MySQL server instance.

 
SELECT CURDATE(), CURTIME(), NOW();

• CURDATE() function displays only date.
• CURTIME() function displays only time.
• NOW() function displays date as well as time.

Below command shows the name of current user logged into MySQL server.

 
SELECT USER();

Below command shows the statistics of the MySQL server like Threads connected, Threads created, read requests, write requests etc.

 
SHOW STATUS;