MySQL 8.0.4rc was just released as "Pre-General Availability Draft: 2018-03-19".
- https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
- https://dev.mysql.com/doc/relnotes/mysql/8.0/en/
I decided to take a quick peek and note my impressions here. Some of this is old news for many as this release has been talked about for awhile but I added my thoughts anyway..
First thing I noticed was a simple issue of using the updated mysql client. My older version was still in my path that resulted in
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded
So simple fix and make sure you are using the valid updated mysql client. Of course other options existed like changing the authentication plugin back to mysql_native_password but why bother, use the secure method. This is a very good enhancement for security so do not be shocked if you have some connection issues while you get your connections using this more secure method.
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 36Server version: 8.0.4-rc-log
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
So the first very cool enhancement…
mysql> show create table user\G*************************** 1. row *************************** Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'caching_sha2_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`password_last_changed` timestamp NULL DEFAULT NULL,
`password_lifetime` smallint(5) unsigned DEFAULT NULL,
`account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_role_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_role_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Password_reuse_history` smallint(5) unsigned DEFAULT NULL,
`Password_reuse_time` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`Host`,`User`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Users and global privileges'
1 row in set (0.00 sec)
Yep user table is InnoDB and has own TableSpace.
With the addition of the new Data Dictionary you will now notice Information_schema changes.
So as a simple example the Columns table historically has not been a view but that has now changed , along with many others as you can see via the url provided.
mysql> show create table COLUMNS \G*************************** 1. row *************************** View: COLUMNS
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost`
This appears to be done to help performance with the information_schema but removing the temporary table creations per queries into the information_schema.
Chapter 14 of the documentation goes into depth on this, the provided url below will help you find more information and future blog posts might touch more on this.
The previously mentioned Data Dictionary then also leads into the ability to have atomic Data Definition Language (DDL) statements or atomic DDL.
This is likely to trip up a few transactions if you do not review your queries before setting up replication to a new MySQL 8.0 instance. I say that because of how the handling of table maintenance could be impacted. If you write clean queries with "If Exists" it won’t be a big problem. Overall it is a more transaction based feature that protects your data and rollback options.
Resource management looks very interesting and I will have to take more time to focus on this as it is a new feature with MySQL 8.0. Overall you can assign groups and no longer have to set priority of query but let your grouping define how a query should behave and resources allotted to it.
mysql> select @@version;+------------+| @@version |
+------------+
| 5.7.16-log |
+------------+
1 row in set (0.00 sec)
mysql> desc INFORMATION_SCHEMA.RESOURCE_GROUPS;
ERROR 1109 (42S02): Unknown table 'RESOURCE_GROUPS' in information_schema
mysql> select @@version;
+--------------+
| @@version |
+--------------+
| 8.0.4-rc-log |
+--------------+
1 row in set (0.00 sec)
mysql> desc INFORMATION_SCHEMA.RESOURCE_GROUPS;
+------------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------+------+-----+---------+-------+
| RESOURCE_GROUP_NAME | varchar(64) | NO | | NULL | |
| RESOURCE_GROUP_TYPE | enum('SYSTEM','USER') | NO | | NULL | |
| RESOURCE_GROUP_ENABLED | tinyint(1) | NO | | NULL | |
| VCPU_IDS | blob | YES | | NULL | |
| THREAD_PRIORITY | int(11) | NO | | NULL | |
+------------------------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
More insight into your InnoDB buffer pool cache in regards to the indexes that are in it is now available.
mysql> desc INFORMATION_SCHEMA.INNODB_CACHED_INDEXES ;+----------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| SPACE_ID | int(11) unsigned | NO | | | |
| INDEX_ID | bigint(21) unsigned | NO | | | |
| N_CACHED_PAGES | bigint(21) unsigned | NO | | | |
+----------------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
If you are unsure what to set the InnoDB Buffer pool , log_sizes or flush method MySQL will set these for you now based on the available memory.
innodb_dedicated_server
[mysqld]innodb-dedicated-server=1mysql> select @@innodb_dedicated_server;
+---------------------------+
| @@innodb_dedicated_server |
+---------------------------+
| 1 |
+---------------------------+
This simple test set my innodb_buffer_pool_size to 6GB for example when the default is 128MB.
Numerous JSON additions have been made as well as regular expression changes. Both of which look promising.
The only replication enhancement per this release itself is that is now supports binary logging of partial updates to JSON documents using a compact binary format.
However overall many features are available ( you can read all about them here) , one of which (I wish my client had tomorrow ) is replication filers per channel.
My test instance already had binary logs enabled, but they are on by default now along with TABLE based versus file based master & slave info, ( big fan of having that transaction based by default )
Overall keep in mind this is just a first glance at this release and very high level thoughts on it, many other changes exist. Looking over other blog posts about this release as well as the manual and release notes will also help. Certainly download and review as it looks to be very promising for administration, security and replication points of view.