電子發(fā)燒友App
MySQL利用binlog恢復(fù)誤操作數(shù)據(jù)
在人工手動(dòng)進(jìn)行一些數(shù)據(jù)庫(kù)寫操作的時(shí)候（比方說(shuō)數(shù)據(jù)訂正），尤其是一些不可控的批量更新或刪除，通常都建議備份后操作。不過(guò)不怕萬(wàn)一，就怕一萬(wàn)，有備無(wú)患總是好的。在線上或者測(cè)試環(huán)境誤操作導(dǎo)致數(shù)據(jù)被刪除或者更新后，想要恢復(fù)，一般有兩種方法。
方法一、利用最近的全量備份+增量binlog備份，恢復(fù)到誤操作之前的狀態(tài)，但是隨著數(shù)據(jù)量的增大，binlog的增多，恢復(fù)起來(lái)很費(fèi)時(shí)。
方法二、如果binlog的格式為row，那么就可以將binlog解析出來(lái)生成反向的原始SQL
以下是利用方法二寫的一個(gè)python腳本binlog_rollback.py，可利用此腳本生成反向的原始SQL。
說(shuō)明：
0、前提是binlog的格式為row
1、要恢復(fù)的表操作前后表結(jié)構(gòu)沒(méi)有發(fā)生變更，否則腳本無(wú)法解析
2、只生成DML（insert/update/delete）的rollback語(yǔ)句
3、最終生成的SQL是逆序的，所以最新的DML會(huì)生成在輸入文件的最前面，并且?guī)狭藭r(shí)間戳和偏移點(diǎn)，方便查找目標(biāo)
4、需要提供一個(gè)連接MySQL的只讀用戶，主要是為了獲取表結(jié)構(gòu)
5、如果binlog過(guò)大，建議帶上時(shí)間范圍，也可以指定只恢復(fù)某個(gè)庫(kù)的SQL
6、SQL生成后，請(qǐng)務(wù)必在測(cè)試環(huán)境上測(cè)試恢復(fù)后再應(yīng)用到線上
腳本代碼
#！/bin/env python
# -*- coding:utf-8 -*-
import os，sys，re，getopt
import MySQLdb
host = ‘127.0.0.1’
user = ‘’
password = ‘’
port = 3306
start_datetime = ‘1971-01-01 00:00:00’
stop_datetime = ‘2037-01-01 00:00:00’
start_position = ‘4’
stop_position = ‘18446744073709551615’
database = ‘’
mysqlbinlog_bin = ‘mysqlbinlog -v’
binlog = ‘’
fileContent = ‘’
output=‘rollback.sql’
only_primary = 0
# ----------------------------------------------------------------------------------------
# 功能：獲取參數(shù)，生成相應(yīng)的binlog解析文件
# ----------------------------------------------------------------------------------------
def getopts_parse_binlog（）：
global host
global user
global password
global port
global fileContent
global output
global binlog
global start_datetime
global stop_datetime
global start_position
global stop_position
global database
global only_primary
try：
options， args = getopt.getopt（sys.argv［1：］， “f:o：h:u：p:P：d：”， ［“help”，“binlog=”，“output=”，“host=”，“user=”，“password=”，“port=”，“start-datetime=”， \
“stop-datetime=”，“start-position=”，“stop-position=”，“database=”，“only-primary=”］）
except getopt.GetoptError：
print “參數(shù)輸入有誤！！！！！”
options = ［］
if options == ［］ or options［0］［0］ in （“--help”）：
usage（）
sys.exit（）
print “正在獲取參數(shù)。..。.”
for name， value in options：
if name == “-f” or name == “--binlog”：
binlog = value
if name == “-o” or name == “--output”：
output = value
if name == “-h” or name == “--host”：
host = value
if name == “-u” or name == “--user”：
user = value
if name == “-p” or name == “--password”：
password = value
if name == “-P” or name == “--port”：
port = value
if name == “--start-datetime”：
start_datetime = value
if name == “--stop-datetime”：
stop_datetime = value
if name == “--start-position”：
start_position = value
if name == “--stop-position”：
stop_position = value
if name == “-d” or name == “--database”：
database = value
if name == “--only-primary” ：
only_primary = value
if binlog == ‘’ ：
print “錯(cuò)誤：請(qǐng)指定binlog文件名！”
usage（）
if user == ‘’ ：
print “錯(cuò)誤：請(qǐng)指定用戶名！”
usage（）
if password == ‘’ ：
print “錯(cuò)誤：請(qǐng)指定密碼！”
usage（）
if database 《》 ‘’ ：
condition_database = “--database=” + “‘” + database + “’”
else：
condition_database = ‘’
print “正在解析binlog.。..。”
fileContent=os.popen（“%s %s --base64-output=DECODE-ROWS --start-datetime=‘%s’ --stop-datetime=‘%s’ --start-position=‘%s’ --stop-position=‘%s’ %s\
|grep ‘###’ -B 2|sed -e ‘s/### //g’ -e ‘s/^INSERT/##INSERT/g’ -e ‘s/^UPDATE/##UPDATE/g’ -e ‘s/^DELETE/##DELETE/g’ ” \
%（mysqlbinlog_bin，binlog，start_datetime，stop_datetime，start_position，stop_position，condition_database））.read（）
#print fileContent
# ----------------------------------------------------------------------------------------
# 功能：初始化binlog里的所有表名和列名，用全局字典result_dict來(lái)儲(chǔ)存每個(gè)表有哪些列
# ----------------------------------------------------------------------------------------
def init_col_name（）：
global result_dict
global pri_dict
global fileContent
result_dict = {}
pri_dict = {}
table_list = re.findall（‘`.*`\\。`.*`’，fileContent）
table_list = list（set（table_list））
#table_list 為所有在這段binlog里出現(xiàn)過(guò)的表
print “正在初始化列名。..。.”
for table in table_list：
sname = table.split（‘。’）［0］.replace（‘`’，‘’）
tname = table.split（‘。’）［1］.replace（‘`’，‘’）
#連接數(shù)據(jù)庫(kù)獲取列和列id
try：
conn = MySQLdb.connect（host=host，user=user，passwd=password，port=int（port））
cursor = conn.cursor（）
cursor.execute（“select ordinal_position，column_name \
from information_schema.columns \
where table_schema=‘%s’ and table_name=‘%s’ ” %（sname，tname））
result=cursor.fetchall（）
if result == （） ：
print ‘Warning：’+sname+‘。’+tname+‘已刪除’
#sys.exit（）
result_dict［sname+‘。’+tname］=result
cursor.execute（“select ordinal_position，column_name \
from information_schema.columns \
where table_schema=‘%s’ and table_name=‘%s’ and column_key=‘PRI’ ” %（sname，tname））
pri=cursor.fetchall（）
#print pri
pri_dict［sname+‘。’+tname］=pri
cursor.close（）
conn.close（）
except MySQLdb.Error， e：
try：
print “Error %d：%s” % （e.args［0］， e.args［1］）
except IndexError：
print “MySQL Error：%s” % str（e）
sys.exit（）
#print result_dict
#print pri_dict
# ----------------------------------------------------------------------------------------
# 功能：拼湊回滾sql，逆序
# ----------------------------------------------------------------------------------------
def gen_rollback_sql（）：
global only_primary
fileOutput = open（output， ‘w’）
#先將文件根據(jù)‘--’分塊，每塊代表一個(gè)sql
area_list=fileContent.split（‘--\n’）
#逆序讀取分塊
print “正在開(kāi)始拼湊sql.。..。”
for area in area_list［：：-1］：
#由于一條sql可能影響多行，每個(gè)sql又可以分成多個(gè)逐條執(zhí)行的sql
sql_list = area.split（‘##’）
#先將pos點(diǎn)和timestamp傳入輸出文件中
for sql_head in sql_list［0］.splitlines（）：
sql_head = ‘#’+sql_head+‘\n’
fileOutput.write（sql_head）
#逐條sql進(jìn)行替換更新，逆序
for sql in sql_list［：：-1］［0：-1］：
try：
if sql.split（）［0］ == ‘INSERT’：
rollback_sql = re.sub（‘^INSERT INTO’， ‘DELETE FROM’， sql， 1）
rollback_sql = re.sub（‘SET\n’， ‘WHERE\n’， rollback_sql， 1）
tablename_pos = 2
table_name = rollback_sql.split（）［tablename_pos］.replace（‘`’， ‘’）
# 獲取該sql中的所有列
col_list = sorted（list（set（re.findall（‘@\d+’， rollback_sql））））
# 因?yàn)榈谝粋€(gè)列前面沒(méi)有逗號(hào)或者and，所以單獨(dú)替換
rollback_sql = rollback_sql.replace（‘@1=’， result_dict［table_name］［0］［1］+‘=’）
for col in col_list［1：］：
i = int（col［1：］） - 1
rollback_sql = rollback_sql.replace（col+‘=’， ‘AND ’ + result_dict［table_name］［i］［1］+‘=’，1）
# 如果only_primary開(kāi)啟且存在主鍵，where條件里就只列出主鍵字段
if int（only_primary） == 1 and pri_dict［table_name］ 《》 （）：
sub_where = ‘’
for primary in pri_dict［table_name］：
primary_name = primary［1］
for condition in rollback_sql.split（‘WHERE’， 1）［1］.splitlines（）：
if re.compile（‘^\s*’+primary_name）.match（condition） or re.compile（‘^\s*AND\s*’+primary_name）.match（condition）：
sub_where = sub_where + condition + ‘\n’
sub_where = re.sub（‘^\s*AND’， ‘’， sub_where， 1）
rollback_sql = rollback_sql.split（‘WHERE’， 1）［0］ + ‘WHERE\n’ + sub_where
if sql.split（）［0］ == ‘UPDATE’：
rollback_sql = re.sub（‘SET\n’， ‘#SET#\n’， sql， 1）
rollback_sql = re.sub（‘WHERE\n’， ‘SET\n’， rollback_sql， 1）
rollback_sql = re.sub（‘#SET#\n’， ‘WHERE\n’， rollback_sql， 1）
tablename_pos = 1
table_name = rollback_sql.split（）［tablename_pos］.replace（‘`’， ‘’）
# 獲取該sql中的所有列
col_list = sorted（list（set（re.findall（‘@\d+’， rollback_sql））））
# 因?yàn)榈谝粋€(gè)列前面沒(méi)有逗號(hào)或者and，所以單獨(dú)替換
rollback_sql = rollback_sql.replace（‘@1=’， result_dict［table_name］［0］［1］ + ‘=’）
for col in col_list［1：］：
i = int（col［1：］） - 1
rollback_sql = rollback_sql.replace（col+‘=’， ‘，’ + result_dict［table_name］［i］［1］+‘=’， 1）.replace（col+‘=’，‘AND ’ +result_dict［table_name］［i］［1］+‘=’）
# 如果only_primary開(kāi)啟且存在主鍵，where條件里就只列出主鍵字段
if int（only_primary） == 1 and pri_dict［table_name］ 《》 （）：
sub_where = ‘’
for primary in pri_dict［table_name］：
primary_name = primary［1］
for condition in rollback_sql.split（‘WHERE’， 1）［1］.splitlines（）：
if re.compile（‘^\s*’ + primary_name）.match（condition） or re.compile（‘^\s*AND\s*’+primary_name）.match（condition）：
sub_where = sub_where + condition + ‘\n’
sub_where = re.sub（‘^\s*AND’， ‘’， sub_where， 1）
rollback_sql = rollback_sql.split（‘WHERE’， 1）［0］ + ‘WHERE\n’ + sub_where
if sql.split（）［0］ == ‘DELETE’：
rollback_sql = re.sub（‘^DELETE FROM’， ‘INSERT INTO’， sql， 1）
rollback_sql = re.sub（‘WHERE\n’， ‘SET\n’， rollback_sql， 1）
tablename_pos = 2
table_name = rollback_sql.split（）［tablename_pos］.replace（‘`’， ‘’）
# 獲取該sql中的所有列
col_list = sorted（list（set（re.findall（‘@\d+’， rollback_sql））））
# 因?yàn)榈谝粋€(gè)列前面沒(méi)有逗號(hào)或者and，所以單獨(dú)替換
rollback_sql = rollback_sql.replace（‘@1=’， result_dict［table_name］［0］［1］ + ‘=’）
for col in col_list［1：］：
i = int（col［1：］） - 1
rollback_sql = rollback_sql.replace（col+‘=’， ‘，’ + result_dict［table_name］［i］［1］+‘=’，1）
rollback_sql = re.sub（‘\n$’，‘;\n’，rollback_sql）
#print rollback_sql
fileOutput.write（rollback_sql）
except IndexError，e：
print “Error：%s” % str（e）
sys.exit（）
print “done！”
def usage（）：
help_info=“”“==========================================================================================
Command line options ：
--help # OUT ： print help info
-f， --binlog # IN ： binlog file. （required）
-o， --outfile # OUT ： output rollback sql file. （default ‘rollback.sql’）
-h， --host # IN ： host. （default ‘127.0.0.1’）
-u， --user # IN ： user. （required）
-p， --password # IN ： password. （required）
-P， --port # IN ： port. （default 3306）
--start-datetime # IN ： start datetime. （default ‘1970-01-01 00:00:00’）
--stop-datetime # IN ： stop datetime. default ‘2070-01-01 00:00:00’
--start-position # IN ： start position. （default ‘4’）
--stop-position # IN ： stop position. （default ‘18446744073709551615’）
-d， --database # IN ： List entries for just this database （No default value）。
--only-primary # IN ： Only list primary key in where condition （default 0）
Sample ：
shell》 python binlog_rollback.py -f ‘mysql-bin.000001’ -o ‘/tmp/rollback.sql’ -h 192.168.0.1 -u ‘user’ -p ‘pwd’ -P 3307 -d dbname
==========================================================================================”“”
print help_info
sys.exit（）
if __name__ == ‘__main__’：
getopts_parse_binlog（）
init_col_name（）
gen_rollback_sql（）
演示
#首先創(chuàng)建一個(gè)只讀賬號(hào)
root:test》 grant select on *.* to ‘query’@‘127.0.0.1’ identified by ‘123456’;
Query OK， 0 rows affected， 1 warning （0.01 sec）
#測(cè)試表結(jié)構(gòu)如下
root:test》 CREATE TABLE `table1` （
-》 `id` int（11） NOT NULL AUTO_INCREMENT，
-》 `c1` int（11） DEFAULT NULL，
-》 `c2` varchar（20） DEFAULT NULL，
-》 `c3` int（11） DEFAULT NULL，
-》 PRIMARY KEY （`id`）
-》 ）;
Query OK， 0 rows affected （0.09 sec）
#插入三條數(shù)據(jù)
root:test》 insert into table1（c1，c2，c3） values （1，‘a(chǎn)’，1），（2，‘b’，2），（3，‘c’，3）;
Query OK， 3 rows affected （0.01 sec）
Records： 3 Duplicates： 0 Warnings： 0
root:test》 select * from table1;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | a | 1 |
| 2 | 2 | b | 2 |
| 3 | 3 | c | 3 |
+----+------+------+------+
3 rows in set （0.00 sec）
#更新一條數(shù)據(jù)
root:test》 update table1 set c3=10 where id=3;
Query OK， 1 row affected （0.01 sec）
Rows matched： 1 Changed： 1 Warnings： 0
root:test》 select * from table1;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | a | 1 |
| 2 | 2 | b | 2 |
| 3 | 3 | c | 10 |
+----+------+------+------+
3 rows in set （0.00 sec）
#刪除一條數(shù)據(jù)
root:test》 delete from table1 where id=1;
Query OK， 1 row affected （0.01 sec）
root:test》 select * from table1;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 2 | 2 | b | 2 |
| 3 | 3 | c | 10 |
+----+------+------+------+
2 rows in set （0.00 sec）
接下來(lái)利用腳本來(lái)生成反向SQL
［root@diandi ~］# python binlog_rollback.py -f /log/mysql/bin/mysql-bin.000002 -o rollback.sql -u query -p 123456 --start-datetime=‘2016-10-28 00:00:00’ -d test
正在獲取參數(shù)。..。.
正在解析binlog.。..。
正在初始化列名。..。.
正在開(kāi)始拼湊sql.。..。
done！
查看反向SQL，最新的DML會(huì)生成在輸入文件的最前面
［root@diandi ~］# cat rollback.sql
## at 155848
##161028 17:07:10 server id 22100 end_log_pos 155898 CRC32 0x5000bca7 Delete_rows： table id 351 flags： STMT_END_F
INSERT INTO `test`。`table1`
SET
id=1
，c1=1
，c2=‘a(chǎn)’
，c3=1;
## at 155560
##161028 17:04:56 server id 22100 end_log_pos 155626 CRC32 0x11d91e2d Update_rows： table id 351 flags： STMT_END_F
UPDATE `test`。`table1`
SET
id=3
，c1=3
，c2=‘c’
，c3=3
WHERE
id=3
AND c1=3
AND c2=‘c’
AND c3=10;
## at 155258
##161028 16:59:31 server id 22100 end_log_pos 155338 CRC32 0x3978c1c1 Write_rows： table id 351 flags： STMT_END_F
DELETE FROM `test`。`table1`
WHERE
id=3
AND c1=3
AND c2=‘c’
AND c3=3;
DELETE FROM `test`。`table1`
WHERE
id=2
AND c1=2
AND c2=‘b’
AND c3=2;
DELETE FROM `test`。`table1`
WHERE
id=1
AND c1=1
AND c2=‘a(chǎn)’
AND c3=1;
執(zhí)行回滾操作
#直接source整個(gè)文件，table1將恢復(fù)到原來(lái)的空表狀態(tài)（實(shí)際情況，在測(cè)試環(huán)境上按需索取，然后再恢復(fù)線上）
root:test》 source /root/rollback.sql
Query OK， 1 row affected （0.01 sec）
Query OK， 1 row affected （0.01 sec）
Rows matched： 1 Changed： 1 Warnings： 0
Query OK， 1 row affected （0.01 sec）
Query OK， 1 row affected （0.01 sec）
Query OK， 1 row affected （0.01 sec）
root:test》 select * from table1;
Empty set （0.00 sec）
具體的參數(shù)使用方法如下：
［root@diandi ~］# python binlog_rollback.py
==========================================================================================
Command line options ：
--help # OUT ： print help info
-f， --binlog # IN ： binlog file. （required）
-o， --outfile # OUT ： output rollback sql file. （default ‘rollback.sql’）
-h， --host # IN ： host. （default ‘127.0.0.1’）
-u， --user # IN ： user. （required）
-p， --password # IN ： password. （required）
-P， --port # IN ： port. （default 3306）
--start-datetime # IN ： start datetime. （default ‘1970-01-01 00:00:00’）
--stop-datetime # IN ： stop datetime. default ‘2070-01-01 00:00:00’
--start-position # IN ： start position. （default ‘4’）
--stop-position # IN ： stop position. （default ‘18446744073709551615’）
-d， --database # IN ： List entries for just this database （No default value）。
--only-primary # IN ： Only list primary key in where condition （default 0）
Sample ：
shell》 python binlog_rollback.py -f ‘mysql-bin.000001’ -o ‘/tmp/rollback.sql’ -h 192.168.0.1 -u ‘user’ -p ‘pwd’ -P 3307 -d dbname
工商網(wǎng)監(jiān)
評(píng)論