MySQL5.7.26 忘记Root密码小计

本是古典何须时尚 2023-08-17 15:20 68阅读 0赞

以前直接修改mysql.user就ok了，现在不行了，正好虚拟机MySQL的root密码忘记了，就简单记录下：（本方法不适合互联网线上项目，除非你不在意这段时间的损失）

> PS：以UbuntuServer为例（CentOS你把配置文件的位置换下就ok了）

坑点：

1.  不能和以前一样直接修改user表了，需要使用新命令
    
     *  **`alter user 'root'@'localhost' identified with mysql_native_password by '复合密码强度的新密码';`**
2.  `ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement`
    
     *  `flush privileges;`

### 修改密码 ###

![1127869-20190615152004033-173568243.jpg][]

#### 附录 ####

dnt@ubuntuserver:~$ vi /etc/mysql/mysql.conf.d/mysqld.cnf 
    dnt@ubuntuserver:~$ cat /etc/mysql/mysql.conf.d/mysqld.cnf |grep skip
    skip-external-locking
    # Instead of skip-networking the default is now to listen only on
    skip_name_resolve=on
    skip-grant-tables
    dnt@ubuntuserver:~$ sudo systemctl restart mysql
    dnt@ubuntuserver:~$ mysql -uroot
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.26-0ubuntu0.18.04.1 (Ubuntu)
    
    Copyright (c) 2000, 2019, 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.
    
    mysql> select user();
    +--------+
    | user() |
    +--------+
    | root@  |
    +--------+
    1 row in set (0.01 sec)
    
    mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'WWW.baidu.com1';
    ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'WWW.baidu.com1';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> exit
    Bye

### 恢复正常 ###

![1127869-20190615152221770-2072656488.jpg][]

#### 附录 ####

dnt@ubuntuserver:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 
    dnt@ubuntuserver:~$ cat /etc/mysql/mysql.conf.d/mysqld.cnf |grep skip
    skip-external-locking
    # Instead of skip-networking the default is now to listen only on
    skip_name_resolve=on
    # skip-grant-tables
    dnt@ubuntuserver:~$ sudo systemctl restart mysql
    dnt@ubuntuserver:~$ mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.26-0ubuntu0.18.04.1 (Ubuntu)
    
    Copyright (c) 2000, 2019, 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.
    
    mysql> select user();
    +----------------+
    | user()         |
    +----------------+
    | root@localhost |
    +----------------+
    1 row in set (0.00 sec)

转载于:https://www.cnblogs.com/dotnetcrazy/p/11027732.html

[1127869-20190615152004033-173568243.jpg]: /images/20230810/f1b99b7b9552448585a92f697517b333.png
[1127869-20190615152221770-2072656488.jpg]: /images/20230810/8ea7a9427433483dbdbf50f2e36b9369.png