MySQL 8.0 will be GA soon (just my assumption: Oracle doesn’t tell me anything about its release plans) and it’s time to think about having a look at it.
If this is your first try of MySQL 8, get prepared for several impacting differences from previous versions.
In this article I won’t tell you what you can do with MySQL 8: there is plenty of material about this, including in this very blog. I will instead concentrate on differences from previous versions that users need to know if they want to avoid surprises.
Let’s start with an observation of the data directory.
After a standard installation, without any additional options, I see the following:
Files that I expected to see
auto.cnf ib_buffer_pool ib_logfile0 ib_logfile1 ibdata1 ibtmp1 (dir) mysql (dir) performance_schema (dir) sys
These files are also present in 5.7.
Files that are new in 8.0
log-bin is ON by default. You need to remember this if you are using a MySQL server for a benchmark test that used to run without binary logs.
ca-key.pem ca.pem client-cert.pem client-key.pem private_key.pem public_key.pem server-cert.pem server-key.pem
Now the MySQL generates all the certificates needed to run connections securely. This will greatly simplify your task when setting up a new instance.
This was completely unexpected! The
mysql database has now its own tablespace. This is probably due to the new Data Dictionary, which is implemented in InnoDB. You will notice that all the InnoDB tables in MySQL use this tablespace, not only dictionary tables. This will help keeping administrative data separate from operational data in the rest of the server.
The undo logs have now their own tablespace by default.
There are a lot of changes in global variables. Here’s the list of what will impact your work when you use MySQL 8.0 for the first time:
character_set_client utf8mb4 character_set_connection utf8mb4 character_set_database utf8mb4 character_set_results utf8mb4 character_set_server utf8mb4
All character sets are now
utf8mb4. In MySQL 5.7, the default values are a mix of
This is huge. Using this plugin, passwords are stored in a different way, which guarantees more security, but will probably break several workflows among the users. The bad thing about this change implementation is that this password format contains characters that don’t display well on screen, and you can see garbled output when inspecting the “user” table.
Loading local files is now prevented by default. If you have a workflow that requires such operations, you need to enable it.
log_bin ON log_slave_updates ON
We’ve seen from an inspection of the local directory that binary logging is enabled by default. But also very important is that
log_slave_update is enabled. This is important to have slaves ready to replace a master, but will severely affect performance in those scenarios where some slaves were supposed to run without that feature.
master_info_repository TABLE relay_log_info_repository TABLE
Also impacting performance is the setting for replication repositories, which are now on TABLE by default. This is something that should have happened already in MySQL 5.6 and was long overdue.
Surprisingly, something that DOES NOT get enabled by default is Global Transaction Identifiers (GTID). This is also a legacy from decisions taken in MySQL 5.6. Due to the GTID implementation, enabling them by default is not possible when upgrading from a previous version. With new data in a fresh installation, it is safe to enable GTID from the start.
There are two new users when the server is created:
mysql.session also exists in 5.7, but it was introduced long after GA, so it still qualifies as a novelty.
Then, when the server starts, you get a grand total of 4 users (
mysql.sys are inherited from MySQL 5.7.)
When MySQL initializes, i.e. when the server starts for the first time and creates the database, you will notice some slowness, compared to previous versions. This is in part due to the data dictionary, which needs to create and fill 30 tables, but it is not a big deal in terms of performance. In some systems, though, the slowness is so acute that you start worrying about the server being stuck.
I noticed this problem in my Intel NUC running with SSD storage. In this box, the initialization time took a serious hit:
There is no mistype. The initialization for 8.0.4 lasts 6 times more than 5.7.
This doesn’t happen everywhere. On a Mac laptop running on SSD the same operation takes almost 9 seconds, while 5.7 deploys in less than 5. It is still a substantial difference, one that has totally disrupted my regular operations in the NUC. I investigated the matter, and I found the reason. In 8.0, we have a new (hidden) table in the data dictionary, called
st_spatial_reference_systems. Up to MySQL 8.0.3, this table was filled using a single transaction containing roughly 5,000
REPLACE INTO statements. It is a lot of data, but it happens quickly. For comparison, in MySQL 8.0.3 the initialization is only 2 seconds slower than 5.7.
The reason for the slowness in 8.0.4 is that there was a new command added to the syntax: CREATE SPATIAL REFERENCE SYSTEM, which is now used 5,000 times to fill the table that was previously filled with a single transaction. I don’t know why someone in the MySQL team thought that changing this operation that is hidden from users was a good idea. The data is contained in the server itself and it goes into a data dictionary table, also not visible to users. I am sure I can find at least two methods to load the data faster. I was told that this glitch will be fixed in the next release. I’m waiting.
Speaking of initialization, the
mysql_install_db script has been removed for good in 8.0. If you are still using it instead of the recommended
mysqld --initialize, you should adapt asap.