我在 Ubuntu 20.04 上用 MariaDB 10.3.38 替换了 MySQL 5.7.41。
不幸的是 MariaDB 无法自动启动systemctl
:
sudo systemctl status mariadb ● mariadb.service - MariaDB 10.3.38 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2023-08-08 17:30:53 CEST; 15h ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/ Main PID: 1888 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 30 (limit: 9425)
Memory: 70.9M
CGroup: /system.slice/mariadb.service
└─1888 /usr/sbin/mysqld
Aug 08 17:30:52 LicenseCentral systemd[1]: Starting MariaDB 10.3.38 database server...
Aug 08 17:30:53 LicenseCentral systemd[1]: Started MariaDB 10.3.38 database server.
Aug 08 17:30:53 LicenseCentral /etc/mysql/debian-start[1926]: Looking for 'mysql' as: /usr/bin/mysql
Aug 08 17:30:53 LicenseCentral /etc/mysql/debian-start[1926]: Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
Aug 08 17:30:53 LicenseCentral /etc/mysql/debian-start[1926]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Aug 08 17:30:53 LicenseCentral /etc/mysql/debian-start[1926]: FATAL ERROR: Upgrade failed
Aug 08 17:30:53 LicenseCentral debian-start[1935]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
如果我尝试通过命令行获取访问权限,我可以访问数据库,并且所有表都存在。
mariadb -u root -p<password>
在 MySQL 5.7.41 中我看到执行了以下 SQL 代码:
sudo mysql -e "alter USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';"\
"FLUSH PRIVILEGES;"
我从 MariaDB 得到了以下结果:
MariaDB [(none)]> SHOW GRANTS FOR CURRENT_USER;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*9FA783C4FXXXXXX1ED0640ACBFDF68A2B354E18A' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
| SET DEFAULT ROLE `0` FOR `root`@`localhost` |
+----------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.001 sec)
我认为我不明白这个using password: NO
提示。有人知道幕后发生了什么吗?我怎样才能让 MariaDB 在sudo systemctl start mariadb
没有密码验证的情况下使用命令启动。
谢谢,马库斯
答案1
当 mysqld 启动时它会自动运行mysql 升级(或者在您的例子中是 mariadb-upgrade)以 root 身份启动。此启动机制不知道 root 用户的密码。在默认安装中,root 用户配置了 SO_PEERCRED 身份验证(即不需要 localhost 密码)。
您可以使用以下方法恢复默认行为:
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
...或者在 /root/.my.cnf 中添加用户名和密码
...或者忽略错误,只需记住在升级后手动编辑并运行 mysql-upgrade。
我建议不要尝试配置您的系统以尝试排除具有 root 访问权限的人访问数据库 - 如果他/她甚至具有基本的能力水平,他们可以绕过您尝试的任何障碍。