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.