记一次mariadb忘记密码事件

刚刚替换自己的笔记本电脑系统为的deepin linux，用的蛮开心，突然想用mysql，想想还是mariadb靠谱。 就一条apt install 安排上了，然而没有时间修改密码，导致后面无法进入数据库，后面千思万想终于设定了密码，特记录一下。

0.环境

• 系统

deepin linux 23 测试版

• mariadb版本

Server version: 10.5.11-MariaDB-1 Debian 11 官网 mariadb.org/

Latest MariaDB releases 10.8.2 (RC), 10.7.3 **, ** 10.6.7 ,  10.5.15, 10.4.24 , 10.3.34 , 10.2.43 . Vote on your current version.

可见我的不是新的，当然很稳定。

1.一条命令安装mariadb

sudo apt install mariadb-server
复制代码

2.无密码模式启动并进入mariadb

sudo mariadb --defaults-file=/etc/mysql/debian.cnf
复制代码

系统提示：

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.5.11-MariaDB-1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

复制代码

3.修改密码

常规的修改密码命令报错，很纳闷，具体如下：

MariaDB [mysql]> select user, host from user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
3 rows in set (0.002 sec)

MariaDB [mysql]> update user set Password=PASSWORD('root') where User='root' and Host='localhost';
ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

复制代码

后来反反复复查找，找到如下格式：root'@'localhost'，并不是直接在表中改，一次成功。。。。。。

MariaDB [mysql]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');
Query OK, 0 rows affected (0.005 sec)

复制代码

具体验证登录如下：

(base) livingbody@livingbody-PC:~$ mysql -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.5.11-MariaDB-1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

复制代码

4.总结

此次修改密码成功，主要有两个点：

• 1.特权模式启动

主要参考了etc/mysql/debian.cnf 文件头的说明，不然还真搞不定

# THIS FILE IS OBSOLETE. STOP USING IT IF POSSIBLE.
# This file exists only for backwards compatibility for
# tools that run '--defaults-file=/etc/mysql/debian.cnf'
# and have root level access to the local filesystem.
# With those permissions one can run 'mariadb' directly
# anyway thanks to unix socket authentication and hence
# this file is useless. See package README for more info.
[client]
host     = localhost
user     = root
[mysql_upgrade]
host     = localhost
user     = root
socket=/var/run/mysqld/mysqld.sock
# THIS FILE WILL BE REMOVED IN A FUTURE DEBIAN RELEASE.
复制代码

• 2.密码修改

直接修改表不行，需要系统命令来修改，这个和老版本的mysql略有区别，特记录一下。

希望该文对大家有用，Good Luck！

相关文章