What’s new in MySQL 8.0 DMR

Introduction

MySQL 8.0 DMR is been released with a bunch of new, exciting features. Notable features are:

• Transactional data dictionary implemented as tables in InnoDB
• Roles as a named collection of privileges
• UTF8 as the default charset
• Invisible indexes, so we can toggle for debugging purposes to see whether an index is important or not for the system performance for certain workloads
• Persistent runtime configuration changes, so the changes are available when MySQL restarts

In this article, I'll about these features and walk through the installation of MySQL 8.0 DMR in Ubuntu 16.04 to see real world examples.

Summary of the new features

Let’s look at the new features of MySQL 8.0 in detail:

• Transactional Data Dictionary. Now MySQL 8.0 has a real data dictionary implemented as a set of SQL tables stored in the InnoDB engine. There is no need of FRM, TRG and PAR files. Now the information schema is implemented as views on data dictionary tables. The principal benefit is that DDL statements are atomic, so the reliability is improved.
• Roles: A role represents a collection of privileges. Now you don’t have to copy and paste a set of GRANT statements when creating new users or changing the privileges of existing one.
• UTF-8 as default charset. The goal is to change the default charset from latin1 to utf8mb4, and the default collation from latin1_swedish_ci to utf8mb4_800_ci_ai.  UTF8 is the dominating character encoding for the Web, so this feature will make life easier for MySQL users.
• Invisible Indexes/Index Toggling. This new feature enables you to make an index active or inactive. Invisible indexes allow easier debugging because you can disable an index without deleting it physically and later you can make it active again without the cost of building the index from scratch.

Notice that index toggling is available only for the read operations; on write operations the index is still maintained in the background.

What really happens under the hood on invisible indexes is that the optimizer does not consider the index when making the query execution plan.

• Persistent runtime configuration changes. With this new feature, you can execute “SET PERSIST innodb_buffer_pool_size = X” statement; instead of “SET GLOBAL innodb_buffer_pool_size = X;”, so the runtime changes are persisted during MySQL restart. Under the hood, these changes are saved in mysqld-auto.cnf file in the MySQL data directory.
• MySQL privilege tables are now stored in the InnoDB engine.
• New functions: UUID_TO_BIN() and BIN_TO_UUID(). Because a UUID such as “f9553cdc-149f-11e7-895a-0264606450cb” can then be stored in 16 bytes as VARBINARY(16) rather than 36 bytes as CHAR(36), these functions allow youto zip/unzip the UUID data when storing. This is a big improvement because we’re saving half of the capacity.

Demo

In this part of the article, let’s see MySQL 8.0 DMR in practice. Let’s install MySQL 8.0 on Ubuntu 16.04 (Xenial Xerus).

First step is to add the MySQL APT repository to your system's software repository list, as shown in the listing 01.

$ wget https://repo.mysql.com//mysql-apt-config_0.8.3-1_all.deb

Listing 01 Next install the downloaded release package and select version 8.0 when you’re asked to choose the versions of MySQL. We also need to update package information from the MySQL APT repository. The commands are shown in listing 02.

$ sudo dpkg -i mysql-apt-config_0.8.3-1_all.deb
$ sudo apt-get update

Listing 02

Finally, we have to install MySQL 8.0 with APT and check the status of the process, as shown in the listing 03.

$ sudo apt-get install mysql-server
$ sudo service mysql status

Listing 03

Now, let’s see some features with real world scenarios. Let's create a new schema, as shown in listing 04.

mysql> CREATE DATABASE sample_db;

Listing 04

Now let’s look at the data directory layout as shown in the listing 05, highlighted in bold. Notice a new .SDI file for each schema, as well as the traditional directory.

# sudo ls -alh /var/lib/mysql
total 121M
drwxr-x---  6 mysql mysql 4.0K Mar 29 13:45 .
drwxr-xr-x 43 root  root  4.0K Mar 29 13:29 ..
-rw-r-----  1 mysql mysql   56 Mar 29 13:29 auto.cnf
-rw-------  1 mysql mysql 1.7K Mar 29 13:29 ca-key.pem
-rw-r--r--  1 mysql mysql 1.1K Mar 29 13:29 ca.pem
-rw-r--r--  1 mysql mysql 1.1K Mar 29 13:29 client-cert.pem
-rw-------  1 mysql mysql 1.7K Mar 29 13:29 client-key.pem
-rw-r-----  1 mysql mysql 1.2K Mar 29 13:29 ib_buffer_pool
-rw-r-----  1 mysql mysql  12M Mar 29 13:45 ibdata1
-rw-r-----  1 mysql mysql  48M Mar 29 13:45 ib_logfile0
-rw-r-----  1 mysql mysql  48M Mar 29 13:29 ib_logfile1
-rw-r-----  1 mysql mysql  12M Mar 29 13:29 ibtmp1
drwxr-x---  2 mysql mysql 4.0K Mar 29 13:29 mysql
-rw-r-----  1 mysql mysql  225 Mar 29 13:29 performance_sche_3.SDI
drwxr-x---  2 mysql mysql 4.0K Mar 29 13:29 performance_schema
-rw-------  1 mysql mysql 1.7K Mar 29 13:29 private_key.pem
-rw-r--r--  1 mysql mysql  451 Mar 29 13:29 public_key.pem
drwxr-x---  2 mysql mysql 4.0K Mar 29 13:45 sample_db
-rw-r-----  1 mysql mysql  215 Mar 29 13:45 sample_db_5.SDI
-rw-r--r--  1 mysql mysql 1.1K Mar 29 13:29 server-cert.pem
-rw-------  1 mysql mysql 1.7K Mar 29 13:29 server-key.pem
drwxr-x---  2 mysql mysql 4.0K Mar 29 13:29 sys
-rw-r-----  1 mysql mysql  210 Mar 29 13:29 sys_4.SDI

