Raspberry Pi - Create new MariaDB database using phpMyAdmin

This post is about how to create new MariaDB database using phpMyAdmin. The database created will later be used as the storage for MQTT messages

Create New Database and Tables

1. Login to phpMyAdmin as root.

2. Enter the name of the database to be created than click on Create.

3. Click on HAC on the left column or in the field that contains a list of the Databases.

To enter the window for creating table.

4. Enter "mqtt_users" then click on Go.

To bring up the window for setting the table structure.

5. Enter the data in the fields inside the 2 red squares as shown below then click Save.

In the pic above, id will be used as the primary key and the checked box for AI means the id will be automatically increased for each new entry.

6. Create another table call "mqtt_acls" by entering "mqtt_acls" in the Name field then click Go.

7. Enter the data in the fields inside the 2 red squares as shown below then click Save.

In the pic above, id will be used as the primary key and the checked box for AI means the id will be automatically increased for each new entry.

Now that there are 2 tables created for the database - HAC.

Add User

1. Click on Privileges.

Then, click on Add user.

2. Enter data to the fields in the red squares as shown below then click Go to save.

Note that the random number for the 2 password fields is generated by running the "./np" command from within the "mosquitto-auth-plug" directory.

Check User Privileges

Check on "mqtt_acls" on the left column then click on "Privileges" to bring up the privileges window for "HAC.mqtt_acls".

Check on "mqtt_users" on the left column then click on "Privileges" to bring up the privileges window for "HAC.mqtt_users".

Reference:

HAC 6. Mosquitto MQTT ACL
https://youtu.be/YGOBKE2xbEE?list=PL-x6E_rBMvai4l2akwY-VaQOto5rm7p7Q