MariaDB is known for having better performance than MySQL and it is thereby a great candidate or OpenHAB. This How To Install MariaDB On OpenHAB (openhabian) helps you to learn how to leverage on it!
Install MariaDB On OpenHAB (openhabian)
Index of This How To Install MariaDB On OpenHAB (openhabian)!
Background
For this how to install MariaDB on OpenHAB (openhabian) I am leveraging on the following infrastructure:
- Raspberry Pi 4 8GB
- OpenHAB 3 Openhabian
- MariaDB
- Corsair USB 512GB USB stick which had in several benchmarks that I found in the Internet a great performance
I do not use a SD card for my Raspberry Pi 8GB.
How to Install MariaDB On OpenHAB (openhabian)
Since openHABian is based on Debian Linux, it is actually easy to install it on the OpenHAB device. You simply execute a couple of commands an than you can rock your new database! You however also like to look into a couple of other aspects, such the basic configuration and phpMyAdmin that is a fantastic tool to maintain your MariaDB.
Install MariaDB On OpenHAB (openhabian)
You start with updating the system.
sudo apt update && sudo apt full-upgrade -y && sudo apt autoremove -y && sudo apt-get autoclean -y
Following this update, you like to execute the next command to install MariaDB on OpenHAB.
sudo apt install mariadb-server
Once done you have the MariaDB installed. Now, you like to ensure it is always running once you start your Raspberry or if you like to reboot it. For this you type the below.
sudo systemctl enable mysqld sudo systemctl start mysqld
Basic configuration
Cybersecurity experts do not recommend using the default root user of MariaDB for any application. It is therefore fair, if you assume its also not recommended for OpenHAB. Instead, you keep the root user account credentials for you and leverage on it only if you need it for example for debugging. Instead, you love to create a user for OpenHAB. So lets get these things done, its not too difficult.
sudo mysql_secure_installation
The script will guide you thru a couple of configurations and asks related questions. I selected:
- Current password -> Just hit enter
- Switch to unix_socket -> Y
- Change root password -> Y –> enter your new root password
- Remove anonymous user -> Y
- Disallow root login remotely -> Y
- Remove test database and access to it -> Y
- Reload privilege tables now -> Y
Create a Database for OpenHAB3
You like to use “sudo”, because with this no dedicated password is required
$ sudo mysql -u root
Now create your database for OpenHAB. I for example called it OpenHAB3.
CREATE DATABASE OpenHAB3;
Once you have created your database, then you like to create a user and password for enabling OpenHAB to access it. I called the user in the belwo for openHAB simply “openhabian”. Cyberexperts recommend to user different and difficult to guess user names. Considering this, you maybe select another username than “openhabian”.
GRANT ALL ON yourdb.* TO openhabian IDENTIFIED BY 'yourpasswordVERY-DIFFICULT-Password'; FLUSH privileges;
How to Install phpMyAdmin
phpMyAdmin is an amazing web-based tool that allows you to access your MariaDB via Webbrowser and Graphical User Interface (GUI) instead of doing everything in the terminal. You will love to have it installed on your OpenHAB. Of course, this installation is optional and not mandatory to maintain your MariaDB on your OpenHAB.
The majority of the how to’s and also tutorials in the internet leverage on Apache 2 Webserver to get phpMyAdmin running. In my case, I have already OpenHAB 3 and the UniFi Controller running on my Raspberry Pi 4 8GB. Whilst in July 2022 it is still easy to run Apache2 next to these installations, I am more concerned about the future and future software updates that maybe require more heavy hardware. For this reason, I select the lighthttpd server. Many benchmark tests in the internet claim that lighthttpd is more effective whilst requiring less ressources.
Install lighttpd webserver, incl. PHP
You love to read, that lighttpd is already available as a package on your openhabian installation. This makes the installation of the lighttpd and the future maintenance more easy.
sudo apt install lighttpd
Lets ensure the default http and https ports of lighthttpd are not in conflict with OpenHAB and any other program you installed on your openhabian. In my case for example the UniFi Controller. For this reason we update /etc/lighthttpd/lighttpd.conf.
udo nano /etc/lighttpd/lighttpd.conf
You now search for “server.port”. Since in my case OpenHAB and UniFi Controller are not running on port 80, I leave the default configuration to port 80. This should look like the below. If you have used your port 80 already to for something else, then you should change it and restart the service (sudo systemctl restart lighttpd).
server.port = 80
Now lets install php. This is also easy on openhabian
sudo apt install php php-cgi php-mysql
Once php is installed, we need to enable it for lighttpd. You do this by the following commands.
sudo lighty-enable-mod fastcgi sudo lighty-enable-mod fastcgi-php
Lets ensure everything gets loaded properly by restarting the lighttpd server
sudo systemctl restart lighttpd
Its time to test our installation!
sudo vi /var/www/html/test.php
You include into this file the following:
<!--?php phpinfo(); ?-->
Install MariaDB On OpenHAB (openhabian)
If you go to your browser and connect to your webserver, than you should see somthing like the below. To connect to your webserver, you type the IP address of your server and include the php test page we just created: 192.x.x.x/test.php.
Your browser should share something like the below with you.
PHP Test Page
Lets install phpMyAdmin as the next step and the goal of this
sudo apt install phpmyadmin
During the installation you will be asked for which server you like to install phpMyAdmin as well as dbconfig-common.
- Select (*) lighttpd
- Select for dbconfig-common <No>
Once done, we need to link the lighttpd source directory with phpMyAdmin. You do this by the below.
sudo ln -s /usr/share/phpmyadmin/ /var/www
Reload the lighttpd server
sudo systemctl restart lighttpd
If you go to your browser and connect to your webserver, than you should see somthing like the below. To connect to your webserver, you type the IP address of your server and include the php test page we just created: 192.x.x.x/phpmyadmin.
Your browser should share something like the below with you.
phpMyAdmin Login screen
You enter the username and password that you have created in the above to access the database. This will like the below.
phpMyAdmin – Overview
You are done! Enjoy!
Additional Information
- Link to Debian Linux
- Link to OpenHAB
- How to Install Openhab
- phpMyAdmin
