1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
use strict;
use warnings FATAL => 'all';
use English qw(-no_match_vars);
use Getopt::Long;
use Pod::Usage;
use MHA::NodeConst;
GetOptions(
\my %opt, qw/
help
version
/,
) or pod2usage(1);
if ( $opt{help} ) {
pod2usage(0);
}
if ( $opt{version} ) {
print "filter_mysqlbinlog version $MHA::NodeConst::VERSION.\n";
exit 0;
}
my $line;
my @rows_buf = ();
my $parsed_events = 0;
sub read_line() {
if ( $line = <STDIN> ) {
push @rows_buf, $line;
return $line;
}
else {
return;
}
}
sub print_rows() {
foreach (@rows_buf) {
print $_;
}
@rows_buf = ();
return 0;
}
while ( $line = read_line() ) {
if ( $line =~ m/^# at \d+\n$/ ) {
$parsed_events++;
do {
read_line();
} while ( ( $line =~ m/^# at \d+\n$/ ) && !print_rows() );
# Remove ROLLBACK statement and equivalent BINLOG statement in a START event.
# START event should show up at the beginning of the binary/relay logs,
# so ignore it if enough binlog events are already parsed.
if ( $line =~ m/^#.*?Start: binlog v.*?created.*\n$/
&& $parsed_events < 10 )
{
read_line();
if ( $line =~ m/^# Warning: this binlog .*\n$/ ) {
read_line();
}
if ( $line =~ m/^ROLLBACK.*\n$/ ) {
pop @rows_buf;
push @rows_buf,
"# ROLLBACK and BINLOG events generated by start_event were trimmed.\n";
read_line();
while ( !( $line =~ m/(^# at \d+\n$)|(^DELIMITER ;\n$)/ ) ) {
pop @rows_buf;
read_line();
}
}
}
print_rows();
}
# Trim a tail ROLLBACK statement. mysqlbinlog adds the following statements
# at the tail of the output, if neither --disable-log-bin nor --set-charset
# was set.
# # End of log file
# ROLLBACK /* added by mysqlbinlog */;
# /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
elsif ( $line =~ m/^# End of log file\n$/ ) {
read_line();
if ( $line =~ m/^ROLLBACK \/\* added by mysqlbinlog \*\/;\n$/ ) {
read_line();
if ( $line =~
m/^\/\*!50003 SET COMPLETION_TYPE=\@OLD_COMPLETION_TYPE\*\/;\n$/ )
{
if ( !read_line() ) {
pop @rows_buf;
pop @rows_buf;
push @rows_buf,
"# Tail ROLLBACK event generated by mysqlbinlog was trimmed.\n";
goto EOF;
}
}
}
print_rows();
}
else {
print_rows();
}
}
EOF:
print_rows();
exit 0;
# ############################################################################
# Documentation
# ############################################################################
=pod
=head1 NAME
filter_mysqlbinlog - Trimming ROLLBACK statements and equivalent BINLOG events added by mysqlbinlog. This script is now obsolete.
=head1 SYNOPSIS
mysqlbinlog binary_or_relay_log_file | filter_mysqlbinlog
Note that this script is now obsolete and not used by MHA by default.
=head1 DESCRIPTION
mysqlbinlog command provided by Oracle implicitly adds ROLLBACK statements and equivalent BINLOG events. But this causes problems when recovering slave servers. To recover slaves, MHA might need to apply the following binlog events.
1) Relay log events from Relay_Log_Pos to the end of the relay log file
2) Differential relay log events from the latest slave
3) Differential binary log events from the dead master
mysqlbinlog command needs to be executed on these files separately. If a transaction does not end by 1) or 2), implicit ROLLBACK event rolls back the transaction, which will result in inconsistency.
filter_mysqlbinlog is a tool to fix this issue.
Note that ROLLBACK statements themselves are added in usual situations. For example, when you execute 1. BEGIN; 2. Updating transactional tables 3. Updating non-transactional tables 4. ROLLBACK, a ROLLBACK statement is written to the binary log to rollback transactional queries. This is normal situation so filter_mysqlbinlog must not remove all ROLLBACK events.
Note that this script is now obsolete and not used by MHA by default.
|
