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:
1 2 3 4 5 6 7 8 9 10 11 | 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.
1 2 | mysql > RESET PERSIST; 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.
1 2 | $ pt-config-diff ./my.sandbox.cnf data/mysqld-auto.cnf 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.
