Infrastructure at your Service

Elisa Usai

Changes that I like in the new MySQL 8.0.21

A new release of MySQL was out on July 13 (8.0.21).
Among all the changes, there are some that I have already tested and that I really appreciate.

Who stopped the MySQL server?

In previous releases, I can already see in the error log file who stopped the MySQL server if this was done through the shutdown statement.
In MySQL 8.0.20:

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
# tail -2 /u01/app/mysql/admin/mysqld2/log/mysqld2.err
2020-07-23T20:39:08.049064+02:00 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.20).
2020-07-23T20:39:09.796726+02:00 0 [System] [MY-010910] [Server] /u01/app/mysql/product/mysql-8.0.20/bin/mysqld: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.

In MySQL 8.0.21:

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
# tail -2 /u01/app/mysql/admin/mysqld1/log/mysqld1.err
2020-07-23T20:39:15.725223+02:00 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.21).
2020-07-23T20:39:17.029262+02:00 0 [System] [MY-010910] [Server] /u01/app/mysql/product/mysql-8.0.21/bin/mysqld: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.

But when the server was stopped through the systemctl command, this information was lost:

# systemctl stop mysqld
# tail -2 /u01/app/mysql/admin/mysqld2/log/mysqld2.err
2020-07-23T20:56:23.488468+02:00 0 [ERROR] [MY-010119] [Server] Aborting
2020-07-23T20:56:23.489010+02:00 0 [System] [MY-010910] [Server] /u01/app/mysql/product/mysql-8.0.20/bin/mysqld: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.

As of MySQL 8.0.21, the error log file traces who stopped the MySQL server in any case (except if you are using the kill -9 command):

# systemctl stop mysqld
# tail -2 /u01/app/mysql/admin/mysqld1/log/mysqld1.err
2020-07-23T21:01:00.731589+02:00 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.21).
2020-07-23T21:01:01.560078+02:00 0 [System] [MY-010910] [Server] /u01/app/mysql/product/mysql-8.0.21/bin/mysqld: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.

How can I enable/disable redo logging?

This is a nice but dangerous feature. Disable redo logging can be interesting when creating a new instance to avoid wasting time during redo log writes and doublewrite buffering operations. But otherwise don’t do that in your production environment!
Check if redo logging is enabled:

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+

Create an user account with permissions to execute these operations:

mysql> GRANT INNODB_REDO_LOG_ENABLE ON *.* to 'load'@'localhost';

Connect to the MySQL server with the load user and disable redo logging:

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+

You can now load your data into the system, then enable again redo logging:

mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+

What if we add attributes and comments to MySQL user accounts?

As of MySQL 8.0.21, we can add attributes as a JSON object and comments during the creation of MySQL user accounts.
A comment:

mysql> CREATE USER 'backup'@'localhost' identified by 'Supercal1frag1l1st1cexp1al1doc10us!' COMMENT 'This is the user account used to backup the MySQL server';
mysql> select User, Host, User_attributes from mysql.user
    -> where User='backup';
+--------+-----------+---------------------------------------------------------------------------------------+
| User   | Host      | User_attributes                                                                       |
+--------+-----------+---------------------------------------------------------------------------------------+
| backup | localhost | {"metadata": {"comment": "This is the user account used to backup the MySQL server"}} |
+--------+-----------+---------------------------------------------------------------------------------------+
mysql> select * from INFORMATION_SCHEMA.USER_ATTRIBUTES 
   -> where USER='backup';
+--------+-----------+-------------------------------------------------------------------------+
| USER   | HOST      | ATTRIBUTE                                                               |
+--------+-----------+-------------------------------------------------------------------------+
| backup | localhost | {"comment": "This is the user account used to backup the MySQL server"} |
+--------+-----------+-------------------------------------------------------------------------+

An attribute:

mysql> CREATE USER 'elisa'@'localhost' identified by 'CatchMe1fY0uCan!' ATTRIBUTE '{"LastName": "Usai", "FirstName": "Elisa", "Email": "elisa.usai\@dbi\-services\.com", "Department": "DBA Team"}';
mysql> select User, Host, User_attributes from mysql.user
    -> where User='elisa';
+-------+-----------+----------------------------------------------------------------------------------------------------------------------------+
| User  | Host      | User_attributes                                                                                                            |
+-------+-----------+----------------------------------------------------------------------------------------------------------------------------+
| elisa | localhost | {"metadata": {"Email": "[email protected]", "LastName": "Usai", "FirstName": "Elisa", "Department": "DBA Team"}} |
+-------+-----------+----------------------------------------------------------------------------------------------------------------------------+
mysql> select * from INFORMATION_SCHEMA.USER_ATTRIBUTES
   -> where User='elisa';
+-------+-----------+--------------------------------------------------------------------------------------------------------------+
| USER  | HOST      | ATTRIBUTE                                                                                                    |
+-------+-----------+--------------------------------------------------------------------------------------------------------------+
| elisa | localhost | {"Email": "[email protected]", "LastName": "Usai", "FirstName": "Elisa", "Department": "DBA Team"} |
+-------+-----------+--------------------------------------------------------------------------------------------------------------+
mysql> select user as User,
    -> host as Host,
    -> concat(attribute->>"$.LastName"," ",attribute->>"$.FirstName") as 'Name',
    -> attribute->>"$.Department" as Department,
    -> attribute->>"$.Email" as Email
    -> from INFORMATION_SCHEMA.USER_ATTRIBUTES
    -> where user='elisa';
+-------+-----------+------------+------------+-----------------------------+
| User  | Host      | Name       | Department | Email                       |
+-------+-----------+------------+------------+-----------------------------+
| elisa | localhost | Usai Elisa | DBA Team   | [email protected] |
+-------+-----------+------------+------------+-----------------------------+

Check your backups execution!

With the MySQL 8.0.21 release, your backup through mysqldump could fail with the following error:

 
# mysqldump --all-databases --user=backup --password > test.sql
Enter password:
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
mysql> show grants for [email protected];
+------------------------------------------------------------------------------+
| Grants for [email protected]                                                  |
+------------------------------------------------------------------------------+
| GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON *.* TO `backup`@`localhost` |
+------------------------------------------------------------------------------+

Why? Actually in the new release the INFORMATION_SCHEMA.FILES table requires by now the PROCESS privilege and this change has an impact on mysqldump operations:

 
mysql> grant PROCESS ON *.* TO `backup`@`localhost`;
Query OK, 0 rows affected (0.02 sec)
# mysqldump --all-databases --user=backup --password > test.sql
Enter password:

And as usual, stay tuned with MySQL! 🙂
by Elisa Usai

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Elisa Usai
Elisa Usai

Delivery Manager & Consultant