How to view MySQL database status and information (memory, database, encoding format, table, column, index, etc.)

  • 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

1. Introduction

2. Text

2.1 view all databases

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.8 viewing database ports

2.9 viewing database index size

2.10 viewing database size

2.11 viewing the maximum number of connections

2.12 view information related to the current thread

2.13 viewing file storage path

2.14 viewing database codes

2.15 help instructions

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 👇🏻  

Tags: Database MySQL

Posted on Sun, 07 Nov 2021 12:16:00 -0500 by plutoplanet