MySQL【Update误操作】回滚

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql5.7 binlog
 
/*!*/;
# at 15937710
# at 15937814
#170526 13:00:15 server id 1  end_log_pos 15938129 CRC32 0x06901892   Table_map: `service`.`apply` mapped to number 108
# at 15938129
#170526 13:00:15 server id 1  end_log_pos 15938780 CRC32 0x9cf36fca   Update_rows: table id 108 flags: STMT_END_F
### UPDATE `service`.`apply`
### WHERE
###   @1='l4cnjqu7tenbe'
###   @2='l4b4jkdo1hku1'
### SET
###   @1='l4cnjqu7tenbe'
###   @2='l4b4jkdo1hku1'
 
# at 15938780
#170526 13:00:15 server id 1  end_log_pos 15938811 CRC32 0xadd8095d   Xid = 162625692
COMMIT/*!*//*!*/;
# at 16786556
# at 16786658
#170526 13:01:02 server id 1  end_log_pos 16786973 CRC32 0xe3c07d87   Table_map: `service`.`apply` mapped to number 108
# at 16786973
#170526 13:01:02 server id 1  end_log_pos 16787632 CRC32 0xcea6eab7   Update_rows: table id 108 flags: STMT_END_F
### UPDATE `service`.`apply`
### WHERE



注意红色字体。

flush table with read lock锁住主库  这点最为重要。


可用的方法

1.MySQL单表恢复方法

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

从过滤掉这一条语句,后同步

2.记一次MySQL删库的数据恢复

http://blog.csdn.net/gzlaiyonghao/article/details/53340475

ibdata1 文件恢复

3.MySQL 误操作后数据恢复(update,delete忘加where条件)

sed脚本需要看懂。

最好参考 http://www.cnblogs.com/gomysql/p/3582058.html  

4.MySQL【Update误操作】回滚

http://www.cnblogs.com/zhoujinyi/archive/2012/12/26/2834897.html

这个案例最佳。

5.案例 - 误删千万的表   

http://dadaman.blog.51cto.com/11373912/1933137

这个案例工具不错


还需要待更新


恢复方法,比较繁琐,实际效果不好,会丢失数据。

1.flush table with read lock锁住主库 

如果不锁库,后面的数据在不断的更新,即使你恢复到故障点,也要关注后续的数据变化。比如一个字段。

2.copy binlog

3.恢复到12点

4.生产建立temp表

5.然后通过code,更新状态

6.查找12点后执行的update语句,找出来,更新这一个字段,因为数据在一直变动。


具体操作

1.恢复到故障前的状态,比如12点 grep -B 15 'type'

2.但是注意,如果是where很细的条件,就是@带数字,所以查起来要小心


操作命令

mysqlbinlog --base64-output=DECODE-ROWS -v --start-datetime="2017-05-25 23:30:00" --stop-datetime="2017-05-26 11:36:59" mysql-bin.xx|grep -B 15 'type'

或者

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.xx|grep -B 15 'type'


使用的脚本


先来看看mysql binlog update的语句样子,mysql版本5.7

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/ * ! * / ;
# at 15937710
# at 15937814
#170526 13:00:15 server id 1  end_log_pos 15938129 CRC32 0x06901892   Table_map: `a`.`apply` mapped to number 108
# at 15938129
#170526 13:00:15 server id 1  end_log_pos 15938780 CRC32 0x9cf36fca   Update_rows: table id 108 flags: STMT_END_F
### UPDATE `a`.`apply`
### WHERE
###   @1='l4cnjqu7tenbe'
###   @2='l4b4jkdo1hku1'
###   @3='xxx'
###   @4='151226'
###   @5='2017-05-26 11:29:42'
......(如果字段很多的话,还有更多)
### SET
###   @1='l4cnjqu7tenbe'
###   @2='l4b4jkdo1hku1'
###   @3='xxx'
###   @4='151226'
###   @5='2017-05-26 11:29:42'
.......



python版本

可以参考http://wangwei007.blog.51cto.com/68019/1306940

python写的分析mysql binlog日志工具


# !/usr/bin/env python

