目录
2.2 执行如下命令,将已知的user1的加密密文更新到root中
2.5 登录正常,但这会root登录进去后,无法执行具体的sql,只要有操作,会报如下的错:
时间久了,root密码已遗忘,只要普通用户的密码记得。
不幸中的幸运是,创建普通用户时,赋予普通用户的权限足够大。不然就得更改数据库配置,配置文件末尾添加skip-grant-tables属性修改了。那比较麻烦,网络上也有很多博客介绍
方式一:
- 停数据库服务
- 修改mysql.cnf文件,添加属性skip-grant-tables
- 启动数据库,root免密登陆
- 此时,alter user 'root'@'localhost' IDENTIFIED BY 'xxxxx' ;,更新root用户密码
- 停mysql服务
- 修改mysql.cnf文件,去掉属性skip-grant-tables
- 启动mysql服务。
root密码修改完成。
方式二:
2.1 也可以像我这样,普通用户登录进去后
SELECT Host, User,authentication_string FROM mysql.user ;
+-----------+---------------+-------------------------------------------+
| Host | User | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *1B3C2E06495E4EA09B0212594330B69A3FF6330B |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | user1 | *BE707F4785F1FD02DCF2BF85AED55095BE461D94 |
| % | user2 | *BE707F4785F1FD02DCF2BF85AED55095BE461D94 |
+-----------+---------------+-------------------------------------------+
2.2 执行如下命令,将已知的user1的加密密文更新到root中
# UPDATE mysql.user SET authentication_string='*BE707F4785F1FD02DCF2BF85AED55095BE461D94' WHERE User='root';
# FLUSH PRIVILEGES;
2.3 查询数据库
mysql> SELECT Host, User,authentication_string FROM mysql.user ;
+-----------+---------------+-------------------------------------------+
| Host | User | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *BE707F4785F1FD02DCF2BF85AED55095BE461D94 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | user1 | *BE707F4785F1FD02DCF2BF85AED55095BE461D94 |
| % | user2 | *BE707F4785F1FD02DCF2BF85AED55095BE461D94 |
+-----------+---------------+-------------------------------------------+
2.4 用root用户登录
2.5 登录正常,但这会root登录进去后,无法执行具体的sql,只要有操作,会报如下的错:
mysql> SELECT Host, User,authentication_string FROM mysql.user ;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SELECT * FROM mysql.user ;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
2.6 需要重新ALTRE下用户信息
mysql> shwo databases;
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 'shwo databases' at line 1
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' IDENTIFIED BY '嘻嘻嘻嘻嘻嘻嘻嘻' ;
Query OK, 0 rows affected (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
2.7 退不退出没关系,在重新执行sql命令时,已显示正常
# mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 156907
Server version: 5.7.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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;
+--------------------+
| Database |
+--------------------+
| information_schema |
| finance |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)