Linux Mysql 误删除授权表root记录

比眉伴天荒 2022-08-19 12:14 106阅读 0赞

数据库mysql的user授权表默认有三条root账号记录，如图。  
![这里写图片描述][20160113174958501]

如操作误删除，同时没有正确插入，再次登陆mysql会报错如下：

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

启动到安全模式下重新授权也无法成功登陆，因安全模式已经跳过授权表，报错如下：

ERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement

可参考如下步骤处理，建议在操作前创建磁盘快照进行备份，避免操作失误后可以迅速恢复。

**1、修改mysql配置文件启动到安全模式下：**

命令：vi /etc/my.cnf  
在\[mysqld\]的段中加上一句：skip-grant-tables

保存并退出vi编辑器，重新启动mysqld

命令：/etc/init.d/mysqld restart

**2、登陆mysql，并重新插入**

命令：/usr/bin/mysql

命令：use mysql；

密码用password函数加密，比如密码为“123456”：

mysql> select password(‘123456);  
![这里写图片描述][20160113175636068]

mysql> insert into user (host,user,Password,Select\_priv,Insert\_priv,Update\_priv,Delete\_priv,Create\_priv,Drop\_priv,Reload\_priv,Shutdown\_priv,Process\_priv,File\_priv,Grant\_priv,References\_priv,Index\_priv,Alter\_priv,Show\_db\_priv,Super\_priv,Create\_tmp\_table\_priv,Lock\_tables\_priv,Execute\_priv,Repl\_slave\_priv,Repl\_client\_priv,Create\_view\_priv,Show\_view\_priv,Create\_routine\_priv,Alter\_routine\_priv,Create\_user\_priv,Event\_priv,Trigger\_priv,max\_questions,max\_updates,max\_connections,max\_user\_connections) values (“localhost”,”root”,”\*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”Y”,”0”,”0”,”0”,”0”);

命令：flush privileges;

命令：quit

**3、打开mysql配置文件，注释”skip-grant-tables” ,保存并退出**

重启mysql后测试登陆正常

命令：/etc/init.d/mysqld restart

命令：mysql -uroot -paliyun

[20160113174958501]: /images/20220731/5c445805674744bca19dae78d189e41c.png
[20160113175636068]: /images/20220731/42803cfc884245da8b17cce2652594c4.png