# coding:utf-8

import os,sys,re

import logging

logging.basicConfig(level=logging.DEBUG,

                format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',

                datefmt='%a, %d %b %Y %H:%M:%S',

                filename='myapp.log',

                filemode='w')


listo=[]

pattern=r'^BEGIN.*?[^(BEGIN|COMMIT)]*COMMIT.*?;$'

patterf=r"/\*!\*/[\w\W]*?/\*!\*/"

patterdc=r"###   @58=[^\s]+"

fname='/home/back/binlog/test1.sql'

dname='/home/back/binlog/test2.sql'

fp = file(fname)

content = fp.read()

#fp.close()

db_key = re.findall(patterf,content)

for i in db_key:

    if 'UPDATE' in i and 'xx' in i and '@58' in i:

        #logging.info('%s' %i)

        dc=re.findall(patterdc,i)

        if dc:

            if len(dc)> 0:

               print len(dc)

               listo.append(i)

print len(listo)

with open(dname,'w+') as f:

    for i in listo:

        f.write(i)

fp.close()



shell脚本

'''

# vim summarize_binlogs.sh

#!/bin/bash

BINLOG_FILE="mysqld-bin.000035"

START_TIME="2017-05-16 13:30:00"

STOP_TIME="2017-05-18 14:00:00"

mysqlbinlog --base64-output=decode-rows -vv --start-datetime="${START_TIME}"  --stop-datetime="${STOP_TIME}" ${BINLOG_FILE} | awk \

'BEGIN {s_type=""; s_count=0;count=0;insert_count=0;update_count=0;delete_count=0;flag=0;} \

{if(match($0, /#15.*Table_map:.*mapped to number/)) {printf "Timestamp : " $1 " " $2 " Table : " $(NF-4); flag=1} \

else if (match($0, /(### INSERT INTO .*..*)/)) {count=count+1;insert_count=insert_count+1;s_type="INSERT"; s_count=s_count+1;}  \

else if (match($0, /(### UPDATE .*..*)/)) {count=count+1;update_count=update_count+1;s_type="UPDATE"; s_count=s_count+1;} \

else if (match($0, /(### DELETE FROM .*..*)/)) {count=count+1;delete_count=delete_count+1;s_type="DELETE"; s_count=s_count+1;}  \

else if (match($0, /^(# at) /) && flag==1 && s_count>0) {print " Query Type : "s_type " " s_count " row(s) affected" ;s_type=""; s_count=0; }  \

else if (match($0, /^(COMMIT)/)) {print "[Transaction total : " count " Insert(s) : " insert_count " Update(s) : " update_count " Delete(s) : " \

delete_count "] \n+----------------------+----------------------+----------------------+----------------------+"; \

count=0;insert_count=0;update_count=0; delete_count=0;s_type=""; s_count=0; flag=0} } '

:wq

# chmod u+x summarize_binlogs.sh



Q1 : Which tables received highest number of insert/update/delete statements?

./summarize_binlogs.sh | grep Table |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr

      3 `sakila`.`payment_tmp`

      3 `sakila`.`country`

      2 `sakila`.`city`

      1 `sakila`.`address`


Q2 : Which table received the highest number of DELETE queries?

./summarize_binlogs.sh | grep -E 'DELETE' |cut -d':' -f5| cut -d' ' -f2 | sort | uniq -c | sort -nr

      2 `sakila`.`country`

      1 `sakila`.`payment_tmp`

      1 `sakila`.`city`

      1 `sakila`.`address`

Q3: How many insert/update/delete queries executed against sakila.country table?

./summarize_binlogs.sh | grep -i '`sakila`.`country`' | awk '{print $7 " " $11}' | sort -k1,2 | uniq -c

      2 `sakila`.`country` DELETE

      1 `sakila`.`country` INSERT


Q4: Give me the top 3 statements which affected maximum number of rows.

./summarize_binlogs.sh | grep Table | sort -nr -k 12 | head -n 3

Timestamp : #150116 13:42:13 Table : `sakila`.`payment_tmp` Query Type : INSERT 16049 row(s) affected

Timestamp : #150116 13:42:28 Table : `sakila`.`payment_tmp` Query Type : UPDATE 6890 row(s) affected

Timestamp : #150116 13:42:20 Table : `sakila`.`payment_tmp` Query Type : DELETE 5001 row(s) affected


Q5 : Find DELETE queries that affected more than 1000 rows.

./summarize_binlogs.sh | grep -E 'DELETE' | awk '{if($12>1000) print $0}'

Timestamp : #150116 13:42:20 Table : `sakila`.`payment_tmp` Query Type : DELETE 5001 row(s) affected

If we want to get all queries that affected more than 1000 rows.


./summarize_binlogs.sh | grep -E 'Table' | awk '{if($12>1000) print $0}'

Timestamp : #150116 13:42:13 Table : `sakila`.`payment_tmp` Query Type : INSERT 16049 row(s) affected

Timestamp : #150116 13:42:20 Table : `sakila`.`payment_tmp` Query Type : DELETE 5001 row(s) affected

Timestamp : #150116 13:42:28 Table : `sakila`.`payment_tmp` Query Type : UPDATE 6890 row(s) affected


'''


