In this article, I will discuss how to create a MySQL database using AWS RDS. Once we have the instance ready, I will walk you through how you can access and use this created instance using MySQL, our workbench or any MySQL client tool.
In order to create a MySQL database instance, sign into the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds. Once signed in, from the RDS Dashboard click on Launch a DB Instance.
We will be using a MySQL database, so click Select next to the MySQL option.
Next you will be asked “Do you plan to use this database for production purposes?”. For this walkthrough, select "No" and click Next Step. If you are intending to use a database for production use, it is best practice to select “Yes” to ensure your database is at a minimum a Multi-AZ deployment for high availability which provides an SLA of 99.95% up time per month.Next, we completed the DB Instance Details page with the following:
- DB Engine Version: Use the default engine version (5.6.13 in this example)
- DB Instance Class: db.t1.micro
- Multi-AZ Deployment: No
- Allocated Storage: 5 GB
- DB Instance Identifier*: awsdb
- Master User Name*: awsuser
- Master User Password*: awspassword
|* You may substitute whatever values for the DB Instance Identifier, Master User Name and Master User Password as you see fit. The remaining steps in the tutorial will use our example values above.|
|For this walkthrough, we will leverage the default DB security group. In the upcoming steps, I will show you how to create the proper ingress rules for you to connect your MySQL client.|
On the Management Options page, accept the default Enabled Automatic Backups, Backup Retention Period and Backup/Maintenance Window settings, and click Next Step. This is where you get the opportunity to specify the number of days (up to 35) that you would like automatic backups to be retained by AWS as well as when you would like backups and maintenance (if required) to occur.
Now, review your settings and click Launch DB Instance. Click the Close button.
Under Instances option of the left navigation menu, monitor your new DB instance until the status changes from creating to available.
|Note: This may take up to 5 minutes as the database is being created and backed up.|
Once the instance is available (on your RDS dashboard on the AWS console), expand it. Take note of the Endpoint (the very first line once you expand). Your end point it the link all the way up to the port number :3306. To access our database instance, we will use this as the host name.
Now, locate the "Security Groups" option on the left navigation pane and click on the default group. The RDS servers have the same security model as Amazon EC2 overall: trust nothing. In order to be able to access this instance, we will have to create a new authorization with our IP so that we can access it from the MySQL client tool on our machine.
To start, click on the default Security Group record to display its details. RDS will automatically detect the CIDR/IP of your current machine and display it in the details of the security group, as depicted below.
To add the CIDR/IP of your current machine, copy the CIDR/IP of your current machine value into the CIDR/IP to Authorize textbox and click Authorize.
It will take a couple of minutes to authorize your CIDR/IP, after which status will display as “authorized”. The number one reason that authorization fails is that your machine is running different CIDR/IP than you entered.
Now that we have authorized your machine to connect to the RDS instance, check and verify that the RDS instance is available. To do this, click on the Instances option of the left navigation menu, see if your new DB instance status is available. If not, continue to wait until the status changes to available.
Once your RDS instance is available, we are ready to use this newly created MySQL database instance (in the cloud) from our MySQL workbench! To do this, open you MySQL workbench. Click the + sign to add a new connection and the following window will open:
Enter your end point as your hostname and the database username and password used while creating the instance (we used awsuser and awspassword in the walkthrough earlier). Again, the hostname can be obtained from the RDS instance details on the AWS Console, as depicted below:
|Remember the hostname is everything up until the port number :3306.|
Click Test Connection and a pop-up window will open which, will say "Connection parameters are correct".This means that the username, password and the hostname were right and that you are authorized to access this instance. After connecting, we can view the schema on the left side just as it appears on our local MySQL installation.
To use our awsdemo database, I will use the SQL query use awsdemo:
Now you know how to create a MySQL database instance, authorize access for your local machine and how to access the RDS instance from our MySQL client.