Proxmox: Installing MariaDB in LXC

MariaDB banner. Credit: MariaDB (https://mariadb.com/)

MariaDB is an SQL database that is useful for apps such as Photoprism and Zoneminder. Today we will install it in a Debian 11 LXC on Proxmox and configure it for access on the local network within your home.

MariaDB Foundation - MariaDB.org

Requirements:

• Proxmox fully installed and configured and access to GUI
• Access to router control panel to set static IP address and basic knowledge of static IPs, DHCP ranges.
• Basic knowledge of the terminal (opening, saving files etc.)
• Basic Proxmox container knowledge (downloading LXC templates, setting up containers etc)

Creating a container:

Create a container with the following resources. MariaDB is not very resource heavy, but feel free to tweak them according to your needs. For detailed screenshots of the container creation process see here.

• Unprivileged container
• Your favorite Linux flavor
• ~8Gb root storage
• 1 CPU core
• 1Gb RAM, default swap
• DHCP option for IPv4

Once done, go ahead and start the container.

Set the IP address:

Setting a static IP address makes it easier for you to access your service.

This step will vary between router to router. You need to log into your control panel, typically accessible via 192.168.xxx.1 and add a static IP address for your container. Make sure your static IP assignment does not overlap with the DHCP assignment range. I typically recommend 192.168.xxx.1 — 192.168.xxx.50 be reserved for static IPs only.

Note: If your container does not pop up on your router device list, go back to the container and run apt update then refresh the router screen.

There should be plenty of YouTube videos covering how to do this for most popular routers. Once you have done this, reboot the container. There is no need to reboot Proxmox.

Installing MariaDB:

Update existing packages:

apt update && apt upgrade

Install MariaDB package:

apt install mariadb-server

Configuring MariaDB:

Run initial setup:

mysql_secure_installation

• When prompted for current root password simply hit Enter to indicate none.
• Answer n for switching to unix socket authentication
• Answer n for changing the root password
• Answer y for everything after that

Allow external access:
By default, MariaDB is configured so that only requests from localhost will be processed and all others will be rejected. This is not the behavior we want as we wish to use this database with other containers on our local network. Since these services are sitting behind our home router and are not exposed to the public internet, this is a suitable route for us.

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Find the line that starts with bind-address and comment it out. Then save the file and restart the container

Adding users for external access:
In order to allow external services to access our database, we will need to create users for them. Use the following command to create a user. In this case I am creating a user for Zoneminder, and giving it access to all Zoneminder databases. Change the values for your needs.

mariadb -u root

GRANT ALL ON zm.* TO 'zmuser'@'%' IDENTIFIED BY 'zmpass' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;

Optional: Setting time zone:
Some services require that the time zone be set correctly in the database. Follow these steps to do that:

Import time zone definitions:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot mysql

Then change the time zone in MariaDB. Add your time zone as appropriate. In my case, its Asia/Singapore:

mariadb -u root

SET GLOBAL time_zone = 'Asia/Singapore';
exit;

And if all worked well, you should now have a MariaDB server running and accessible on your home network! For more troubleshooting, see credit section below.

Credit:

The following resources were very helpful to me, and may help guide you if you need to troubleshoot:

How To Install MariaDB on Debian 11 | DigitalOcean

Changing the connection timezone in MySQL

Time Zones

How to enable Remote access to your MariaDB/MySQL database on Ubuntu Bionic or MariaDB < v10.6

Disclaimer:

This is not a guide for setup in production or business environments, and is most certainly not ready to be exposed to the public internet.

I am not an IT professional. I am not tech support. I am a college student with a server. You are ultimately responsible for any commands you run on your system.

If you have any questions, leave a comment. Enjoy!