Maridb忘记用户密码处理方法

原创

Expect_lei 2022-09-29 11:34:48 博主文章分类：MariaDB ©著作权

©著作权归作者所有：来自51CTO博客作者Expect_lei的原创作品，请联系作者获取转载授权，否则将追究法律责任

关于Maridb介绍：http://www.cndba.cn/Expect-le/article/2370

1 问题

[root@MariDB-Master ~]# mysql -uroot -p123456
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

无法登陆mysql。

2 处理方法

2.1 停止mysql

[root@MariDB-Master ~]# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]

或者

service mysql stop

2.2 忽略授权，重新打开mysql

[root@MariDB-Master ~]# mysqld_safe --skip-grant-tables --user=mysql &
[1] 2440
[root@MariDB-Master ~]# 171206 11:21:20 mysqld_safe Logging to '/usr/local/mysql/data/MariDB-Master.err'.
171206 11:21:20 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@MariDB-Master ~]#
[root@MariDB-Master ~]#

查看一下是否启动成功

[root@MariDB-Master ~]# ps -ef|grep mysql
root      2440  2313  0 11:21 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql
mysql     2563  2440  1 11:21 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --skip-grant-tables --log-error=/usr/local/mysql/data/MariDB-Master.err --pid-file=MariDB-Master.pid --socket=/tmp/mysql.sock --port=3306

root      2599  2313  0 11:21 pts/0    00:00:00 grep mysql

2.3 修改密码

MariaDB [mysql]> set password=password("123");
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

这里注意不能使用这种方法修改密码。

正确修改方法：

MariaDB [mysql]> update user set password=password("cndba") where user='root' and host='localhost';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#刷新权限

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
MariaDB [mysql]> quit;
Bye

2.4 关闭当前已safe打开的mysql

注意：密码是刚刚修改的密码。

[root@MariDB-Master ~]# mysqladmin -uroot -pcndba shutdown
[1]  +  Done                    mysqld_safe --skip-grant-tables --user=mysql

2.5 重新正常打开mysql

[root@MariDB-Master ~]# /etc/init.d/mysql start 或service mysql start
Starting MySQL.171206 11:26:55 mysqld_safe Logging to '/usr/local/mysql/data/MariDB-Master.err'.
171206 11:26:55 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
                                                           [  OK  ]

2.6 再次登录

#首先尝试错误密码

[root@MariDB-Master ~]# mysql -uroot -p123

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

#正确密码，登录成功

[root@MariDB-Master ~]# mysql -uroot -pcndba
Welcome to the MariaDB monitor.  Commands end with ; or /g.
Your MariaDB connection id is 11
Server version: 10.2.10-MariaDB-log MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

MariaDB [(none)]>

至此密码修改完成。