云服务器 Linux MySQL 误删除授权表 root 记录

  • A+
所属分类:MySQL
高性能企业级服务器首台5折

问题现象


数据库 MySQL 的 user 授权表默认有三条 root 账号记录,如图:

如操作误删除,同时没有正确插入,再次登陆 MySQL 会报错如下:


1
ERROR 1045 (28000): Access denied <span class="hljs-keyword">for</span> user <span class="hljs-string">'root'</span>@<span class="hljs-string">'localhost'</span> (using password: YES)

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


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

处理办法


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

1、修改 MySQL 配置文件启动到安全模式下:


1
<span class="hljs-comment"># vi /etc/my.cnf </span>

注意:以实际 MySQL 配置文件 my.cnf 路径为准。

在[mysqld]的段中加上一句:skip-grant-tables 

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


1
<span class="hljs-comment"># /etc/init.d/mysqld restart </span>

2、登陆 MySQL ,并重新插入


1
2
<span class="hljs-comment">#/usr/bin/mysql</span>
<span class="hljs-comment">#use mysql;</span>

密码用password函数加密,比如密码为“aliyun“:


1
<span class="hljs-comment">#mysql&gt; select password('aliyun');</span>


1
<span class="hljs-comment">#mysql&gt; 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","*2411E566C2C7E14C6946F80619D4BE802887A4DB","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");</span>


1
2
<span class="hljs-comment">#flush privileges;</span>
<span class="hljs-comment">#quit</span>

3、打开 MySQL 配置文件,注释“skip-grant-tables” ,保存并退出。

重启 MySQL 后测试登陆正常:


1
2
<span class="hljs-comment">#/etc/init.d/mysqld restart </span>
<span class="hljs-comment">#mysql -uroot -paliyun</span>

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: