Setting Up A MySQL Database on Bluehost

Why MySQL for IoT Data?

MySQL is one of the most used databases on the web. Many organizations have pre-existing resources, such as shopping carts and order tables, which already exist in MySQL Databases. 

In this blog you will learn how to set up a MySQL database to accept input from a serverless function so clicks are captured from IoT buttons. These IoT buttons can be configured in countless ways and perform actions like re-ordering physical stock or sending notifications. Check out our IoT Button Roundup Blog for some ideas on how these buttons can make your life a little easier!

Creating the Database
1. Sign on to your Bluehost* account and click on the advanced tab, then click on MySQL Databases.
*Wordpress sites on top of a MySQL Database, so if you have a WordPress site, or external web hosting, you probably  have access to a MySQL database. The screen example below may vary for different hosting providers.

2.  Under the create new Database Heading, type in the name of the database and then click on the Create Database.

3.  You should get a success message, then click on the Go Back link.

4.  This will take us back to the databases homepage, scroll down and you will see the new database has been added to the list.

5.  Scroll down to create a user for this database. Type in a username, a password, and confirm the password. Then click on the Create User.

6.  You will receive a success message, then click on Go Back.

7.  Now we are going to add this user to our database. Scroll down and under “Add User to Database” select the user you just created, and then select the database and click Add.

8.  Select All Privileges to make this a full access user.

9.  Next we are going to make a user just for our IoT button. Scroll down to Add New User, enter a username, a password and confirm the password, and then click on create user. Add this user to the database just like you did the full access user.

10.  We don’t need to give our IoT button user full access, so just use the permissions here. Click on Make Changes and then Go Back.

11.  Make note of you both your full access and button user’s name and password as well as the name of the database as you will need these later. You will also need the name of the database host. To find this, click on Domains in the menu bar to the left.

12.  Before we  can work with our database on our local computer, we need to record our computer’s IP address in a table on the service provider’s site so that we can access the MySQL Database. First let’s get our IP address. Simply go to Google and type in “What’s my IP?”

13.  Next you will add your IP address to the white list on Bluehost so you can work with the database from your computer. Click on Advanced on the menu bar to the left and choose Remote MySQL.

14.  Add the IP address to the host field —  it’s a good idea to add the name of your computer or service in the comment field. Click on Add Host.

Working with the Database
1.  For this example we will be using DBeaver, a cross platform application that works with most SQL databases via SQL Statements, or a GUI interface. You can download the application from https://dbeaver.io.

2.  Once the app is installed, open it and you will be prompted to select a database, choose MySQL.

3.  Enter the following details necessary to connect to the hosted MySQL Database. 

Hostname – yourhostname.com

  • Port – 3306
  • Database – yourdatabasename
  • Username – fullaccessuser
  • Password – fullaccesspassword

Press the Test Connection button

4.  Since we have not connected to a MySQL database yet, we will be prompted to download a driver for the database, click on the download button in the lower left hand corner.

5.  Press the Test Connection button again, and you should be able to successfully connect to your database.

6.  A popup will appear asking if you want to create a sample database. Since we have already created a database, click No.

7.  A ‘Tip of the day’ will show up every time you start the app unless you deselect the “Show tips on startup” checkbox. Close the window to start working with your database.

8.  Click on the connection to your hosted MySQL Instance in the panel to the left and open up the disclosure triangle so we can see the MySQL Directory.

9.  Open up the disclosure triangle for the databases and you will see the database we created earlier.

10.  Next we will create the table and columns needed to capture data from an IoT Button. Select the database, Right Click, select create and then choose Table.

11.  Click on the Properties Tab and name the table.

12.  Now let’s create the columns. Select the database, Right Click, select create and then Column.

13.  The Properties popup will first appear. Name the column  ID, and give it a data type of INT, it will not be null and will auto increment. Press OK.

14.  Right Click to create another column, we are naming this one buttonJSON, with a data type of TEXT.  Click OK.

15.  Next we will create the Primary Key. Right Click on Constraints and choose Create Constraint.

16.  Click on the checkbox next to “id” and press OK.  Choose save from the file menu, or Command S to save the changes to your database.

17.   You should get a confirmation popup, click on Persist to make these changes in the hosted database.

18.  If you get an error message, you may have disconnected from the database while setting up these changes. Right Click on the MySQL Instance and choose Invalidate/Reconnect to establish a new connection and try saving the changes again.

Allow Access For Serverless Apps IP

The last step in getting IoT data into our MySQL Database is to allow the IP address of the serverless app sending the button data access to the database at your service provider.  For Bluehost, click on advanced in the menu bar and choose Remote MySQL and add the IP address for the serverless app just like you added the IP address for your computer.  


Check out the Revolution11 blog on configuring your AWS Virtual Private Cloud (VPC), so that your serverless app has an IP address.

Congratulations, you you are now ready to use your server less app with a hosted MySQL Database!

Setting Up A MySQL Database on Bluehost

Configuring a Virtual Private Cloud (VPC)

What You Will Learn in This Blog
Configure your Amazon Web Services (AWS) Virtual Private Cloud (VPC) so the serverless apps you are running within your VPC have a public IP address. This  means you can use your serverless apps with external SQL Databases.

