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.
Data Directory
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) sysThese files are also present in 5.7.
Files that are new in 8.0
binlog.000001
binlog.indexlog-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.pemNow the MySQL generates all the certificates needed to run connections securely. This will greatly simplify your task when setting up a new instance.
mysql.ibdThis 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.
undo_001
undo_002The undo logs have now their own tablespace by default.
Global variables
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 utf8mb4All character sets are now utf8mb4. In MySQL 5.7, the default values are a mix of utf8 and latin1.
default_authentication_plugin caching_sha2_passwordThis 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.
local_infile OFFLoading 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 ONWe'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 TABLEAlso 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.
Users
There are two new users when the server is created:
mysql.infoschema
mysql.session Theoretically, 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 (root and mysql.sys are inherited from MySQL 5.7.)
Mixed oddities
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:
| Version | time |
|---|---|
| 5.0.96 | 1.231s |
| 5.1.72 | 1.346s |
| 5.5.52 | 2.441s |
| 5.6.39 | 5.540s |
| 5.7.21 | 6.080s |
| 8.0.3 | 7.826s |
| 8.0.4 | 38.547s |
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.
This list is far from being exhaustive. I recommend reading What's new in MySQL 8 before upgrading.
If you are impatient, dbdeployer can help you test MySQL 8 quickly and safely.
