专栏首页数据库干货铺MySQL user表被删除了怎么办

MySQL user表被删除了怎么办

技术交流群总是能带来很多实际生产环境遇到的问题,例如,近期就有人遇到user表内容被清空的情况。如果发生了此情况,千万不要慌,更不能隐瞒问题(这位朋友就比较惨,别人删了也没敢告知,结果binlog已经清理了),以免不利于恢复。现在针对几种情况,进行恢复操作的演示。

01 user表被delete

如果有权限的同学误执行了情况mysql.user表内容的情况,如果是delete的方式还是相对容易恢复的(binlog存在的情况)

1.1 模拟误删除

/* 当前user表的内容 */mysql> select user,host from mysql.user;+---------------+--------------+| user          | host         |+---------------+--------------+| repl          | 192.168.28.% || mysql.session | localhost    || mysql.sys     | localhost    || root          | localhost    |+---------------+--------------+4 rows in set (0.00 sec)
mysql> select now();+---------------------+| now()               |+---------------------+| 2020-04-16 06:37:07 |+---------------------+1 row in set (0.00 sec)

现在执行误删除

mysql> delete from  mysql.user;Query OK, 4 rows affected (0.01 sec)
mysql>

1.2 恢复

当前情况下,如开启了binlog(生产环境一般都是开启了的),则可以从binlog恢复

注意如果有全部及其之后的binlog 则可以通过恢复备份并追加binlog的方式恢复数据(后续其他文章再专题介绍),本文基于无全备,仅有最近的日志情况下恢复(主要是为了使用binlog2sql工具)

1.2.1 先恢复root账号

因为删除后,其他用户无法重新连接数据库了,需要紧急恢复root账号,再做后续其他账号的恢复,恢复步骤为:

修改为跳过授权的模式,即在配置文件my.cnf文件中添加 skip-grant-tables

重启数据库

