忘记登录密码

1. 介绍

        有时候我们需要更改MySQL的密码，分两种情况。

• 知道用户密码，修改密码
• 忘记登录密码，修改密码

2. 修改密码

        我以MySQL的root登录用户，MySQL5.7【其他版本稍微有所不同】为例。知道root登录密码，修改root用户密码。

修改密码

[root@cai ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27

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> set global validate_password_policy=0;  
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1; 
Query OK, 0 rows affected (0.00 sec)

mysql> set password=password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)

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



[root@cai ~]# mysql -uroot -proot
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 5
Server version: 5.7.27 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> 

密码

1. 注意登录mysql最好是-p 回车然后再输入密码，
    [root@cai ~]# mysql -uroot -p   # 不在后面直接输入密码
    Enter password:  # 输入密码，密码不可见

2. 密码是特殊字符的直接在-p后面的，登录就会报错。例如
    [root@cai ~]# mysql -uroot -pfJqa%rpo7lalias  # 密码是fJqa%rpo7lalias，直接-p后面加上会报错
    mysql: [Warning] Using a password on the command line interface can be insecure.
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    [root@cai ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.27

    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> 

set global validate_password_policy=0; 关闭mysql的弱密码检查

set global validate_password_length=1; 关闭mysql密码长度检查

3. 忘记密码

        我们说的忘记MySQL的密码指的是忘记root密码[root用户类似linux的root用户，是超级管理员用户]，你忘记MySQL的其他用户，直接用root登录，直接修改密码即可。

update user set password=PASSWORD('cmz') WHERE user='caimengzhi';

• 修改配置文件

        在MySQL的配置文件中国的mysqld中添加skip-grant-tables，重启mysql

忘记密码

[root@cai ~]# egrep -v '#|^$' /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-grant-tables

[root@cai ~]# systemctl restart mysqld
[root@cai ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2019-07-27 20:23:44 CST; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 32330 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 32312 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 32335 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─32335 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 27 20:23:44 cai systemd[1]: Starting MySQL Server...
Jul 27 20:23:44 cai systemd[1]: Started MySQL Server.
[root@cai ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 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> set password=password('cmz');
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.00 sec)

mysql> set password=password('cmz');
ERROR 1133 (42000): Can't find any matching row in the user table
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> set password for 'root'@'localhost' = password('cmz');
Query OK, 0 rows affected, 1 warning (0.00 sec)

修改配置，删除skip-grant-tables，然后重启mysql
[root@cai ~]# grep  'skip' /etc/my.cnf
#skip-grant-tables

[root@cai ~]# systemctl restart mysqld

重新登录mysql
[root@cai ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@cai ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.27 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>