Mysql 5.6 vs 5.7 differences

blog

MySQL 5.7 has been GA since October 2015. At the time of writing, it is still a very new release. But more and more companies are looking into upgrading, as it has a list of great new features. Schema changes can be performed with less downtime, with more online configuration options. Multi-source and parallel replication improvements make replication more flexible and scalable. Native support for JSON data type allows for storage, search and manipulation of schema-less data.

An upgrade, however, is a complex process – no matter which major MySQL version you are upgrading to. There are a few things you need to keep in mind when planning this, such as important changes between versions 5.6 and 5.7 as well as detailed testing that needs to precede any upgrade process. This is especially important if you would like to maintain availability for the duration of the upgrade.

Which version are you using?

With MySQL 5.7, and actually with any MySQL version, one would only upgrade from the version prior to it. . It means that while you can easily upgrade from MySQL 5.6 to 5.7, if you are on MySQL 5.5, the upgrade has to be executed in two steps  – first 5.5 -> 5.6 and then 5.6 -> 5.7. The upgrade process may also differ in such case. While a straight upgrade from MySQL 5.6 to 5.7 can be executed as a binary upgrade, the first step [5.5 -> 5.6] should be performed by dumping and then reloading the data.

Changes between MySQL 5.6 and 5.7

As usual with new MySQL versions, many new features have been added and some existing behaviors have been altered. This may cause potential issues with your setup if you neglect the importance of research and testing – new behavior may not be compatible with your application.

One of the main changes is the way that internal metrics are made available. In MySQL 5.6, you could query information_schema.global_status table and get some data about MySQL internals. In MySQL 5.7, this data is available through the performance_schema – such change may render your monitoring and trending software useless unless you enable ‘compatibility’ mode.

Some changes have been introduced in the default SQL mode – right now MySQL uses STRICT_TRANS_TABLES by default. This is great change but it sure can break compatibility with older applications.

Another important change is related to the authentication mechanism. Pre-4.1 passwords [‘old passwords’] have been removed, a new authentication system has been added. Amongst others, password expiration policy has been introduced – this can become a serious issue as the default settings may not be safe for systems upgraded from older MySQL versions – it’s better to set policies manually instead of relying on defaults, which may change in the future.

Those are definitely not the only changes which may pose a problem in an upgrade process. We’ve covered these in more detail in our database upgrade guide, “Migrating to MySQL 5.7”.

Subscribe to get our best and freshest content