Install and Configure MySQL 8 for AIQ on Windows

Installing

Install MySQLand is not required on for Test Node instances.

MySQL Server Requirements

SinceConfigure MySQL 8 isfor requiresAIQ moreon resourcesWindows than

Use this guide when preparing a Windows-hosted MySQL 8 database for an AIQ environment. Confirm the supported database version, sizing, and security requirements before installing or changing an existing database.

Preparation

• Confirm the target AIQ version 5.7,and issupported isMySQL necessary8 configuration.
• Use a dedicated database host or instance when possible.
• Plan storage, backup location, retention, and recovery requirements.
• Create service and database accounts with only the permissions required by AIQ.
• Document port, hostname, schema, account, and maintenance-window details.

Validation workflow

1. Install MySQL 8 using the approved Windows installer and configuration profile.
2. Apply the required character set, time zone, networking, and service settings.
3. Create the AIQ database/schema and account according to assignthe installation plan.
4. Test connectivity from the AIQ host before starting AIQ services.
5. Run a bigger instance type. Following are the minimumlogin and recommendedsmall 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

   Performance or load testing requires a stronger database as there are performance related processes executed by store procedures. For this case this is a recommended setup:

   • vCPU - 8
   • Memory (GB) - 32
   • Network Performance (Gbps) - Up to 5

   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 (ourexecution smoke test RDSafter reached a maximum of 150 connections by the time this has been written), this parameterAIQ 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.connected. 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

ItMaintenance isnote: recommendedBack 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 createup 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 creatingupgrades, theconfiguration actualchanges, database.or Refercleanup to the correct values in the Parameter Group info above.

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

 

Creating RDS MySQL Database

1. On the Console Home page, select the region in which you need the database.
   
2. Select RDS.
   
3. Click Create Database.
   
4. Select Standard Create and MySQL.
   
5. Select MySQL version 8, Production, and Single DB Instance.
   
6. Enter your credentials.
   
7. Select the DB Instance class, the Storage Type, and the size of the Allocated Storage.
   
8. Select Enable storage autoscaling and the VPC.
   
9. Choose the DB Subnet group, Public Access setting and VPC Security Group.
   
10. Choose your Database authentication method.
    
11. Select your monitoring preferences.
    
12. Verify the settings are correct and click Create Database.
    
13. Create a new parameter group for AIQ MySQL databases.
14. After creating the database.
    
    
15. Check connection to the database.
16. Install the MySQL client.
    Syntax example: mysql -h <example-endpoint-db.rds.amazonaws.com> -P 3306 -u <user_name> -p <push enter>
17. Run the MySQLInitial.sql script using the admin/root user.
18. 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 Serviceoperations.

 

---

Last updated: October 02,8, 20242026

If you have any feedback on this topic, please contact us at documentation@appvance.ai