While our RDS instance is being created, let’s get ready to import our local database schema to our RDS instance. In Chapter 1, you saw that there was a MySQL database packaged with the sample project, at
/setup/photoalbums.sql. We’re going to use MySQL Workbench to connect to our RDS instance and import this file into the database. If you prefer another interface for connecting to MySQL databases, that’s fine. We’re using MySQL Workbench simply because it’s easy.
Just to jog your memory, we created an RDS security group, which grants access only to our designated EC2 instances and your personal IP address. (If you’re working in multiple locations, you’ll have to add more IPs to the security group.) We created an RDS instance in that security group, and we’re about to make sure we can connect to the instance, at which point we’ll know everything is working as planned. Open up MySQL Workbench, or whatever MySQL client you’re using. In the top-left corner, you should see a + button, which you will click to create a new connection. You should see a window like the one in Figure 3-11. Name the connection Photoalbums RDS. The connection method can stay as TCP/IP. We will have to refer back to RDS to fill out the rest of the fields.
73
Your hostname is going to be generated by RDS when the instance has been created successfully. Let’s head back to the console and find that. In the RDS dashboard, you should see your instance in the table view, with a status of available in green. Click the row to reveal details about the instance, as shown in Figure 3-12.
Figure 3-12. RDS instance details
Right at the top, you should see Endpoint. This is the URL at which you can connect to your instance, on port 3306. Only connections from your IP will be accepted. Copy this URL and paste it into the Hostname field in MySQL Workbench (remember to leave off :3306 when you paste). Input the username and password you chose earlier and click OK.
MySQL Workbench will automatically open a connection to the database. We’re now ready to run the SQL export script from the sample project. Under the File menu, select Run SQL Script and navigate to the .sql file. A window will appear with the contents of the SQL script, giving you the option to change the Default Schema Name or Default Character Set. In the Default Schema Name drop-down, choose photoalbums. Leave the character set blank and click Run. The window should change its subtitle to Output, and the output should look something like this:
Preparing...
Importing photoalbums.sql... Finished executing script Operation completed successfully
Click the Close button, and find the photoalbums database under the SCHEMAS heading in the left- hand navigation. Control-click (or right-click, on a PC) photoalbums, and select Refresh All. In a moment, you should be able to expand Tables under photoalbums and see the tables you worked with locally already: albums, photos, and users (see Figure 3-13). We got that running rather quickly, didn’t we? Now if only our app could connect to it as well!
OpsWorks RDS Layer
You will recall that I discussed RDS layers earlier in Chapter 2. Now that we have an RDS instance, we’re going to register it with the application. In the AWS Console, return to the OpsWorks dashboard. Select your stack and, from the Navigate menu, choose Layers.
You should only see one layer in the list: Node.js App Server. Below that, click the +Layer button. We’re back to the Add Layer view, and this time, we have to click the RDS tab to create an RDS layer. The setup is somewhat different than that you saw for the App Server layer, as shown in Figure 3-14.
75
The photoalbums instance was auto-detected, because it’s in the same region. All you have to do is type in your username and password and click Register with Stack. You will be returned to the Layers view, and you will now see the RDS layer below the App Server layer, as shown in Figure 3-15. But it says no app is connected!
Figure 3-15. Layers in our stack, with RDS layer added
For our app to be connected to the RDS layer, we have to set the database as the Data Source for the App Server. First, click Connect app, which will simply take you to the list of apps in your stack. In the Actions column for Photoalbums, click edit, which will allow you to edit a few of the settings for the app.
The second heading is titled Data Sources. You will notice that currently the Data source type is set to None. Click the radio button next to RDS; choose photoalbums (mysql) as your Database instance; and enter photoalbums as your Database name. It should look exactly like Figure 3-16. Don’t leave this screen just yet; there’s one more thing we have to do here.
Figure 3-16. RDS selected as data source for the app