修改密码报异常
mysql> update user set password=password("y1jhcfzx!") where user="root";
异常
[root@ddtest-mysql01 ~]# mysql -uroot -p
enter password:
error 1045 (28000): access denied for user 'root'@'localhost' (using password: yes)
1、修改my.cnf 跳过认证
添加 skip-grant-tables
[root@ddtest-mysql01 ~]# vi /etc/my.cnf
[mysqld]
port=3306
datadir=/mysqldata01/data
pid-file=/mysqldata01/mysql57.pid
socket=/mysqldata01/mysql57.sock
log-error=/mysqldata01/mysql57-err.log
user=mysql
skip-grant-tables
[client]
socket=/mysqldata01/mysql57.sock
2、重启
[root@ddtest-mysql01 ~]# /etc/init.d/mysqld restart
shutting down mysql... success!
starting mysql. success!
3、重置密码
[root@ddtest-mysql01 ~]# mysql
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 2
server version: 5.7.28 source distribution
copyright (c) 2000, 2019, 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> 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 mysql.user set password=password('y1jhcfzx!') where user='root';
query ok, 0 rows affected, 1 warning (0.00 sec)
rows matched: 4 changed: 0 warnings: 1
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
mysql> update mysql.user set authentication_string=password('y1jhcfzx!') where user='root';
query ok, 4 rows affected, 1 warning (0.00 sec)
rows matched: 4 changed: 4 warnings: 1
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
4、my.cnf 跳过认证去掉
把之前添加的skip-grant-tables 去掉或注释再重启,/etc/init.d/mysqld restart
[root@ddtest-mysql01 ~]# mysql -uroot -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 11
server version: 5.7.28 source distribution
copyright (c) 2000, 2019, 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>
mysql>
mysql> select user,host from user;
+-------+-------------+
| user | host |
+-------+-------------+
| root | 127.0.0.1 |
| root | ::1 |
| | dd-mysq01 |
| root | dd-mysq01 |
| | localhost |
| root | localhost |
+-------+-------------+
12 rows in set (0.00 sec)