反思

1.审核平台

2.流程,svn钩子的状态回归,与脚本关联



本文转自 liqius 51CTO博客,原文链接:http://blog.51cto.com/szgb17/1929921,如需转载请自行联系原作者

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4月前
|
SQL 监控 关系型数据库
MySQL怎么全局把一张表的数据回滚
MySQL怎么全局把一张表的数据回滚
354 2
|
9月前
|
SQL 存储 关系型数据库
轻松入门MySQL:深入理解MySQL日志,二进制日志、中继日志、回滚日志和重做日志(19)
轻松入门MySQL:深入理解MySQL日志,二进制日志、中继日志、回滚日志和重做日志(19)
750 0
|
9月前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
134 0
|
1天前
|
存储 SQL 关系型数据库
MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日志redo log
MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日志redo log、原理、写入过程;binlog与redolog区别、update语句的执行流程、两阶段提交、主从复制、三种日志的使用场景;查询日志、慢查询日志、错误日志等其他几类日志
MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日志redo log
|
6月前
|
SQL 关系型数据库 MySQL
【MySQL】根据binlog日志获取回滚sql的一个开发思路
【MySQL】根据binlog日志获取回滚sql的一个开发思路
|
8月前
|
存储 关系型数据库 MySQL
|
3月前
|
SQL 关系型数据库 MySQL
mysql数据误删后的数据回滚
【11月更文挑战第1天】本文介绍了四种恢复误删数据的方法:1. 使用事务回滚,通过 `pymysql` 库在 Python 中实现;2. 使用备份恢复,通过 `mysqldump` 命令备份和恢复数据;3. 使用二进制日志恢复,通过 `mysqlbinlog` 工具恢复特定位置的事件;4. 使用延迟复制从副本恢复,通过停止和重启从库复制来恢复数据。每种方法都有详细的步骤和示例代码。
664 2
|
4月前
|
SQL 关系型数据库 MySQL
MySQL异常一之: You can‘t specify target table for update in FROM clause解决办法
这篇文章介绍了如何解决MySQL中“不能在FROM子句中指定更新的目标表”(You can't specify target table for update in FROM clause)的错误,提供了错误描述、需求说明、错误做法和正确的SQL写法。
1136 0
|
5月前
|
SQL 监控 关系型数据库
MySQL数据库中如何检查一条SQL语句是否被回滚
检查MySQL中的SQL语句是否被回滚需要综合使用日志分析、事务状态监控和事务控制语句。理解和应用这些工具和命令,可以有效地管理和验证数据库事务的执行情况,确保数据的一致性和系统的稳定性。此外,熟悉事务的ACID属性和正确设置事务隔离级别对于预防数据问题和解决事务冲突同样重要。
142 2
|
6月前
|
关系型数据库 MySQL Shell
MySQL回滚脚本: 误操作delete binlog回滚shell脚本
MySQL回滚脚本: 误操作delete binlog回滚shell脚本