Introduction To MySQL Versions

Versioning is a process of categorizing either unique version names or numbers to the unique set of software program as it is developed and released. The commonly used version name for denoting the initial release of a software or program is version 1.0. There is no industry standard rule available that decide how version number should be formatted. Thus, each company has its own methods for assigning the version name to the software. When the new features in software and programs have introduced, fixes bugs, patched security holes, then version number increased that indicates those improvements.

The latest support for working with MySQL is version number v5.8. It contains many essential changes, including new features added and removed, fixed bugs and security issues, etc. This version contains the release history from MySQL 8.0 to MySQL 8.0.21. It is available from April 2018 and ends the support in April 2026.

When you are going to install MySQL in your system, you must have to choose the version and distribution format to use. You can install MySQL in two ways, where first is a development release, and the second is General Availability (GA) release. The development release provides the newest feature and is not recommended to use in production. The General Availability (GA) release, also known as production or stable release, is mainly used for production. Therefore, you must have to decide the most recent General Availability release.

How do I check MySQL version?

Go to MySQL workbench and log to the server.There is a field called Server Status under MANAGEMENT.Click on Server Status and find out the version.

From the Command Line

If you have SSH access to the server, there are several different commands that can help you determine the version of your MySQL. The MySQL server binary is named mysqld. To get the server version run the binary using the --version or -V option:

mysqld --versionCopy

The command will output information about the MySQL version and exit. In this example the version of the MySQL server is 5.7.27:

mysqld  Ver 5.7.27-0ubuntu0.18.04.1 for Linux on x86_64 ((Ubuntu))
Copy

mysqladmin is a client utility that is used to perform administrative operations on the MySQL servers. It can be also used to query the MySQL version:

mysqladmin -VCopy

The output will be slightly different from the previous command:

mysqladmin  Ver 8.42 Distrib 5.7.27, for Linux on x86_64
Copy

From the MySQL Shell

A command client utility such as mysql, can also be used to determine the version of the MySQL server. To connect to the MySQL server simply type mysql:

mysqlCopy

Once connected to the MySQL shell, the version will be printed on the screen:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)
Copy

To get the information about the MySQL version and other components, query the version variables:

SHOW VARIABLES LIKE "%version%";Copy

+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.27                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.27-0ubuntu0.18.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+
8 rows in set (0.02 sec)
Copy

There are also some other statements and commands that can show you the server version. SELECT VERSION() statement will display only the MySQL version.

SELECT VERSION();Copy

The STATUS command shows the MySQL version as well as the information about the server status:

STATUS;Copy

Using PHP

If you are on a shared hosting and you don’t have access to the command line or to a MySQL client like PhpMyAdmin, you can determine the version of the MySQL server using PHP. In your website document root directory upload the following PHP file using a FTP or SFTP client. Make sure you change the my_user and my_password with an actual MySQL user account :

<?php

// Create a database connection.
$link = mysqli_connect("localhost", "my_user", "my_password");

// Print the MySQL version.
echo mysqli_get_server_info($link);

// Close the connection.
mysqli_close($link);

CopyADVERTISINGOpen the file in your browser and the version of the MySQL server will be displayed on your screen:

5.7.27-0ubuntu0.18.04.1

What are the different version of MySQL?

MySQL is offered under two different editions: the open source MySQL Community Server and the proprietary Enterprise Server.

Features Added in MySQL 8.0

The following features are added in MySQL 8.0 version:

Data Dictionary: It incorporates the transactional data dictionary to stores information about the database objects. Previous versions stored data in metadata files and non-transactional tables.

Atomic DDL Statement: It is an Atomic Data Definition Language statement that combines storage engine operations, data dictionary updates, and binary log associated with a DDL operation into a single atomic transaction.

Upgrade Procedures: Previously, the installation of the new MySQL version automatically upgrade the data dictionary table at the next startup, and then DBA is expected to invoke mysql_upgrade command manually for completing the upgrading process. After MySQL 8.0.16, it is not dependent on the DBA to invoke mysql_upgrade command for completing the up-gradation process.

Security and account management: There is some enhancement added to improve the security and provide it to enable greater DBA flexibility in account management.

Resource Management: Now, MySQL allows you to create and support resource groups, assign threads to a particular group so that it can execute according to the resource available for the group. Group attributes can control its resource consumption by threads in the group.

