MariaDB is a fork of the popular MySQL database management system that is developed and maintained by the open-source community. It is also commercially supported, offering enterprise-level features and support for organizations that require them. MariaDB aims to maintain high compatibility with MySQL, ensuring a drop-in replacement capability.
To create a new Proxmox VE Mariadb LXC, run the command below in the Proxmox VE Shell.
bash -c "$(wget -qLO - https://github.com/community-scripts/ProxmoxVE/raw/main/ct/mariadb.sh)"
Default Settings: 1GB RAM - 4GB Storage - 1vCPU
The subsequent step involves executing the included security script. This script modifies certain default options that are less secure. Our purpose for running it is to prevent remote root logins and eliminate unnecessary database users.
Run the security script:
mysql_secure_installation
Enter current password for root (enter for none): enter
Switch to unix_socket authentication [Y/n] y
Change the root password? [Y/n] n
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y
We will create a new account called admin with the same capabilities as the root account, but configured for password authentication.
mysql
Prompt will change to MariaDB [(none)]>
Create a new local admin (Change the username and password to match your preferences)
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
Give local admin root privileges (Change the username and password to match above)
GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
Now, we’ll give the user admin root privileges and password-based access that can connect from anywhere on your local area network (LAN), which has addresses in the subnet 192.168.100.0/24. This is an improvement because opening a MariaDB server up to the Internet and granting access to all hosts is bad practice… Change the username , password and subnet to match your preferences:
GRANT ALL ON *.* TO 'admin'@'192.168.100.%' IDENTIFIED BY 'password' WITH GRANT OPTION;
Flush the privileges to ensure that they are saved and available in the current session:
FLUSH PRIVILEGES;
command below this, exit the MariaDB shell:
exit
Log in as the new database user you just created:
mysql -u admin -p
Create a new database:
CREATE DATABASE homeassistant;
command below this, exit the MariaDB shell:
exit
Reboot the lxc
Checking status.
systemctl status mariadb
Change the recorder: db_url: in your HA configuration.yaml
Example:
recorder:
db_url: mysql://admin:password@192.168.100.26:3306/homeassistant?charset=utf8mb4
Adminer is a full-featured database management tool
Adminer Interface: IP/adminer/