MySQL各个版本root账号没有最高权限的解决方法

一、详细报错

---

ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)

报错原因（分析过程）：

---

root@localhost用户密码修改导致

解决方法：

---

跳过权限验证启动数据库，并修改密码。如下操作包含MySQL5.6.51、MySQL5.7.36、MySQL8.0.26，不同版本的操作指令细节有差别。

二、8.0.26版本的MySQL数据库

1、配置文件中增加skip-grant-tables参数

2、重启mysql实例

ps -ef |grep mysqld 
kill 进程
然后启动数据库
mysqld --defaults-file=/etc/my.cnf &

3、调整root用户密码

3.1 方法一：

root@localhost : (none) 11:38:37> alter user root@localhost identified by '新密码'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement root@localhost : (none) 11:38:49> flush privileges; 
Query OK, 0 rows affected (0.04 sec)root@localhost : (none) 11:38:55> alter user root@localhost identified by '新密码';
Query OK, 0 rows affected (0.01 sec)root@localhost : (none) 11:38:59> alter user root@'%' identified by '新密码'; 
Query OK, 0 rows affected (0.01 sec)root@localhost : (none) 11:39:13> flush privileges; 
Query OK, 0 rows affected (0.01 sec)

3.2 方法二(此版本PASSWORD函数已经废弃)：

root@localhost : (none) 13:57:55> update mysql.user set authentication_string='' where user='root';Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0root@localhost : (none) 13:58:03> select user,host,authentication_string from mysql.user; 
| user | host | authentication_string |
+------------------+-----------+-----+
| root | % | |
| root | localhost | |root@localhost : (none) 13:59:10> flush privileges;root@localhost : (none) 13:59:42> alter user root@localhost identified by '新密码'; 
Query OK, 0 rows affected (0.01 sec)root@localhost : (none) 14:03:24> alter user root@'%' identified by '新密码'; 
Query OK, 0 rows affected (0.01 sec)root@localhost : (none) 14:03:54> flush privileges;

4、清除skip-grant-tables参数，重新启动数据库实例。

配置文件中删除skip-grant-tables参数然后然后启动数据库
mysqld --defaults-file=/etc/my.cnf &

三、5.7.36版本的MySQL数据库

1、配置文件中增加skip-grant-tables参数

2、重启mysql实例

ps -ef |grep mysqld 
kill 进程然后启动数据库
mysqld --defaults-file=/etc/my.cnf &

3、调整root用户密码

3.1 方法一：

root@localhost : (none) 15:57:14> alter user root@'%' identified by '新密码';ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement root@localhost : (none) 15:57:23> flush privileges;
Query OK, 0 rows affected (0.04 sec)root@localhost : (none) 15:57:31> alter user root@'%' identified by '新密码'; 
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statementroot@localhost : (none) 15:57:36> set global super_read_only=off;
Query OK, 0 rows affected (0.00 sec)root@localhost : (none) 15:58:18> show variables like '%read_only%'; 
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | OFF |root@localhost : (none) 15:58:08> alter user root@'%' identified by '新密码'; 
Query OK, 0 rows affected (0.03 sec)root@localhost : (none) 15:58:30> alter user root@'localhost' identified by '新密码'; 
Query OK, 0 rows affected (0.01 sec)root@localhost : (none) 15:58:15> flush privileges;
Query OK, 0 rows affected (0.01 sec)

4.2 方法二：

root@localhost : (none) 10:25:23> update mysql.user set authentication_string=PASSWORD('新密码') where user='root';
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statementroot@localhost : (none) 10:25:39> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | ON |
+-----------------------+-------+
5 rows in set (0.01 sec)root@localhost : (none) 10:26:04> set global super_read_only=off;
Query OK, 0 rows affected (0.00 sec)root@localhost : (none) 12:02:25> update mysql.user set authentication_string=PASSWORD('新密码') where user='root';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 2 Changed: 1 Warnings: 1Warning (Code 1681): 'PASSWORD' is deprecated and will be removed in a future release.
root@localhost : (none) 12:02:58> flush privileges;
Query OK, 0 rows affected (0.04 sec)

4、清除skip-grant-tables参数，重新启动数据库实例。

配置文件中删除skip-grant-tables参数然后然后启动数据库
mysqld --defaults-file=/etc/my.cnf &

四、5.6.51 版本的MySQL数据库

1、配置文件中增加skip-grant-tables参数

2、重启mysql实例

ps -ef |grep mysqld 
kill 进程
然后启动数据库
mysqld --defaults-file=/etc/my.cnf &

3、调整root用户密码

root@localhost : (none) 11:04:24> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('新密码');ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
root@localhost : (none) 11:05:43> flush privileges;
Query OK, 0 rows affected (0.03 sec)root@localhost : (none) 11:06:12> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('新密码');
Query OK, 0 rows affected (0.03 sec)root@localhost : (none) 11:06:12> SET PASSWORD FOR 'root'@'%' = PASSWORD('新密码');
Query OK, 0 rows affected (0.03 sec)root@localhost : (none) 11:06:21> show variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| tx_read_only | OFF |
+------------------+-------+
3 rows in set (0.00 sec)root@localhost : (none) 11:07:13> flush privileges;

4、清除skip-grant-tables参数，重新启动数据库实例，页面显示实例正常。

配置文件中删除skip-grant-tables参数然后然后启动数据库
mysqld --defaults-file=/etc/my.cnf &