Setting up MYSQL RDS Instance from AWS

  1. Log in to your Amazon account with valid credentials

  2. Once logged in successfully, click Services and select RDS from the list as seen in the below screenshot

  3. Now click the "Launch a DB Instance" button

  4. In the next screen, select MySQL as the engine and Click the 'Select' Button

  5. Under Production, Select MySQL as shown below and click Next Step

    NOTE: Please make sure the instances that you create and the database are in the same region.

    • DB Instance Class: Select the DB instance class that allocates the computational, network, and memory capacity required by the planned workload of this DB instance

    • Multi-AZ Deployment can be marked as 'No'

    • Storage Type can be Provisioned IOPS (SSD) storage is suitable for I/O-intensive database workloads. Provides flexibility to provision I/O ranging from 1,000 to 30,000 IOPS

    Under Settings:

    • DB Instance Identifier: Specify a name that is unique for all DB instances owned by your AWS account in the current region. DB instance identifier is case insensitive, but stored as all lower-case, as in "mydbinstance"

    • Master Username: Specify an alphanumeric string that defines the login ID for the master user. You use the master user login to start defining all users, objects, and permissions in the databases of your DB instance. Master Username must start with a letter, as in "awsuser"

    • Master Password: Specify a string that defines the password for the master user. Master Password must be at least eight characters long, as in "mypassword"

    NOTE: The Above "Master Username" and "Master Password" will be used to connect to SQL Workbench which is explained in the later section.

  6. Click Next

  7. In the next step, you can select the DB instance in any available VPC or create a new one

  8. DB Security Group: This is Important

  9. Select the security group or groups that have rules authorizing connections from all of the EC2 instances and devices that need to access the data stored in the DB instance. By default, security groups do not authorize any connections; you must specify rules for all instances and devices that will connect to the DB instance

  10. All other fields can be left to default

  11. Your DB Instance will be in 'creating' Status for about 4-5 minutes and will be in 'Available' status as seen in the below screenshot

  12. Open Workbench that is installed (if not this needs to be installed) in your local machine or on any of the EC2 machines

  13. Click Database > Connect to Database

  14. Host Name should be the End Point URL and provide valid credentials (which were created while creating the DB i.e. "Master Username" and "Master Password")

  15. Endpoint URL can be obtained from dB instance page by clicking that arrow button as seen in the below screen-shot which needs to be replaced in place of 'localhost' from the URL

  16. The user will be successfully connected to the database

Creating Database and Tables:

  1. Open MySQL Workbench and connect to the local instance of MYSQL

  2. Drag and drop MySQLInitial.SQL file into the workbench and execute the query

    File located at "\AIQ\TestNode\webapps\AppvanceServer\WEBINF\classes\database"

  3. Now drag and drop MySQLAppvance.sql and execute

    File located at "\AIQ\TestNode\webapps\AppvanceServer\WEBINF\classes\database

  4. Launch Appvance IQ

  5. Navigate to the Global Options page (Should be logged in as Admin) > Go to Admin Options – Results, change the URL, and click Test Database Connection to see the "Connection Success" message

To enable functions, procedures, and triggers for my Amazon RDS MySQL instance

You can enable functions, procedures, and triggers when binary logging is enabled without the SUPER privilege by setting the log_bin_trust_function_creators parameter to true for the DB parameter group that you create for your MySQL instance.

Note: A default DB parameter group is created if you create a DB instance without specifying a DB parameter group that you create. For more information, see Working with DB Parameter Groups.

  1. Create a DB Parameter Group (Check below on how to create a DB parameter)

  2. Modify the DB parameter group as follows:log_bin_trust_function_creators=1

  3. Choose Save Changes

    Note: After you create or modify a DB parameter group, wait at least five minutes before creating your first DB instance that uses that DB parameter group

  4. In the navigation pane, choose Instances

  5. Choose the instance that you want to associate with the DB parameter group

  6. Choose Instance Actions and choose Modify

  7. Select the parameter group you want to associate with the DB instance

    The parameter group name is changed immediately, but the parameter group changes aren't applied until you reboot the instance without failover

To create a DB parameter group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/

  2. In the navigation pane, choose Parameter groups

  3. Choose to Create a parameter group

    The Create parameter group window appears

  4. In the Parameter group family list, select a DB parameter group family

  5. In the Group name box, type the name of the new DB parameter group

  6. In the Description box, type a description for the new DB parameter group

  7. Choose Create

Troubleshooting

Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled

If you get the above error while running the MySQLAppvance.sql script on AWS RDS, make sure that the RDS parameter group has the value 1 (ON) for the log_bin_trust_function_creators parameter. The RDS database MUST be rebooted for parameter changes to take effect.