MySQL 8: An Overview of the Latest Resources and Security

The availability of the MySQL Community Server 8.0.12 version was announced by Unisecure Data Centers recently. MySQL aims to improve certain developer activities such as DLL Operations, sub-query optimization, and many others. We are going to take a closer look at the latest version and see what it has to offer.

Resource groups

This feature might be the most interesting ones. It can be used by developers in order to assign threads to specific groups. We do not have that many details about the resource groups feature, as there is only one documentation page for it.

You should know that there are attributes for a group, and they allow them to control certain resources, as well as their consumption. The attributes can be changed by the DBAs. For example, virtual CPU represents a manageable resource. Administrators can decide which thread will have priority when it comes to the usage of CPU, and certain resource groups can be associated with virtual CPUs.

There are also disadvantages if this is set by thread. For example, you might end up not assigning the group properly, as long as you don’t analyse the process list. You might also need to have a script running all the time. Additionally, there are some risks if you do this. If you use ProxySQL and multiplexing or connection pooling when you set this to a thread, it is possible that you assign limitations to queries.

Security

The MySQL8 added numerous security enhancements. For example, there is a new caching_sha2_password authentication plugin available. Password hashing is used again, but this time caching is used in order to fix possible latency issues during connection.

Roles are also supported by MySQL, and they can be both granted and revoked. Another addition, is the fact that password history information is maintained. It is possible to restrict reuse of previous passwords. A list of passwords can be created, and a new password cannot be from that list. The restrictions are available for a certain period of time.

The DBAs are also able to require that all attempts to change the password of an account are verified. The verification process requires the user to specify the current password that he or she wants to replace. Therefore, it is impossible to change the password without knowing the current one.

Data dictionary

The new version of MySQL also comes with a data dictionary that contains information about database objects. Before this release, the dictionary existed in nontransactional tables and metadata files, but it is no longer stored there.

Improvements for InnoDB

There are also some InnoDB improvements. For example, restarting the server won’t affect the AUTO_INCREMENT = N table option. The value remains the same, even if the server restarts several time. This means that if you set a specific value, it won’t be alterered.

JSON additions

There are also several changes that aim to improve the functionality of JSON. For example, the ->> operator was added. Additionally, two JSON aggregation functions were added: JSON_ARRAYAGG() and JSON_OBECTAGG ().

Optimizer

There are also several optimizer enhancements

MySQL supports descending indexes. Until now, it was possible to scan an index in reverse order, but this affected the performance. Thanks to descending indexes, this isn’t the case anymore. MySQL also added support for the creation of functional index key parts, used to index expression values.

Invisible indexes are supports as well. This kind of index is maintained normally, with one exception: the index is not used by the optimizer. By default, indexes are visible, but you can choose to make them invisible.

Nicole Hicks

Nicole Hicks a graduate of UFT. She’s based in Toronto but travels much of the year. Nicole has written for NPR, Motherboard, MSN Money, and the Huffington Post. Nicole is a financial reporter, focusing on technology, national security, and policing.

You might like

Leave a Reply

Your email address will not be published. Required fields are marked *

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