MySQL/HeatWave Audit Archive and Dump to Object Storage

Auditing is one of the key concerns with Security & Compliance for many organizations.   

This article is written to share a tutorial how to do audit log archiving by reading the audit log.  The audit archiving process is to read the audit log using audit_log_read function provided to access to the audit.   The data once is archived to Table "audit_archive.audit_data" and the table is renamed to "audit_data_<timestamp>" accordingly.  The table is dumped to OCI Object Storage bucket using mysql shell utility.  The tutorial is for demo purpose only.   

MySQL Enterprise Audit is the feature allowing audit to be implemented.   For managed service of "MySQL", namely HeatWave Service on Oracle Cloud Infrastructure,  Enterprise Audit is installed.   If running MySQL Enterprise Edition on your compute VM, please refer to the documentation on how to install MySQL Enterprise Audit 

Ref : https://dev.mysql.com/doc/refman/8.4/en/audit-log-installation.html

The current practice with default AUDIT feature in HeatWave is to generate the AUDIT record in JSON format.  Here below is  system variables "audit%" example when HeatWave MySQL is provisioned.

mysql> show variables like 'audit%';

+--------------------------------------+---------------------+

| Variable_name                        | Value               |

+--------------------------------------+---------------------+

| audit_log_buffer_size                | 10485760            |

| audit_log_compression                | GZIP                |

| audit_log_connection_policy          | ALL                 |

| audit_log_current_session            | OFF                 |

| audit_log_database                   | mysql_audit         |

| audit_log_disable                    | OFF                 |

| audit_log_encryption                 | NONE                |

| audit_log_exclude_accounts           |                     |

| audit_log_file                       | /db/audit/audit.log |

| audit_log_filter_id                  | 1                   |

| audit_log_flush                      | OFF                 |

| audit_log_flush_interval_seconds     | 60                  |

| audit_log_format                     | JSON                |

| audit_log_format_unix_timestamp      | ON                  |

| audit_log_include_accounts           |                     |

| audit_log_max_size                   | 5368709120          |

| audit_log_password_history_keep_days | 0                   |

| audit_log_policy                     | ALL                 |

| audit_log_prune_seconds              | 604800              |

| audit_log_read_buffer_size           | 32768               |

| audit_log_rotate_on_size             | 52428800            |

| audit_log_statement_policy           | ALL                 |

| audit_log_strategy                   | ASYNCHRONOUS        |

+--------------------------------------+---------------------+

23 rows in set (0.09 sec)

Audit Log Pruning

Audit Log Pruning is enabled for JSON format on OCI HeatWave Service. 

For details about Audit Log Pruning, please refer to documentation : https://dev.mysql.com/doc/refman/8.4/en/audit-log-logging-configuration.html#audit-log-pruning

The audit_log_max_size is configured as 5368709120 (~5GB).  The audit_log_prune_seconds is configured as 604800 (7 days).  

Based on the documentation above, 

"Nonzero values of audit_log_max_size take precedence over nonzero values of audit_log_prune_seconds. "

The audit_log_max_size as 5GB takes precedence for pruning action.   The "audit_log_rotate_on_size" is about 50MB.  

Demo Environment 

- Oracle Cloud Infrastructure (OCI) HeatWave Service 

- Version : 9.0.1

- Compute VM : Oracle Linux 8

    - MySQL Shell version : 9.0.1

    - oci cli installed and configured 

        https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm#InstallingCLI__oraclelinux8

Creating Table Structure for AUDIT ARCHVE

The archived audit records are stored in DB table(s).   

Table	Description
audit_config	To keep track the last record  of the timestamp and id for audit log identifier for each server_uuid
audit_data_template	The Table Structure template for audit_data
audit_data	The Table to store the running archived records.
audit_data_<timestamp>	Each time archiving is executed, it retrieves audit records from timestamp registerd in audit_config table until all records are retrieved.  Thereafter it renames the table to the audit_data_<timestamp>.