Table Encryption Management: Now, table encryption is managed globally by defining and enforcing encryption defaults. The default_table_encryption variable or DEFAULT ENCRYPTION clause defines encryption default when creating a schema and general tablespace.

InnoDB enhancements: The InnoDB enhancement were added in auto-increment counter, index tree corruption, memcached plugin, InnoDB_deadlock_detect, tablespace encryption feature, storage engine, InnoDB_dedicated_server, zlib library, and many more.https://22c7c2a84fb33cb1c442a14e1c48b9f0.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.html

Character Set Support: The default character set now changed from latin1 to utf8mb4. The new character set has many new collations, including utf8mb_ja_0900_as_cs.

JSON Enhancements: The following enhancements or additions are introduced in the MySQL’s json functionality: Inline path (->>) operator, json aggregate functions JSON_ARRAYAGG() and JSON_OBJECTAGG(), utility function JSON_PRETTY(), JSON_STORAGE_SIZE()(, JSON_STORAGE_FREE(). In sorting json values, now each value is represented by variable-length part of sort key instead of a fixed 1K size. It also added merge function JSON_MERGE_PATCH to add 2 json object and JSON_TABLE() function.

Data Type Support: In data type specifications, it can support the use of expressions as default values.

Optimizer Enhancement: This version added optimizer enhancement such as invisible indexes, descending indexes, support the creation of a functional index. It can use constant folding for comparison between a column and a constant value.

Window Function: This version supports many new window functions such as RANK(), LAG(), and NTILE().

Some other important features are:

• It enhances Regular expression support.
• Error Logging re-written to use MySQL component architecture.
• A new backup lock introduced that permits DML while preventing an operation, which can result in an inconsistent state.
• It enhances connection management. Now, TCP/IP port can be configured specifically for administrative connections. It gives more control in compression to minimize the bytes sent over the connection to the server.
• In previous versions, the plugins were written in C or C++. Now, it must be written in only the C++ language. MySQL 8.0.17 version provides clone plugins, which permit InnoDB data locally or from a remote server. The clone plugin also supports replication.
• In this version, the time zone support for TIMESTAMP and DATETIME values.
• This version also added the SQL standard table value constructor and explicit table clause.

Features Deprecated in MySQL 8.0

The MySQL 8.0 version has deprecated many features and can be removed in the future series. Some of the features are explained below:

• The character set utf8mb3 is deprecated.
• The sha256_password is deprecated and removed in future versions. Now, the default authentication will be caching_sha2_password.
• The validate_password plugin will be deprecated soon and can be removed in future versions.
• The ENGINE clause will be deprecated for the ALTER TABLESPACE and DROP TABLESPACE.
• AUTO_INCREMENT and UNSIGNED attribute are deprecated for FLOAT and DOUBLE column type.
• Now, it uses JSON_MERGE_PRESERVE() function instead of JSON_MERGE().
• The SQL_CALC_FOUND_ROWS modifier, FOUND_ROWS() function, –no–dd–upgrade server option, mysql_upgrade client, and mysql_upgrade_info are also deprecated.
• The use of MYSQL_PWD environment variable, which specifies the MYSQL password, is deprecated now.

Features Removed in MySQL 8.0

• The InnoDB_locks_unsafe_for_binlog system variable was removed, information_schema_stats variable is replaced by information_schema_stats_expiry.
• Some features related to the account management were removed, which are: GRANT statement for creating user, PASSWORD() function, old_passwords system variable, etc.
• The code related to the InnoDB system table is obsolete and was removed from the MySQL 8.0 version. The INFORMATION_SCHEMA view which is based on InnoDB system table now replaced by internal system view and renamed as:

• This version also removed some query catch, which is FLUSH QUERY CACHE, RESET QUERY CACHE statement, SQL_CACHE SELECT modifier, etc.
• The sync_frm system variable is removed because of the .frm files become obsolete.
• The multi_range_count, log_warning, and global scope for the sql_log_bin system variable have been removed.
• Some of the encryption-related items such as ENCODE(), DECODE(), ENCRYPT(), etc. have also removed.
• It removed mysql_install_db program is removed, and instead if this it uses –initialize or –initialize_insecure option.

Let us understand the release history of previous versions of MySQL through the following table: