MySQL 8.0 Architecture and Enhancement Webinar: Q & A

April 11, 2019

Author

Lalit Choudhary

Insight for DBAs

MySQL

Webinars

Share this Post:

In this blog, I will provide answers to the Q & A for the MySQL 8.0 Architecture and Enhancement webinar.

First, I want to thank everybody for attending my April 9, 2019, webinar. The recording and slides are available here. Below is the list of your questions that I was unable to answer fully during the webinar.

Q: What kind of Encryption levels does MySQL 8.0 provide?

The MySQL data-at-rest encryption feature supports the Advanced Encryption Standard (AES) block-based encryption algorithm and aes_256_cbc encryption algorithm for data at rest is hard coded.

Q: At what frequency does the redo log buffer flush the changes to disk? When is the commit variable set to zero?

Here’s an overview:

innodb_flush_log_at_trx_commit variable can be configured to set flush frequency in MySQL 5.7 and 8.0 . Its default setting is 1.

innodb_flush_log_at_trx_commit =0 logs are written and flushed to disk once per second. In the event of a crash, you could lose transactions if the logs have not been flushed.

innodb_flush_log_at_trx_commit =1 is required for full ACID compliance. MySQL writes and flushes logs to disk at each transaction commit. Default Setting.

innodb_flush_log_at_trx_commit =2 logs are written after each transaction commit and flushed to disk once per second. In a crash, you could lose transactions where the logs have not been flushed.

Q: How do you reset persistent variables?

Using the RESET PERSIST command we can remove persisted global system variable settings from the mysqld-auto.cnf.

Example:

mysql > SET PERSIST binlog_encryption=ON;<br>Query OK, 0 rows affected (0.00 sec)<br><br>$ cat data/mysqld-auto.cnf<br>{ "Version" : 1 , "mysql_server" : { "mysql_server_static_options" : { "binlog_encryption" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1554896858076255 , "User" : "msandbox" , "Host" : "localhost" } } } } }<br><br>MySQL > RESET PERSIST binlog_encryption;<br>Query OK, 0 rows affected (0.00 sec)<br><br>$ cat data/mysqld-auto.cnf<br>{ "Version" : 1 , "mysql_server" : { } }

1

mysql > SET PERSIST binlog_encryption=ON; Query OK, 0 rows affected (0.00 sec) $ cat data/mysqld-auto.cnf { "Version" : 1 , "mysql_server" : { "mysql_server_static_options" : { "binlog_encryption" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1554896858076255 , "User" : "msandbox" , "Host" : "localhost" } } } } } MySQL > RESET PERSIST binlog_encryption; Query OK, 0 rows affected (0.00 sec) $ cat data/mysqld-auto.cnf { "Version" : 1 , "mysql_server" : { } }

To reset all persistent variables use following command.

mysql > RESET PERSIST;<br>Query OK, 0 rows affected (0.00 sec)

1	mysql > RESET PERSIST;<br>Query OK, 0 rows affected (0.00 sec)

Q: Does pt-config-diff work with these persistent vs. my.cnf variable settings?

No, it will not work. Due to config format differences in these files.

$ pt-config-diff ./my.sandbox.cnf data/mysqld-auto.cnf <br>Cannot auto-detect the MySQL config format at /home/lalit/perl5/bin/pt-config-diff line 3010.

1	$ pt-config-diff ./my.sandbox.cnf data/mysqld-auto.cnf <br>Cannot auto-detect the MySQL config format at /home/lalit/perl5/bin/pt-config-diff line 3010.

Q: Regarding the UNDO Tablespace, do we have any specific retention to follow?

This is not required because in MySQL 8.0, the innodb_undo_log_truncate variable is enabled by default. It will perform an automatic truncate operation on the UNDO tablespace. When undo tablespaces exceed the threshold value defined by innodb_max_undo_log_size (default value is 1024 MiB) they are marked for truncation.

Truncating the undo tablespace performs the following action:

Performs deactivation of undo tablespace

Truncation of undo tablespace

Reactivation of undo tablespaces

Q: Corrupted data on disk where the secondary indexes don’t match to the primary?

I’ll be happy to respond to this, but I’ll need a little bit more information… feel free to add more detail to the comments section and I’ll see if I can provide some insight.

Thanks for attending this webinar on MySQL 8.0 Architecture and Enhancement Webinar . You can find the slides and a recording here.