MySQL Keyring Component Installation for TDE

MySQL Plugin has been extensively used with MySQL.   It is being evolved into COMPONENT deployment.   This article is written to share the steps with MySQL Keyring Component Installation.   

MySQL Enterprise Edition includes encrypted file component for Keyring.    This provides a more secure way to store the master key with TDE.

Installation of component with keyring has to be static rather than running SQL command "INSTALL COMPONENT".   There are 2 scopes with component installation.

Global vs Local

With Global component installation, the configuration is located with the MySQL installation folder.

With Local component installation, the global configuration is referenced to locate the local configuration from the Datadir.

MySQL - determine transaction size

Running MySQL InnoDB Cluster / Group Replication, there is a transaction size limit (https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_transaction_size_limit)

The default value of "group_replication_transaction_size_limit"  is about 143 MB (150000000 bytes)

How can we determine the size of a batch job (single transaction) in MySQL?

Setting a small value with variable group_replication_transaction_size_limit and running the transaction produces the error message in error log. 

For example :

Assuming there is primary node on port 3310

mysql -uroot -p -h127.0.0.1 -P3310  -e "  set global group_replication_transaction_size_limit=1024;"

mysql -uroot -p -h127.0.0.1 -P3310 << EOL

create database if not exists demox;

create table demox.mytable (f1 int not null auto_increment primary key, f2 varchar(1024));

insert into demox.mytable (f2) values (repeat('a',1024));

EOL

Checking the error log file, we may see the message like 

2023-03-21T06:40:40.997931Z 1133317 [ERROR] [MY-011608] [Repl] Plugin group_replication reported: 'Error on session 1133317. Transaction of size 1833 exceeds specified limit 1024. To increase the limit please adjust group_replication_transaction_size_limit option.'

2023-03-21T06:40:40.997991Z 1133317 [ERROR] [MY-010207] [Repl] Run function 'before_commit' in plugin 'group_replication' failed

By adjusting the transaction size limit, the size is logged in the error log file.

 Note: This is not a good practice for production usage.  Adjusting the global variable  'group_replication_transaction_size_limit' affects all transactions.     

Secured MySQL InnoDB Cluster with Certificate creation using OpenSSL

This is a demo tutorial to show how we can create InnoDB Cluster with newly installed Certificate and having X509 certificate verification via MySQL Router connection.   

Recorded Video

The full process is recorded on Youtube - showing creating InnoDB Cluster with newly installed CA, Server Certificates.  The Router creation is configured with SSL Server certificate from the same CA certificate across Server nodes.   The creation of User (create user my509user identified by '....' require X509) using X509 certificate PASSTHROUGH verification via Router connection.

https://www.youtube.com/watch?v=w1xgpjw0VTw

Environment

The following environment was tested 

Oracle Linux Server release 8.6

MySQL Server 8.0.31

MySQL Shell 8.0.31

MySQL Router 8.0.31

Github Script

The github script provides the steps to guide thru Installation of 3 nodes 

https://github.com/ivanxma/mylab/tree/main/13-InnoDBCluster/99-SSL-cert-IC

To configure the node1/node2/node3 hostname under ./comm.sh   (Change the hostname based on your environment)

```

export HOST1=workshop20

export HOST2=workshop22

export HOST3=workshop23

```

Background

The creation of InnoDB Cluster creates Internal User (mysql_innodb_cluster_<server_id>@'%') 

Here is an example :

mysql> select user,host from mysql.user;

+--------------------------+-------------+

| user                     | host        |

+--------------------------+-------------+

| gradmin                  | %           |

| mysql_innodb_cluster_101 | %           |

| mysql_innodb_cluster_201 | %           |

| mysql_innodb_cluster_301 | %           |

| mysql.infoschema         | localhost   |

| mysql.session            | localhost   |

| mysql.sys                | localhost   |

| root                     | localhost   |

+--------------------------+-------------+

8 rows in set (0.00 sec)