What is a Virtual Private Cloud?
A space where Amazon resources such as databases, serverless applications, storage, and other useful tools reside as a part of a virtual network. 

What are Subnets?
Subnets are segments of a network, broken down by IP address ranges. Various AWS resources are assigned to the IP addresses within the ranges.

What are Route Tables?
Route tables are  a set of rules that determines how traffic is routed on a network.

What is a Network Address Translation (NAT) Gateway?
A NAT Gateway routes internal traffic to a public IP address. This is important because we want to use the NAT Gateway to associate our serverless functions with a public IP address — we can then use our serverless apps with a MySQL database that is not hosted with AWS.

Configuring Your Subnets
1.  Log into the AWS Console as a Root User and search for the VPC service. 

2.  On the left menu select Subnets.

3.  You will see the list of subnets that are already set up.

4.  It’s important to label the subnets to make them easier to identify as we work with them. Let’s label the subnet where the last two sections are zero as ‘public.’

5.   Locate the Name field and click on the pencil icon to edit the field. Type in ‘public’ and confirm the change by clicking the checkbox in the right corner below the edit box.

6.   The name ‘public’ now appears in the list. Amazon suggests that your Lambdas use two subnets.  Repeat the labeling process for two more of the subnets, changing the name fields to Lambda Private 1 & Lamba Private 2.

7.  Your subnet list should look like this:

Setting Up Route Tables
8.  Next, click on route tables on the left menu.

9.  You will see an existing route table here.

10.   We will now create a couple of new route tables. Click on the Create Route Table in the upper left.

11.  For the first name route table, we are going to use the name Public Subnet, and we are going to select our existing VPC from the dropdown. Press the create button.

12.  You will land on this success screen once the route table is created. Press the close button to return to the list of route tables.

13.  You can see the new Route Table back in our list of route tables.

14.  We are going to repeat this process for our second route table, but we are going to name this one Private Lambda.

Associating Subnets with Route Tables
15. Now we are going to set up the Public Subnet, click on it and then select the subnet associations tab.

16. Press Edit Subnet Associations.

17.  You will see the four subnets we have, this is where those labels come in handy.

18.  We are selecting the public subnet for the public route table. Click on it and press Save.

19.  We are then returned to the route table home, and we can now see the association with our public subnet.

20.  Next, Let’s set up the Private Lambda Route Table, click on that and select the subnet associations tab, then press the Subnet Associations Button.

21.  Amazon recommends two subnets for Lambdas so we are selecting the 2 Lambdas we labeled just for that. Then Press Save the in the lower right hand corner.

22.  You can see that two subnets are now associated with the Private Lambda Route Table when we return to the list of route tables.

Setting Up The Public Route Table
23.  Select the Public Route and press the Edit routes button.

24.  This will bring you to the list of routes. We are going to add a public route. Click on the add route button on the left.

25.  For the destination, we are going to manually add the public route which is represented by 0.0.0.0/0.  For the target we are going to choose our existing internet gateway.

26.  You will get a success message. Press Close.

Setting Up a NAT Gateway
27.  Now we are going to set up a NAT Gateway to provide an external IP address for our Lambda. Click on Nat Gateways on the left menu.

28.  We don’t have any NAT gateways set up yet so we are going to create one. Press the Create NAT Gateway button in the upper left.

29.  For the subnet, pick the public subnet from the list. We are going to create a new elastic IP address by clicking on the Create New EIP Button. Then we are going to press the Create a NAT Gateway button in the lower right hand side of the screen.

30.  You should get this success message, click on the close button in the lower right to return to the list of NAT Gateways.

Associating Private Route Table with the Nat Gateway
31.  We now need to associate our Private Route Table with this NAT Gateway so our Lambdas can use the external IP address we just created. Click on Route Tables on the menu bar to the left.

32.  Select the Private Lambda Route, then press the Routes tab and choose the Edit routes button.

33.  Add the public route destination, 0.0.0.0/0 and then choose the NAT gateway we just created as the target. Press the save route button in the lower right hand corner.

Giving your Lambda Role Permission to Use the VPC
34.  Now we are going to give our Lambda role permission to use this VPC. Return to the console home page and find and click on IAM.

35.  On the IAM Home Screen choose Roles from the menu to the left.

36.  You will land on the Roles home page, scroll down to see the existing roles.

37. Scroll down to the list of existing roles and click on the Lambda you are interested in.

38.  You’ll see our Lambda has basic permissions to execute. We want to let it use VPC, so click on the Attach Policies Button.

39.  Search for VPC and select “AmazaonVPCFullAccess” and press the “Attach Policy” button in the lower right hand side of the Screen.

40.  When we return to this roles’ detail screen we can see the VPC policy is attached.

Configuring Your Lambda to Use the VPC
41.  Go to the AWS consul homepage to edit the Lambda application to use the VPC. For this example, we are using a serverless app that inserts the results of an IoT button click into a SQL database. We are using our default VPC, our 2 private subnets for the Lambda, and the default security settings. Press the save button in the upper right hand corner.

42.  Now test the Lambda, you can see how to set up these test events by watching this video, which covers installation of this serverless app on this channel. Congratulations! Success.

Configuring a Virtual Private Cloud (VPC)