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": "[email protected]", "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 backup@localhost; +------------------------------------------------------------------------------+ | Grants for backup@localhost | +------------------------------------------------------------------------------+ | 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