| 1 |
# |
| 2 |
# Get the mysqlbinlog tool --verbose mode to dump the Binlog contents with |
| 3 |
# 'SQL' statements in triple-comments over multiple lines, e.g. : |
| 4 |
# |
| 5 |
### INSERT |
| 6 |
### SET |
| 7 |
### @1=1 |
| 8 |
### @2=2 |
| 9 |
# |
| 10 |
# Then munch this output into single-line statements |
| 11 |
# e.g. : |
| 12 |
# INSERT SET @1=1 @2=2 |
| 13 |
# |
| 14 |
# Then filter + sort to get deterministic order independent of Ndb table |
| 15 |
# fragmentation, epoch in ndb_apply_status etc. |
| 16 |
# |
| 17 |
|
| 18 |
--disable_query_log |
| 19 |
let $MYSQLD_DATADIR= `select @@datadir;`; |
| 20 |
--exec $MYSQL_BINLOG --verbose $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql |
| 21 |
|
| 22 |
create table raw_binlog_rows (txt varchar(1000)); |
| 23 |
|
| 24 |
--eval load data local infile '$MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql' into table raw_binlog_rows columns terminated by '\n'; |
| 25 |
|
| 26 |
create table binlog_stmt_parts_unassoc (txt varchar(1000), line_count int AUTO_INCREMENT PRIMARY KEY, stmt_boundary int); |
| 27 |
|
| 28 |
set @line_count=0; |
| 29 |
set @stmt_boundary=0; |
| 30 |
|
| 31 |
# Use replace() here to get rid of any unwanted Windows |
| 32 |
# CRs |
| 33 |
insert into binlog_stmt_parts_unassoc (txt,stmt_boundary) |
| 34 |
select replace(txt, '\r', ''), |
| 35 |
(txt like '%INSERT%' or # Identify statement boundaries |
| 36 |
txt like '%UPDATE%' or |
| 37 |
txt like '%DELETE%') |
| 38 |
from raw_binlog_rows |
| 39 |
where |
| 40 |
txt like '###%'; # Discard non verbose output |
| 41 |
|
| 42 |
#select * from binlog_stmt_parts_unassoc; |
| 43 |
|
| 44 |
create table binlog_stmt_parts_assoc ( |
| 45 |
txt varchar(1000) collate utf8mb4_bin, |
| 46 |
line_count int, |
| 47 |
stmt_num int |
| 48 |
); |
| 49 |
|
| 50 |
set @stmt_count = 0; |
| 51 |
|
| 52 |
insert into binlog_stmt_parts_assoc |
| 53 |
select t.txt, |
| 54 |
t.line_count, |
| 55 |
(SELECT SUM(x.stmt_boundary) |
| 56 |
FROM binlog_stmt_parts_unassoc x |
| 57 |
WHERE x.line_count <= t.line_count) |
| 58 |
from binlog_stmt_parts_unassoc t order by t.line_count; |
| 59 |
|
| 60 |
|
| 61 |
#select * from binlog_stmt_parts_assoc; |
| 62 |
|
| 63 |
create table binlog_stmts ( |
| 64 |
txt varchar(1000) collate utf8mb4_bin, |
| 65 |
stmt_num int |
| 66 |
); |
| 67 |
|
| 68 |
insert into binlog_stmts |
| 69 |
select group_concat(right(txt, # Combine rows in statment into 1 |
| 70 |
length(txt) - 4) # Trim ### from line start |
| 71 |
order by line_count |
| 72 |
separator ' '), stmt_num |
| 73 |
from binlog_stmt_parts_assoc |
| 74 |
group by stmt_num; |
| 75 |
|
| 76 |
#select * from binlog_stmts; |
| 77 |
|
| 78 |
# Drop ndb_apply_status entries and sort by the statment |
| 79 |
# text to get a deterministic order. |
| 80 |
# |
| 81 |
# Reasonable order would be sort by (PK-cols, stmt_num) |
| 82 |
# - Sorting by PK-cols would give determinism between events from different |
| 83 |
# fragments |
| 84 |
# - Multiple ops on same pk would be in order of application |
| 85 |
# |
| 86 |
# However, as that's harder, and unnecessary given that we just want |
| 87 |
# deterministic output, not applicable SQL, we will just sort by |
| 88 |
# the statement text |
| 89 |
# |
| 90 |
--enable_query_log |
| 91 |
--eval select txt from binlog_stmts where txt $binlog_condition order by txt |
| 92 |
|
| 93 |
--disable_query_log |
| 94 |
drop table raw_binlog_rows; |
| 95 |
drop table binlog_stmt_parts_unassoc; |
| 96 |
drop table binlog_stmt_parts_assoc; |
| 97 |
drop table binlog_stmts; |
| 98 |
--enable_query_log |
| 99 |
--remove_file $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql |