几种修改root密码的方法

1、set password

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

2、mysqladmin

#第一次设置密码
mysqladmin -u root password "newpass"
#修改密码
mysqladmin -u root password oldpass "newpass"

3、update user表

mysql> UPDATE mysql.user SET Password = PASSWORD('newpass') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;

MySQL密码安全策略

为了加强安全性，MySQL5.7为root用户随机生成了一个密码，在error log中，关于error log的位置，如果安装的是RPM包，则默认是/var/log/mysqld.log

mysql> select @@log_error;
+---------------------+
| @@log_error         |
+---------------------+
| /var/log/mysqld.log |
+---------------------+
1 row in set (0.00 sec)

获取临时密码

grep "temporary password" /var/log/mysqld.log
2016-01-19T05:16:36.218234Z 1 [Note] A temporary password is generated for root@localhost: waQ,qR%be2(5

用该密码登录到服务端后，必须马上修改密码，不然会报如下错误：

mysql> select user();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

如果只是修改为一个简单的密码，会报以下错误：

mysql>  ALTER USER USER() IDENTIFIED BY '12345678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

查看 mysql 初始的密码策略

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| 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      |
+--------------------------------------+--------+
6 rows in set (0.00 sec)

  • validate_password_dictionary_file：指定密码验证的文件路径

  • validate_password_length： 固定密码的总长度

  • validate_password_mixed_case_count：整个密码中至少要包含大/小写字母的总个数

  • validate_password_number_count：整个密码中至少要包含阿拉伯数字的个数

  • validate_password_policy：指定密码的强度验证等级，默认为 MEDIUM

  • validate_password_special_char_count：整个密码中至少要包含特殊字符的个数

关于 validate_password_policy 的取值：

  • 0/LOW：只验证长度；
  • 1/MEDIUM：验证长度、数字、大小写、特殊字符；
  • 2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；

修改密码的强度验证等级

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

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_dictionary_file    |       |
| validate_password_length             | 8     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
6 rows in set (0.00 sec)