MariaDB启动正常但systemd日志报错，跨机房PDNS SQL复制功能正常

在两个数据中心间搭建了基于PDNS的MariaDB主从复制，复制功能正常，主从数据同步无异常，执行show slave status结果也一切正常。但每次执行systemctl status mariadb时都会出现报错。

此前运行过MariaDB安全脚本，该脚本强制root用户执行mysql命令必须携带-p参数，怀疑此设置与报错相关。同时猜测问题可能出在/etc/systemd/system/mysqld.service systemd单元文件未配置密码输入导致。

附systemd日志：

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 'pdns-secondary@172.21.8.17:3306',replication started in log 'mysql-bin.000001' at position 8040

从日志可以明确，报错根源是debian-start脚本在MariaDB启动时，尝试以无密码方式用root用户连接数据库，但之前运行的安全脚本已经禁用了root无密码登录权限。以下是具体修复步骤：

• 创建专用本地管理用户
  为debian-start脚本创建一个拥有全权限的本地用户，避免修改root的登录限制：

  CREATE USER 'debian-sys-maint'@'localhost' IDENTIFIED BY 'your_secure_password';
  GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' WITH GRANT OPTION;
  FLUSH PRIVILEGES;
  

  注意替换your_secure_password为实际的强密码。

• 配置脚本认证信息
  编辑/etc/mysql/debian.cnf文件，更新[client]段的用户和密码为刚创建的账号信息：

  [client]
  host     = localhost
  user     = debian-sys-maint
  password = your_secure_password
  socket   = /var/run/mysqld/mysqld.sock
  

• 重启服务并验证
  重启MariaDB服务使配置生效：

  systemctl restart mariadb
  

  然后查看服务状态确认报错消失：

  systemctl status mariadb
  

无需修改/etc/systemd/system/mysqld.service单元文件，问题核心是debian-start脚本的认证配置缺失，而非systemd单元未设置密码提示。

内容的提问来源于stack exchange，提问作者ndizz