Getting MySQL server information
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.
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.
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.
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.
To view all tables in the database, you can use below command.
To view table schema, you can use below command.
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.
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.
Below command shows the statistics of the MySQL server like Threads connected, Threads created, read requests, write requests etc.