- Preparing for the 2022 spring recruitment or summer internship, this column will continue to output MySQL series articles. I wish you a little progress every day! Private letter writer at the end of the article, let's go to Dachang together.
- This article summarizes how to view MySQL database status and information, which will be updated daily~
- For knowledge points such as Redis introduction to mastery, concurrent programming, comprehensive introduction to Java and Hongmeng development, please refer to my previous blogs
- Believe in yourself, the more you live, the stronger you are. You should open a way to the mountains and build a bridge in case of water! Life, you give me pressure, I return you miracle!
catalogue
2.2 viewing the database in use
2.3 viewing tables in the current database
2.5 display authorized users and their permissions
2.6 viewing server error or warning information
2.7 view database creation statement and table creation statement
2.9 viewing database index size
2.11 viewing the maximum number of connections
2.12 view information related to the current thread
2.13 viewing file storage path
1. Introduction
Nowadays, when we operate databases, we use ready-made database management tools, such as Navicat, SQLyog and so on. These tools can provide a visual operation interface, which greatly improves our operation efficiency, but this often makes us forget a lot of database statements, such as creating table statements, querying database information, etc. In this article, we learn several very useful sentences to help you when there is no visualization tool.
2. Text
2.1 view all databases
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | liziba | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
2.2 viewing the database in use
No database is selected at this time, null is displayed
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec)
Select database liziba
mysql> USE liziba; Database changed mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | liziba | +------------+ 1 row in set (0.00 sec)
2.3 viewing tables in the current database
mysql> SHOW TABLES; +------------------+ | Tables_in_liziba | +------------------+ | user | +------------------+ 1 row in set (0.00 sec)
2.4 viewing the column information of the table
mysql> SHOW COLUMNS FROM user; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | age | int(11) | NO | | NULL | | | sex | smallint(6) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
2.5 display authorized users and their permissions
To view authorization information for all users:
mysql> SHOW GRANTS;
To view the authorization information of a specific user:
SHOW GRANTS FOR 'root'@'localhost';
2.6 viewing server error or warning information
Check the error information. Here, I type ERRORS as ERROES, and the server will generate an error log
mysql> SHOW ERROES; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ERROES' at line 1 mysql> mysql> mysql> SHOW ERRORS; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ERROES' at line 1 | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> SHOW WARNINGS; +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARRINGS' at line 1 | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 s
2.7 view database creation statement and table creation statement
View the database building statements of liziba database
mysql> SHOW CREATE DATABASE liziba; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | liziba | CREATE DATABASE `liziba` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)
View the table creation statement of the user table and pay attention to using use xxx in advance
mysql> SHOW CREATE TABLE user; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user | CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key', `name` varchar(255) NOT NULL COMMENT 'user name', `age` int(11) NOT NULL COMMENT 'Age', `sex` smallint(6) NOT NULL COMMENT 'Gender', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
2.8 viewing database ports
If you forget the port, you can view the port information (if you can log in, hahaha!)
mysql> SHOW VARIABLES LIKE 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set, 1 warning (0.00 sec)
2.9 viewing database index size
First, we use database information_schema, which records the relevant information of the table
mysql> use information_schema; Database changed
Viewing the tables table column, you can see the table related information attributes of the record, including index_ The length column records the index size of the table. The unit is B, table_ The schema column records the database to which the current table belongs.
We can according to table_schema = 'liziba', view the size of the index in the current database
mysql> SELECT CONCAT(ROUND(SUM(INDEX_LENGTH)/(1024*1024), 4), 'mb') AS 'Database Index Size' -> FROM tables -> WHERE table_schema = 'liziba'; +---------------------+ | Database Index Size | +---------------------+ | 0.0000mb | +---------------------+ 1 row in set (0.00 sec)
2.10 viewing database size
In addition to calculating the index size, we can also count the size of the database. We only need to add up the table data memory and index data memory.
mysql> SELECT CONCAT(((ROUND(SUM(data_length), 4) + ROUND(SUM(index_length), 4)) / (1024 * 1024)), ' mb') AS 'Database Size' -> FROM tables -> WHERE table_schema = 'liziba'; +---------------+ | Database Size | +---------------+ | 0.01562500 mb | +---------------+ 1 row in set (0.00 sec)
image.png
2.11 viewing the maximum number of connections
151 is the default maximum number of connections
mysql> SHOW VARIABLES LIKE '%max_connections%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_connections | 151 | | mysqlx_max_connections | 100 | +------------------------+-------+ 2 rows in set, 1 warning (0.00 sec)
2.12 view information related to the current thread
Note that you can use STATUS to view thread related STATUS, and VARIABLES to view configuration parameters. The following query shows the number of connections, the number of threads created, and the running threads
mysql> SHOW STATUS LIKE 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 1 | | Threads_connected | 3 | | Threads_created | 4 | | Threads_running | 2 | +-------------------+-------+ 4 rows in set (0.00 sec)
2.13 viewing file storage path
mysql> SHOW VARIABLES LIKE '%datadir%'; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | datadir | E:\mysql-8.0.15-winx64\data\ | +---------------+------------------------------+ 1 row in set, 1 warning (0.00 sec)
2.14 viewing database codes
It's very useful to check the database code. Sometimes when we synchronize data, the data will be abnormal due to different codes. We can check the database code format in the following ways.
mysql> SHOW VARIABLES LIKE 'collation%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | gbk_chinese_ci | | collation_database | utf8_general_ci | | collation_server | utf8mb4_0900_ai_ci | +----------------------+--------------------+ 3 rows in set, 1 warning (0.00 sec)
2.15 help instructions
You can view commands using the HELP command.
For example, HELP SHOW;
mysql> HELP SHOW;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
HELP STATUS;
mysql> HELP STATUS; Many help items for your request exist. To make a more specific request, please type 'help <item>', where <item> is one of the following topics: FLUSH SHOW SHOW ENGINE SHOW FUNCTION STATUS SHOW MASTER STATUS SHOW PROCEDURE STATUS SHOW SLAVE STATUS SHOW STATUS SHOW TABLE STATUS
👇🏻 Pay attention to the official account for more information 👇🏻