mysql 忘记root密码怎么办

• 首先，查看一下sql版本号(不同的版本号，密码字段名不一样，后面会讲)

guofu@guofu-Inspiron-3558 ~ $ mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

• 关闭mysql 服务，

sudo service mysql stop 

• 修改mysql配置文件，可以使用find命令全盘搜索一下（一般来说，是/etc/my.cnf)

guofu@guofu-Inspiron-3558 ~ $ sudo find / -name my.cnf
find: ‘/run/user/1000/gvfs’: Permission denied
/etc/my.cnf # 默认用的就是这个
/etc/alternatives/my.cnf
/etc/mysql/my.cnf
/var/lib/docker/overlay2/b08489947e59c12464254cbfba2e9a681b5085fb3d025b94b0b79cc05ce9524f/diff/root/my.cnf

• 修改my.cnf文件，跳过权限校验，在mysqld部分，datadir后面加一行skip-grant-tables

[client]
#password   = your_password
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
datadir = /usr/local/mysql/var 
skip-grant-tables #就是这里

• 重启mysql

sudo service mysql start

• 在你的mysql目录下执行登录免校验命令,并更新root密码

guofu@guofu-Inspiron-3558 ~ $ mysql 
# 查库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| mysql              |
| mywebsite          |

+--------------------+
12 rows in set (0.09 sec)
# 切到mysql库
mysql> use mysql
Database changed
mysql> show tables;
#查看user表的字段，我这里是mysql5.7 密码字段对应的是authentication_string ，mysql5.6及以下对应的是Password,具体看表字段
mysql> select *  from user limit 1 \G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_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
            Alter_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
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *F8B4AA8D30D01323E5B141CA4871D3B0805AB8BB  #密码字段
      password_expired: N
 password_last_changed: 2020-03-05 13:50:46
     password_lifetime: NULL
        account_locked: N
1 row in set (0.03 sec)
#设置root密码
mysql> update user set authentication_string=password('root') where User='root'; 
Query OK, 1 row affected, 1 warning (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 1
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)
#退出
mysql> exit;
Bye

• 此时root密码已经设置成功，修改回刚刚改过的/etc/my.cnf文件，重启mysql即可

guofu@guofu-Inspiron-3558 ~ $ 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-log Source distribution