MySQL单表恢复步骤详解

点击上方关注“Java后端技术栈

午休正酣,一个电话打散了睡意。电话那头的第一句话便是“开发童鞋写update SQL的时候忘了加where条件了!”相信每一名DBA,听到这个消息,心里都会有一种想骂街的冲动!万幸的是,只是单表写花了,而不是哪位大神在DB里面玩drop table。虽然已经很久没进行单表恢复了,但大致步骤都还在脑海中,没花多久就搞定了~

言归正传,记录一下单表恢复的步骤和关键点,提醒自己也提醒大家。

第一步:

找一台性能比较高的服务器作为还原机,从备份池中将最近的一次备份恢复到这台还原机上。当然这个前提是你有备份,且备份是可用的。(什么?你告诉我没有做备份,那么同学你可以洗洗睡了!)

注意:这个时候不要启动同步,务必保持不同步状态。

ps:对于DBA来说,备份是最重要的一个环节,不但要有,还要定期检查备份是否是可用的,这是DBA的必要素质之一。

第二步:

联系那个犯错的开发同学要错误的SQL语句和时间点,然后从主库的binlog中找到这条SQL的执行点。具体操作举例如下:

### 使用mysqlbinlog将二进制日志转化为明文SQL日志
mysqlbinlog mysql-bin.000123 > /data1/000123.sql

### 使用linux的grep命令根据“key word”找到那个引发数据写花的SQL所在的位置
cat 000123.sql |grep -C 10 'key word' --color

### 标红色的是问题SQL,及这条SQL开始的时间点,及下一条SQL的开始时间点,这2个pos位置非常重要
# at 20393709
#131205 20:55:08 server id 18984603  end_log_pos 20393779     Query    thread_id=16296016    exec_time=0    error_code=0
SET TIMESTAMP=1386248108/*!*/;
BEGIN
/*!*/;
# at 20393779
#131205 20:55:08 server id 18984603  end_log_pos 20394211     Query    thread_id=16296016    exec_time=0    error_code=0
SET TIMESTAMP=1386248108/*!*/;
update table tablename set names='xxxx';
# at 20394211
#131205 20:55:08 server id 18984603  end_log_pos 20394238     Xid = 92465981
COMMIT/*!*/;
# at 20394238
#131205 20:55:10 server id 18984603  end_log_pos 20394308     Query    thread_id=16296017    exec_time=0    error_code=0
SET TIMESTAMP=1386248110/*!*/;
BEGIN

第三步:

根据第二步得到的pos位置,启动同步关系,但是需要停止到问题SQL之前的pos位置上,具体使用如下命令:

### pos位置等于问题SQL begin的pos位置slave start until master_log_file='mysql-bin.000123',master_log_pos=20393709;

然后跳过这个问题SQL,将同步change到下一个pos位置上,具体使用如下命令:

### pos位置等于问题SQL commit之后的pos位置change master to master_log_file='mysql-bin.000123',master_log_pos=20394238;

从上面两条命令我们看出,第二步得到的两个pos位置很关键。

第四步:

在主库上将写花的表改名,其目的有二个,其一,停止对这个表的写入(当然这对业务会有一定的影响,会出现一段时间内的写入失败报警,需要提前和业务部门联系好),其二,一旦恢复失败,至少还有一个写花的表存在,可以很快的恢复成我们恢复操作之前的状态。

### 在主库上执行
rename table tablename to tablename_bak;

然后在还原机上执行dump操作,执行这个操作的使用需要注意,如果表中有中文字符,一定记得需要添加--default-chararter-set参数

mysqldump -uusername -ppassword -S/tmp/mysql.sock dbname tablename --opt> tablename.sql

最后将这个文件传输到主库服务器上,完成最后的恢复操作

### 可以选择登陆mysql之后
source tablename.sql;

### 也可以在cmd界面
mysql -uusername -ppassword -S/tmp/mysql.sock < tablename.sql

当然也可以不这么麻烦,直接执行如下命令,但是个人习惯处于保存历史操作记录和中间结果的需要,我都是按照以上步骤完成,这样可以保存一个随时可以恢复的文本文件,心理更踏实一点。

### 快速的方法可以按照如下操作,在还原机上的命令
mysql -uusername -ppassword -S /tmp/mysql.sock dbname tablename | mysql -uusername -ppassowrd -hhost -Pport

通过以上命令就可以直接完成dump并导入的操作了,缺点就是不会有一份备份文件生成。

第五步:

基本DBA的事情就没有了,这时候就需要告诉开发同学恢复完毕,进行应用测试及数据正确性效验了。如果一切都没有问题之后,我们需要将刚才rename的表drop掉,整个恢复操作就算大功告成了。

drop table if exists tablename_bak;

数据库恢复是每个DBA必备的技能,需要熟练掌握,希望读过这篇文章的同学们都可以轻车熟路的进行恢复操作。

ps:这个操作,最好一万年都不要用一次就最好了。

来源:billy鹏

https://www.cnblogs.com/billyxp/p/3460682.html

在这金三银四的季节,栈长为大家准备了几份面试宝典:

  • 《java面试宝典5.0》

  • 《Java(BAT)面试必备》

  • 《350道Java面试题:整理自100+公司》

  • 《资深java面试宝典-视频版》

  • 大量电子书籍

分别适用于初中级,中高级,以及资深级工程师的面试复习。

内容包含java基础、javaweb、各个性能优化、JVM、锁、高并发、反射、Spring原理、微服务、Zookeeper、数据库、数据结构、限流熔断降级等等。

获取方式:点“在看”,V信扫描上面二维码:注明面试领取,更多精彩陆续奉上。

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付 19.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值