MySQL 5.7 的初始化操作（root初始密码、修改密码、密码策略、关闭IPv6监听）

21232asa · 发表于 2016-5-30 09:42:22

我这里是通过mysql官方的yum源来安装的mysql-community-server ，当前版本是MySQL 5.7.12 。

1
2
3
4

wget
rpm -ivh mysql57-community-release-el6-8.noarch.rpm
yum install mysql-community-server
service mysqld start

第一次启动后会有个初始化的过程，会产生root账户的随机密码。

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

找到生成的随机密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

mysql -u root -p'zXMgg%#L3=;1'

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 4
Server version: 5.7.12

Copyright (c) 2000, 2016, 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> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

登陆上过后，进行正常操作会受限，提示你必须修改密码后才能进行操作。

好吧，根据提示修改密码：

1
2
3
4

mysql> SET PASSWORD = PASSWORD('123456');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> SET PASSWORD = PASSWORD("root");
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

但是提示根据当前密码策略，设置的密码不允许。
查阅官方文档后发现有以下三种密码策略：

Policy	Tests Performed
0 or LOW	Length
1 or MEDIUM	Length; numeric, lowercase/uppercase, and special characters
2 or STRONG	Length; numeric, lowercase/uppercase, and special characters; dictionary file

当前密码策略默认为1 也就是 MEDIUM

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

mysql root@localhost:(none)> 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_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    |
+---------------------------------------+---------+
13 rows in set
Time: 0.030s

所以你更改密码的策略是数字小写字母大写字母特殊字符长度至少8位。
更改完密码就可以进行数据库的操作了。

1
2
3
4
5
6
7
8
9
10
11

mysql root@localhost:(none)> show DATABASES;
+--------------------+
| Database          |
|--------------------|
| information_schema |
| mysql             |
| performance_schema |
| sys             |
+--------------------+
4 rows in set
Time: 0.009s

接下来修改默认密码策略（当然实际环境是不推荐修改为更低安全策略的）

1
2
3

mysql root@localhost:(none)> set global validate_password_policy = 0;
Query OK, 0 rows affected
Time: 0.003s

现在设置完默认密码策略后，就只有密码长度限制了。默认为字符长度至少8位。
其中：
validate_password_number_count指定了密码中数据的长度，
validate_password_special_char_count指定了密码中特殊字符的长度，
validate_password_mixed_case_count指定了密码中大小字母的长度。

这些参数，默认值均为1，所以validate_password_length最小值为4，如果你显性指定validate_password_length的值小于4，尽管不会报错，但validate_password_length的值将设为4。

1
2
3
4
5
6
7
8
9
10
11
12

mysql root@localhost:(none)> set global validate_password_length = 3;
Query OK, 0 rows affected
Time: 0.004s

mysql root@localhost:(none)> show VARIABLES  like "validate_password_length"
+--------------------------+---------+
| Variable_name          | Value |
|--------------------------+---------|
| validate_password_length |    4 |
+--------------------------+---------+
1 row in set
Time: 0.010s

如果修改了validate_password_number_count，validate_password_special_char_count，validate_password_mixed_case_count中任何一个值，则validate_password_length将进行动态修改。

MySQL 5.7 默认安装了 validate_password 插件。所以多了以上步骤。

----------------------------------------------------------------------------
通过my.cnf 配置文件设置密码策略的级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

"/etc/my.cnf" 28L, 987C 22,1 All
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/ ... ation-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
validate_password_policy=2

最后一行 validate_password_policy 设置mysql启动的时候密码策略级别。如果设置为3 ，那么需要指定字典文件。

当然你也可以通过 my.cnf 配置文件关闭 validate_password 插件。
只需要添加一行

1	validate_password = off

编辑完配置文件后，重启mysqld服务即可生效。

1
2
3
4
5
6
7

mysql root@localhost:(none)> show VARIABLES like "validate_password%"
+-----------------+---------+
| Variable_name | Value |
|-----------------+---------|
+-----------------+---------+
0 rows in set
Time: 0.008s

关闭validate_password插件后，就没有了validate_password的一些参数变量。

MySQL官方对于 validate_password 插件的使用介绍：

http://dev.mysql.com/doc/refman/ ... d_validate-password

--------------------------------------------------------------------------------
MySQL 新版本默认监听在IPv6的地址族上。