参考文章简要的1
https://blog.51cto.com/402753795/1826074
参考文章细节的2
https://blog.csdn.net/weixin_43733154/article/details/104639335
参考文章细节的3
https://blog.csdn.net/weixin_33674976/article/details/92863378
python脚本
b.py
#_*_ coding:utf-8 _*_
import sys
import os
import io
binlogfile = sys.argv[1]
database_name = sys.argv[2]
table_name = sys.argv[3]
print('--database="'+database_name+'"')
def format_binlog():
os.system('/www/server/mysql/bin/mysqlbinlog --start-datetime="2021-11-25 08:20:45" --stop-datetime="2021-11-25 16:50:00" -v -v --base64-output=DECODE-ROWS /www/server/mysql/bin/'+binlogfile+' > '+binlogfile+'.txt')
def pickupbinlog():
f = io.open(binlogfile+'.txt','r')
fw = io.open(database_name+'_'+table_name+'_'+binlogfile+'.txt','a')
priv_str = ''
priv_line = ''
goal_flag = 0
for row in f:
# 处理首行
if row[0:3] == '###' and priv_str != '###':
if database_name in row and table_name in row:
goal_flag = 1
fw.write(priv_line)
fw.write(row)
# 处理末行
if row[0:3] != '###' and priv_str == '###':
goal_flag = 0
# 处理目标操作
if row[0:3] == '###' and priv_str == '###' and goal_flag == 1:
fw.write(row)
priv_str = row[0:3]
priv_line = row
f.close()
fw.close()
if __name__ == '__main__':
# python2.7 pickupbinlog.py mysql-bin.001051 dbname tablename
# python3 pickupbinlog.py mysql-bin.001051 dbname tablename
format_binlog()
pickupbinlog()
执行命令 python b.py binlog文件名 数据库名 数据表名
假设binlog文件名：mysql-bin.000471
假设数据库名：beiyaozhongtai
数据表名：zt_product_base
执行命令如下：
python b.py mysql-bin.000471 beiyaozhongtai zt_product_base
非删除语句系统会生成sql数据如下：格式
# at 1569
#160713 13:52:06 server id 1 end_log_pos 1569 Table_map: `students`.`test` mapped to number 33
#160713 13:52:06 server id 1 end_log_pos 1789 Update_rows: table id 33 flags: STMT_END_F
### UPDATE `students`.`test`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */
### @4='86' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */
### @4='86' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### UPDATE `students`.`test`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='李四' /* STRING(60) meta=65084 nullable=0 is_null=0 */
删除的语句是这样的
############################## 处理 a.txt 文件,生成 b.txt 文件 #######################
[root@node21 tmp]# sed -n '/^###/'p a.txt >b.txt
[root@node21 tmp]# cat b.txt
### DELETE FROM `lili`.`test1`
### WHERE
### @1=10 /* INT meta=0 nullable=0 is_null=0 */
### @2='cl10' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4=30 /* TINYINT meta=0 nullable=0 is_null=0 */
### @5=180 /* FLOAT meta=4 nullable=0 is_null=0 */
### @6=65.5 /* FLOAT meta=4 nullable=0 is_null=0 */
### @7='四川信息10' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### @8='专科' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @9='18382024230' /* STRING(33) meta=65057 nullable=0 is_null=0 */
### @10='158317100@qq.com' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### @11=7000 /* FLOAT meta=4 nullable=0 is_null=0 */
先吧删除语句转换成insert语句
############################## 处理 b.txt 文件 去井号,生成 c.txt 文件 #######################
[root@node21 tmp]# sed 's/### //g' b.txt >c.txt
[root@node21 tmp]# cat c.txt
DELETE FROM `lili`.`test1`
WHERE
@1=10 /* INT meta=0 nullable=0 is_null=0 */
@2='cl10' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
@3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
@4=30 /* TINYINT meta=0 nullable=0 is_null=0 */
@5=180 /* FLOAT meta=4 nullable=0 is_null=0 */
@6=65.5 /* FLOAT meta=4 nullable=0 is_null=0 */
@7='四川信息10' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
@8='专科' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
@9='18382024230' /* STRING(33) meta=65057 nullable=0 is_null=0 */
@10='158317100@qq.com' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
@11=7000 /* FLOAT meta=4 nullable=0 is_null=0 */
然后去掉井号 并且在参数后边加上逗号
############################## 处理 c.txt 文件,生成 d.txt 文件 #######################
[root@node21 tmp]# sed 's#/\*.*\*/#,#g' c.txt >d.txt
如果有富文本里面含注释语句容易出错可以更精细匹配
sed 's#/\*.*\*/
### @#,#g' c.txt >d.txt
[root@node21 tmp]# cat d.txt
DELETE FROM `lili`.`test1`
WHERE
@1=10 ,
@2='cl10' ,
@3=1 ,
@4=30 ,
@5=180 ,
@6=65.5 ,
@7='四川信息10' ,
@8='专科' ,
@9='18382024230' ,
@10='158317100@qq.com' ,
@11=7000 ,
继续处理把delete转成insert
############################## 处理 d.txt 文件,生成 e.txt 文件 #######################
[root@node21 tmp]# sed 's#DELETE FROM#INSERT INTO#g' d.txt >e.txt
[root@node21 tmp]# cat e.txt
INSERT INTO `lili`.`test1`
WHERE
@1=10 ,
@2='cl10' ,
@3=1 ,
@4=30 ,
@5=180 ,
@6=65.5 ,
@7='四川信息10' ,
@8='专科' ,
@9='18382024230' ,
@10='158317100@qq.com' ,
@11=7000 ,
############################## 处理 e.txt 文件,生成 f.txt 文件 #######################
[root@node21 tmp]# sed 's#WHERE#SELECT#g' e.txt >f.txt
[root@node21 tmp]# cat f.txt
INSERT INTO `lili`.`test1`
SELECT
@1=10 ,
@2='cl10' ,
@3=1 ,
@4=30 ,
@5=180 ,
@6=65.5 ,
@7='四川信息10' ,
@8='专科' ,
@9='18382024230' ,
@10='158317100@qq.com' ,
@11=7000 ,
############################## 处理 f.txt 文件,最后一个@结尾的逗号换成:生成 h.txt 文件 #######################
[root@node21 tmp]# sed -r 's#(@11=.*)(,)#\1;#g' f.txt >h.txt
[root@node21 tmp]# cat h.txt
INSERT INTO `lili`.`test1`
SELECT
@1=10 ,
@2='cl10' ,
@3=1 ,
@4=30 ,
@5=180 ,
@6=65.5 ,
@7='四川信息10' ,
@8='专科' ,
@9='18382024230' ,
@10='158317100@qq.com' ,
@11=7000 ;
############################## 处理 h.txt 文件,生成 aa.sql 文件 ######################
[root@node21 tmp]# sed -r 's#(@.*=)(.*)#\2#g' h.txt >>aa.sql
[root@node21 tmp]# cat aa.sql
INSERT INTO `lili`.`test1`
SELECT
10 ,
'cl10' ,
1 ,
30 ,
180 ,
65.5 ,
'四川信息10' ,
'专科' ,
'18382024230' ,
'158317100@qq.com' ,
7000 ;
############################## 在aa.sql文件后面添加commit;命令 ####################
[root@node21 tmp]# sed -i '$a commit;' aa.sql
[root@node21 tmp]# cat aa.sql
INSERT INTO `lili`.`test1`
SELECT
10 ,
'cl10' ,
1 ,
30 ,
180 ,
65.5 ,
'四川信息10' ,
'专科' ,
'18382024230' ,
'158317100@qq.com' ,
7000 ;
commit;
面对非delete情况可以一步到位
这个sed会一步完成去井号，去@符，结尾加逗号
参数@4 【where最后一个@结尾用；】
这句是调换where 和 set位置
sed '/### WHERE/{:a;N;//### SET/!ba;s/([^\n])\n(.)\n(.*)/\3\n\2\n\1\n/}'
快捷生成以上sed 参数方法 利用mysql 命令获取
数据库名 'beiyao-zt-test'
数据表名 'zt_product_base'
set @irowa =0 ;
set @irowb =51 ;
SELECT GROUP_CONCAT(CONCAT("s/@",@irowa:=@irowa+1,'=/',COLUMN_NAME,'=/g') SEPARATOR ";") as a, GROUP_CONCAT("\n\(.*\)" SEPARATOR "") as b, GROUP_CONCAT(CONCAT("\",@irowb:=@irowb-1,'\n') SEPARATOR "") as b FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'beiyao-zt-test' AND TABLE_NAME = 'zt_product_base'
sed '/### WHERE/{:a;N;/### SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 源文件.txt | sed -r '/### WHERE/{:a;N;/@50/!ba;s/### @2.*//g}' | sed 's#/\*.*\*/#,#g'| sed '/### WHERE/{:a;N;/@1/!ba;s/,/;/g};s/### //g;s#/\*.*\*/##g;s/COMMIT,//g' | sed '/^$/d' >test1
yichen修改后 加了50个字段的
sed '/WHERE/{:a;N;/SET/!ba;s/([^\n])\n(.)\n(.)/\3\n\2\n\1/}' beiyaozhongai_zt_product_base_mysql-bin.000471.txt | sed -r '/WHERE/{:a;N;/@50/!ba;s/### @2.//g}' | sed 's/### //g;s#/*.*/#,#g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s#/*.*/##g;s/COMMIT,//g' | sed '/^$/d' >d471.txt
结果如下：
UPDATE `students`.`test`
SET
@1=1 ,
@2='张三' ,
@3='男' ,
@4='86' ,
WHERE
@1=1 ;
UPDATE `students`.`test`
SET
@1=2 ,
@2='李四' ,
@3='男' ,
@4='88' ,
WHERE
@1=2 ;
把数据库名去掉
sed "s/\`beiyaozhongtai\`\.//g"
然后方式1 替换 指定@字段为数据表的字段
获取所有字段
数据库名'beiyao-zt-test'
表名'zt_product_base'
然后在sql 文件头部加上@1=id;@2=name。。。
SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;
set @irow =0 ;
SELECT GROUP_CONCAT(CONCAT("SET @",@irow:=@irow+1,'="',COLUMN_NAME,'"') SEPARATOR ";") FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'beiyao-zt-test' AND TABLE_NAME = 'zt_product_base'
#如果报错
1> 1227 - Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
需要root超级管理员授权
grant SYSTEM_VARIABLES_ADMIN on *.* to '被授权的mysql用户名';
得到如下结果
SET @1="id";
SET @2="upc";
SET @3="goods_no";
SET @4="product_name";
#下步把对应字段替换
执行下语句会生成cat文本批量替换语句
set @irow =0 ;
SELECT GROUP_CONCAT(CONCAT("s/@",@irow:=@irow+1,'=/',COLUMN_NAME,'=/g') SEPARATOR ";") FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'beiyao-zt-test' AND TABLE_NAME = 'zt_product_base'
s/@1=/id=/g;s/@2=/upc=/g;s/@3=/goods_no=/g;s/@4=/product_name=/g;s/@5=/common_name=/g;s/@6=/brand_name=/g;s/@7=/spec=/g;s/@8=/manufacture=/g;s/@9=/producing_area=/g;s/@10=/approval_number=/g;s/@11=/drug_type=/g;s/@12=/dosage_form=/g;s/@13=/packing_unit=/g;s/@14=/main_ingredients=/g;s/@15=/properties=/g;s/@16=/indications=/g;s/@17=/dosage=/g;s/@18=/adr=/g;s/@19=/taboo=/g;s/@20=/caution=/g;s/@21=/announcements=/g;s/@22=/drug_interations=/g;s/@23=/gravida_drug=/g;s/@24=/old_drug=/g;s/@25=/child_drug=/g;s/@26=/pharma_toxicology=/g;s/@27=/overdose=/g;s/@28=/pharmacokinetic=/g;s/@29=/license_legal_person=/g;s/@30=/indate=/g;s/@31=/storage_condition=/g;s/@32=/instruction_book=/g;s/@33=/attending_disease=/g;s/@34=/prescription_type=/g;s/@35=/thumbnails=/g;s/@36=/first_image=/g;s/@37=/images=/g;s/@38=/detail=/g;s/@39=/remark=/g;s/@40=/mid_package=/g;s/@41=/big_package=/g;s/@42=/create_time=/g;s/@43=/update_time=/g;s/@44=/side_effect=/g;s/@45=/spec_int=/g;s/@46=/scope_of_application=/g;s/@47=/pinyin_full=/g;s/@48=/pinyin=/g;s/@49=/type_name_one=/g;s/@50=/type_name_two=/g
然后在前面加上sed -i 即可 结尾用文件名
sed -i 上面的语句 xxx.sql
#把最后一个字段结尾逗号去掉
sed -i -r 's/(type_name_two.*),/\1/g' recover.sql
############################## 在recover.sql文件后面添加commit;命令 ####################
[root@node21 tmp]# sed -i '$a commit;' recover.sql
如果字段数量和数据库相对应也可以值替换@1= 改成id=
sed -i 's/@1=/id=/g' recover.sql
现在是这样
WHERE
id=55344 ;
UPDATE `beiyaozhongtai`.`zt_product_base`
SET
id=55345 ,
@2='6970403270604' ,
@3=NULL ,
@4='盐酸金刚乙胺糖浆 (津彤)' ,
删除所有@符
sed -r 's#(@.*=)(.*)#\2#g' recover.sql >>aa.sql
现在是这样
WHERE
id=55344 ;
UPDATE `beiyaozhongtai`.`zt_product_base`
SET
id=55345 ,
6970403270604' ,
NULL ,
'盐酸金刚乙胺糖浆 (津彤)' ,
在把数据库名去掉 把表名替换beiyaozhongtai. 需要转义符\
sed -i 's/`beiyaozhongtai`\.//g' recover.sql
或者所有@编号字段都指定数据库字段
#替换@为数据库的字段
sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/score/g' recover.sql
#匹配最后一个字段结尾的逗号去掉
sed -i -r 's/(score.*),/\1/g' recover.sql
现在的内容就是数据库可以执行的sql了
UPDATE `students`.`test`
SET
id=1 ,
name='张三' ,
sex='男' ,
score='86'
WHERE
id=1 ;
UPDATE `students`.`test`
SET
id=2 ,
name='李四' ,
sex='男' ,
score='88'
WHERE
id=2 ;
UPDATE `students`.`test`
网友评论