一、安装后第一次登录

（一）问题产生背景：

安装完 最新版的 mysql8.0.1后不知道初始密码，无法进行数据库安全配置、无法进入客户端；

（二）解决方案：

第一步：找到临时密码

安装默认的临时密码在/var/log/mysqld.log目录下。
在安装mysql-server后的第一条日志中。
[Server] A temporary password is generated for root@localhost: Aj20E8kTt.2o

  1. 2020-11-26T07:42:09.052157Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.22) initializing of server in progress as process 5077
  2. 2020-11-26T07:42:09.064961Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
  3. 2020-11-26T07:42:13.158331Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
  4. 2020-11-26T07:42:22.243192Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Aj20E8kTt.2o
  5. 2020-11-26T07:42:37.603159Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22) starting as process 19320
  6. 2020-11-26T07:42:37.616905Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
  7. 2020-11-26T07:42:37.813748Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
  8. 2020-11-26T07:42:37.936818Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /run/mysqld/mysqlx.sock
  9. 2020-11-26T07:42:38.080385Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
  10. 2020-11-26T07:42:38.080693Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
  11. 2020-11-26T07:42:38.102157Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.

第二步：进行安全配置重置密码

运行mysql_secure_installation

[mate@localhost ~]$ mysql_secure_installation 
##输入临时密码，进行更换

二、忘记密码时：

第一步：修改配置文件免密码登录mysql

1.1 打开my.cnf配置文件
image.png
1.2 在 配置文件最后加上如下语句 并保持退出文件

skip-grant-tables

image.png
1.3 重启mysql服务

[root@DESKTOP-TT5APIG home]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

第二步：免密码登录到mysql上

[root@DESKTOP-TT5APIG home]# mysql -u root -p
//password直接回车

//password直接回车

第三步： 给root用户重置密码

3.1 首先查看当前root用户相关信息，在mysql数据库的user表中

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> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | $A$005$4)T(FkN+I*l8_!bG7RA2eBP3CGvRia/On46WMG0tHu1HWJwCj8IlCnE3T1             | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+

说明： host: 允许用户登录的ip‘位置’%表示可以远程；
user:当前数据库的用户名；
authentication_string: 用户密码；
在mysql 5.7.9以后废弃了password字段和password()函数；
plugin： 密码加密方式；
3.2 如果当前root用户authentication_string字段下有内容，先将其设置为空

mysql> update user set authentication_string='' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3.3 退出mysql, 删除/etc/my.cnf文件最后的 skip-grant-tables 重启mysql服务
3.4 使用root用户进行登录，因为上面设置了authentication_string为空，所以可以免密码登录
3.5 运行mysql_secure_installation,设置新密码

[mate@localhost ~]$ mysql_secure_installation 

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : n

 ... skipping.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

3.6 也可以使用ALTER设置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> ALTER user 'root'@'localhost' IDENTIFIED BY 'Hfut2019110!';
Query OK, 0 rows affected (0.02 sec)

注意：
如果密码不符合安全性等级，提示设置出错，可以设置复杂的密码（包含大小写、数字、符号），也可以更改密码等级。

#设置密码太简单
mysql>  ALTER user 'root'@'localhost' IDENTIFIED BY 'hfut2019110';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

#显示关于密码变量的表
mysql> SHOW VARIABLES LIKE 'validate_password%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

#提示必须设置一个密码才能查看关于密码变量的表
mysql> ALTER user 'root'@'localhost' IDENTIFIED BY 'Hfut2019110!';
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

#降低密码政策，设置时注意表中变量的名字，与网上的不一样
mysql> set global validate_password_policy=0;
ERROR 1193 (HY000): Unknown system variable 'validate_password_policy'

mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)

#降低密码安全性后再设置简单密码
mysql> ALTER user 'root'@'localhost' IDENTIFIED BY 'hfut2019110';
Query OK, 0 rows affected (0.01 sec)

不知道初始root密码时修改密码的详细操作


[root@DESKTOP-TT5APIG home]# mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can not connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
#提示没有开启mysql-server ，开启命令
[root@DESKTOP-TT5APIG home]# systemctl start mysqld.service
[root@DESKTOP-TT5APIG home]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: 
Error: Access denied for user 'root'@'localhost' (using password: YES)

#提示密码错误
#修改配置文件，跳过安全验证，重启mysql-sever
[root@DESKTOP-TT5APIG home]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

[root@DESKTOP-TT5APIG home]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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数据库
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> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | $A$005$4)T(F
kN+I*l8_!bG7RA2eBP3CGvRia/On46WMG0tHu1HWJwCj8IlCnE3T1 | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

#修改密码为空
mysql> update user set authentication_string='' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> exit
Bye

#重新启动
[root@DESKTOP-TT5APIG home]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@DESKTOP-TT5APIG home]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22

Copyright (c) 2000, 2020, 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>  ALTER user 'root'@'localhost' IDENTIFIED BY 'hfut2019110';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

#显示关于密码变量的表
mysql> SHOW VARIABLES LIKE 'validate_password%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

#提示必须设置一个密码才能查看关于密码变量的表
mysql> ALTER user 'root'@'localhost' IDENTIFIED BY 'Hfut2019110!';
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

#降低密码政策，设置时注意表中变量的名字，与网上的不一样
mysql> set global validate_password_policy=0;
ERROR 1193 (HY000): Unknown system variable 'validate_password_policy'

mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)

#降低密码安全性后再设置简单密码
mysql> ALTER user 'root'@'localhost' IDENTIFIED BY 'hfut2019110';
Query OK, 0 rows affected (0.01 sec)