Method 1: Query via SQL
If you can connect to the database and execute SQL, you can directly use SQL to view it. The following two types of SQL can query the version number:
Writing method 1:
SELECT VERSION();
Writing method 2:
SELECT @@VERSION;
Example of query results for executing SQL:
+------------+ | version() | +------------+ | 5.7.16-log | +------------+
Method 2: Through the command line
If you can connect to the database directly via the command line.
Method 2.1: When the command line is just connected to the database
Use on the command linemysqlWhen the command is connected to the database, just after connecting, you can see the database version from the output information. Example:
> mysql -uroot -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
inServer version: 5.7.16-log MySQL Community Server (GPL)In this line we can see the version of the MySQL database.
Method 2.2: Use the status command
If we have passedmysqlThe command is connected to the database and can also be passedstatusOrder or\sCheck the database version information and the relevant status of the database.
Example 1:
mysql> status -------------- mysql Ver 8.0.33 for Win64 on x86_64 (MySQL Community Server - GPL) <--------------- here it is mysql Client（Command Line Tools）Version Connection id: 5 Current database: Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.7.16-log MySQL Community Server (GPL) <--------------- This is MySQL 服务端Version Protocol version: 10 Connection: localhost via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 2 hours 40 min 24 sec Threads: 2 Questions: 213 Slow queries: 0 Opens: 111 Flush tables: 1 Open tables: 104 Queries per second avg: 0.022
Example 2:
mysql> \s -------------- mysql Ver 8.0.33 for Win64 on x86_64 (MySQL Community Server - GPL) <--------------- here it is mysql Client（Command Line Tools）Version Connection id: 5 Current database: Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.7.16-log MySQL Community Server (GPL) <--------------- This is MySQL 服务端Version Protocol version: 10 Connection: localhost via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 2 hours 40 min 24 sec Threads: 2 Questions: 213 Slow queries: 0 Opens: 111 Flush tables: 1 Open tables: 104 Queries per second avg: 0.022
Extension: Method 3: Distinguish open source version and magic version through version_comment
SHOW VARIABLES LIKE 'version_comment';
This method actually checks the version notes of MySQL.
Example:
+-----------------+------------------------------+ | Variable_name | Value | +-----------------+------------------------------+ | version_comment | MySQL Community Server (GPL) | +-----------------+------------------------------+
version_comment is actually a global variable of the database. For the open source community version, the value of this variable is the same. However, there are some databases developed based on MySQL or compatible with MySQL. When it is necessary to distinguish them from MySQL, they can be distinguished based on this global variable.
For example, an example of the result query after the TiDB database is executed:
TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Example of results found after StarRocks database is executed:
StarRocks version 3.0.3
Extension: Method 4: View all version-related information
We have introduced the method to view the specific compiled version of MySQL through the global variable version_comment, so would you think? Is there anything else about the MySQL neutral version? Next, I will introduce a method to teach you to capture all information related to global variables and versions in one go.
Execute the SQL below to view all variables with version in their names.
SHOW GLOBAL VARIABLES LIKE '%version%';
Sample results after execution:
+-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.7.16 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.16-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Win64 | +-------------------------+------------------------------+
Correction of errors across the network
Error Correction 1: mysql --help or mysql -V method
Some articles will teach you how to usemysql --helpormysql -Vormysql --versionView the database version in a way.
But in this way, what I actually view ismysqlThe version of the client (command line tool), not the version of the MySQL database server.
Error Correction 2: View version through package management tools such as rpm
Some articles will teach you how to userpm -qa | grep mysqlThis way to view the version installed by the server.
The limitations of this method are:
(1) This method can only be executed on the server where the MySQL database is installed.
(2) Only applicable to some Linux operating systems that use rpm package management tools
The possible pitfalls of this method are:
The MySQL and package management tools that are actually running on the server may not query the same SQL. Because this method only queries the SQL version installed through the package management tool. However, the actual running MySQL may be installed offline through compressed packages and not through package management tools. In this case, there may be multiple versions of MySQL on the server at the same time. It may even be run through virtualization software such as docker, so we must avoid being misled by this situation.
postscript
Why write this article? The scenes that people encounter in their work and life are always complicated. Even simple problems often have some pitfalls, and they are often such pitfalls. If people are not careful, they will take a detour, such as the use above.statusWhen the command is used, the client and server versions are output at the same time. If you are not careful, you will easily misread it. This article hopes to sort out a simple scene clearly and explain the pitfalls clearly so that everyone can avoid detours when encountering similar scenes in work and study. I hope it will be helpful to everyone.
The above is the detailed content of N methods for obtaining version numbers in MySQL (the most complete network and a complete collection of pit avoidance). For more information about obtaining version numbers in MySQL, please pay attention to my other related articles!