长时间没有使用mysql数据库,忘记了登录密码怎么办?
第一次设置mysql数据库密码,怎么设置?
第一步:查看debian.cnf配置文件
sudo cat /etc/mysql/debian.cnf
显示情况如下:
haha@ubuntu:~/work$ sudo cat /etc/mysql/debian.cnf
[sudo] password for haha:
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = tfFpRFcdqQgUQ6Qb //注意这个,我们要用它
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = tfFpRFcdqQgUQ6Qb
socket = /var/run/mysqld/mysqld.sock
第二步:使用debian-sys-maint用户名登录,密码就是上面的:tfFpRFcdqQgUQ6Qb
mysql -u debian-sys-maint -p
显示如下:
haha@ubuntu:~/work$ mysql -u debian-sys-maint -p
Enter password: //输入上面的密码:tfFpRFcdqQgUQ6Qb
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> //进入这里登录成功。
第三步:使用mysql本身自带的“mysql数据库”,来修改密码
use mysql;
update mysql.user set authentication_string=password('你要设置的密码') where user='root' and Host ='localhost';
//注意:这里可能会执行出错.
//出错执行:ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
这里的“user='root' and Host ='localhost'”表示给哪个用户,哪个主机IP设置密码。
update user set plugin="mysql_native_password";
flush privileges;
quit; //退出mysql,需要重启mysql
在设置这里的第2个指令时,可能会遇到问题。请看最后解决方案。
第四步:重启mysql,重新登录
sudo service mysql restart
mysql -u 用户名 -p // 启动后,输入修改好的密码,即刻登录成功。
注意:
我们再操作第三步时,可能会出现这样的问题:
mysql> use mysql;
Database changed
mysql>UPDATE mysql.user SET authentication_string=password('123456') WHERE User='root' AND Host ='localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('123456') WHERE User='root' AND Host ='localhost'' at line 1
上面说你有一个sql语法错误。
不要被“sql语法错误”迷惑,他的解决方案其实很简单:
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
当然你执行这个指令,它会告诉你另一个错误:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
就是你设置的密码不符合mysql当前规定的策略要求。
啥意思:就是你的密码太简单了,他不让你这样设置。
MySQL有密码设置的规范,具体是validate_password_policy的值有关
针对这个策略问题,解决方案如下:
(1)可查看mysql完整的初始密码规则:
SHOW VARIABLES LIKE ‘validate_password%’;
执行效果如下:
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 | //你看长度要求是8个字符
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM | //策略等级是中等
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.43 sec)
validate_password.length决定密码的长度,修改方法如下:
set global validate_password.length=4; //长度修改为4.
mysql> set global validate_password.length=4;
Query OK, 0 rows affected (0.00 sec)
validate_password_policy决定密码的验证策略,默认等级为MEDIUM,可修改为LOW:
set global validate_password.policy=0;
mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)
再次查看:
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
到这里,你在设置密码为 123456,就可以了。