mysql(Linux)忘记密码

修改MySQL的登录设置

进入配置文件my.cnf

[root@10-23-78-167 mysql]# vim /etc/my.cnf 

---

在[mysqld]的段下加上一句：skip-grant-tables

skip-grant-tables

注意：skip-grant-tables的位置是在[mysqld]的下面

---

重启mysql服务

[root@10-23-78-167 mysql]# service mysqld restart

重启mysql服务错误解决–可能是my.cnf配置文件存在问题

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

---

登录mysql

[root@10-23-78-167 mysql]# mysql -u root
mysql> use mysql
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysqld        | %         |
| server        | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
+---------------+-----------+

没有root用户的话添加root用户

mysql>  insert into user set user='root',ssl_cipher='',x509_issuer='',x509_subject='';
Query OK, 1 row affected (0.00 sec)

mysql> update user set Host='localhost',select_priv='y',  
    -> Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> quit;
Bye
[root@10-23-78-167 mysql]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@10-23-78-167 mysql]# mysql -u root
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysqld        | %         |
| server        | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

将host列改成%

mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysqld        | %         |
| root          | %         |
| server        | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

修改秘密

注意：flush privileges;不能省略

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

去掉配置文件my.cnf中的skip-grant-tables

进入文件，使用’dd’命令–删除当前行

[root@10-23-78-167 mysql]# vim /etc/my.cnf 
[root@10-23-78-167 mysql]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@10-23-78-167 mysql]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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

可能遇到的问题解决

问题一：

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

问题解决：
设置验证密码长度

set global validate_password_length=4;

设置密码验证策略【默认等级为MEDIUM(中等),可通过以下命令修改为LOW(低)】

set global validate_password_policy=0;

修改密码

alter user user() identified by "1234";

---

问题二：

mysql> alter user 'root'@'localhost' identified by 'xuebaqiang'; 
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

解决办法：

flush privileges;

mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysqld        | %         |
| server        | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
+---------------+-----------+

问题三：

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

问题解决：
my.cnf配置文件有问题，看skip-grant-tables的位置是否正确

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

查询对应用户的权限

mysql> show grants for root@'%';

mysql5.7修改用户权限

[root@10-23-78-167 mysql]# grant all privileges on *.* to root@113.31.145.47 identified by '123456';
-bash: grant: command not found

需要登录后才能使用mysql

mysql> grant all privileges on *.* to root@113.31.145.47 identified by '123456';
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

解决ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';  
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'   IDENTIFIED BY '123456' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

1142 - INSERT command denied to user ‘root’@‘211.161.248.117’ for table ‘course’

不能解决该问题