MySQL入门03：MySQL修改root密码的方法

Oracle数据库技术 2021-04-17

1745

编者按：

纸上得来终觉浅，元知此事要躬行。

【免责声明】本公众号文章仅代表个人观点，与任何公司无关。

目录结构：MySQL修改root密码的方法

知道密码的情况

1.使用ALTER USER命令修改密码
2.使用SET PASSWORD命令修改密码

忘记密码的情况

使用--init-file
使用--skip-grant-tables

常见问题及解决

降低策略的级别
单独修改策略项
密码没有加上引号
ERROR 1819 (HY000)
ERROR 1396 (HY000)
ERROR 1064 (42000)
使用--skip-grant-tables --user=mysql选项启动出错：Permission denied

MySQL修改root密码的方法

作为守护数据库安全的第一道关卡是root账户及其密码。
学会对root密码管理无疑是守门人和开锁人的一项基础技能。

知道密码的情况

在已知密码的情况下，可以登录MySQL数据库后，通过ALTER USER(MySQL 5.7.6以上版本)或者SET PASSWORD命令进行修改。

1.使用ALTER USER命令修改密码

在MySQL 5.7.6或则MariaDB 10.1.20以上的版本可以使用ALTER USER命令修改用户密码

例：

--登录mysql-bash-4.1$ mysql -u root -pmysql> alter user root  identified by 'MyNewPass4!';Query OK, 0 rows affected (0.02 sec)

2.使用SET PASSWORD命令修改密码

使用SET PASSWORD命令修改Mysql的用户密码。

MySQL 5.7以后版本：

例：

SET PASSWORD FOR <用户名>@<host名> = 'my_new_password';--当前用户mysql> SET PASSWORD = 'my_new_password';Query OK, 0 rows affected (0.33 sec)

MySQL 5.7以前版本需要PASSWORD()函数。

例：

mysql> SET PASSWORD = PASSWORD('new_password');

参考：
https://dev.mysql.com/doc/refman/8.0/en/alter-user.html

13.7.1.1 ALTER USER Statement
https://dev.mysql.com/doc/refman/8.0/en/set-password.html
13.7.1.10 SET PASSWORD Statement

忘记密码的情况

在不知道root密码（密码丢失）的情况下，可以通过如下方法登录mysql修改密码。

使用--init-file

可以通过创建一个包含密码语句本地文件，然后使用--init-file选项启动MySQL。

例：

创建一个本地文件

-bash-4.1$ vi refresh/home/init-file.txt

文件内容如下：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass2!';

确认文件内容：

-bash-4.1$ cat refresh/home/init-file.txtALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass2!';

关闭MySQL

-bash-4.1$ sudo service mysqld statusmysqld (pid  2031) is running...-bash-4.1$ sudo service mysqld stopStopping mysqld:                                           [  OK  ]-bash-4.1$ sudo service mysqld statusmysqld is stopped

Linux 7以后可以使用如下systemctl命令。

systemctl stop mysqld.service 或者/etc/init.d/mysqld stop

使用如下命令启动MySQL

   -bash-4.1$ sudo mysqld --user=mysql --init-file=/refresh/home/init-file.txt  &[1] 2255

通过新密码进行连接测试。
mysql -u root -p

使用--skip-grant-tables

可以使用使用--skip-grant-tables选项启动Mysql ,从而跳过权限验证登录MySQL后修改root密码。

关闭MySQL

-bash-4.1$ sudo service mysqld statusmysqld (pid  2031) is running...-bash-4.1$ sudo service mysqld stopStopping mysqld:                                           [  OK  ]-bash-4.1$ sudo service mysqld statusmysqld is stopped

Linux 7以后可以使用如下systemctl命令。

systemctl stop mysqld.service 或者/etc/init.d/mysqld stop

使用--skip-grant-tables启动MySQL

-bash-4.1$ sudo mysqld --skip-grant-tables --user=mysql &[1] 29840-bash-4.1$ mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7Server version: 8.0.23 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.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> flush privileges;Query OK, 0 rows affected (0.05 sec)

修改root密码

mysql> ALTER USER root identified by 'MyNewPass1!';Query OK, 0 rows affected (0.02 sec)

测试用新密码登录MySQL

-bash-4.1$ mysql -u root -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.23 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.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

-bash-4.1$ sudo service mysqld statusmysqld (pid  29841) is running...-bash-4.1$ sudo service mysqld stopStopping mysqld:                                           [  OK  ][1]+  Done                    sudo mysqld --skip-grant-tables --user=mysql-bash-4.1$ sudo service mysqld statusmysqld is stopped-bash-4.1$ sudo service mysqld startStarting mysqld:                                           [  OK  ]或者-bash-4.1$ sudo service mysqld restartStopping mysqld:                                           [  OK  ]Starting mysqld:                                           [  OK  ]

测试连接：

-bash-4.1$ mysql -u root -p

参考：
https://dev.mysql.com/doc/refman/8.0/en/server-options.html

5.1.7 Server Command Options

https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

B.3.3.2 How to Reset the Root Password
B.3.3.2.2 Resetting the Root Password: Unix and Unix-Like Systems
B.3.3.2.3 Resetting the Root Password: Generic Instructions

常见问题及解决

