Setting up sql replication with pdns from one datacenter to another, it works, but when i 'systemctl status mariadb' I get these errors everytime. I am able to see the records update from master and to slave, and 'show slave status' shows everything works.
I did run the MariaDB secure script that won't allow 'mysql' command as root to proceed without the '-p' parameter, so maybe this has something to do with it. Odd that they'd include this script in the package (Ubuntu 22.04 but using the MariaDB project repo as an apt source) if it would complain though.
What exactly is going on here?
Feb 27 20:36:18 res01.internal.zzz004.xxxxxxx.net systemd[1]: Started MariaDB 11.2.3 database server.
Feb 27 20:36:18 res01.internal.zzz004.xxxxxxx.net /etc/mysql/debian-start[55523]: Upgrading MySQL tables if necessary.
Feb 27 20:36:18 res01.internal.zzz004.xxxxxxx.net mariadbd[55505]: 2024-02-27 20:36:18 6 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Feb 27 20:36:18 res01.internal.zzz004.xxxxxxx.net /etc/mysql/debian-start[55526]: Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
Feb 27 20:36:18 res01.internal.zzz004.xxxxxxx.net /etc/mysql/debian-start[55526]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Feb 27 20:36:18 res01.internal.zzz004.xxxxxxx.net /etc/mysql/debian-start[55526]: FATAL ERROR: Upgrade failed
Feb 27 20:36:18 res01.internal.zzz004.xxxxxxx.net /etc/mysql/debian-start[55532]: Checking for insecure root accounts.
Feb 27 20:36:18 res01.internal.zzz004.xxxxxxx.net mariadbd[55505]: 2024-02-27 20:36:18 7 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Feb 27 20:36:18 res01.internal.zzz004.xxxxxxx.net debian-start[55535]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Feb 27 20:36:18 res01.internal.zzz004.xxxxxxx.net mariadbd[55505]: 2024-02-27 20:36:18 4 [Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin.000001' at position 8040
I suspect it has something to do with /etc/systemd/system/mysqld.service systemd unit file and this isn't prompting for a password
Because the root user is only used by the systemd service, reset it back to the secure Authentication Plugin - Unix Socket.
ALTER USER root@localhost IDENTIFIED VIA unix_socket;
With the user being able to be authenticated by unix_socket, aka running as root, password authentication to this user is no longer needed and its more secure.