In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
Editor to share with you how MySQL extracts SQL statements through binlog in ROW mode. I hope you will get something after reading this article. Let's discuss it together.
Linux
Based on binlog of row mode, generate rollback statement of DML (insert/update/delete)
Generate readable sql files by parsing binlog through mysqlbinlog-v
Extract valid sql that needs to be processed
The line at the beginning of "#". If the start-position entered is in the middle of an event group, it will result in an "unrecognized event" error
Reverses the sql of INSERT/UPDATE/DELETE, and 1 complete sql can only occupy 1 line
INSERT: INSERT INTO = > DELETE FROM, SET = > WHERE
UPDATE: WHERE = > SET, SET = > WHERE
DELETE: DELETE FROM = > INSERT INTO, WHERE = > SET
Replace the position @ {1pm 2p3} with the column name.
Get the column order and corresponding column names through desc table
Special column type value for special treatment
Reverse order
Note:
The table structure must be the same as the current table structure [remember]
Because the row mode is idempotent and the recovery is one-time, only sql is extracted, not BEGIN/COMMIT
Only INSERT/UPDATE/DELETE can be processed.
Mysql > select * from yoon
+-+
| | actor_id | first_name | last_name | last_update | |
+-+
| | 1 | HANK | YOON | 2006-02-15 04:34:33 |
| | 2 | HANK | YOON | 2006-02-15 04:34:33 |
| | 3 | HANK | YOON | 2006-02-15 04:34:33 |
| | 4 | HANK | YOON | 2006-02-15 04:34:33 |
| | 5 | HANK | YOON | 2006-02-15 04:34:33 |
| | 6 | HANK | YOON | 2006-02-15 04:34:33 |
| | 7 | HANK | YOON | 2006-02-15 04:34:33 |
| | 8 | HANK | YOON | 2006-02-15 04:34:33 |
| | 9 | HANK | YOON | 2006-02-15 04:34:33 |
| | 10 | HANK | YOON | 2006-02-15 04:34:33 |
| | 11 | HANK | YOON | 2006-02-15 04:34:33 |
+-+
11 rows in set (0.00 sec)
Mysql > delete from yoon
Query OK, 11 rows affected (1.03 sec)
Mysql > select * from yoon
Empty set (0.00 sec)
Be sure to pay attention to the spaces between commands, otherwise you will not be able to extract the SQL statement:
[root@hank-yoon data] # perl binlog-rollback.pl-f 'mysql-bin.000001'-o' / export/data/mysql/data/yoon.sql'-u 'root'-p' yoon'
Warning: Using a password on the command line interface can be insecure.
[root@hank-yoon data] # ls
Auto.cnf hank ibdata2 ib_logfile1 modify.pl mysql-bin.000001 performance_schema test yoon.sql
Binlog-rollback.pl ibdata1 ib_logfile0 ib_logfile2 mysql mysql-bin.index sakila yoon
[root@hank-yoon data] # cat yoon.sql
INSERT INTO `yoon`.`yoon`SET `last_ id` = 11, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
INSERT INTO `yoon`.`yoon`SET `last_ id` = 10, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
INSERT INTO `yoon`.`yoon`SET `last_ id` = 9, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
INSERT INTO `yoon`.`yoon`SET `last_ id` = 8, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
INSERT INTO `yoon`.`yoon`SET `last_ id` = 7, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
INSERT INTO `yoon`.`yoon`SET `last_ id` = 6, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
INSERT INTO `yoon`.`yoon`SET `last_ id` = 5, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
INSERT INTO `yoon`.`yoon`SET `last_ id` = 4, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
INSERT INTO `yoon`.`yoon`SET `last_ id` = 3, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
INSERT INTO `yoon`.`yoon`SET `last_ id` = 2, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
INSERT INTO `yoon`.`yoon`SET `last_ id` = 1, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
Mysql > INSERT INTO `yoon`.`yoon` SET `last_ id` = 11, `first_ name` = 'HANK', `last_ name` =' YOON', `last_ update` = from_unixtime (1139949273)
Query OK, 1 row affected (0.01sec)
Mysql > select * from yoon
+-+
| | actor_id | first_name | last_name | last_update | |
+-+
| | 11 | HANK | YOON | 2006-02-15 04:34:33 |
+-+
Click (here) to collapse or open
#! / usr/lib/perl-w
Use strict
Use warnings
Use Class::Struct
Use Getopt::Long qw (: config no_ignore_case); # GetOption
# register handler system signals
Use sigtrap 'handler',\ & sig_int,' normal-signals'
# catch signal
Sub sig_int () {
My ($signals) = @ _
Print STDERR "# Caught SIG$signals.\ n"
Exit 1
}
My opt
My $srcfile
My $host = '127.0.0.1'
My $port = 3306
My ($user,$pwd)
My ($MYSQL, $MYSQLBINLOG, $ROLLBACK_DML)
My $outfile ='/ dev/null'
My (% do_dbs,%do_tbs)
# tbname= > tbcol, tbcol: @ n = > colname,type
My tbcol_pos
My $SPLITER_COL =','
My $SQLTYPE_IST = 'INSERT'
My $SQLTYPE_UPD = 'UPDATE'
My $SQLTYPE_DEL = 'DELETE'
My $SQLAREA_WHERE = 'WHERE'
My $SQLAREA_SET = 'SET'
My $PRE_FUNCT ='= ='
# =
# generate rollback statements for DML (insert/update/delete) based on binlog in row mode
# generate readable sql files by parsing binlog through mysqlbinlog-v
# extract valid sql that needs to be processed
# the line at the beginning of "#". If the start-position entered is in the middle of an event group, it will result in an "unrecognized event" error
#
# reverse the sql of INSERT/UPDATE/DELETE, and a complete sql can only occupy 1 line
# INSERT: INSERT INTO = > DELETE FROM, SET = > WHERE
# UPDATE: WHERE = > SET, SET = > WHERE
# DELETE: DELETE FROM = > INSERT INTO, WHERE = > SET
# replace position @ {1pm 2p3} with column name
# obtain column order and corresponding column names through desc table
# Special column type value for special treatment
# reverse order
#
# Note:
# the table structure must be the same as the current table structure [remember]
# because the row mode is idempotent and the recovery is one-time, only sql is extracted, not BEGIN/COMMIT
# only INSERT/UPDATE/DELETE can be processed
# =
Sub main {
# get input option
& get_options ()
#
& init_tbcol ()
#
& do_binlog_rollback ()
}
& main ()
#-
# Func: get options and set option flag
#-
Sub get_options {
# Get options info
GetOptions (\% opt
'help', # OUT: print help info
'f | srcfile=s', # IN: binlog file
'o | outfile=s', # out: output sql file
'h | host=s', # IN: host
'u | user=s', # IN: user
'p | password=s', # IN: password
'P | port=i', # IN: port
'start-datetime=s', # IN: start datetime
'stop-datetime=s', # IN: stop datetime
'start-position=i', # IN: start position
'stop-position=i', # IN: stop position
'd | database=s', # IN: database, split comma
't | table=s', # IN: table, split comma
'i | ignore', # IN: ignore binlog check ddl and so on
'debug', # IN: print debug information
) or print_usage ()
If (! scalar (% opt)) {
& print_usage ()
}
# Handle for options
If ($opt {'f'}) {
$srcfile = $opt {'f'}
} else {
& merror ("please input binlog file")
}
$opt {'h'} and $host = $opt {'h'}
$opt {'u'} and $user = $opt {'u'}
$opt {'p'} and $pwd = $opt {'p'}
$opt {'P'} and $port = $opt {'P'}
If ($opt {'o'}) {
$outfile = $opt {'o'}
# clear outfile
`outfile` echo'> $
}
#
$MYSQL = qq {mysql-h$host-u$user-paired PWD'- P$port}
& mdebug ("get_options::MYSQL\ n\ t$MYSQL")
# extract binlog without displaying column definition information, use-v instead of-vv
$MYSQLBINLOG = qq {mysqlbinlog-v}
$MYSQLBINLOG. = "--start-position=". $opt {'start-position'} if $opt {' start-position'}
$MYSQLBINLOG. = "--stop-position=". $opt {'stop-position'} if $opt {' stop-postion'}
$MYSQLBINLOG. = "--start-datetime='". $opt {'start-datetime'}. "'" If $opt {'start-datetime'}
$MYSQLBINLOG. = "--stop-datetime='$opt {'stop-datetime'}'" if $opt {'stop-datetime'}
$MYSQLBINLOG. = "$srcfile"
& mdebug ("get_options::MYSQLBINLOG\ n\ t$MYSQLBINLOG")
# check whether ddl sql: CREATE is included in binlog | ALTER | DROP | RENAME
& check_binlog () unless ($opt {'i'})
# without mysqlbinlog filtering, some sql may be missed in USE dbname;, so it is not filtered in mysqlbinlog
# specify the database
If ($opt {'d'}) {
My @ dbs = split (/, /, $opt {'d'})
Foreach my $db (@ dbs) {
$do_dbs {$db} = 1
}
}
# specify table
If ($opt {'T'}) {
My @ tbs = split (/, /, $opt {'T'})
Foreach my $tb (@ tbs) {
$do_tbs {$tb} = 1
}
}
# extract valid DML SQL
$ROLLBACK_DML = $MYSQLBINLOG. "| grep'^ # #'"
# remove comments:'# #'- >''
# remove leading and trailing spaces
$ROLLBACK_DML. = "| sed's Universe accounts #\\ sUnix /\\ s *\ $/ / g'"
& mdebug ("rollback dml\ n\ t$ROLLBACK_DML")
# check whether the content is empty
My $cmd = "$ROLLBACK_DML | wc-l"
& mdebug ("check contain dml sql\ n\ t$cmd")
My $size = `$cmd`
Chomp ($size)
Unless ($size > 0) {
& merror ("binlog DML is empty:$ROLLBACK_DML")
}
}
#-
# Func: check binlog contain DDL
#-
Sub check_binlog {
& mdebug ("$PRE_FUNCT check_binlog")
My $cmd = "$MYSQLBINLOG"
$cmd. = "| grep-E-I'^ (CREATE | ALTER | DROP | RENAME)'"
& mdebug ("check binlog has DDL cmd\ n\ t$cmd")
My $ddlcnt = `$cmd`
Chomp ($ddlcnt)
My $ddlnum = `$cmd | wc-l`
Chomp ($ddlnum)
My $res = 0
If ($ddlnum > 0) {
# prefix ddl sql
$ddlcnt = `echo'$ddlcnt' | sed's / ^ / / g``
& merror ("binlog contain $ddlnum DDL:$MYSQLBINLOG. Ddl sql:\ n$ddlcnt")
}
Return $res
}
#-
# Func: init all table column order
# if input-database-table params, only get set table column order
#-
Sub init_tbcol {
& mdebug ("$PRE_FUNCT init_tbcol")
# extract DML statement
My $cmd. = "$ROLLBACK_DML | grep-E'^ (INSERT | UPDATE | DELETE)'"
# extract the table name and remove the duplicate
# $cmd. = "| awk'{if (\ $1 ~\" ^ UPDATE\ ") {print\ $2} else {print\ $3}'| uniq"
$cmd. = "| awk'{if (\ $1 ~\" ^ UPDATE\ ") {print\ $2} else {print\ $3}'| sort | uniq"
& mdebug ("get table name cmd\ n\ t$cmd")
Open ALLTABLE, "$cmd |" or die "can't open file:$cmd\ n"
While (my $tbname =) {
Chomp ($tbname)
# if (exists $tbcol_pos {$tbname}) {
# next
#}
& init_one_tbcol ($tbname) unless (& ignore_tb ($tbname))
}
Close ALLTABLE or die "can't close file:$cmd\ n"
# init tb col
Foreach my $tb (keys% tbcol_pos) {
& mdebug ("tbname- > $tb")
My% colpos =% {$tbcol_pos {$tb}}
Foreach my $pos (keys% colpos) {
My $col = $colpos {$pos}
My ($cname,$ctype) = split (/ $SPLITER_COL/, $col)
& mdebug ("\ tpos- > $pos,cname- > $cname,ctype- > $ctype")
}
}
}
#-
# Func: init one table column order
#-
Sub init_one_tbcol {
My $tbname = shift
& mdebug ("$PRE_FUNCT init_one_tbcol")
# get table structure and column order
My $cmd = $MYSQL. "--skip-column-names-- silent-e'desc $tbname'"
# extract column names and concatenate them
$cmd. = "| awk-F\'\\ t\'{print NR\" $SPLITER_ Col`\ "\ $1\" `$ SPLITER_COL\ "\ $2}'"
& mdebug ("get table column infor cmd\ n\ t$cmd")
Open TBCOL, "$cmd |" or die "can't open desc $tbname;"
My colpos
While (my $line =) {
Chomp ($line)
My ($pos,$col,$coltype) = split (/ $SPLITER_COL/,$line)
& mdebug ("linesss=$line\ n\ t\ tpos=$pos\ n\ t\ tcol=$col\ n\ ttype=$coltype")
$colpos {$pos} = $col.$SPLITER_COL.$coltype
}
Close TBCOL or die "can't colse desc $tbname"
$tbcol_pos {$tbname} =\% colpos
}
#-
# Func: rollback sql: INSERT/UPDATE/DELETE
#-
Sub do_binlog_rollback {
My $binlogfile = "$ROLLBACK_DML"
& mdebug ("$PRE_FUNCT do_binlog_rollback")
# INSERT | UPDATE | DELETE
My $sqltype
# WHERE | SET
My $sqlarea
My ($tbname, $sqlstr) = (',')
My ($notignore, $isareabegin) = (0Jing 0)
# output sql file
Open SQLFILE, "> > $outfile" or die "Can't open sql file:$outfile"
# binlog file
Open BINLOG, "$binlogfile |" or die "Can't open file: $binlogfile"
While (my $line =) {
Chomp ($line)
If ($line = ~ / ^ (INSERT | UPDATE | DELETE) /) {
# export sql
If ($sqlstr ne') {
$sqlstr. = ";\ n"
Print SQLFILE $sqlstr
& mdebug ("export sql\ n\ t". $sqlstr)
$sqlstr =''
}
If ($line = ~ / ^ INSERT/) {
$sqltype = $SQLTYPE_IST
$tbname = `echo'$line' | awk'{print\ $3}'`
Chomp ($tbname)
$sqlstr = qq {DELETE FROM $tbname}
} elsif ($line = ~ / ^ UPDATE/) {
$sqltype = $SQLTYPE_UPD
$tbname = `echo'$line' | awk'{print\ $2}'`
Chomp ($tbname)
$sqlstr = qq {UPDATE $tbname}
} elsif ($line = ~ / ^ DELETE/) {
$sqltype = $SQLTYPE_DEL
$tbname = `echo'$line' | awk'{print\ $3}'`
Chomp ($tbname)
$sqlstr = qq {INSERT INTO $tbname}
}
# check ignore table
If (& ignore_tb ($tbname)) {
$notignore = 0
& mdebug ("# IGNORE#:line:". $line)
$sqlstr =''
} else {
$notignore = 1
& mdebug ("# DO#:line:". $line)
}
} else {
If ($notignore) {
& merror ("can't get tbname") unless (defined ($tbname))
If ($line = ~ / ^ WHERE/) {
$sqlarea = $SQLAREA_WHERE
$sqlstr. = qq {SET}
$isareabegin = 1
} elsif ($line = ~ / ^ SET/) {
$sqlarea = $SQLAREA_SET
$sqlstr. = qq {WHERE}
$isareabegin = 1
} elsif ($line = ~ / ^\ @ /) {
$sqlstr. = & deal_col_value ($tbname, $sqltype, $sqlarea, $isareabegin, $line)
$isareabegin = 0
} else {
& mdebug (":: unknown sql:". $line)
}
}
}
}
# export last sql
If ($sqlstr ne') {
$sqlstr. = ";\ n"
Print SQLFILE $sqlstr
& mdebug ("export sql\ n\ t". $sqlstr)
}
Close BINLOG or die "Can't close binlogfile: $binlogfile"
Close SQLFILE or die "Can't close out sql file: $outfile"
# reverse order
# 1SecretG: only the first line does not execute G, and append the contents of hold space back to pattern space
# h: copy pattern space to hold space
# $! d: delete all but the last line
My $invert = "sed-I '1started G * h;\ $! d' $outfile"
My $res = `$Secrett`
& mdebug ("inverter order sqlfile: $invert")
}
#-
# Func: transfer column pos to name
# deal column value
#
# & deal_col_value ($tbname, $sqltype, $sqlarea, $isareabegin, $line)
#-
Sub deal_col_value ($) {
My ($tbname, $sqltype, $sqlarea, $isareabegin, $line) = @ _
& mdebug ("$PRE_FUNCT deal_col_value")
& mdebug ("input:tbname- > $tbname,type- > $sqltype,area- > $sqlarea,areabegin- > $isareabegin,line- > $line")
My @ vals = split (/, $line)
My $pos = substr ($vals [0], 1)
My $valstartpos = length ($pos) + 2
My $val = substr ($line,$valstartpos)
My% tbcol =% {$tbcol_pos {$tbname}}
My ($cname,$ctype) = split (/ $SPLITER_COL/,$tbcol {$pos})
& merror ("can't get $tbname column $cname type") unless (defined ($cname) | | defined ($ctype))
& mdebug ("column infor:cname- > $cname,type- > $ctype")
# join str
My $joinstr
If ($isareabegin) {
$joinstr =''
} else {
# WHERE is replaced by SET, use, connect
If ($sqlarea eq $SQLAREA_WHERE) {
$joinstr =','
# SET is replaced with WHERE using AND connection
} elsif ($sqlarea eq $SQLAREA_SET) {
$joinstr = 'AND'
} else {
& merror ("! The scripts error")
}
}
#
My $newline = $joinstr
# NULL value
If (($val eq 'NULL') & & ($sqlarea eq $SQLAREA_SET)) {
$newline. = qq {$cname IS NULL}
} else {
# timestamp: record seconds
If ($ctype eq 'timestamp') {
$newline. = qq {$cname=from_unixtime ($val)}
# datetime: @ n=yyyy-mm-dd hh::ii::ss
} elsif ($ctype eq 'datetime') {
$newline. = qq {$cname='$val'}
} else {
$newline. = qq {$cname=$val}
}
}
& mdebug ("\ told > $line\ n\ tnew > $newline")
Return $newline
}
#-
# Func: check is ignore table
# params: IN table full name # format: `dbname`.`tbname`
# RETURN:
# 0 not ignore
# 1 ignore
#-
Sub ignore_tb ($) {
My $fullname = shift
# Delete `
$fullname = ~ s / `/ g
My ($dbname,$tbname) = split (/\. /, $fullname)
My $res = 0
# specified database
If ($opt {'d'}) {
# same as the specified library
If ($do_dbs {$dbname}) {
# specify table
If ($opt {'T'}) {
# different from the specified table
Unless ($do_tbs {$tbname}) {
$res = 1
}
}
# different from the specified library
} else {
$res = 1
}
}
# & mdebug ("Table check ignore:$fullname- > $res")
Return $res
}
#-
# Func: print debug msg
#-
Sub mdebug {
My (@ msg) = @ _
Print "@ msg\ n" if ($opt {'debug'})
}
#-
# Func: print error msg and exit
#-
Sub merror {
My (@ msg) = @ _
Print ": @ msg\ n"
& print_usage ()
Exit (1)
}
#-
# Func: print usage
#-
Sub print_usage {
Print perl binlog-rollback.pl-f 'mysql-bin.000001'-o' / tmp/t.sql'-u 'user'-p' pwd'-I
Shell > perl binlog-rollback.pl-f 'mysql-bin.000001'-o' / tmp/t.sql'-u 'user'-p' pwd'-- debug
Shell > perl binlog-rollback.pl-f 'mysql-bin.000001'-o' / tmp/t.sql'-h '192.168.1.2'-u 'user'-p' pwd'-P 3307
Shell > perl binlog-rollback.pl-f 'mysql-bin.000001'-o' / tmp/t.sql'-u 'user'-p' pwd'-- start-position=107
Shell > perl binlog-rollback.pl-f 'mysql-bin.000001'-o' / tmp/t.sql'-u 'user'-p' pwd'-- start-position=107-- stop-position=10000
Shell > perl binlog-rollback.pl-f 'mysql-bin.000001'-o' / tmp/t.sql'-u 'user'-p' pwd'-d 'db1,db2'
Shell > perl binlog-rollback.pl-f 'mysql-bin.0000*'-o' / tmp/t.sql'-u 'user'-p' pwd'-d 'db1,db2'-T' tb1,tb2'
=
EOF
Exit
}
1
After reading this article, I believe you have a certain understanding of "how MySQL extracts SQL statements through binlog in ROW mode". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.