mysql 修改root密码_mysql 5.7 root密码重置(centos 7)

mysql5.7版本之后，与mariadb不同，在安装之后，在启动之时，会进行自动随机密码的设定，所以在systemctl start mysqld之后，会出现mysql -uroot -p无法登陆的情况

mysql root原始密码查看

实际上mysqld在启动时，会自动设定root密码的，可以在其相关日志里面查看到，如下:

[root@bogon ~]# grep password /var/log/mysqld.log2017-07-31T10:31:57.368883Z 1 [Note] A temporary password is generated for root@localhost: TjcrBm.j,7eU2017-07-31T10:32:30.201882Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)2017-07-31T10:34:40.233539Z 0 [Note] Shutting down plugin 'validate_password'2017-07-31T10:34:41.483983Z 0 [Note] Shutting down plugin 'sha256_password'2017-07-31T10:34:41.483990Z 0 [Note] Shutting down plugin 'mysql_native_password'

mysql root的初始密码为TjcrBm.j,7eU(每人情况不一样，具体值在mysql日志中查看)

mysql密码重置

当然有些人可能是root密码真忘记了，那样可以通过mysql免密码登陆

在其配置文件/etc/my.cnf中加入skip-grant-tables=1即可

[root@bogon ~]# grep -v ^# /etc/my.cnf | grep -v ^$[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidskip-grant-tables=1

然后重启mysql，使用mysql命令即可进入

[root@bogon ~]# systemctl restart mysqld[root@bogon ~]# ss -tnlState      Recv-Q Send-Q             Local Address:Port                            Peer Address:Port              LISTEN     0      128                    127.0.0.1:9000                                       *:*                  LISTEN     0      128                            *:111                                        *:*                  LISTEN     0      128                            *:80                                         *:*                  LISTEN     0      128                            *:22                                         *:*                  LISTEN     0      100                    127.0.0.1:25                                         *:*                  LISTEN     0      80                            :::3306                                      :::*                  LISTEN     0      128                           :::111                                       :::*                  LISTEN     0      128                           :::22                                        :::*                  LISTEN     0      100                          ::1:25                                        :::*  [root@bogon ~]# mysqlmysql: [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 4Server version: 5.7.19Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> 

修改mysql root密码

mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> update user set authentication_string = password("123456") where user="root";Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 1mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

然后将/etc/my.cnf中的skip-grant-tables=1注释掉，重启mysql服务即可。

在此要注意的是，之前版本密码修改字段为password，在5.7版本之后字段为authentication_string

mysql密码难度修改

mysql密码修改为123456之后，有人发现使用123456能进入mysql，但是却不能使用mysql的任何功能。会出现如下情况:

[root@bogon ~]# mysql -uroot -p123456mysql: [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 4Server version: 5.7.19Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> show databases;ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

实际上，在初始更改root密码时，并不能直接使用update来更改，需要使用alter user命令来更改

mysql> alter user 'root'@'localhost' identified by '123456';ERROR 1819 (HY000): Your password does not satisfy the current policy requirements## mysql在5.7版本中加了密码安全等级，弱密码不能使用mysql> alter user 'root'@'localhost' identified by 'QWEqwe123!@#';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

mysql 安全策略以及root密码清空

在上述修改密码之后，可查看mysql的密码安全策略

[root@bogon ~]# mysql -uroot -p'QWEqwe123!@#'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 6Server version: 5.7.19 MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> show variables like '%password%';+---------------------------------------+--------+| Variable_name                         | Value  |+---------------------------------------+--------+| default_password_lifetime             | 0      || disconnect_on_expired_password        | ON     || log_builtin_as_identified_by_password | OFF    || mysql_native_password_proxy_users     | OFF    || old_passwords                         | 0      || report_password                       |        || sha256_password_proxy_users           | OFF    || validate_password_check_user_name     | OFF    || 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      |+---------------------------------------+--------+14 rows in set (0.00 sec)

其中可以看到validate有很多参数

validate_password_dictionary_file：插件用于验证密码强度的字典文件路径。

validate_password_length：密码最小长度。

validate_password_mixed_case_count：密码至少要包含的小写字母个数和大写字母个数。

validate_password_number_count：密码至少要包含的数字个数。

validate_password_policy：密码强度检查等级，0/LOW、1/MEDIUM、2/STRONG。

validate_password_special_char_count：密码至少要包含的特殊字符数。

其中，关于validate_password_policy-密码强度检查等级：

0/LOW：只检查长度。

1/MEDIUM：检查长度、数字、大小写、特殊字符。

2/STRONG：检查长度、数字、大小写、特殊字符字典文件。

可以直接在mysql中进行参数的修改

mysql> set global validate_password_policy=0;Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_special_char_count=0;Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_number_count=0;Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_mixed_case_count=0;Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_length=0;Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%password%';+---------------------------------------+-------+| Variable_name                         | Value |+---------------------------------------+-------+| default_password_lifetime             | 0     || disconnect_on_expired_password        | ON    || log_builtin_as_identified_by_password | OFF   || mysql_native_password_proxy_users     | OFF   || old_passwords                         | 0     || report_password                       |       || sha256_password_proxy_users           | OFF   || validate_password_check_user_name     | OFF   || validate_password_dictionary_file     |       || validate_password_length              | 0     || validate_password_mixed_case_count    | 0     || validate_password_number_count        | 0     || validate_password_policy              | LOW   || validate_password_special_char_count  | 0     |+---------------------------------------+-------+14 rows in set (0.00 sec)

当然，此种变更，只能在当前环境下生效，重启mysql后会失效

想要长期生效，需要将更改写入配置文件中才可永久生效

也可以直接在配置文件中进行修改禁用validate-password功能

编辑my.cnf配置文件，在mysqld下面加入“validate-password=off”,然后重启mysql即可。

[root@bogon ~]# grep -v ^# /etc/my.cnf  | grep -v ^$[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockvalidate_password=offskip-name-resolve=oninnodb_file_per_table=onsymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

将mysql的密码安全等级降低之后，可以将root密码修改回来

mysql> update mysql.user set authentication_string = password('') where user='root';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> exit

以上密码root即清除为空了。使用mysql即可直接进入mysql。当然生产环境中不推荐此种做法

也可以直接清除mysql password插件

[root@vm-10-112-42-140 mysql]# mysql -uroot -p'PoGtyalq9i>r'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 3Server version: 5.7.22Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> uninstall plugin 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 'QWEqwe123!@#';Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> uninstall plugin validate_password;Query OK, 0 rows affected (0.07 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> update mysql.user set authentication_string = password('') where user='root';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> exit