mysql5.7版本之后&＃xff0c;与mariadb不同&＃xff0c;在安装之后&＃xff0c;在启动之时&＃xff0c;会进行自动随机密码的设定&＃xff0c;所以在systemctl start mysqld之后&＃xff0c;会出现mysql -uroot -p无法登陆的情况
mysql root原始密码查看
实际上mysqld在启动时&＃xff0c;会自动设定root密码的&＃xff0c;可以在其相关日志里面查看到&＃xff0c;如下:
[root&＃64;bogon ~]# grep password /var/log/mysqld.log
2017-07-31T10:31:57.368883Z 1 [Note] A temporary password is generated for root&＃64;localhost: TjcrBm.j,7eU
2017-07-31T10:32:30.201882Z 3 [Note] Access denied for user &＃39;root&＃39;&＃64;&＃39;localhost&＃39; (using password: NO)
2017-07-31T10:34:40.233539Z 0 [Note] Shutting down plugin &＃39;validate_password&＃39;
2017-07-31T10:34:41.483983Z 0 [Note] Shutting down plugin &＃39;sha256_password&＃39;
2017-07-31T10:34:41.483990Z 0 [Note] Shutting down plugin &＃39;mysql_native_password&＃39;
mysql root的初始密码为TjcrBm.j,7eU(每人情况不一样&＃xff0c;具体值在mysql日志中查看)
mysql密码重置
当然有些人可能是root密码真忘记了&＃xff0c;那样可以通过mysql免密码登陆
在其配置文件/etc/my.cnf中加入skip-grant-tables&＃61;1即可
[root&＃64;bogon ~]# grep -v ^# /etc/my.cnf | grep -v ^$
[mysqld]
datadir&＃61;/var/lib/mysql
socket&＃61;/var/lib/mysql/mysql.sock
symbolic-links&＃61;0
log-error&＃61;/var/log/mysqld.log
pid-file&＃61;/var/run/mysqld/mysqld.pid
skip-grant-tables&＃61;1
然后重启mysql&＃xff0c;使用mysql命令即可进入
[root&＃64;bogon ~]# systemctl restart mysqld
[root&＃64;bogon ~]# ss -tnl
State 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&＃64;bogon ~]# mysql
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.19
Copyright (c) 2000, 2017, 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 &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.
mysql>
修改mysql 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> update user set authentication_string &＃61; password("123456") where user&＃61;"root";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
然后将/etc/my.cnf中的skip-grant-tables&＃61;1注释掉&＃xff0c;重启mysql服务即可。
在此要注意的是&＃xff0c;之前版本密码修改字段为password&＃xff0c;在5.7版本之后字段为authentication_string
mysql密码难度修改
mysql密码修改为123456之后&＃xff0c;有人发现使用123456能进入mysql&＃xff0c;但是却不能使用mysql的任何功能。会出现如下情况:
[root&＃64;bogon ~]# mysql -uroot -p123456
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.19
Copyright (c) 2000, 2017, 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 &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; 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.
实际上&＃xff0c;在初始更改root密码时&＃xff0c;并不能直接使用update来更改&＃xff0c;需要使用alter user命令来更改
mysql> alter user &＃39;root&＃39;&＃64;&＃39;localhost&＃39; identified by &＃39;123456&＃39;;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
## mysql在5.7版本中加了密码安全等级&＃xff0c;弱密码不能使用
mysql> alter user &＃39;root&＃39;&＃64;&＃39;localhost&＃39; identified by &＃39;QWEqwe123!&＃64;#&＃39;;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql 安全策略以及root密码清空
在上述修改密码之后&＃xff0c;可查看mysql的密码安全策略
[root&＃64;bogon ~]# mysql -uroot -p&＃39;QWEqwe123!&＃64;#&＃39;
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 6
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.
mysql> show variables like &＃39;%password%&＃39;;
&＃43;---------------------------------------&＃43;--------&＃43;
| Variable_name | Value |
&＃43;---------------------------------------&＃43;--------&＃43;
| 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 |
&＃43;---------------------------------------&＃43;--------&＃43;
14 rows in set (0.00 sec)
其中可以看到validate有很多参数
validate_password_dictionary_file&＃xff1a;插件用于验证密码强度的字典文件路径。
validate_password_length&＃xff1a;密码最小长度。
validate_password_mixed_case_count&＃xff1a;密码至少要包含的小写字母个数和大写字母个数。
validate_password_number_count&＃xff1a;密码至少要包含的数字个数。
validate_password_policy&＃xff1a;密码强度检查等级&＃xff0c;0/LOW、1/MEDIUM、2/STRONG。
validate_password_special_char_count&＃xff1a;密码至少要包含的特殊字符数。
其中&＃xff0c;关于validate_password_policy-密码强度检查等级&＃xff1a;
0/LOW&＃xff1a;只检查长度。
1/MEDIUM&＃xff1a;检查长度、数字、大小写、特殊字符。
2/STRONG&＃xff1a;检查长度、数字、大小写、特殊字符字典文件。
可以直接在mysql中进行参数的修改
mysql> set global validate_password_policy&＃61;0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_special_char_count&＃61;0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_number_count&＃61;0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_mixed_case_count&＃61;0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length&＃61;0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like &＃39;%password%&＃39;;
&＃43;---------------------------------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;---------------------------------------&＃43;-------&＃43;
| 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 |
&＃43;---------------------------------------&＃43;-------&＃43;
14 rows in set (0.00 sec)
当然&＃xff0c;此种变更&＃xff0c;只能在当前环境下生效&＃xff0c;重启mysql后会失效
想要长期生效&＃xff0c;需要将更改写入配置文件中才可永久生效
也可以直接在配置文件中进行修改禁用validate-password功能
编辑my.cnf配置文件&＃xff0c;在mysqld下面加入“validate-password&＃61;0”,然后重启mysql即可。
[root&＃64;bogon ~]# grep -v ^# /etc/my.cnf | grep -v ^$
[mysqld]
datadir&＃61;/var/lib/mysql
socket&＃61;/var/lib/mysql/mysql.sock
validate-password&＃61;0
symbolic-links&＃61;0
log-error&＃61;/var/log/mysqld.log
pid-file&＃61;/var/run/mysqld/mysqld.pid
将mysql的密码安全等级降低之后&＃xff0c;可以将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> update user set authentication_string &＃61; password("") where user&＃61;"root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
以上密码root即清除为空了。使用mysql即可直接进入mysql。当然生产环境中不推荐此种做法
京公网安备 11010802041100号