man mk-table-sync (1): Synchronize MySQL tables efficiently.

SYNOPSIS

This tool changes data, so for maximum safety, you should back up your data before you use it.

To sync db.tbl1 from host1 to host2:

mk-table-sync --execute u=user,p=pass,h=host1,D=db,t=tbl host2

Sync all tables in host1 to host2 and host3:

 mk-table-sync --execute host1 host2 host3

Resolve differences mk-table-checksum found on this master's slaves:

 mk-table-sync --execute --replicate test.checksum master1

Make slave1 have the same data as its replication master:

 mk-table-sync --execute --sync-to-master slave1

Ditto, resolving differences mk-table-checksum found:

 mk-table-sync --execute --sync-to-master \
   --replicate test.checksum slave1

Sync server2 in a master-master replication configuration, where server2's copy of db1.tbl1 is known or suspected to be incorrect:

 mk-table-sync --execute --sync-to-master h=server2,D=db1,t=tbl1

Note that in the master-master configuration, the following will NOT do what you want, because it will make changes directly on server2, which will then flow through replication and change server1's data:

 # Don't do this in a master-master setup!
 mk-table-sync --execute h=server1,D=db1,t=tbl1 h=server2

RISKS

The following section is included to inform users about the potential risks, whether known or unknown, of using this tool. The two main categories of risks are those created by the nature of the tool (e.g. read-only tools vs. read-write tools) and those created by bugs.

With great power comes great responsibility! This tool changes data, so it is a good idea to back up your data. It is also very powerful, which means it is very complex, so you should run it with the ``--dry-run'' option to see what it will do, until you're familiar with its operation. If you want to see which rows are different, without changing any data, use ``--print'' instead of ``--execute''.

Be careful when using mk-table-sync in any master-master setup. Master-master replication is inherently tricky, and it's easy to make mistakes. You need to be sure you're using the tool correctly for master-master replication. See the ``SYNOPSIS'' for the overview of the correct usage.

Also be careful with tables that have foreign key constraints with "ON DELETE" or "ON UPDATE" definitions because these might cause unintended changes on the child tables.

In general, this tool is best suited when your tables have a primary key or unique index. Although it can synchronize data in tables lacking a primary key or unique index, it might be best to sychronize that data by another means.

At the time of this release, there is a potential bug using ``--lock-and-rename'' with MySQL 5.1, a bug detecting certain differences, a bug using ROUND() across different platforms, and a bug mixing collations.

The authoritative source for updated information is always the online issue tracking system. Issues that affect this tool will be marked as such. You can see a list of such issues at the following URL: <http://www.maatkit.org/bugs/mk-table-sync>.

See also ``BUGS'' for more information on filing bugs and getting help.

DESCRIPTION

WARNING this tool is unfinished and could perform slowly. The Chunk algorithm is great when it can be used, and so is Nibble, but otherwise GroupBy is the default choice and it may not perform very well. Please run with ``--dry-run'' before subjecting your servers to this tool, and make backups of your data!

This tool is designed to do one-way and bidirectional synchronization of data. It finds differences efficiently with one of several algorithms (see ``ALGORITHMS''). It makes changes on the destination table(s) so it matches the source.

It does not synchronize table structures, indexes, or any other schema changes. It synchronizes only data.

It can operate through replication by comparing a slave with its master and making changes on the master. These changes will flow through replication and correct any differences found on the slave.

It accepts a list of DSNs (see the ``--help'' output) to tell it where and how to connect.

There are many ways to invoke it. The following is the abbreviated logic:

   if DSN has a t part, sync only that table:
      if 1 DSN:
         if --sync-to-master:
            The DSN is a slave.  Connect to its master and sync.
      if more than 1 DSN:
         The first DSN is the source.  Sync each DSN in turn.
   else if --replicate:
      if --sync-to-master:
         The DSN is a slave.  Connect to its master, find records
         of differences, and fix.
      else:
         The DSN is the master.  Find slaves and connect to each,
         find records of differences, and fix.
   else:
      if only 1 DSN and --sync-to-master:
         The DSN is a slave.  Connect to its master, find tables and
         filter with --databases etc, and sync each table to the master.
      else:
         find tables, filtering with --databases etc, and sync each
         DSN to the first.

If you're confused about how it the DSNs are interpreted, use the ``--explain-hosts'' option and it will tell you.

OUTPUT

If you specify the ``--verbose'' option, you'll see information about the differences between the tables. There is one row per table. Each server is printed separately. For example,

   # Syncing h=host1,D=test,t=test1
   # DELETE REPLACE INSERT UPDATE ALGORITHM EXIT DATABASE.TABLE
   #      0       0      3      0 Chunk     2    test.test1

Table test.test1 on host1 required 3 "INSERT" statements to synchronize and it used the Chunk algorithm (see ``ALGORITHMS''). Because differences were found, its ``EXIT STATUS'' was 2.

If you specify the ``--print'' option, you'll see the actual SQL statements that the script uses to synchronize the table if ``--execute'' is also specified.

If you want to see the SQL statements that mk-table-sync is using to select chunks, nibbles, rows, etc., then specify ``--print'' once and ``--verbose'' twice. Be careful though: this can print a lot of SQL statements.

There are cases where no combination of "INSERT", "UPDATE" or "DELETE" 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 UPDATE statements:

 +---+---+  +---+---+
 | a | b |  | a | b |
 +---+---+  +---+---+
 | 1 | 2 |  | 1 | 1 |
 | 2 | 1 |  | 2 | 2 |
 +---+---+  +---+---+

The tool rewrites queries to "DELETE" and "REPLACE" in this case. This is automatically handled after the first index violation, so you don't have to worry about it.

REPLICATION SAFETY

Synchronizing a replication master and slave safely is a non-trivial problem, in general. There are all sorts of issues to think about, such as other processes changing data, trying to change data on the slave, whether the destination and source are a master-master pair, and much more.

In general, the safe way to do it is to change the data on the master, and let the changes flow through replication to the slave like any other changes. However, this works only if it's possible to REPLACE into the table on the master. REPLACE works only if there's a unique index on the table (otherwise it just acts like an ordinary INSERT).

If your table has unique keys, you should use the ``--sync-to-master'' and/or ``--replicate'' options to sync a slave to its master. This will generally do the right thing. When there is no unique key on the table, there is no choice but to change the data on the slave, and mk-table-sync will detect that you're trying to do so. It will complain and die unless you specify "--no-check-slave" (see ``--[no]check-slave'').

If you're syncing a table without a primary or unique key on a master-master pair, you must change the data on the destination server. Therefore, you need to specify "--no-bin-log" for safety (see ``--[no]bin-log''). If you don't, the changes you make on the destination server will replicate back to the source server and change the data there!

The generally safe thing to do on a master-master pair is to use the ``--sync-to-master'' option so you don't change the data on the destination server. You will also need to specify "--no-check-slave" to keep mk-table-sync from complaining that it is changing data on a slave.

ALGORITHMS

This tool has a generic data-syncing framework, within which it is possible to use any number of different algorithms to actually find differences. It chooses the best algorithm automatically. While I plan to add more algorithms in the future, the following are implemented now:

Chunk

Finds an index whose first column is numeric (including date and time types), and divides the column's range of values into chunks of approximately ``--chunk-size'' rows. Syncs a chunk at a time by checksumming the entire chunk. If the chunk differs on the source and destination, checksums each chunk's rows individually to find the rows that differ.

It is efficient when the column has sufficient cardinality to make the chunks end up about the right size.

The initial per-chunk checksum is quite small and results in minimal network traffic and memory consumption. If a chunk's rows must be examined, only the primary key columns and a checksum are sent over the network, not the entire row. If a row is found to be different, the entire row will be fetched, but not before.

Nibble

Finds an index and ascends the index in fixed-size nibbles of ``--chunk-size'' rows, using a non-backtracking algorithm (see mk-archiver for more on this algorithm). It is very similar to ``Chunk'', but instead of pre-calculating the boundaries of each piece of the table based on index cardinality, it uses "LIMIT" to define each nibble's upper limit, and the previous nibble's upper limit to define the lower limit.

It works in steps: one query finds the row that will define the next nibble's upper boundary, and the next query checksums the entire nibble. If the nibble differs between the source and destination, it examines the nibble row-by-row, just as ``Chunk'' does.

GroupBy

Selects the entire table grouped by all columns, with a COUNT(*) column added. Compares all columns, and if they're the same, compares the COUNT(*) column's value to determine how many rows to insert or delete into the destination. Works on tables with no primary key or unique index.

Stream

Selects the entire table in one big stream and compares all columns. Selects all columns. Much less efficient than the other algorithms, but works when there is no suitable index for them to use.

Future Plans

Possibilities for future algorithms are TempTable (what I originally called bottom-up in earlier versions of this tool), DrillDown (what I originallly called top-down), and GroupByPrefix (similar to how SqlYOG Job Agent works). Each algorithm has strengths and weaknesses. If you'd like to implement your favorite technique for finding differences between two sources of data on possibly different servers, I'm willing to help. The algorithms adhere to a simple interface that makes it pretty easy to write your own.

BIDIRECTIONAL SYNCING

Bidirectional syncing is a new, experimental feature. To make it work reliably there are a number of strict limitations:

  * only works when syncing one server to other independent servers
  * does not work in any way with replication
  * requires that the table(s) are chunkable with the Chunk algorithm
  * is not N-way, only bidirectional between two servers at a time
  * does not handle DELETE changes

For example, suppose we have three servers: c1, r1, r2. c1 is the central server, a pseudo-master to the other servers (viz. r1 and r2 are not slaves to c1). r1 and r2 are remote servers. Rows in table foo are updated and inserted on all three servers and we want to synchronize all the changes between all the servers. Table foo has columns:

  id    int PRIMARY KEY
  ts    timestamp auto updated
  name  varchar

Auto-increment offsets are used so that new rows from any server do not create conflicting primary key (id) values. In general, newer rows, as determined by the ts column, take precedence when a same but differing row is found during the bidirectional sync. ``Same but differing'' means that two rows have the same primary key (id) value but different values for some other column, like the name column in this example. Same but differing conflicts are resolved by a ``conflict''. A conflict compares some column of the competing rows to determine a ``winner''. The winnning row becomes the source and its values are used to update the other row.

There are subtle differences between three columns used to achieve bidirectional syncing that you should be familiar with: chunk column (``--chunk-column''), comparison column(s) (``--columns''), and conflict column (``--conflict-column''). The chunk column is only used to chunk the table; e.g. ``WHERE id >= 5 AND id < 10''. Chunks are checksummed and when chunk checksums reveal a difference, the tool selects the rows in that chunk and checksums the ``--columns'' for each row. If a column checksum differs, the rows have one or more conflicting column values. In a traditional unidirectional sync, the conflict is a moot point because it can be resolved simply by updating the entire destination row with the source row's values. In a bidirectional sync, however, the ``--conflict-column'' (in accordance with other "--conflict-*" options list below) is compared to determine which row is ``correct'' or ``authoritative''; this row becomes the ``source''.

To sync all three servers completely, two runs of mk-table-sync are required. The first run syncs c1 and r1, then syncs c1 and r2 including any changes from r1. At this point c1 and r2 are completely in sync, but r1 is missing any changes from r2 because c1 didn't have these changes when it and r1 were synced. So a second run is needed which syncs the servers in the same order, but this time when c1 and r1 are synced r1 gets r2's changes.

The tool does not sync N-ways, only bidirectionally between the first DSN given on the command line and each subsequent DSN in turn. So the tool in this example would be ran twice like:

  mk-table-sync --bidirectional h=c1 h=r1 h=r2

The ``--bidirectional'' option enables this feature and causes various sanity checks to be performed. You must specify other options that tell mk-table-sync how to resolve conflicts for same but differing rows. These options are:

  * L<"--conflict-column">
  * L<"--conflict-comparison">
  * L<"--conflict-value">
  * L<"--conflict-threshold">
  * L<"--conflict-error">  (optional)

Use ``--print'' to test this option before ``--execute''. The printed SQL statements will have comments saying on which host the statment would be executed if you used ``--execute''.

Technical side note: the first DSN is always the ``left'' server and the other DSNs are always the ``right'' server. Since either server can become the source or destination it's confusing to think of them as ``src'' and ``dst''. Therefore, they're generically referred to as left and right. It's easy to remember this because the first DSN is always to the left of the other server DSNs on the command line.

EXIT STATUS

Exit status is as follows:

   STATUS  MEANING
   ======  =======================================================
   0       Success.
   1       Internal error.
   2       At least one table differed on the destination.
   3       Combination of 1 and 2.

OPTIONS

Specify at least one of ``--print'', ``--execute'', or ``--dry-run''.

``--where'' and ``--replicate'' are mutually exclusive.

--algorithms

type: string; default: Chunk,Nibble,GroupBy,Stream

Algorithm to use when comparing the tables, in order of preference.

For each table, mk-table-sync will check if the table can be synced with the given algorithms in the order that they're given. The first algorithm that can sync the table is used. See ``ALGORITHMS''.

--ask-pass

Prompt for a password when connecting to MySQL.

--bidirectional

Enable bidirectional sync between first and subsequent hosts.

See ``BIDIRECTIONAL SYNCING'' for more information.

--[no]bin-log

default: yes

Log to the binary log ("SET SQL_LOG_BIN=1").

Specifying "--no-bin-log" will "SET SQL_LOG_BIN=0".

--buffer-in-mysql

Instruct MySQL to buffer queries in its memory.

This option adds the "SQL_BUFFER_RESULT" option to the comparison queries. This causes MySQL to execute the queries and place them in a temporary table internally before sending the results back to mk-table-sync. The advantage of this strategy is that mk-table-sync can fetch rows as desired without using a lot of memory inside the Perl process, while releasing locks on the MySQL table (to reduce contention with other queries). The disadvantage is that it uses more memory on the MySQL server instead.

You probably want to leave ``--[no]buffer-to-client'' enabled too, because buffering into a temp table and then fetching it all into Perl's memory is probably a silly thing to do. This option is most useful for the GroupBy and Stream algorithms, which may fetch a lot of data from the server.

--[no]buffer-to-client

default: yes

Fetch rows one-by-one from MySQL while comparing.

This option enables "mysql_use_result" which causes MySQL to hold the selected rows on the server until the tool fetches them. This allows the tool to use less memory but may keep the rows locked on the server longer.

If this option is disabled by specifying "--no-buffer-to-client" then "mysql_store_result" is used which causes MySQL to send all selected rows to the tool at once. This may result in the results ``cursor'' being held open for a shorter time on the server, but if the tables are large, it could take a long time anyway, and use all your memory.

For most non-trivial data sizes, you want to leave this option enabled.

This option is disabled when ``--bidirectional'' is used.

--charset

short form: -A; type: string

Default character set. If the value is utf8, sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.

--[no]check-master

default: yes

With ``--sync-to-master'', try to verify that the detected master is the real master.

--[no]check-privileges

default: yes

Check that user has all necessary privileges on source and destination table.

--[no]check-slave

default: yes

Check whether the destination server is a slave.

If the destination server is a slave, it's generally unsafe to make changes on it. However, sometimes you have to; ``--replace'' won't work unless there's a unique index, for example, so you can't make changes on the master in that scenario. By default mk-table-sync will complain if you try to change data on a slave. Specify "--no-slave-check" to disable this check. Use it at your own risk.

--[no]check-triggers

default: yes

Check that no triggers are defined on the destination table.

Triggers were introduced in MySQL v5.0.2, so for older versions this option has no effect because triggers will not be checked.

--chunk-column

type: string

Chunk the table on this column.

--chunk-index

type: string

Chunk the table using this index.

--chunk-size

type: string; default: 1000

Number of rows or data size per chunk.

The size of each chunk of rows for the ``Chunk'' and ``Nibble'' algorithms. The size can be either a number of rows, or a data size. Data sizes are specified with a suffix of k=kibibytes, M=mebibytes, G=gibibytes. Data sizes are converted to a number of rows by dividing by the average row length.

--columns

short form: -c; type: array

Compare this comma-separated list of columns.

--config

type: Array

Read this comma-separated list of config files; if specified, this must be the first option on the command line.

--conflict-column

type: string

Compare this column when rows conflict during a ``--bidirectional'' sync.

When a same but differing row is found the value of this column from each row is compared according to ``--conflict-comparison'', ``--conflict-value'' and ``--conflict-threshold'' to determine which row has the correct data and becomes the source. The column can be any type for which there is an appropriate ``--conflict-comparison'' (this is almost all types except, for example, blobs).