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!