Note : the RENAME USER does not work.

 (e.g.  mysql > rename user mysql_innodb_cluster_101@'%' to mysql_innodb_cluster_101@'10.0.%' )  

"%" as host may can be restricted to specific IP subnet via replicationAllowedHost option with MySQL Shell Admin API.   

https://dev.mysql.com/doc/mysql-shell/8.0/en/create-cluster.html#mysql-innodb-cluster-replicationallowedhost

As an example to restrict user creation with Host from subnet 192.0.2.0/24:

mysql-js> dba.createCluster('testCluster', {replicationAllowedHost:'192.0.2.0/24'})

Group Replication with communication stack "MYSQL" goes through the standard MySQL port (for example 3306.  The encrypted communication / recovery channel is established with SSL certificate.    

https://dev.mysql.com/doc/refman/8.0/en/group-replication-secure-socket-layer-support-ssl.html

group_replication_ssl_mode  is configured as REQUIRED to ensure secured SSL between nodes.

REQUIRED

Establish a secure connection if the server supports secure connections.

group_replication_ssl_mode can also be configured as VERIFY_CA to ensure certificate being used and verified.

VERIFY_CA

Like REQUIRED, but additionally verify the server TLS certificate against the configured Certificate Authority (CA) certificates.

The following sections describe the steps to setup the VERIFY_CA option and creation of certificates using OpenSSL command.

1. On each node to initialize MySQL Data Directory [ Script ] 

2. Create CA certificate on Node1 and using the same CA certificate to create server-cert.pem for each server 

Refer to CA creation [ Script ]

Refer to Server certificate creation [ Script ] 

3. Startup the server with unique $SERVER_ID on each HOST [ Script ] 

4.On each host, configure Group Replication Admin User [ Script ] 

5. Create 3 nodes Innodb Cluster with MySQL Shell with specific options to secure the SSL connection

 [ Create Cluster Script ] [ Add Instances Script ]

6. Bootstrap MySQL Router [ Script ]

7. Create Router Certificate with same CA certificate and Configure the mysqlrouter.conf  

[ Create Router Script ] [ Config PASSTHROUGH ]

8. Start Router [ Script ] 

9 Finally, creating a user with X509 and Login via MySQL Router

    Login to Primary Server with 'root'

mysql > create user my509user@'%' identified by 'my509pass' REQUIRE X509;

     Alternatively, user creation can follow more constraint with 

REQUIRE SUBJECT "/...."

 REQUIRE ISSUER "/..."

    For example (Check with the client certificate for the ISSUER or SUBJECT content  [ openssl x509 -text -in <certificate file> ] )

    example 1 : mysql > create user my509user@'%' identified by 'my509pass' REQUIRE ISSUER "/O=MySQL/CN=MySQL" ;

    example 2 : mysql > create user my509user@'%' identified by 'my509pass' REQUIRE SUBJECT "/CN=hostname";

    example 3 : mysql > create user my509user@'%' identified by 'my509pass' REQUIRE SUBJECT "/CN=hostname" AND ISSUER "/O=MySQL/CN=MySQL;

 

    Login using certificate via the Router  For example : (Given that the 'client-key.pem' 'client-cert.pem' is created on each host)

mysql -umy509user -pmy509pass -h127.0.0.1 -P6446 --ssl-mode=VERIFY_CA --ssl-ca=~/data/3310/ca.pem --ssl-cert=~/data/3310/client-cert.pem --ssl-key=~/data/3310/client-key.pem 

References

https://github.com/ivanxma/mylab/tree/main/13-InnoDBCluster/99-SSL-cert-IC

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster-setting-options.html

https://dev.mysql.com/doc/mysql-shell/8.0/en/create-cluster.html#mysql-innodb-cluster-replicationallowedhost

MySQL Performance Schema to identify SQL Statements which the execution duration exceeds average timing by 30%

 

Statement Digest in MySQL 

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-digests.html

The parser is also used by the STATEMENT_DIGEST_TEXT() and STATEMENT_DIGEST() functions, which applications can call to compute a normalized statement digest and a digest hash value, respectively, from an SQL statement.

events_statement_summary_by_digest in Performance Schema contains information

+-----------------------------+-----------------+------+-----+---------+-------+

| Field                       | Type            | Null | Key | Default | Extra |

+-----------------------------+-----------------+------+-----+---------+-------+

| SCHEMA_NAME                 | varchar(64)     | YES  | MUL | NULL    |       |

| DIGEST                      | varchar(64)     | YES  |     | NULL    |       |

| DIGEST_TEXT                 | longtext        | YES  |     | NULL    |       |

| COUNT_STAR                  | bigint unsigned | NO   |     | NULL    |       |

| SUM_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| MIN_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| AVG_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| MAX_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| SUM_LOCK_TIME               | bigint unsigned | NO   |     | NULL    |       |

| SUM_ERRORS                  | bigint unsigned | NO   |     | NULL    |       |

| SUM_WARNINGS                | bigint unsigned | NO   |     | NULL    |       |

| SUM_ROWS_AFFECTED           | bigint unsigned | NO   |     | NULL    |       |

| SUM_ROWS_SENT               | bigint unsigned | NO   |     | NULL    |       |

| SUM_ROWS_EXAMINED           | bigint unsigned | NO   |     | NULL    |       |

| SUM_CREATED_TMP_DISK_TABLES | bigint unsigned | NO   |     | NULL    |       |

| SUM_CREATED_TMP_TABLES      | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_FULL_JOIN        | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_FULL_RANGE_JOIN  | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_RANGE            | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_RANGE_CHECK      | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_SCAN             | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_MERGE_PASSES       | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_RANGE              | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_ROWS               | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_SCAN               | bigint unsigned | NO   |     | NULL    |       |

| SUM_NO_INDEX_USED           | bigint unsigned | NO   |     | NULL    |       |

| SUM_NO_GOOD_INDEX_USED      | bigint unsigned | NO   |     | NULL    |       |

| SUM_CPU_TIME                | bigint unsigned | NO   |     | NULL    |       |

| COUNT_SECONDARY             | bigint unsigned | NO   |     | NULL    |       |

| FIRST_SEEN                  | timestamp(6)    | NO   |     | NULL    |       |

| LAST_SEEN                   | timestamp(6)    | NO   |     | NULL    |       |

| QUANTILE_95                 | bigint unsigned | NO   |     | NULL    |       |

| QUANTILE_99                 | bigint unsigned | NO   |     | NULL    |       |

| QUANTILE_999                | bigint unsigned | NO   |     | NULL    |       |

| QUERY_SAMPLE_TEXT           | longtext        | YES  |     | NULL    |       |

| QUERY_SAMPLE_SEEN           | timestamp(6)    | NO   |     | NULL    |       |

| QUERY_SAMPLE_TIMER_WAIT     | bigint unsigned | NO   |     | NULL    |       |

+-----------------------------+-----------------+------+-----+---------+-------+

The events_statements_history_long[events_statement_current / events_statement_history] contains information about a SQL statement and Digest together with the execution time.  With referencing the events_statement_history_long, we can track the execution timing compared to the average execution time from table "events_statements_summary_by_digest".

1. To enable 'performance_schema" to collect events_statements_history_long, we need to enable the consumer (for the *history_long).  The default value is OFF which means the events_statements_history_long is empty without any data.   Enabling the events_statements_history_long allows more SQL statements to be captured and compared with the average execution time.

mysql > update setup_consumers set enabled='YES' where name = 'events_statements_history_long';

2. Retrieving the list of SQL Statements where the digest is the same and the execution time is having 30% over the average timing.

mysql > select a.sql_text,  a.digest, (a.timer_end - a.timer_start) duration, b.avg_timer_wait 

          > from events_statements_history_long a, events_statements_summary_by_digest b 

          > where a.digest = b.digest and b.avg_timer_wait > (a.timer_end - a.timer_start) *1.3

Performance schema / sys schema contains valuable information which we can use them to track / tune our system.  It can be the SQL.  It can be storage or files.  

You can share more with others about how you can reuse the performance_schema tables to improve your application.

Enjoy reading!

China Encryption（国密算法）with MySQL

China Encryption Standards 

There are SM1,SM2, SM3, SM4, SM7, SM9, ...etc with China encryption standards.  With international encryption standards, we can leverage encryption libraries from publicly available source(s). For example, one of the most common and popular use encryption library is OpenSSL. 

This tutorial is written to provide steps with one of the "SSL" library having SMx (China Encryption Library) namely BabaSSL with MySQL.  It does not serve as a purpose of the only way adopting China Encryption Standards with MySQL. Applying compatible / Latest OpenSSL library from China Encryption vendor(s)/source(s) may possibly work in similar way as documented in this tutorial.

Environment (for the tutorial sharing) 

1. Compute Resource (VM) 

2. Operating System - Compute VM is provisioned with Oracle Linux 8 

3. MySQL community with 8.0.xis installed via public yum repository 

4. BabaSSL 8.3 [ BabaSSL 8.3.2-dev ] is used. It is based on OpenSSL 1.1.1h dated 22 Sep 2020

Introduction 

MySQL leverages OpenSSL library to provide ciphers with TLS encryption over communication channel.  To allow switching the OpenSSL library with China Standards and MySQL can dynamically use the underlying library feature to enforce the communication using China Encryption Standard.

Steps 

1. Provision VM and OS/packages update 

2. Download BabaSSL 8.3.2 stable source build for compilation 

3. Compile BabaSSL and Install BabaSSL 

4. Install MySQL 8.0.30 (from yum repository) 

5. Configure TLS with SMx for MySQL 

6. Change system service for mysqld with alternate BabaSSL library path 

7. Reload and restart mysqld service 

8. MySQL (mysql) client with BabaSSL library over TLS with SMx connection 

Provision VM and OS / Packages update

Compute Instance (VM) is provisioned with Oracle Linux 8.  Once the VM is provisioned and it is ready for connection, login to the shell terminal and apply update and package updates

Login from terminal as  

```

ssh -i <privatekey>  opc@<public IP> 

# sudo yum update

# sudo yum install wget

```

Download BabaSSL 8.3.2 stable source build for compilation 

To download the 8.3 stable source zip file, execute the following commands and extract the zip file 

```

# wget https://github.com/Tongsuo-Project/Tongsuo/archive/refs/heads/8.3-stable.zip

# unzip 8.3-stable.zip

```

To compile the source, change directory to the unzipped folder 'Tongsuo-8.3-stable" and execute commands as follows :

```

# cd Tongsuo-8.3-stable

# mkdir bld

# ../config

# make

```

The installation will put the BabaSSL into /usr/local/bin and /usr/local/lib64 and corresponding default installation path(s).   

Note : The default installation does not replace any standard OS system own OpenSSL but it puts into /usr/local as alternate installation.

```

# sudo make install

```

Finally, make change to /etc/profile and append the following

```

# export LD_LIBRARY_PATH=/usr/local/lib64:$LD_LIBRARY_PATH

```

Exit the terminal and re-login to the VM 

Launch a SSH connection to the Compute VM with new library path appended (from updated /etc/profile).   

To check BabaSSL and SMx ciphers, execute the following commands and validate if BabaSSL is installed.

```

# openssl version

# openssl -v ciphers|grep SM

```

Output should be displayed as follows :

MySQL Router Restful API

MySQL Router with InnoDB Cluster allows transparent routing access for application to backend MySQL Servers.  

Restful API (Http access) feature was added to MySQL Router since 8.0.17.

This article is written to provide a tutorial with Router Restful API.   It is tested with MySQL 8.0.29 release.