mysql> set password for root@localhost = password(‘123’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password(‘123’)' at line 1

密码没有加上引号

mysql> alter user root@localhost identified by pass;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pass' at line 1

ERROR 1819 (HY000)

修改密码时发生ERROR 1819 (HY000) 错误

mysql> alter user root@localhost identified by 'pass';ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

原因：密码不满足安全要求

MySQL数据库的高版本（5.7以后）默认会有validate_password插件，该插件启用后将强制实施密码验证策略。如果新密码不满足密码验证策略就会报ERROR 1819 (HY000) 错误。

级别	长度	字符类型	单词匹配
LOW	至少为8个字符	N/A	N/A
MEDIUM	同上	必须包含至少1个数字字符，1个小写字符，1个大写字符和1个特殊（非字母数字）字符	N/A
STRONG	同上	同上	必须与字典文件中的单词不匹配
参数(5.7)	validate_password_length	validate_password_number_count validate_password_mixed_case_count validate_password_special_char_count	validate_passwor
参数(8.0)	validate_password.length	validate_password.number_count validate_password.mixed_case_count validate_password.special_char_count	validate_password.dictionary_file

默认密码策略是MEDIUM。

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.14 sec)

解决方法：

降低策略的级别

--5.7mysql> SET GLOBAL validate_password_policy=LOW;ORmysql> SET GLOBAL validate_password_policy=0;--8.0mysql>  set global validate_password.policy=LOW;ORmysql> set global validate_password.policy=0;

例：

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.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             | LOW   || validate_password.special_char_count | 1     |+--------------------------------------+-------+7 rows in set (0.00 sec)

单独修改策略项

例：

mysql> alter user root  identified by 'pass';ERROR 1819 (HY000): Your password does not satisfy the current policy requirementsmysql> SET GLOBAL validate_password.length = 4;Query OK, 0 rows affected (0.00 sec)mysql> alter user root  identified by 'pass';Query OK, 0 rows affected (0.05 sec)mysql> SHOW VARIABLES LIKE 'validate_password%';+--------------------------------------+-------+| Variable_name                        | Value |+--------------------------------------+-------+| validate_password.check_user_name    | ON    || validate_password.dictionary_file    |       || validate_password.length             | 4     |  ★★| validate_password.mixed_case_count   | 1     || validate_password.number_count       | 1     || validate_password.policy             | LOW   || validate_password.special_char_count | 1     |+--------------------------------------+-------+7 rows in set (0.00 sec)

参考：
https://dev.mysql.com/doc/refman/8.0/en/validate-password.html

6.4.3 The Password Validation Component

ERROR 1396 (HY000)

mysql> alter user root@localhost identified by 'my_new_pass';ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

原因：
用户的host不一致。

mysql>  alter user root@localhost identified by 'my_new_pass';ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'mysql> show databases    -> ;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select user,host from user;+------------------+-----------+| user             | host      |+------------------+-----------+| root             | %         || mysql.infoschema | localhost || mysql.session    | localhost || mysql.sys        | localhost |+------------------+-----------+4 rows in set (0.00 sec)

解决方法:
修改host名或者去掉host名。

例：

mysql> alter user root@'%' identified by 'my_new_pass';Query OK, 0 rows affected (0.02 sec)或者mysql> alter user root identified by 'my_new_pass';Query OK, 0 rows affected (0.04 sec)

ERROR 1064 (42000)

mysql>  SET PASSWORD = PASSWORD('new_password');ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('new_password')' at line 1

原因：MySQL 5.7 以后不需要PASSWORD()函数。

例：

mysql>  SET PASSWORD = 'my_new_password';Query OK, 0 rows affected (0.02 sec)

使用--skip-grant-tables --user=mysql选项启动出错：Permission denied

-bash-4.1$ mysqld --skip-grant-tables --user=mysql &[1] 29806-bash-4.1$ 2021-02-26T00:10:32.691961Z 0 [Warning] [MY-010091] [Server] Can't create test file var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test2021-02-26T00:10:32.692079Z 0 [System] [MY-010116] [Server] usr/sbin/mysqld (mysqld 8.0.23) starting as process 298062021-02-26T00:10:32.698349Z 0 [Warning] [MY-010091] [Server] Can't create test file var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test2021-02-26T00:10:32.698375Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for var/lib/mysql/ is case insensitive2021-02-26T00:10:32.698711Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root2021-02-26T00:10:32.699063Z 0 [ERROR] [MY-010187] [Server] Could not open file '/var/log/mysqld.log' for error logging: Permission denied2021-02-26T00:10:32.699141Z 0 [ERROR] [MY-010119] [Server] Aborting2021-02-26T00:10:32.699366Z 0 [System] [MY-010910] [Server] usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23)  MySQL Community Server - GPL.[1]+  Exit 1                  mysqld --skip-grant-tables --user=mysql-bash-4.1$-bash-4.1$-bash-4.1$ mysqlERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

解决方法：使用sudo 或者root用户

例：

-bash-4.1$ sudo mysqld --skip-grant-tables --user=mysql &[1] 29840-bash-4.1$ mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7Server version: 8.0.23 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.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>

后续文章更加精彩，欢迎关注本公众号。

——End——

专注于技术不限于技术！

用碎片化的时间，一点一滴地提高数据库技术和个人能力。

欢迎关注！

MySQL相关：

手把手教你在Windows 10安装MySQL 8.0（详细图文）

MySQL入门：Linux 6 RPM方式安装MySQL 8.0

MySQL入门02：关于MySQL连接的ABC

MySQL 5.6认证考试将于2020年9月30日停考

又是一个时代进程，MySQL 5.6结束其生命周期（EOL）

适合MySQL小白的书：我的译作《MySQL基础教程》