Listing 05

Let's take a look at what's inside the SDI for the newly created schema sample_db. We can see that SDI files are really the metadata (description) of the database as shown in listing 06.

# sudo cat /var/lib/mysql/sample_db_5.SDI
{
    "sdi_version": 1,
    "dd_version": 1,
    "dd_object_type": "Schema",
    "dd_object": {
        "name": "sample_db",
        "default_collation_id": 8,
        "created": 0,
        "last_altered": 0
    }
}

Listing 06

It’s important to say that for tables not created on Innodb engine, we get a similar file with the metadata of the table. Let's create a new MyISAM table, as shown in listing 07.

mysql> CREATE TABLE sample_db.myisam1(
  id int primary key auto_increment,
  field01 int not null,
  field02 int,
  field03 varchar(200)
) engine=myisam;

Listing 07

Now let's check the data directory for this database again, as shown in listing 08. We can see the SDI file for this table highlighted in bold.

# sudo ls -alh /var/lib/mysql/sample_db
total 20K
drwxr-x--- 2 mysql mysql 4.0K Mar 29 13:59 .
drwxr-x--- 6 mysql mysql 4.0K Mar 29 13:45 ..
-rw-r----- 1 mysql mysql 5.8K Mar 29 13:59 myisam1_323.SDI
-rw-r----- 1 mysql mysql    0 Mar 29 13:59 myisam1.MYD
-rw-r----- 1 mysql mysql 1.0K Mar 29 13:59 myisam1.MYI

Listing 08

Now that I've shown what’s different in the physical layout of MySQL database, let me explain the role concept.

Let’s start by creating a new role named role1 and grant read privileges on the schema sample_db to this new role, as shown in the listing 09.

mysql> CREATE ROLE role1;
mysql> GRANT SELECT ON sample_db.* TO role1;

Listing 09 

We can check the privileges associated to the role role1, as shown in the listing 10.

mysql> SHOW GRANTS FOR role1;
+----------------------------------------------+
| Grants for role1@%                           |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `role1`@`%`            |
| GRANT SELECT ON `sample_db`.* TO `role1`@`%` |
+----------------------------------------------+

Listing 10

Let's create a new user and assign the role role1 to get the underlying read privileges and check the privileges for the user1 after that, as shown in listing 11.

mysql> GRANT role1 TO user1;
mysql> ALTER USER user1 DEFAULT ROLE role1;
mysql> SHOW GRANTS FOR user1;
+-----------------------------------+
| Grants for user1@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%` |
| GRANT `role1`@`%` TO `user1`@`%`  |
+-----------------------------------+
 
mysql> SHOW GRANTS FOR user1 USING role1;
+----------------------------------------------+
| Grants for user1@%                           |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%`            |
| GRANT SELECT ON `sample_db`.* TO `user1`@`%` |
| GRANT `role1`@`%` TO `user1`@`%`             |
+----------------------------------------------+

Listing 11

Notice that roles are really users with granted privileges. In fact, to implement roles MySQL 8.0 really assigns a user to another one. Let's see the list of users of our system, as shown in listing 12.

mysql> SELECT host, user FROM mysql.user;
+-----------+-----------+
| host      | user      |
+-----------+-----------+
| %         | role1     |
| %         | user1     |
| localhost | mysql.sys |
| localhost | root      |
+-----------+-----------+

Listing 12

If we connect to MySQL as user1 and try to insert a new record, we receive an error because the request is denied. But we can read the table. The statements are shown in listing 13.

# mysql -u user1 -p123456 sample_db
 
mysql> INSERT INTO sample_db.myisam1 values(1,1,1,'a');
ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'myisam1'
 
mysql> SELECT * FROM sample_db.myisam1;

Listing 13

And finally, let's talk about invisible indexes. Invisible indexes are a type of index that can be marked as visible or invisible, in order to being considered or not by the optimizer when creating the execution path.

In order to illustrate the concepts, let's create a sample table as shown in the listing 14.

mysql> CREATE TABLE sample_db.test01(
 id int primary key auto_increment,
 field01 int,
 key ndx_field01(field01)
)engine=innodb;

Listing 14

Let’s create a query and see the execution plan, as shown in the listing 15. In this case, the index ndx_field01 is used correctly.

mysql> explain select id from sample_db.test01 where field01=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test01
   partitions: NULL
         type: ref
possible_keys: ndx_field01
          key: ndx_field01
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index

Listing 15

Let's see the index definitions for this table. Notice the field Visible set to YES as shown in the listing 16.

mysql> show indexes from sample_db.test01\G
*************************** 1. row ***************************
        Table: test01
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: test01
   Non_unique: 1
     Key_name: ndx_field01
 Seq_in_index: 1
  Column_name: field01
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES

Listing 16

We can make the above index invisible and check again the index definitions, as shown in the listing 17

mysql> alter table sample_db.test01 alter index ndx_field01 invisible;
mysql> show indexes from sample_db.test01\G
*************************** 1. row ***************************
        Table: test01
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: test01
   Non_unique: 1
     Key_name: ndx_field01
 Seq_in_index: 1
  Column_name: field01
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: NO

Listing 17

Let's explain the query again to see that the index is not considered as part of the execution plan, as shown in the listing 18.

mysql> explain select id from sample_db.test01 where field01=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test01
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

Listing 18

Conclusion

In this article, I've explained the new features of MySQL 8.0. I’ve also illustrated some concepts through common real-world scenarios. Now you can start testing these new features yourself.