MySQL Deployment in AWS with RDS

This article describes how to deploy MI application in AWS with RDS for MySQL.  

Table of contents:

  1. Create RDS Security Group and Allow Access to the DB
  2. Create RDS DB Instance
    1. Create New Database
    2. Provide DB Settings
    3. Set DB Instance Class
    4. Configure Storage Options
    5. Set DB VPC
    6. Set DB Authentication
    7. Additional Configuration
    8. Configure Backup
    9. Finish DB Creation
  3. Configure RDS Parameter Group Based on MI Recommendations
  4. Provide Parameters' Values
    1. Access Parameter Group
    2. Edit Parameters
  5. Assign Parameter Group to RDS Instance
  6. Reboot DB Instance
  7. Install MI Using RDS

1. Create RDS Security Group and Allow Access to the DB

Log in to your AWS account > Services > VPC

  1. Select Security Groups under the Security section on the left part of the Console
  2. [Create Security Group]
  3. Fill out the Basic details section, enter the Security group name and Description, leave other fields as-is
    • Leave the Outbound section as is and click [Create Security Group] button at the bottom of the page

2. Create RDS DB Instance

2.1. Create New Database

Access Services > RDS

  1. [Create Database]
  2. Choose Standard Create method
  3. Under the Engine Options section select Engine type as MySQL and Edition as 8.0.20
  4. Deployment options: select Multi-AZ DB instance

2.2. Provide DB Settings

2.3. Set DB Instance Class

Set DB instance class to Standard classes (includes m classes)

2.4. Configure Storage Options

Under the Storage section select the appropriate storage size and whether to auto-scale it or not and scaling limits

2.5. Set DB VPC

Under the Connectivity section select your VPC, set Allow Public Access to 'Yes'. For the VPC Security Group option set Choose Existing and select the Security Group that you have previously created.

2.6. Set DB Authentication

2.7. Additional Configuration

Expand the Additional Configuration section and scroll down to the Maintenance options. Deactivate Enable auto minor version upgrade checkbox. You can also set the desired Maintenance Window here.

2.8. Configure Backup

An important part of every DB setup is backup configuration. By default, automated backups are enabled with a retention period of 7 days. You can change the retention period under Backup options in the same section, as well as set the desired backup window.

2.9. Finish DB Creation

Once you have finished providing DB settings, click [Create Database]. The RDS list is opened, and the new database is shown as Creating.

3. Configure RDS Parameter Group Based on MI Recommendations

Access RDS Console > Parameter Groups > [Create Group]

Fill the form, select 'mysql-8.0' from Parameter group family, DB Parameter Group from Type drop-down. Enter Group name and Description of your choice. Click [Create].

4. Provide Parameters' Values

4.1. Access Parameter Group

RDS Parameters list will open up, click on the newly created group name to open it with recommended parameters for different instances sizes based on RAM available.

4.2. Edit Parameters

Click [Edit Parameters], locate the parameters listed in the table below and for each parameter enter the recommended values based on your instance's RAM size. [Save] after modifying each parameter.

KeyRAM Amount
16 GB32 GB64 GB
sql_mode
NO_ENGINE_SUBSTITUTIONNO_ENGINE_SUBSTITUTION
NO_ENGINE_SUBSTITUTION
log_bin_trust_function_creators
111
performance_schema
OFFOFFOFF
max_heap_table_size
16106127361610612736
1610612736
key_buffer_size
34217728536870912536870912
join_buffer_size
83886081677721616777216
sort_buffer_size
838860883886088388608
read_buffer_size
8388608838860816777216
read_rnd_buffer_size
419430483886088388608
table_definition_cache
81921638416384
table_open_cache
1638416384
16384
table_open_cache_instances
111
tmp_table_size
536870912536870912
536870912
innodb_buffer_pool_size
42949672961073741824010737418240
innodb_file_per_table
111
innodb_adaptive_hash_index
000
optimizer_search_depth
121212
thread_stack
10485761048576
1048576
connect_timeout
101010
wait_timeout
2880028800
28800
innodb_open_files
20482048
2048
max_connections
400400400
innodb_lock_wait_timeout
600600600
innodb_flush_method
O_DIRECTO_DIRECT
O_DIRECT
innodb_flush_log_at_trx_commit
222
innodb_purge_threads
111
max_allowed_packet
107341824107341824
107341824
gtid_mode
000
low_priority_updates
111
require_secure_transport
OFFOFFOFF

NOTE: See Fine-Tuning MySQL Parameters for more details on MySQL parameters.

5. Assign Parameter Group to RDS Instance

Access RDS instances list > Your RDS Instance > [Modify] > Additional Configuration  

Select the newly created parameter group from the DB parameter group drop-down, then [Continue] at the bottom of the page.

6. Reboot DB Instance

  1. In the Scheduling of Modifications section, select Apply Immediately
  2. [Modify DB Instance]
  3. Wait for the instance state to return to Available
  4. To apply the new parameters to the DB engine, reboot your RDS instance:
    • In the DB Instances list panel, select Reboot from the Actions menu at the top-right corner.

7. Install MI Using RDS

Navigate to your installation folder of Metric Insights application and issue the following command:

./installer.py --components web,seed,data-analyzer,dataprocessor,monitoring --db-hostname <Your RDS Hostname> --db-user admin --db-password <Your RDS Password>

You can find your DB hostname via RDS > Databases > Your RDS Instance > Connectivity & security > Endpoint & port > Endpoint

If your MI installation is using a timezone other than RDS do, then the --mysql-timezone option should be passed as well with the MI Installation timezone specified as its value.