登录数据库,添加root@'localhost'账号(可以从相同版本数据库中导出一个root账号的sql语句恢复

/usr/local/mysql5.7/bin/mysqldump -uroot -p'123456'  -t --socket=/data/mysql3307/tmp/mysql.sock mysql user --where " 1=1 and  user='root' and host='localhost'" >1.sql

恢复数据

mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> INSERT INTO `user` VALUES ('localhost','root','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','Y','','','','',0,0,0,0,'mysql_native_password',password('123456'),'N',now(),NULL,'N');Query OK, 1 row affected (0.01 sec)

改为授权模式:将配置文件里的 skip-grant-tables 注释或删除

再次重启数据库即可用刚恢复的root账号登录了

mysql> select user,host from mysql.user;+------+-----------+| user | host      |+------+-----------+| root | localhost |+------+-----------+1 row in set (0.00 sec)

:如果清空数据或后续说的删除了user表后没有退出当前会话,就无需之前的修改参数及重启数据库了,可以直接先从其他实例中先恢复root账号。

1.2.2 安装binlog2sql

binlog2sql依赖python2.7及以上版本,且依赖包通过pip安装,对于python的升级及pip的安装可参考历史文章

升级python,就是这么简单

一分钟搞定pip安装

yum install -y git wgetgit clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sqlpip install -r requirements.txt -i https://pypi.tuna.tsinghua.edu.cn/simple

1.2.3 使用binlog2sql工具恢复数据

使用binlog2sql的闪回工具将删除解析处理生成一个insert的sql脚本

python binlog2sql.py --flashback  -h localhost -u root -p 123456 -d mysql -t user --start-file='mysql-bin.000002' --start-datetime='2020-04-16 06:37:07' --stop-datetime='2020-04-16 06:40:00' >mysql.sql

将数据导入mysql.user表即可。

注:关于binlog2log的其他用法请参考https://github.com/danfengcao/binlog2sql

02 user表被drop

2.1 模拟user 表被drop

mysql> drop table mysql.user;Query OK, 0 rows affected (0.01 sec)

2.2 恢复

2.2.1 恢复表结构

表结构的恢复比较简单,可以从其他相同版本的数据库里复制user表的建表语句,然后导入即可

CREATE TABLE `user` (  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',  `ssl_cipher` blob NOT NULL,  `x509_issuer` blob NOT NULL,  `x509_subject` blob NOT NULL,  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',  `authentication_string` text COLLATE utf8_bin,  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `password_last_changed` timestamp NULL DEFAULT NULL,  `password_lifetime` smallint(5) unsigned DEFAULT NULL,  `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  PRIMARY KEY (`Host`,`User`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

2.2.2 恢复数据

drop的方式删除数据后无法直接通过之前的binlog闪回恢复了,需要依赖于全备来恢复,关于备份恢复可以参考以下文章来恢复,此处不再赘述

mysql备份及恢复1

mysql物理备份及还原

03 小结

对于删除表后的恢复其实不止以上这些方式,另外还可以通过操作系统级别进行恢复,但各种恢复方式均和实际场景有关,希望大家也自行探索或多参与技术交流,提高自己的技能水平。

本文分享自微信公众号 - 数据库干货铺(database_gjc),作者:懂点IT的耿小厨

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL innodb表使用表空间ibd文件复制或迁移表

    MySQL InnoDB引擎的表通过拷贝物理文件来进行单表或指定表的复制,可以想到多种方式,今天测试其中2种:

    July
  • 从MySQL的ibtmp1文件太大说起

    测试环境机器磁盘空间不足的告警打破了下午的沉寂,一群人开始忙活着删数据。但是,不久前刚清理了一波数据,测试环境在没做压测的情况下不至于短短一个月不到就涨了200...

    July
  • MySQL到底能有多少个字段

    今天技术讨论群里 “一切随遇而安”同学看书时出现一个疑问,一个MySQL的表中到底可以有多少个字段?带着这个疑问,我们展开了探讨,也接着讨论了一个单字段长度的问...

    July
  • Ubuntu 下如何修改 MySQL 密码

    Ubuntu 下如何修改 MySQL 密码呢?搜集大致有以下几种方式、应该是比较常用的方法

    hedeqiang
  • 3D 图形学基础 (上)

    本文主要针对一些对3D有兴趣的同学,普及图形学知识,不涉及深入的技术探讨和样例介绍。对于不是从事相关开发的同学也能了解相关的知识。

    serena
  • Basemap系列教程:3D

    尽管很多人不喜欢3D地图,但是仍可以使用 Basemap 和 matplotlib mplot3d [注1] 工具绘制3D地图。

    bugsuse
  • 通过欧拉计划学习Rust编程(第13~16题)

    最近想学习Libra数字货币的MOVE语言,发现它是用Rust编写的,所以先补一下Rust的基础知识。学习了一段时间,发现Rust的学习曲线非常陡峭,不过仍有快...

    申龙斌
  • 通过欧拉计划学习Rust编程(第13~16题)

    最近想学习Libra数字货币的MOVE语言,发现它是用Rust编写的,所以先补一下Rust的基础知识。学习了一段时间,发现Rust的学习曲线非常陡峭,不过仍有快...

    MikeLoveRust
  • 2019-03-27 高效学习,快速变现:不走弯路的五大学习策略

    Seaborn Lee,一名会在 B 站直播写代码,会玩杂耍球、弹 Ukulele、极限健身、跑步、写段子、画画、翻译、写作、演讲、培训的程序员。喜欢用编程实现...

    Albert陈凯
  • python基础类型(一):字符串和列表

    注意到最后三个的单双引号是嵌套使用的,但是最后一个的使用方法是错误的,因为当我们混合使用两种引号时必须有一种用来划分字符串的边界,即在两边的引号不能出现在字符串...

    渔父歌

扫码关注云+社区

领取腾讯云代金券