MySQL 5.7 vs MySQL 8

In this article, we will learn about MySQL 5.7 vs MySQL 8. MySQL is known as the most popular and used RDBMS with the latest major release – MySQL 5.7 over 5 years ago.

The development version has started and is available for download in the MySQL repository. For example the MySQL development version for Windows: MySQL Installer 8.0.3 RC and the Ubuntu versions: Download the MySQL APT repository

There are a few differences that will affect the way you work with MySQL when you go from 5.7 to 8. For example, if you want to change your root password in MySQL 8, you need to perform different steps compared to 5.7

MySQL 8 set the new root password

If you can connect to your MySQL server and want to change your password via query, you can do this by

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

or removing the root password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

New in MySQL 8

Character Sets

Utf8mb4 collations – The idea is to change the default character set from latin1 to utf8mb4 and the default collation from latin1_swedish_ci to utf8mb4_800_ci_ai. UTF8 is the dominant character encoding for the web (supports 4-byte UTF-8 characters/emoji in your database) and this step will make life easier for the vast majority of MySQL users. This is great news and a step forward towards standardized multilingual support in data-driven applications.

Roles

Roles have long been a much sought-after feature. In general, this is a representation of a set of privileges. Now you can define a role for users instead of having to remember to grant insert, update, etc. for those users, just create a role and you’re good to go.

Invisible indexes

This function helps you when you need to check whether an index can be deleted or not. To test performance without this index, you can make it invisible. If you suspect that an index is in use and is affecting performance, you can restore the invisible index. The optimizer does not see invisible indexes, and you can monitor query performance before dropping the index.

System table moved to InnoDB

The first storage engine for MySQL – MyISAM and was previously available in MySQL. MySQL 5.7 continues to use MyISAM for MySQL grant tables in MySQL schema, but in MySQL, they are moved to InnoDB.

MyISAM would be available in MySQL 8.0, but with a few restrictions

  • After the introduction of the new data dictionary, the MyISAM tables are no longer part of the system schema.
  • You can create a table engine = MyISAM and it will work as before
  • Copying the MyISAM table to a running MySQL server does not work because it is not recognized.

SET PERSIST for global variables

Another great innovation is the permanent modification of the runtime configuration. Since version 8 can be used

SET PERSIST innodb_buffer_pool_size = X;

instead of

SET GLOBAL innodb_buffer_pool_size = X;

so that runtime changes are retained on restart.

UUID Enhancements

UUIDs are considered to generate unique IDs in tables. Since version 8, MySQL can store these values in a VARBINARY (16) column instead of CHAR (36). New functions are introduce to work with these UUID values: BIN_TO_UUID (), UUID_TO_BIN (), IS_UUID (). Better memory usage, better performance, more flexible way of working with unique identifiers.

Descending Indexes

Descending indexes are now available. They can be scanned in reverse order without sacrificing performance. In the past, desc or asc was ignored. This feature is extremely useful when you need to recover newly created records versus older ones.

That is all we learn about MySQL 5.7 vs MySQL 8

Reference

Please share this post:
Posts created 79

Ask a Question

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

Begin typing your search term above and press enter to search. Press ESC to cancel.

%d bloggers like this: