.\" Automatically generated by Pod::Man v1.37, Pod::Parser v1.32
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sh \" Subsection heading
.br
.if t .Sp
.ne 5
.PP
\fB\\$1\fR
.PP
..
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings. \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote. | will give a
.\" real vertical bar. \*(C+ will give a nicer C++. Capital omega is used to
.\" do unbreakable dashes and therefore won't be available. \*(C` and \*(C'
.\" expand to `' in nroff, nothing in troff, for use with C<>.
.tr \(*W-|\(bv\*(Tr
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
. ds -- \(*W-
. ds PI pi
. if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
. if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch
. ds L" ""
. ds R" ""
. ds C` ""
. ds C' ""
'br\}
.el\{\
. ds -- \|\(em\|
. ds PI \(*p
. ds L" ``
. ds R" ''
'br\}
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.Sh), items (.Ip), and index
.\" entries marked with X<> in POD. Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.if \nF \{\
. de IX
. tm Index:\\$1\t\\n%\t"\\$2"
..
. nr % 0
. rr F
.\}
.\"
.\" For nroff, turn off justification. Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.hy 0
.if n .na
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear. Run. Save yourself. No user-serviceable parts.
. \" fudge factors for nroff and troff
.if n \{\
. ds #H 0
. ds #V .8m
. ds #F .3m
. ds #[ \f1
. ds #] \fP
.\}
.if t \{\
. ds #H ((1u-(\\\\n(.fu%2u))*.13m)
. ds #V .6m
. ds #F 0
. ds #[ \&
. ds #] \&
.\}
. \" simple accents for nroff and troff
.if n \{\
. ds ' \&
. ds ` \&
. ds ^ \&
. ds , \&
. ds ~ ~
. ds /
.\}
.if t \{\
. ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
. ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
. ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
. ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
. ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
. ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
. \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
. \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
. \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
. ds : e
. ds 8 ss
. ds o a
. ds d- d\h'-1'\(ga
. ds D- D\h'-1'\(hy
. ds th \o'bp'
. ds Th \o'LP'
. ds ae ae
. ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "MYSQL-TABLE-SYNC 1"
.TH MYSQL-TABLE-SYNC 1 "2007-10-15" "perl v5.8.8" "User Contributed Perl Documentation"
.SH "NAME"
mysql\-table\-sync \- Efficiently synchronize data between two MySQL tables.
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
To compare two tables, try one of these:
.PP
.Vb 2
\& mysql-table-sync -d col1,col2 u=user,p=pass,h=host1,D=db,t=tbl host2
\& mysql-table-sync -a bottomup -B 128 u=user,p=pass,h=host1,D=db,t=tbl host2
.Ve
.PP
To sync the destination to the source, try
.PP
.Vb 1
\& mysql-table-sync -x -d col1,col2 u=user,p=pass,h=host1,D=db,t=tbl host2
.Ve
.PP
To show the differences between the tables, use the \*(L"\-\-verbose\*(R" option.
Issue this option multiple times for more detail. To see queries that will
make the second table the same as the first, use the \*(L"\-\-print\*(R" option. To
see the queries issued while searching for differences, use the \*(L"\-\-debug\*(R"
option.
.PP
There is a special syntax for connecting to MySQL servers. Each server name
on the command line can be either just a hostname, or a key=value,key=value
string. Keys are a single letter:
.PP
.Vb 11
\& KEY MEANING
\& === =======
\& h Connect to host
\& P Port number to use for connection
\& S Socket file to use for connection
\& u User for login if not current user
\& p Password to use when connecting
\& F Only read default options from the given file
\& D Database containing the table to be synced
\& t Table to be synced
\& i Index to drill into when syncing
.Ve
.SH "OPTIONS"
.IX Header "OPTIONS"
Many options are enabled by default and can be disabled by prefixing with \-\-no.
.IP "\-\-algorithm" 4
.IX Item "--algorithm"
The algorithm to use when comparing the tables. Top-down drills into the tables
with \s-1GROUP\s0 \s-1BY\s0 queries. Bottom-up builds levels of summary tables with
logarithmically fewer rows at each successive level, and then navigates the
tables in reverse to find differences. Top-down is explained in \*(L"\s-1FINDING\s0 \s-1DIFFERENCES\s0 \s-1TOP\-DOWN\s0\*(R" and bottom-up in \*(L"\s-1FINDING\s0 \s-1DIFFERENCES\s0 \s-1BOTTOM\-UP\s0\*(R".
.IP "\-\-analyze" 4
.IX Item "--analyze"
Only applies to bottom-up search. Specifies that mysql-table-sync should examine the
summary tables and find table differences, and if desired, issue queries to
resolve them. This is the default behavior, but you can disable it if you just
want to build the summary tables.
.IP "\-\-askpass" 4
.IX Item "--askpass"
Prompts the user for a password when connecting to MySQL.
.IP "\-\-branchfactor" 4
.IX Item "--branchfactor"
Branch factor for bottom-up algorithm. Each successive summary table will group
this many rows together in the next summary table. This means each summary
table will be approximately this fraction of the size of the previous table. If
you don't specify an exact power of two, mysql-table-sync will round it to the
nearest power of two. The default is 128.
.IP "\-\-bufferresults" 4
.IX Item "--bufferresults"
Fetch all rows from MySQL before comparing. This is disabled by default. If
enabled, all rows will be fetched into memory for comparing. This may result in
the results \*(L"cursor\*(R" being held open for a shorter time on the server, but if
the tables are large, it could take a long time anyway, and eat all your memory.
For most non-trivial data sizes, you want to leave this disabled.
.IP "\-\-build" 4
.IX Item "--build"
Build summary tables for bottom-up algorithm. Enabled by default. You can
disable to use existing summary tables.
.IP "\-\-cleanup" 4
.IX Item "--cleanup"
Clean up scratch tables for bottom-up algorithm. Enabled by default. You can
disable this to leave the scratch (summary) tables for later analysis or
troubleshooting.
.IP "\-\-collate" 4
.IX Item "--collate"
Use MySQL to compare strings if necessary. Enabled by default. Perl cannot
reliably compare character strings the same way MySQL does, so if it's unsure
how two strings should be sorted, mysql-table-sync will send them to MySQL and
ask its opinion. You should leave this enabled.
.IP "\-\-columns" 4
.IX Item "--columns"
Comma-separated list of columns to compare and synchronize. If you specify this
list, mysql-table-sync will completely ignore any other columns. Specifying
a column that doesn't exist is not an error.
.IP "\-\-debug" 4
.IX Item "--debug"
Print debugging output to \s-1STDOUT\s0. This is mostly the queries used to compare
data. It is not complete at the moment.
.IP "\-\-deleteinsert" 4
.IX Item "--deleteinsert"
Convert all \s-1UPDATES\s0 to \s-1DELETE\s0 and \s-1INSERT\s0. This can help prevent conflicts due
to unique indexes on columns other than the primary key. This option will
become obsolete when I make mysql-table-sync auto-detect this situation and handle it
itself.
.IP "\-\-drilldown" 4
.IX Item "--drilldown"
Drilldown groupings for the top-down algorithm. This governs how the
comparison algorithm searches for differences. If you specify col2,col3 it will
group rows by col2, find groups that differ, search within them grouped by col3,
and then search within these rows on primary key. The behavior of this option
is likely to change in the future.
.IP "\-\-engine" 4
.IX Item "--engine"
Storage engine to use for the bottom-up summary tables. If you don't specify,
it is controlled by the server's default storage engine.
.IP "\-\-execute" 4
.IX Item "--execute"
After finding differences, execute the queries required to sync the tables.
.IP "\-\-forupdate" 4
.IX Item "--forupdate"
Use \s-1SELECT\s0 \s-1FOR\s0 \s-1UPDATE\s0 or \s-1LOCK\s0 \s-1IN\s0 \s-1SHARE\s0 \s-1MODE\s0 for checksums. This only applies to
the top-down algorithm. It can help prevent the tables being changed while
you're examining them. mysql-table-sync automatically decides which type of
locks to acquire, based on other command-line options. This doesn't do anything
on some storage engines, such as MyISAM.
.IP "\-\-help" 4
.IX Item "--help"
Show a brief help message.
.IP "\-\-lock" 4
.IX Item "--lock"
Lock tables when beginning work. This uses table-level \s-1LOCK\s0 \s-1TABLES\s0. This can
help prevent tables being changed while you're examining them.
.IP "\-\-maxcost" 4
.IX Item "--maxcost"
Maximum rowcount before aborting. In either algorithm, if you specify this
option mysql-table-sync will abort if it discovers more than this many rows need
to be changed to sync the tables. There may be more rows to change than can be
definitely discovered up front, but this might be a good way to stop before
doing too much work.
.IP "\-\-onlydo" 4
.IX Item "--onlydo"
Only do \s-1INSERT\s0, \s-1UPDATE\s0, or \s-1DELETE\s0 queries while syncing tables. The default is
to do all three, but if you want to, you can specify any combination of actions.
For example, if you want to do a two-way sync of rows that have been added to
either table, one way to do it is specify '\*(L"\-\-onlydo\*(R" iu' and then run
mysql-table-sync again the \*(L"other direction\*(R" with the same iu argument. That
will avoid deleting extra rows from the destination table, which the second
invocation will then copy back to the source table.
.IP "\-\-prefix" 4
.IX Item "--prefix"
Tablename prefix for bottom-up algorithm. In case the default prefix clashes
with existing tables for some reason, or in case you want to use work tables
that were built previously. The default is a pseudo-random value that is
constant within a given day. If you run mysql-table-sync again the next day, it
will choose a different prefix.
.IP "\-\-print" 4
.IX Item "--print"
Print all sync queries to \s-1STDOUT\s0. If you don't trust mysql\-table\-sync, or just
want to see what it will do, this is a good way. These queries are valid \s-1SQL\s0
and you can run them yourself if you want to sync the tables manually.
.IP "\-\-queries" 4
.IX Item "--queries"
Output the \*(L"\-\-debug\*(R" debugging output as \s-1SQL\s0 that can be executed. By
default it's commented out with \s-1SQL\s0 comments, so it won't interfere with the
output of \*(L"\-\-print\*(R". This option removes the comments.
.IP "\-\-separator" 4
.IX Item "--separator"
Separator for \s-1CONCAT_WS\s0, which is used to concatenate all columns in each row
for checksumming. The default is '#', but you might want to change it if
you're suspicious about this for any reason (possible collisions, for example).
.IP "\-\-singletxn" 4
.IX Item "--singletxn"
Do all work in a single transaction. This has no effect unless you're syncing
tables that use a transactional storage engine.
.IP "\-\-size" 4
.IX Item "--size"
Table size in bottom-up algorithm. Size estimates used to build the summary
tables may be off. If this happens, mysql-table-sync will die and tell you what
value to use for this parameter. Most of the time the size estimates should be
accurate enough (they just have to be within some logarithmic order of
magnitude), so estimating size up front is a good optimization. It prevents
\&\s-1\fICOUNT\s0()\fR queries that aren't needed.
.IP "\-\-strategy" 4
.IX Item "--strategy"
Query strategy when syncing (r=replace, s=ins/upd/del). If you specify r,
\&\s-1INSERT\s0 and \s-1UPDATE\s0 queries will be issued as \s-1REPLACE\s0. This is most useful when
you're syncing a slave to its master via replication (see
\&\*(L"\-\-synctomaster\*(R").
.IP "\-\-synctomaster" 4
.IX Item "--synctomaster"
Change the table on the master/source server, and let replication propagate the
changes to the destination table. If you're syncing a master and slave, this is
almost certainly the option you should use. Implies \*(L"\-\-strategy\*(R"=r.
.IP "\-\-temp" 4
.IX Item "--temp"
Use temporary tables in bottom-up algorithm. The default is to use real tables.
.IP "\-\-timeoutok" 4
.IX Item "--timeoutok"
Keep going if \*(L"\-\-wait\*(R" fails. If you specify \*(L"\-\-wait\*(R" and the slave doesn't catch
up to the master's position before the wait times out, the default behavior is
to abort. This option makes mysql-table-sync keep going anyway. Warning: if
you are trying to get a consistent comparision between the two servers, you
probably don't want to keep going after a timeout.
.IP "\-\-verbose" 4
.IX Item "--verbose"
Explain the differences found while comparing the tables. Specify up to three
times for more verbosity. See \*(L"\s-1OUTPUT\s0\*(R" for more details about the output.
.IP "\-\-verify" 4
.IX Item "--verify"
Verify checksum compatibility across servers. Some older versions of MySQL
won't concatenate columns the same, so checksums might differ.
mysql-table-checksum checks for this and aborts if the servers have different
behavior. If you know your columns will never contain the empty string, you can
safely disable this check.
.IP "\-\-version" 4
.IX Item "--version"
Output version information and exit.
.IP "\-\-wait" 4
.IX Item "--wait"
Make the slave wait to catch up to the master before comparing the tables. The
value is the number of seconds to wait before timing out (see also \*(L"\-\-timeoutok\*(R").
mysql-table-sync issues \s-1LOCK\s0 \s-1TABLES\s0 on the master, then finds the master's
binlog position and waits for the slave to catch up to that position before
comparing tables. This is useful for guaranteeing a consistent comparision
between the servers.
.IP "\-\-where" 4
.IX Item "--where"
Use this option to limit the portion of the table being synchronized. The
value will be added to queries in the \s-1WHERE\s0 clause . Do not include the
keyword \s-1WHERE\s0 in the option; that will be added automatically. Be sure to
quote and escape as required by your shell. For example:
.Sp
.Vb 1
\& mysql-table-sync [OPTIONS...] --where "id between 5 and 10"
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
I wrote this tool to help me resync slaves that \*(L"drift\*(R" from their masters,
which can happen for any number of reasons. I wanted a solution that would work
well for MySQL replication, so I didn't have to re-initialize the slaves, which
can be prohibitively expensive \*(-- if there's enough data, even stopping and
restarting the slave is costly, as it takes a while to \*(L"warm up\*(R" the server.
Add to that the overhead of copying huge amounts of data over the network, and
the time involved, and a way to resync the slaves \*(L"live\*(R" is very attractive
indeed. There are also many constraints introduced by replication, which I
wanted to either avoid or use to my advantage.
.PP
I know not everyone has exactly these needs, so I made mysql-table-sync much more
generic than I'd need to patch a table that's out of sync on a slave.
.PP
The \s-1DBA\s0 must choose the algorithm and parameters to use when reconciling
differences between the tables. See below for help making this decision.
Different algorithms have more or less network traffic, impact on the servers,
or work better in certain circumstances. The tool supports a variety of
algorithms so you can resolve the differences as efficiently as possible within
whatever parameters matter to you.
.SH "SYSTEM REQUIREMENTS"
.IX Header "SYSTEM REQUIREMENTS"
You need Perl, \s-1DBI\s0, DBD::mysql, and some core packages that ought to be
installed in any reasonably new version of Perl.
.PP
You need MySQL version 4.1.1 or greater; earlier versions lack the \s-1\fIBIT_XOR\s0()\fR
function required to take a groupwise checksum.
.SH "OVERVIEW"
.IX Header "OVERVIEW"
This tool implements two algorithms to find differences between two MySQL
tables, which need not be on the same server. One is \*(L"bottom\-up\*(R" and builds
summary tables from each table, then traverses them to find rows and chunks of
rows that differ. The other is \*(L"top\-down\*(R" and builds no summary tables, but
repeatedly queries the target table. Each algorithm has strengths and
limitations, and is suitable for different situations.
.PP
Once you've identified the differences, you can also choose from several methods
of resolving them. One method is to do inserts, updates and deletes to the
destination table. The other assumes the destination table is on a replication
slave and makes the changes on the source (master) server, counting on
replication to propagate the changes. Again it's up to a smart \s-1DBA\s0 to decide
which method is best.
.PP
There are also variations on all the techniques, in support of locking,
master/slave consistency, partial-row updates, and so forth.
.SH "FINDING DIFFERENCES BOTTOM-UP"
.IX Header "FINDING DIFFERENCES BOTTOM-UP"
The bottom-up method of finding differences begins by checksumming every row in
the source and destination target tables. The result of the checksum is stored
in a scratch table on the server.
.PP
This scratch table is what I call the \*(L"level 0\*(R" table. It contains the target
table's primary key columns and a checksum of all the columns, concatenated.
This checksum is easy to compare and makes it possible to see whether the rows
differ. Level 0 contains one row per row in the target table.
.PP
Table \*(L"level 1\*(R" is derived from level 0 by grouping a number of rows together
and checksumming the group. How many rows are grouped together is up to the
user, but it must be a power of two. 128 is a suggestion I've seen. I refer to
this number as the \*(L"branch factor\*(R" because the summary tables conceptually build
a tree.
.PP
The grouping works by dividing the checksum of each row in level 0 by some power
of the branch factor and taking the remainder. A checksum is a number, though
it is usually written as a string of hex digits, such as
acbd18db4cc2f85cedef654fccc4a4d8, so you can divide and take the remainder
(modulus) easily. (This tool actually uses some bitwise arithmetic to optimize
this, but I won't go into it here). The power of the branch factor decreases as
the levels are built, so the remainders get smaller and smaller, grouping the
rows into fewer and fewer summary rows.
.PP
Level 1's primary key is not the target table's primary key. It is the modulus
of the group from which the row was derived. For example, if a number of rows
in level 0 have a modulus of 11, they will be grouped together into a single row
in level 1, with the primary key value of 11.
.PP
Assuming a branch factor of 128, level 1 has 1/128th as many rows as level 0,
give or take. Level 2 is built from level 1, and has 1/128th as many rows in it
again, and so on until level N, which has just a single row.
.PP
After building scratch tables 0 through N, mysql-table-sync begins at level N and works
backwards. At level N, there is just a single row. If the checksum in this row
matches on source and destination, the tables must be identical, and there is no
more work to do. If they differ though, some rows in level N\-1 must differ, and
mysql-table-sync examines the \*(L"parent\*(R" rows in level N\-1. It continues to do this until
it travels all the way back \*(L"up the tree\*(R" to level 0 and identifies exactly
which rows in the target tables are different. It uses breadth-first search.
.PP
I've glossed over many subtleties. For example, as the summary tables are
built, not only their checksums but the remainders are computed on the fly; the
remainders are stored in the summary tables and are indexed for efficient lookup
as the algorithm traverses back up the tree seeking differences. A running
count is also stored so at any given point you know how many rows in the target
table got rolled into the one row you're examining, no matter what level you're
at in the tree. Some optimizations can be used to short-circuit the process
when entire chunks of rows are missing from one of the tables, and so on. But
these optimizations and subtleties are just efficiencies, and are not necessary
for correctness.
.PP
Here are some details about the table structures: The _\|_crc column contains the
checksum of the row from which it was derived. The _\|_cnt column contains the
running count of rows from which it was derived, except in the case of level 0
where each row is derived from one row. The _\|_rem column contains the checksum
modulus the power of the branching factor. The _\|_par column in level N is a
\&\*(L"pointer\*(R" to the _\|_rem column in level N\-1.
.SH "FINDING DIFFERENCES TOP-DOWN"
.IX Header "FINDING DIFFERENCES TOP-DOWN"
The top-down algorithm is nearly the reverse of the bottom-up algorithm.
Instead of building summary tables bottom-up from many rows and ending with one
row, then searching top-down back through the summaries, it does an n\-ary search
on clusters of the tables, which I refer to as \*(L"groupings.\*(R" The search begins
with grouped data and ends with single rows, instead of beginning with single
rows and working towards summaries. There are no summary tables.
.PP
The basic idea is to choose an appropriate grouping strategy which will allow
MySQL to use indexes to drill down through regions of the table, grouping each
region together at first and comparing whole chunks of data between the source
and destination. Suppose the tables contain day-level data for many client
accounts; clients can have many accounts. Day, client, account and whatever the
primary key is, are all indexed. The drill-down strategy might first group the
table by day and see which days differ between the tables, then within the days
that differ group by client, then account, and finally descend to the individual
row level, using the primary key.
.PP
This approach is also a breadth-first search as I've implemented it. At each
level in the drill\-down, mysql-table-sync knows a set of truths, such as the value of
certain columns in the rows, the number of rows that might be bad, and so on.
In this respect it is fairly similar to the bottom-up approach.
.SH "PROS AND CONS OF THE TWO SEARCH METHODS"
.IX Header "PROS AND CONS OF THE TWO SEARCH METHODS"
Each method for finding the rows that differ has its own strengths and
weaknesses, and is suited for different scenarios.
.PP
The bottom-up approach has these advantages:
.IP "\(bu" 4
The checksum and modulo arithmetic ensure a uniform hierarchy of rows in the
\&\*(L"tree\*(R" of N\-level summary tables. Taking the modulo of a checksum is
essentially a random number, which will distribute rows approximately evenly in
each successive summary.
.IP "\(bu" 4
The algorithm makes no assumptions about keys or data types, and will work on
any table with a primary key or a user-specified index. All you need is a way
to identify a row, in the final analysis.
.IP "\(bu" 4
The technique works the same on every table structure; there's no need to think
about the \*(L"best\*(R" way to do it for a specific table.
.IP "\(bu" 4
The summary tables can be kept and re-used for successive analyses, or to
restart an analysis that fails for some reason (for example, you specified a
maximum cost before halting, and it was exceeded but you've changed your mind;
no need to rebuild the summaries, you can just restart).
.IP "\(bu" 4
If you assume there are occasional \*(L"bad\*(R" rows scattered through the table, the
entire tree of summary tables will need to be examined to find them.
Pre-calculating this is an up-front penalty that pays off in efficiency once you
try to find the bad rows.
.IP "\(bu" 4
This algorithm's best and worst cases, in terms of pre-computing the summary
tables, are identical. Given that you know the table size, you know how
expensive it will be.
.IP "\(bu" 4
Parts of the algorithm can be parallelized readily, though I have not yet done
so, as I want to make sure the implementation is correct first (I plan to do
this soon).
.IP "\(bu" 4
This algorithm is network\-efficient, as the potentially large rows in the target
tables (suppose each row has very large \s-1BLOBS\s0 in it) are not sent across the
network. Only the checksums are sent across the network, until the bad rows
themselves are identified.
.PP
The algorithm has its shortcomings too, though:
.IP "\(bu" 4
It is necessary to do some possibly significant work up front to design the
summary tables properly. For example, you need to know the maximum possible
number of rows to be examined. I have tried to optimize this process as much as
possible by examining index statistics and making estimates. This is fairly
cheap, and should work in nearly every case, but it makes the coding much more
complex, just to avoid things like a \s-1COUNT\s0(*) query, which is notoriously slow
on InnoDB.
.IP "\(bu" 4
All the summary tables may add up to a \s-1LOT\s0 of data on very large tables. If the
target table is narrow, the summary tables may be even larger than the target
tables, though there will never be more rows in level 0 than the target table
has.
.IP "\(bu" 4
It's hard to make this approach play nicely with replication. If you build
temporary tables in memory, you're playing havoc with statement-based
replication should the slave crash. Even if you build them on disk, which is
durable and restartable, the summary tables built on the master will replicate
to the slave. The slave server will be doing double work with the master's
queries running on it. Either way, building summary tables on the server is
anathema to replication.
.IP "\(bu" 4
It's hard to lock the table for consistency, should you wish to. You can't
design the summary tables until you start querying the target tables, yet you
can't write to the summary tables while only holding a lock on the target table,
which would require releasing and re-acquiring the lock on the target \*(-- race
conditions abound. I do have a workaround to this problem in mind, but have not
implemented it yet (and it would not solve the problem in cases where the
destination table is being written to).
.IP "\(bu" 4
The original algorithm, as designed by Coelho, didn't use any indexes or
pre-computed and cached remainders and counts on the summary tables. This is
extremely inefficient on large tables, causing repeated table scans. While I
have modified the algorithm to avoid this, it comes at the cost of larger table
and index size on the server.
.IP "\(bu" 4
The checksum/modulo approach destroys locality of reference in the target
tables. Suppose the rows that differ between the source and destination tables
are concentrated in a small region of the table; the checksum/modulo math will
randomly scatter these neighbors throughout the summary tables. This precludes
some types of optimizations. This is very important on extremely large tables,
as it causes lots of random I/O during the search phase. It's also a realistic
scenario for large tables, which may tend to be append-only logs or similar
(credit card transactions, for example).
.IP "\(bu" 4
The reverse of something I mentioned earlier as a benefit becomes a drawback
when only a small part of the table is bad. Building the entire summary table
tree for just one bad row is wasteful. Most of the tree will compare equal, but
there's no chance for early optimizations by pruning those branches; they've
already been built by the time the search notices they aren't needed.
.IP "\(bu" 4
All the \s-1INSERT\s0 .. \s-1SELECT\s0 statements necessary to build the summary tables will
acquire shared row locks on InnoDB tables. This overhead adds up.
.PP
The top-down approach is quite different, both in behavior and implementation
details. Here are some of its strengths:
.IP "\(bu" 4
This approach is network\-efficient. Large rows are not sent across the network,
just as with the bottom-up approach. However, it is also memory and space
efficient on the servers, as there are no summary tables to build.
.IP "\(bu" 4
This approach does allow for early optimizations such as tree-pruning in the
search.
.IP "\(bu" 4
The target table's natural groupings, created by its indexes, can (and should)
be exploited.
.IP "\(bu" 4
The queries are not replicated because they don't affect any data. The queries
on the master will not cause extra work on the slave.
.IP "\(bu" 4
It's easy to lock the tables with one of several strategies, including
intentionally locking InnoDB tables with \s-1SELECT\s0 \s-1FOR\s0 \s-1UPDATE\s0 or \s-1SELECT\s0 \s-1LOCK\s0 \s-1ON\s0
\&\s-1SHARE\s0 \s-1MODE\s0, and MySQL's own table locks.
.IP "\(bu" 4
There is no need in this algorithm to compute branching factors and numbers of
summary levels needed according the the size of the target tables. There's no
up-front analysis to do. You don't need to pre-compute anything before
searching for differences.
.IP "\(bu" 4
Spatial locality can be exploited. Adjacent \*(L"bad\*(R" rows stay adjacent during the
search. This can help avoid random I/O during the search.
.IP "\(bu" 4
There are more opportunities for bulk operations, such as noticing a large chunk
of the table can be inserted or deleted en masse. While there are some in the
bottom-up approach, there are not as many, and they're harder to optimize.
.IP "\(bu" 4
The best case for this algorithm is not the same cost as the worst case. This
can be good or bad, depending on the scenario.
.IP "\(bu" 4
The grouping strategy determines the \*(L"branch factor\*(R" of the search tree, and it
can vary from level to level in the search, depending on the data and the
strategy chosen. This can be a good thing in the hands of a smart \s-1DBA\s0, or a bad
thing in a novice's hands.
.IP "\(bu" 4
As the search descends through the tree of groupings, it needs to examine fewer
and fewer columns in the target tables.
.PP
The top-down approach is not without its weaknesses:
.IP "\(bu" 4
The search strategy can be simpler or more complex. It's up to the \s-1DBA\s0. There
are certainly more possibilities, and choosing a good top-down strategy might be
hard.
.IP "\(bu" 4
If there's no locality to exploit, the top-down approach might not be able to
prune the search tree, and it might end up doing more work than the bottom\-up.
.IP "\(bu" 4
The drill-down must repeatedly checksum the entire row (except the columns it
holds as constants in each level of the search). If the rows are very large,
for example if there are large text columns, this might be a lot of work for the
server to do over and over.
.SH "RESOLVING DIFFERENCES"
.IX Header "RESOLVING DIFFERENCES"
Once mysql-table-sync has found the differences, you probably want to resolve them.
There are two major ways I know to do this, and several variations.
.PP
The most obvious way is to simply issue \s-1INSERT\s0, \s-1UPDATE\s0, and \s-1DELETE\s0 statements
against the destination server.
.PP
A more subtle approach is to take advantage of replication and issue the
statements on the master, letting replication propagate them to the slave. In
this case a slightly different approach is needed. If a row is missing on the
slave, you can't just \s-1INSERT\s0 it on the master, or you'll presumably get a
duplicate key error. You could do \s-1INSERT\s0 \s-1IGNORE\s0 or \s-1REPLACE\s0 instead.
.PP
The tool uses \s-1REPLACE\s0 by default when you're using replication. To tell it to
use \s-1INSERT\s0 and \s-1UPDATE\s0 instead, use the \*(L"\-\-strategy\*(R"=s option. You can use
\&\*(L"\-\-strategy\*(R"=s to use \s-1REPLACE\s0 even when you're not using replication to fix the
destination table.
.PP
You can also use the \*(L"\-\-onlydo\*(R" option to only issue some kinds of statements \*(--
for example, suppose you want to run the \s-1INSERT\s0 and \s-1UPDATE\s0 statements but not
the \s-1DELETE\s0.
.SH "SPECIAL CASES"
.IX Header "SPECIAL CASES"
There are cases where no combination of \s-1UPDATE\s0 statements can resolve
differences without violating some unique key. For example, suppose there's a
primary key on column a and a unique key on column b. Then there is no way to
sync these two tables with straightforward \s-1UPDATE\s0 statements:
.PP
.Vb 6
\& +---+---+ +---+---+
\& | a | b | | a | b |
\& +---+---+ +---+---+
\& | 1 | 2 | | 1 | 1 |
\& | 2 | 1 | | 2 | 2 |
\& +---+---+ +---+---+
.Ve
.PP
If you run into this, you can use the \*(L"\-\-deleteinsert\*(R" option to convert all
\&\s-1UPDATE\s0 statements into \s-1DELETE\s0 followed by \s-1INSERT\s0. The tool orders statements
such that this will succeed.
.PP
If necessary, you can use the \*(L"\-\-singletxn\*(R" option to make sure sychronizing
either succeeds or rolls back entirely. Of course this will have no effect on
non-transactional tables such as MyISAM. Running the entire sync in a single
transaction causes extra overhead.
.PP
Another special case is comparing strings between servers. If your primary key
or drilldown columns are character data, mysql-table-sync will potentially issue queries
to the source server when it doesn't think it can reliably compare two strings
exactly the same way MySQL does. The collation used is the column's collation
on the source server, so if the character set or collation differ between the
tables being synced, it's hard to say what the results will be. This feature
will cause more network traffic between the servers as it compares the tables.
This is a feature I don't have fully baked yet. You can disable it with the
\&\*(L"\-\-nocollate\*(R" option.
.SH "GUARANTEEING CONSISTENCY"
.IX Header "GUARANTEEING CONSISTENCY"
I wrote this tool to synchronize tables live, without stopping the servers
they're on. This requires some kind of locking to guarantee a consistent write
after reading. This tool supports several methods.
.PP
The first is simple table locks. If you specify the \*(L"\-\-lock\*(R" option, it will lock
the table for reading or writing, depending on how you want to update. If you
want to update on a master and let replication propagate the changes, it locks
for write on the master and read on the slave; if you want to make changes on
the slave, it locks for read on the master and doesn't lock on the slave, to
avoid blocking the replication thread on the slave.
.PP
If you are using InnoDB tables, you can get consistency without locking the
whole table. This is especially useful if you're only trying to synchronize
part of the table that you know to be bad. Use the \*(L"\-\-forupdate\*(R" option to make
the \s-1SELECT\s0 statements acquire locks. As above, it gets either shared (\s-1LOCK\s0 \s-1IN\s0
\&\s-1SHARE\s0 \s-1MODE\s0) or exclusive (\s-1FOR\s0 \s-1UPDATE\s0) locks, depending on how you are syncing.
This only applies to the top-down algorithm. The bottom-up algorithm creates
tables, which implicitly commits and releases locks unless you use temporary
tables (the \*(L"\-\-temp\*(R" option). If you're using temporary tables with the bottom-up
algorithm, there are implicit shared locks on the target tables if they're
InnoDB.
.PP
Finally, if you're working on a master and slave server, you should probably
specify the \*(L"\-\-wait\*(R" option in conjunction with one of the above. This locks on
the master, finds the master's position, and then waits for the slave to catch
up to that position. The argument to the option is the number of seconds the
slave should wait before timing out. By default \*(L"\-\-wait\*(R" implies \*(L"\-\-lock\*(R", but you
can specify \*(L"\-\-nolock\*(R" if you want to override this and use InnoDB's row-level
locks. Also by default, if the wait timeout is exceeded or another
\&\s-1MASTER_POS_WAIT\s0 error occurs, the program will exit with an error, but you can
use the \*(L"\-\-timeoutok\*(R" option to control this.
.PP
You can use the \*(L"\-\-singletxn\*(R" option to make the entire sync run in one
transaction.
.SH "OUTPUT"
.IX Header "OUTPUT"
Output varies greatly depending on the command-line options you specify.
There are several different kinds of output: debugging, status, and query. I
have tried to ensure it will always be valid \s-1SQL\s0, though much of it will be
commented out.
.PP
If you specify the \*(L"\-\-print\*(R" option, the queries needed to sync the destination
table with the source table will be printed to \s-1STDOUT\s0.
.PP
If you specify the \*(L"\-\-debug\*(R" option, the queries needed to discover the
differences between source and destination will be printed to \s-1STDOUT\s0, commented
out. You can use this to see how many queries are executed for a given
strategy, or whatever other debugging you want. If you remove the comment
characters with the \*(L"\-\-queries\*(R" option, you can also replay the process of finding
the differences.
.PP
If you specify the \*(L"\-\-verbose\*(R" option, you'll see information about the process of
discovering the differences between the tables. The output is quite different
for top-down and bottom-up algorithms. Specify this option multiple times to
increase the amount of information you see. This output is complex enough
that I'll need to explain it separately.
.Sh "TOP-DOWN \s-1OUTPUT\s0"
.IX Subsection "TOP-DOWN OUTPUT"
At its most verbose, the top-down output may resemble this:
.PP
.Vb 21
\& -- Level 2: CHECK group of 81 rows WHERE `col2` = '20'
\& -- Level 2: INSERT group of 18 rows WHERE `col2` = '42'
\& -- Level 2 total: 18 bad rows 81 to inspect
\& -- Level 2 summary: 1 bad groups in 3 src groups 2 dst groups
\& -- Level 2 changes: 0 updates 18 inserts 0 deletes
\& -- Level 1: CHECK group of 6 rows WHERE `col3` = '737696900' AND `col2` = '20'
\& -- Level 1: CHECK group of 1 rows WHERE `col3` = '737953400' AND `col2` = '20'
\& -- Level 1: CHECK group of 1 rows WHERE `col3` = '737955900' AND `col2` = '20'
\& -- Level 1 total: 18 bad rows 8 to inspect
\& -- Level 1 summary: 3 bad groups in 32 src groups 32 dst groups
\& -- Level 1 changes: 0 updates 18 inserts 0 deletes
\& -- Level 0 total: 18 bad rows 2 to inspect
\& -- Level 0 summary: 2 bad groups in 6 src groups 6 dst groups
\& -- Level 0 changes: 0 updates 18 inserts 0 deletes
\& -- Level 0: UPDATE 1 row WHERE `col3` = '737953400' AND `col1` = '87551' AND `col2` = '20'
\& -- Level 0 total: 19 bad rows 1 to inspect
\& -- Level 0 summary: 1 bad groups in 1 src groups 1 dst groups
\& -- Level 0 changes: 1 updates 18 inserts 0 deletes
\& -- Level 0 total: 19 bad rows 0 to inspect
\& -- Level 0 summary: 0 bad groups in 1 src groups 1 dst groups
\& -- Level 0 changes: 1 updates 18 inserts 0 deletes
.Ve
.PP
Outdented lines are actions that must be taken later, indented lines are
play-by-play status as differences between the tables are found. There will be
one group of indented lines for each group of rows drilled into and found to
have differences.
.PP
The first two lines are details of level 2. At level 2, col2 is held as a
constant. There is one group of 81 rows where col2 = 20, which does not match
from source to destination. It needs further checking and is marked as \s-1CHECK\s0.
The next level will drill down into this group. Also at level 2, there is one
group of 18 rows that needs to be inserted to sync the destination table. This
does not need to be drilled into on the next level.
.PP
The next three lines summarize the findings at level 2, and the work that
remains to be done. Line 1 shows level 2 found a total of 18 rows known to
differ between the source and destination, and there are 81 more to inspect
further. Line 2 shows level 2 found 1 entire group of rows known to be bad
(the group that must be inserted) after inspecting 3 groups from the source
table and 2 from the destination table. The difference, 3\-2, is the one group
that must be inserted. Line 3 shows 18 rows have been queued for insertion
en masse.
.PP
The next six lines are what happens in the one group at level 1. At level 1,
the group of rows where col2 = 20 is drilled into, grouped on col3. The first
three lines of output show the algorithm finds three groups of rows that don't
match. The next three show the total bad-row count still at 18, so no new bad
rows have been found, but the number of rows that must be drilled into is much
smaller now \*(-- only 8 rows. Level 1 found 3 bad groups by checking 32 groups
from each table, and queued no new rows into the known-bad list.
.PP
The level 0 output shows these 3 groups being examined a row at a time, with no
more drilldown possible. And you can see the one bad row being found.
Eventually the last line of output shows 18 rows must be inserted (no change
from before) and 1 row must be updated to sync the destination table.
.PP
It might help to see what happens with only one level of verbosity, this time on
a 50,000 row table with 5 rows missing from the destination:
.PP
.Vb 11
\& -- Level 2 total: 0 bad rows 6385 to inspect
\& -- Level 1 total: 0 bad rows 5142 to inspect
\& -- Level 1 total: 1 bad rows 4235 to inspect
\& -- Level 1 total: 1 bad rows 2919 to inspect
\& -- Level 1 total: 1 bad rows 2560 to inspect
\& -- Level 1 total: 1 bad rows 325 to inspect
\& -- Level 0 total: 2 bad rows 142 to inspect
\& -- Level 0 total: 2 bad rows 141 to inspect
\& -- Level 0 total: 3 bad rows 61 to inspect
\& -- Level 0 total: 4 bad rows 37 to inspect
\& -- Level 0 total: 5 bad rows 0 to inspect
.Ve
.PP
Now you can see it progressing from 0 known bad rows, with 6385 to do, all the
way to 5 known bad rows and 0 left to do.
.Sh "BOTTOM-UP \s-1OUTPUT\s0"
.IX Subsection "BOTTOM-UP OUTPUT"
At maximum verbosity, the output from the bottom-up algorithm may look like this
(this output is from the same 100\-row tables as above).
.PP
.Vb 25
\& -- Level 2 UPDATE parent: 0
\& -- Level 2 total: 100 rows
\& -- Level 2 summary: 1 parents 100 src rows 82 dst rows
\& -- Level 2 changes: 0 updates 0 inserts 0 deletes 0 total
\& -- Level 2 bulk-op: 0 inserts 0 ins-rows 0 deletes 0 del-rows
\& -- Level 1 UPDATE parent: 0
\& -- Level 1 BULKIN parent: 1
\& -- Level 1 UPDATE parent: 2
\& -- Level 1 UPDATE parent: 3
\& -- Level 1 UPDATE parent: 4
\& -- Level 1 UPDATE parent: 5
\& -- Level 1 UPDATE parent: 8
\& -- Level 1 UPDATE parent: 9
\& -- Level 1 UPDATE parent: 10
\& -- Level 1 UPDATE parent: 11
\& -- Level 1 UPDATE parent: 14
\& -- Level 1 UPDATE parent: 15
\& -- Level 1 total: 84 rows
\& -- Level 1 summary: 11 parents 100 src rows 82 dst rows
\& -- Level 1 changes: 0 updates 1 inserts 0 deletes 1 total
\& -- Level 1 bulk-op: 1 inserts 1 ins-rows 0 deletes 0 del-rows
\& -- Level 0 total: 19 rows
\& -- Level 0 summary: 0 parents 83 src rows 66 dst rows
\& -- Level 0 changes: 1 updates 18 inserts 0 deletes 19 total
\& -- Level 0 bulk-op: 1 inserts 1 ins-rows 0 deletes 0 del-rows
.Ve
.PP
The first level, level 2, says the parent row whose remainder is 0 (this will
always be the case at the first level in bottom\-up) differs. At this point it
looks like the parent must be updated to reconcile source and destination
tables, but it's not yet known which individual rows must be changed. The level
2 summary says there are 100 rows grouped together from parent rows, that is 1
parent value with 100 rows in the source and 82 in the destination (the
difference is the 18 rows that must be inserted, but that is not yet known).
The next two lines of output show what work is queued to do \*(-- row-level
updates, inserts and deletes, and bulk inserts and deletes. Each bulk insert or
delete knows how many rows it will affect.
.PP
The next set of output, for level 1, shows that this level of the tree has 11
rows that don't match between source and destination. These are again marked as
\&\s-1UPDATE\s0 because they differ, but it's still not known why. One row doesn't exist
in the destination and is marked as \s-1BULKIN\s0, for \*(L"bulk insert.\*(R" This level of
drill-down was able to narrow the part of the table possibly bad from 100 to 84
rows.
.PP
At level 0, this narrows down to just 19 rows. Most of these are inserted
singly, and there is one update.
.PP
Again, here is what happens with just one level of verbosity on the same
50,000\-row tables as above:
.PP
.Vb 3
\& -- Level 2 total: 50000 rows
\& -- Level 1 total: 1945 rows
\& -- Level 0 total: 5 rows
.Ve
.PP
At the beginning, all 50,000 rows look bad, but as it navigates the tree, it
narrows it down to just the 5 missing rows.
.SH "COMPATIBILITY"
.IX Header "COMPATIBILITY"
My goal is a superb solution for MySQL. However, I think you can probably
make some minor changes and use this tool on other platforms.
.SH "HISTORY AND ACKNOWLEDGEMENTS"
.IX Header "HISTORY AND ACKNOWLEDGEMENTS"
My work is based in part on Giuseppe Maxia's work on distributed databases,
and code derived from that
article. There is more explanation, and a link to the code, at
.
.PP
Another programmer extended Maxia's work even further. Fabien Coelho changed
and generalized Maxia's technique, introducing symmetry and avoiding some
problems that might have caused too-frequent checksum collisions. This work
grew into pg_comparator, . Coelho also
explained the technique further in a paper titled \*(L"Remote Comparison of Database
Tables\*(R" ().
.PP
This existing literature mostly addressed how to find the differences between
the tables, not how to resolve them once found. I needed a tool that would not
only find them efficiently, but would then resolve them. I first began thinking
about how to improve the technique further with my article
,
where I discussed a number of problems with the Maxia/Coelho \*(L"bottom\-up\*(R"
algorithm. After writing that article, I began to write this tool. I wanted to
actually implement their algorithm with some improvements so I was sure I
understood it completely. I discovered it is not what I thought it was, and is
considerably more complex than it appeared to me at first. Fabien Coelho was
kind enough to address some questions over email.
.PP
The improvements to the bottom-up algorithm are my original work, as is the
top-down algorithm. The techniques to actually resolve the differences are also
my own work.
.PP
Another tool that can synchronize tables is the SQLyog Job Agent from webyog.
Thanks to Rohit Nadhani, \s-1SJA\s0's author, for the conversations about the general
techniques. There is a comparison of mysql-table-sync and \s-1SJA\s0 at
.PP
Thanks to the following people and organizations for helping in many ways:
.PP
Alan Rimm-Kaufman and John Miller (The Rimm-Kaufman Group),
Dane Miller,
Fabien Coelho,
Giuseppe Maxia and others at MySQL \s-1AB\s0,
Kristian Koehntopp (MySQL \s-1AB\s0),
Rohit Nadhani (WebYog),
The helpful monks at Perlmonks,
Travis Rodak,
.SH "BUGS"
.IX Header "BUGS"
Please use the Sourceforge bug tracker, forums, and mailing lists to request
support or report bugs: .
.SH "COPYRIGHT, LICENSE AND WARRANTY"
.IX Header "COPYRIGHT, LICENSE AND WARRANTY"
This program is copyright (c) 2007 Baron Schwartz.
Feedback and improvements are welcome.
.PP
\&\s-1THIS\s0 \s-1PROGRAM\s0 \s-1IS\s0 \s-1PROVIDED\s0 \*(L"\s-1AS\s0 \s-1IS\s0\*(R" \s-1AND\s0 \s-1WITHOUT\s0 \s-1ANY\s0 \s-1EXPRESS\s0 \s-1OR\s0 \s-1IMPLIED\s0
\&\s-1WARRANTIES\s0, \s-1INCLUDING\s0, \s-1WITHOUT\s0 \s-1LIMITATION\s0, \s-1THE\s0 \s-1IMPLIED\s0 \s-1WARRANTIES\s0 \s-1OF\s0
\&\s-1MERCHANTIBILITY\s0 \s-1AND\s0 \s-1FITNESS\s0 \s-1FOR\s0 A \s-1PARTICULAR\s0 \s-1PURPOSE\s0.
.PP
This program is free software; you can redistribute it and/or modify it under
the terms of the \s-1GNU\s0 General Public License as published by the Free Software
Foundation, version 2; \s-1OR\s0 the Perl Artistic License. On \s-1UNIX\s0 and similar
systems, you can issue `man perlgpl' or `man perlartistic' to read these
licenses.
.PP
You should have received a copy of the \s-1GNU\s0 General Public License along with
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
Place, Suite 330, Boston, \s-1MA\s0 02111\-1307 \s-1USA\s0.
.SH "AUTHOR"
.IX Header "AUTHOR"
Baron Schwartz.
.SH "VERSION"
.IX Header "VERSION"
This manual page documents Ver 0.9.7 Distrib 1053 \f(CW$Revision\fR $.