Installing and Configuring MySQL 8 Database (Windows)

Installing MySQL is not required on for Test Node instances.

MySQL Server Requirements

Since MySQL 8 is requires more resources than MySQL version 5.7, is is necessary to assign a bigger instance type. Following are the minimum and recommended resource configurations.

Do not use MySQL 8.1 as it has some changes not yet supported.

Minimum

vCPU - 4
Memory (GB) - 16
Network Performance (Gbps) - 1

Recommended MySQL Parameters

Setup the MySQL parameters (my.cnf or my.ini) with the below values.

If you are creating a AWS RDS, do not run the MySQLAppvance.sql script until you have created and applied the parameter group with below settings, otherwise you will get the following error:

"ERROR 1419 (HY000): You don't have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)"

After applying this settings you need to stop and start the RDS instance so this parameters options take effect.

Parameter Name	Description	Suggested Value	AWS RDS Only
bulk_insert_buffer_size	This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB, we are suggesting 512MB for massive transactional load.	536870912	No
connect_timeout	The number of seconds that the mysqld server waits for a connect packet before responding with `Bad handshake`. The default value is handle by AWS dynamically but we are suggesting 10 secs even more depending if there is latency between controller and test nodes and the RDS.	10	No
max_allowed_packet	The maximum size of one packet or any generated/intermediate string/media, or any parameter sent by the `mysql_stmt_send_long_data()` C API function. No value by default, 400MB is suggested.	419430400	No
max_connections	The maximum permitted number of simultaneous client connections, 500 connections would be more than enough even for the more demanding environments (our smoke test RDS reached a maximum of 150 connections by the time this has been written), this parameter is a TOTAL global connections watermark.	500 (Do not apply for RDS instances!)	No
max_user_connections	The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means “no limit”, but this behavior would be inconvenient since in the case of an external factor that affects the RDS connections; such that they'll increase without an error until the mysqld service hangs up, a value of 200 is recommended	200	No
myisam_use_mmap	It’s a switch to use memory mapping for reading and writing `MyISAM` tables, by default the value is 0 but must be 1.	1	No
read_buffer_size	Each thread that does a sequential scan for a `MyISAM` table allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value is rounded down to the nearest multiple of 4KB. This option is also used in the following context for all storage engines: For caching the indexes in a temporary file (not a temporary table), when sorting rows for `ORDER BY`. For bulk insert into partitions. For caching results of nested queries. A lot of sub-queries and not natural relations queries are performed against different and nested tables, so it’s needed to set an important amount of cache memory for this purpose 524288 (512MB)	524288	Yes
wait_timeout	This is really important, MySQL 8 kernel works totally different than 5.7 and it’s very sensitive to latency letting all the un-closed connections sleep for a lot of time before closing them by default causing a lot of accumulated connections using resources even when they are not in use anymore. Set this parameter to 300seconds as the maximum time to wait before destroying the connection	1080 (AIQ-6619)	Yes
validate-password	If password validation doesn’t need to performed just set this parameter to OFF, if it is needed let it remains as default (ON) just set passwords according to MySQL 8 security policies.
log_bin_trust_function_creators		1 (stop and start RDS for this to take effect)	Yes

Setting up AWS RDS instances

It is recommended that you install MySQL on a separate server machine such as an Amazon RDS.

You must have an AWS account. First create the parameter group and then create the database so you can specify the new parameter group when creating the database.

Create AIQ Parameter Group

You will need to create a parameter group for AIQ mysql database before creating the actual database. Refer to the correct values in the Parameter Group info above.

In Amazon RDS, select Parameter Group.
Enter the details for the parameter group. Click Create.
Click Edit Parameters to configure the parameters. See Recommended MySQL Parameters for the list of parameters and values.
Click Save Changes.

Creating RDS MySQL Database

On the Console Home page, select the region in which you need the database.
Select RDS.
Click Create Database.
Select Standard Create and MySQL.
Select MySQL version 8, Production, and Single DB Instance.
Enter your credentials.
Select the DB Instance class, the Storage Type, and the size of the Allocated Storage.
Select Enable storage autoscaling and the VPC.
Choose the DB Subnet group, Public Access setting and VPC Security Group.
Choose your Database authentication method.
Select your monitoring preferences.
Verify the settings are correct and click Create Database.
Create a new parameter group for AIQ MySQL databases.
After creating the database.
Check connection to the database.
Install the MySQL client.

Syntax example: mysql -h <example-endpoint-db.rds.amazonaws.com> -P 3306 -u <user_name> -p <push enter>
Run the MySQLInitial.sql script using the admin/root user.
Log out and log back into the mysql client but using the aiq/aiq user, then execute the following statement:

After you have installed and configured MySQL, proceed to the next step: Runing Appvance IQ on Windows as a Service