mysql - 忘记root密码的解决方案

思路：

1. mysql连接到服务器，通过两种方式：
   1. 本地socket模式
   2. 远程TCP/IP模式
2. 用户密码的验证，是通过mysql数据库的user表，来进行识别的
3. 只要采取一种方法（绕过mysql_server的密码验证过程），即可成功登陆上mysql服务器，从而可以通过mysql.user表，重新设定root密码

实现：

免密码登陆mysql服务器：

1. 本质：

   1. --skip-grant-tables --skip-networking
      
      1. --skip-grant-tables : 跳过授权表（实现免密码登陆），实际实现原理：不将mysq.user授权表 刷新到内存中
      2. --skip-networking : 防止远程登陆

2. 具体命令：

   1. 方式一：

      /root/mysql/database/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking

      其中，mysqld_safe 是mysql路径bin文件夹下的一个命令。实际操作记录：

      　# 1、关闭mysqld服务  

        [root@localhost ~]# service mysqld stop
        Shutting down MySQL.. SUCCESS!

      
      # 2、使用mysqld_safe 启动mysql服务
      [root@localhost ~]# /root/mysql/database/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking
      2021-01-11T19:56:27.731523Z mysqld_safe Logging to '/root/mysql/data/3306/localhost.localdomain.err'.
      2021-01-11T19:56:27.756552Z mysqld_safe Starting mysqld daemon with databases from /root/mysql/data/3306
      
      
      ^Z
      [1]+  Stopped                 /root/mysql/database/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking
      
      # 3、登陆mysql
      [root@localhost ~]# mysql
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 2
      Server version: 5.7.28 MySQL Community Server (GPL)
      
      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.00 sec)
      
      mysql> 
      # 4、更新root密码：

        mysql> update mysql.user set authentication_string=PASSWORD('123') where `user`='root';
        Query OK, 0 rows affected, 1 warning (0.01 sec)
        Rows matched: 1 Changed: 0 Warnings: 1

        # 5、退出mysql客户端

        mysql> exit

        Bye

        # 6、重启mysql服务

        [root@localhost ~]# service mysqld restart
        Shutting down MySQL.. SUCCESS!
        Starting MySQL. SUCCESS!
        [root@localhost ~]#

      　# 7、重新登录mysql

        [root@localhost ~]# mysql -uroot -p123

        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 3
        Server version: 5.7.28 MySQL Community Server (GPL)

        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>

       

   2. 方式二：

      # 核心代码：
      # 1、关闭mysql服务
      service mysqld stop
      
      # 2、免密码开启mysql服务
      service mysqld start --skip-grant-tables --skip-networking
      
      # 3、免密登录mysql
      mysql
      
      # 4、更新root密码：
      mysql> update mysql.user set authentication_string=PASSWORD('newpassword') where `user`='root';
      
      # 5、退出mysql客户端
      mysql> exit
      
      # 6、重启mysql服务
      service mysqld restart
      
      # 7、使用root账号+密码方式登录mysql
      mysql -uroot -p123