Mariadb

[tteck]

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:

mariadb-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.

mariadb

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:

mariadb -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

phpMyAdmin Interface: IP/phpMyAdmin