Photo by brickset.com
Here is my Log book
I am going to see if I can setup MariaDB (MySQL) in my free tier of AWS.
This learning / development will span over a few days depending on how much free time I get.
I want to be able to:
- Post new entries and persist them to a MariaDB database hosted in AWS RDS.
- Get all entries from the database.
- The OpenFaaS functions will be using secrets and environment variables.
- Fetch and persist the product image into the table.
- Run a scheduled
cron(I guess) job to do the above (fetch image).
Setting up a MariaDB database in AWS
First hurdle was trying to log in with my non-root admin user and was asked to change the password. The first thing you must do once you create an AWS account is to create a new admin user that is not the root user account. You should practically never use the root user on AWS. The problem for me was that I setup a strict password policy and it was trial and error (and logging in as root user) before I could reset the expired password. P.S. Symbol did not include the character > that was generated by 1Password.
- Open up the AWS Console
- I am using the eu-west2 region which is London
- Search for RDS and select it
- Scrolled down a bit until I found this. Doubled checked the region is correct.
- Create database
- Select MariaDB as the engine
- Select the Template to be Free tier
- DB instance identifier: openfaas
- Generated the master password in 1Password
- DB instance class: db.t2.micro — 1vCPU, 1 GIB RAM.
- Disabled Storage autoscaling
- Under Connectivity
- Enabled Public Access
- Select Create new VPC security group (this should allow my current IP to access the instance)
- Entered a name for this new VPC
- Additional configuration
- Initial database name: openfaasdb
- Disabled automatic backups since I won’t need it for this learning exercise
- Create Database!
- Now we just sit and wait till it has been created.
Installing MySQLWorkbench as GUI
Incase you did not know, MySQL was acquired by Oracle a good while ago. MariaDB sprung up as a fork from MySQL (we won’t get into the politics here).
The best GUI I have found to use on Mac with MySQL / MariaDB is MySQLWorkbench (owned by Oracle).
You can download the installer here. Select "No thanks, just start my download."
- Add a new connection. + button next to MySQL Connections.
- On the RDS console, select the newly created database.
- Look for the Endpoint and Port values.
- Copy the Endpoint value and paste it as the Hostname in MySQLWorkbench.
- Do the same with the port if you changed the default to not be 3306.
- Enter the username (default is admin).
- Store the password in your keychain.
- Set a name for this new connection: openfaasdb – amazon rds
- Click the Test Connection button.
- Continue Anyway. You get this warning whenever you connect to MariaDB servers.
- If the connection succeeded then you will get the following message.
- Click Ok
- Double click the new connection. Get presented with the MySQL warning again. Select the "do not bother me about this BS again".
- Select the Schemas tab.
- Right click on the new database (mine from the example is openfaasdb) and Set as Default Schema. This way in queries I don’t have to specify the schema.
Create the database table
Open a new query in MySQLWorkbench and paste the following.
CREATE TABLE `openfaasdb`.`legosets` ( `pkID` INT NOT NULL AUTO_INCREMENT, `LegoID` INT NOT NULL, `Description` VARCHAR(200) NULL, `ProductURL` VARCHAR(4096) NULL, `ImageURL` VARCHAR(4096) NULL, PRIMARY KEY (`pkID`), UNIQUE INDEX `LegoID_UNIQUE` (`LegoID` ASC));
Execute the query and with a bit of luck the new table has been created.
Let us populate some sample data into the table. Again execute the following queries.
INSERT INTO `openfaasdb`.`legosets` (`LegoID`, `Description`, `ProductURL`, `ImageURL`) VALUES ('21322', 'Pirates of Barracuda Bay', 'https://www.lego.com/en-gb/product/pirates-of-barracuda-bay-21322', 'https://live.staticflickr.com/65535/49698377257_be3f773feb_b.jpg'); INSERT INTO `openfaasdb`.`legosets` (`LegoID`, `Description`, `ProductURL`, `ImageURL`) VALUES ('21325', 'Medieval Blacksmith', 'https://www.lego.com/en-gb/product/medieval-blacksmith-21325', 'https://images.brickset.com/sets/AdditionalImages/21325-1/21325-1_95850.jpg');
Note: I will later be altering the table to include a BLOB to store a cached version of the product Image (download and store ImageURL)