MySQL5.7重置root用户密码 原创 数据库 2021年9月1日 07:40 夏至未至 1427 当前内容 4657 字，在路上，马上到，马上到 ### 前提 本次实测数据库环境为5.7.22，据了解，5.7.9之后都是可以这么做的 #### 1. 编辑实例配置文件 vi /etc/my.cnf 确定是自己数据库实例配置文件 #### 2. 跳过密码验证 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd skip-grant-tables [mysqld_safe] log-error=mysql.log pid-file=mysql.pid # # include all files from the config directory # !includedir /etc/my.cnf.d 上边 **skip-grant-tables** 需要放在 [mysqld] 小节下 #### 3. 重启实例 [root@node1 ClientService]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! 重启实例 **skip-grant-tables** 跳过鉴权才能生效 #### 4. 连接数据库 [root@node1 bin]# mysql -uroot -S /var/lib/mysql/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> 由于跳过鉴权，密码是不需要填的 #### 5. root密码置为空 mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> mysql> update user set authentication_string='' where user='root'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #### 6. 刷新数据库 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> 这一步不能忽略，需要加上 #### 7. 退出当前命令窗口后，编辑配置文件，去除 skip-grant-tables ，重启实例，无密码连接数据库(前边置为空了密码) [root@node1 bin]# vi /etc/my.cnf [root@node1 bin]# [root@node1 bin]# [root@node1 bin]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@node1 bin]# [root@node1 bin]# [root@node1 bin]# mysql -uroot -S /var/lib/mysql/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.22 Copyright (c) 2000, 2018, 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> #### 8. 修改密码 mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> 前边置空了密码，所以这里登陆也不需要密码，但是这次不要密码和上次跳过鉴权是不一样的，这次虽然不要密码能登陆，但是权限极低，但是修改密码的权限还是有的，如上修改密码 如果报错如下： ERROR 1396 (HY000):Operation ALTER USER failed for 'root'@'localhost' 那么，请将 `localhost` 改成 `%`，即： mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> #### 9. 带密码登录验证 [root@node1 bin]# mysql -uroot -S /var/lib/mysql/mysql.sock ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@node1 bin]# [root@node1 bin]# [root@node1 bin]# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> 设置密码后，如果不带密码断然是登陆不上的，带上密码，登陆成功，至此，root密码就成功重置了。 本文标题： MySQL5.7重置root用户密码 本文作者： 夏至未至 发布时间： 2021年9月1日 07:40 最近更新： 2022年9月8日 10:23 原文链接： 许可协议： 署名-非商业性-禁止演绎 4.0 国际(CC BY-NC-ND 4.0) 请按协议转载并保留原文链接及作者 MySQL(28) 密码(2) 上一个 Linux下MySQL设置环境变量 下一个 GIT常用命令:git cherry-pick 当前文章评论暂未开放,请移步至留言处留言。
