How to view mysql

MySQL is a widely used relational database management system that can store and manage large amounts of data. Whether you are using MySQL for daily management or debugging, you need to check a lot of information. Therefore, this article will provide common viewing methods and query commands for MySQL to help readers better understand and manage MySQL databases.

1. Check the MySQL version information

In daily maintenance of the MySQL database, you often need to check the MySQL version information. The following are three ways to view MySQL version information:

1. View MySQL version command:

mysql --version

2. After logging in to the MySQL database, enter the following command:

SELECT VERSION();

3. Enter the following command on the MySQL client:

SHOW VARIABLES LIKE 'version%';

The above three methods can view the version information of MySQL, and the third method can view the updated version information. More detailed version information.

2. Check MySQL database related information

1. Check the current running status of MySQL:

SHOW [GLOBAL | SESSION] STATUS;

After entering the above command, you can see the status information of the MySQL service.

2. View MySQL startup parameters:

SHOW VARIABLES;

This command can view the MySQL runtime parameters, including System time, cache size and other configuration information.

3. View all databases in MySQL:

SHOW DATABASES;

This command can list all databases in MySQL, including The system default database.

3. View MySQL user and permission information

1. View MySQL user information:

SELECT USER,HOST FROM mysql.user;

Through the above command, you can view the user and host information created in MySQL.

2. View MySQL permission information:

SHOW GRANTS FOR username@host;

This command can view the permissions of a user Permissions held in MySQL.

3. View the information of the current MySQL login user:

SELECT CURRENT_USER();

The above command can view the current login MySQL The user's username and host information.

4. Use the MySQL command line tool to view table information

The MySQL command line tool provides a set of very useful commands for viewing table information in the MySQL database, including the following commands:

1. Switch the current database:

USE database_name;

Use the above command to specify the currently connected database so that subsequent operations can target The database is carried out.

2. View all tables:

SHOW TABLES;

This command can list all table names in the current database.

3. View table structure:

DESC table_name;

This command can view the structural information of the specified table, including the information of each field.

4. View table data:

SELECT * FROM table_name;

The above command can view all data in the specified table. In addition, you can also add some conditional statements to filter out data that meets the conditions.

5. View the index information of the table:

SHOW INDEXES FROM table_name;

It is helpful to view the index information of the table. It helps you optimize table performance.

5. View information through MySQL logs

MySQL generates various logs by default, including error logs, query logs, slow query logs, etc. The following is how to view information through the MySQL log:

1. View the error log:

You can usually specify the path to the error log in the MySQL configuration file (my.cnf) , if not specified, it defaults to /var/log/mysql/error.log. By viewing the error log, you can understand the current operating status of MySQL and the cause of the problem.

2. View query log:

The query log records all SQL statements executed by MySQL. It helps us understand the execution status of MySQL and understand the performance of the query. You can enable the query log by specifying the path to the query log in my.cnf and turning on the query log switch.

3. View the slow query log:

The slow query log records SQL statements whose query time exceeds the specified time (usually 1 second). Enabling slow query logs can help us understand the performance bottlenecks of the MySQL database so that we can optimize performance.

6. Summary

This article introduces commonly used MySQL viewing methods and query commands, including viewing MySQL version information, database related information, user and permission information, table information, and log viewing. These viewing methods and commands can help developers and database administrators better manage and maintain MySQL databases, and we hope they will be helpful to readers.

The above is the detailed content of How to view mysql. For more information, please follow other related articles on the PHP Chinese website!