近期就有人遇到user表内容被清空的情况。如果发生了此情况,千万不要慌,更不能隐瞒问题(今天这位朋友就比较惨,别人删了也没敢告知,结果binlog已经清理了),这样有利于恢复。现在针对几种情况,进行恢复操作的演示。
1. user表内容被清空
如果有权限的同学误执行了情况mysql.user表内容的情况,如果是delete的方式还是相对容易恢复的(binlog存在的情况)
1.1 模拟误删除
/*当前user表的内容*/mysql>selectuser,hostfrommysql.user;+---------------+--------------+|user|host|+---------------+--------------+|repl|192.168.28.%||mysql.session|localhost||mysql.sys|localhost||root|localhost|+---------------+--------------+4rowsinset(0.00sec) mysql>selectnow();+---------------------+|now()|+---------------------+|2020-04-1606:37:07|+---------------------+1rowinset(0.00sec)
现在执行误删除
mysql>deletefrommysql.user; QueryOK,4rowsaffected(0.01sec) 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.sockmysqluser--where"1=1anduser='root'andhost='localhost'">1.sql
恢复数据
mysql>usemysql; Readingtableinformationforcompletionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechanged mysql>INSERTINTO`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'); QueryOK,1rowaffected(0.01sec)
改为授权模式: 将配置文件里的skip-grant-tables 注释或删除
再次重启数据库即可用刚恢复的root账号登录了
mysql>selectuser,hostfrommysql.user;+------+-----------+|user|host|+------+-----------+|root|localhost|+------+-----------+1rowinset(0.00sec)
注: 如果清空数据或后续说的删除了user表后没有退出当前会话,就无需之前的修改参数及重启数据库了,可以直接先从其他实例中先恢复root账号。
1.2.2安装binlog2sql
binlog2sql依赖python2. 7 及以上版本,且依赖包通过pip安装,对于python的升级及pip的安装可参考历史文章
升级python,就是这么简单
一分钟搞定pip安装
yuminstall-ygitwget gitclonehttps://github.com/danfengcao/binlog2sql.git&&cdbinlog2sql pipinstall-rrequirements.txt-ihttps://pypi.tuna.tsinghua.edu.cn/simple
1.2.3 使用binlog2sql工具恢复数据
使用binlog2sql的闪回工具将删除解析处理生成一个insert的sql脚本
pythonbinlog2sql.py--flashback-hlocalhost-uroot-p123456-dmysql-tuser--start-file='mysql-bin.000002'--start-datetime='2020-04-1606:37:07'--stop-datetime='2020-04-1606:40:00'>mysql.sql
将数据导入mysql.user表即可。
注: 其他用法请参考https://github.com/danfengcao/binlog2sql
2. user 表被drop
2.1 模拟user 表被drop
mysql>droptablemysql.user; QueryOK,0rowsaffected(0.01sec)
2.2 恢复
2.2.1 恢复表结构
表结构的恢复比较简单,可以从其他相同版本的数据库里复制user表的建表语句,然后导入即可
CREATETABLE`user`( `Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'', `User`char(32)COLLATEutf8_binNOTNULLDEFAULT'', `Select_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Insert_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Update_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Delete_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Create_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Drop_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Reload_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Shutdown_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Process_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `File_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Grant_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `References_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Index_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Alter_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Show_db_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Super_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Create_tmp_table_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Lock_tables_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Execute_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Repl_slave_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Repl_client_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Create_view_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Show_view_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Create_routine_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Alter_routine_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Create_user_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Event_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Trigger_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `Create_tablespace_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `ssl_type`enum('','ANY','X509','SPECIFIED')CHARACTERSETutf8NOTNULLDEFAULT'', `ssl_cipher`blobNOTNULL, `x509_issuer`blobNOTNULL, `x509_subject`blobNOTNULL, `max_questions`int(11)unsignedNOTNULLDEFAULT'0', `max_updates`int(11)unsignedNOTNULLDEFAULT'0', `max_connections`int(11)unsignedNOTNULLDEFAULT'0', `max_user_connections`int(11)unsignedNOTNULLDEFAULT'0', `plugin`char(64)COLLATEutf8_binNOTNULLDEFAULT'mysql_native_password', `authentication_string`textCOLLATEutf8_bin, `password_expired`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', `password_last_changed`timestampNULLDEFAULTNULL, `password_lifetime`smallint(5)unsignedDEFAULTNULL, `account_locked`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N', PRIMARYKEY(`Host`,`User`) )ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='Usersandglobalprivileges'
2.2.2 恢复数据
drop的方式删除数据后无法直接通过之前的binlog闪回恢复了,需要依赖于全备来恢复,关于备份恢复可以参考以下文章来恢复,此处不再赘述
mysql备份及恢复1
mysql物理备份及还原
3. 小结
对于删除表后的恢复其实不止以上这些方式,另外还可以通过操作系统级别进行恢复,但各种恢复方式均和实际场景有关,希望大家也自行探索或多参与技术交流,提高自己的技能水平。
本文转载自微信公众号【数据库干货铺】。
(举报)