mysql 同步工具 mk-table-sync
google和javaeye都很牛叉哈！ &!--
INDEX BEGIN --&NAMESYNOPSISRISKSDESCRIPTIONOUTPUTREPLICATION SAFETYALGORITHMSBIDIRECTIONAL SYNCINGEXIT STATUSOPTIONSDSN OPTIONSDOWNLOADINGENVIRONMENTSYSTEM REQUIREMENTSBUGSCOPYRIGHT, LICENSE AND WARRANTYAUTHORABOUT MAATKITHISTORY AND ACKNOWLEDGEMENTSVERSION&!--
INDEX END --& NAMEmk-table-sync - Synchronize MySQL tables efficiently. 同步mysql表 SYNOPSISThis tool changes data, so for maximum safety, you should back up your databefore you use it. 为安全最好先备份数据To sync db.tbl1 from host1 to host2:
机器1 to 2 mk-table-sync --execute u=user,p=pass,h=host1,D=db,t=tbl
host2Sync all tables in host1 to host2 and host3: mk-table-sync --execute host1 host2 host3Resolve differences mk-table-checksum found on this master's slaves: mk-table-sync --execute --replicate test.checksum master1Make slave1 have the same data as its replication master: mk-table-sync --execute --sync-to-master slave1Ditto, resolving differences mk-table-checksum found: mk-table-sync --execute --sync-to-master /
--replicate test.checksum slave1Sync server2 in a master-master replication configuration, where server2's copyof db1.tbl1 is known or suspected to be incorrect: mk-table-sync --execute --sync-to-master h=server2,D=db1,t=tbl1Note that in the master-master configuration, the following will NOT do what youwant, because it will make changes directly on server2, which will then flowthrough 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 RISKSThe following section is included to inform users about the potential risks,whether known or unknown, of using this tool.
The two main categories of risksare those created by the nature of the tool (e.g. read-only tools vs. read-writetools) and those created by bugs.With great power comes great responsibility!
This tool changes data, so it is agood idea to back up your data.
It is also very powerful, which means it isvery complex, so you should run it with the --dry-run option to see what itwill do, until you're familiar with its operation.
If you want to see whichrows are different, without changing any data, use --print instead of--execute.Be careful when using mk-table-sync in any master-master setup.
Master-masterreplication is inherently tricky, and it's easy to make mistakes.
Yyou need tobe sure you're using the tool correctly for master-master replication.
See theSYNOPSIS for the overview of the correct usage.In general, this tool is best suited when your tables have a primary key orunique index.
Although it can synchronize data in tables lacking a primary keyor 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, a bug mixing collations,and a bug chunking inside mk-table-checksum boundaries.The authoritative source for updated information is always the online issuetracking system.
Issues that affect this tool will be marked as such.
You cansee 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. DESCRIPTIONWARNING this tool is unfinished and could perform slowly.
The Chunkalgorithm is great when it can be used, and so is Nibble, but otherwise GroupByis 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 ofyour 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 itmatches the source.It does not synchronize table structures, indexes, or any other schemachanges.
It synchronizes only data.It can operate through replication by comparing a slave with its master andmaking changes on the master.
These changes will flow through replication andcorrect any differences found on the slave.It accepts a list of DSNs (see the --help output) to tell it where and howto 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 --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.
The DSN is the master.
Find slaves and connect to each,
find records of differences, and fix.
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.
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. OUTPUTIf you specify the --verbose option, you'll see information about the differences between the tables.
There is one row per table.
Each server isprinted separately.
For example,
# Syncing h=host1,D=test,t=test1
# DELETE REPLACE INSERT UPDATE ALGORITHM EXIT DATABASE.TABLE
test.test1Table test.test1 on host1 required 3 INSERT statements to synchronizeand it used the Chunk algorithm (see ALGORITHMS).
Because differences werefound, its EXIT STATUS was 2.If you specify the --print option, you'll see the actual SQL statementsthat the script uses to synchronize the table if --execute is alsospecified.If you want to see the SQL statements that mk-table-sync is using to selectchunks, nibbles, rows, etc., then specify --print once and --verbosetwice.
Be careful though: this can print a lot of SQL statements.There are cases where no combination of INSERT, UPDATE or DELETEstatements can resolve differences without violating some unique key.
Forexample, 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 UPDATEstatements: +---+---+
+---+---+ | a | b |
| a | b | +---+---+
+---+---+ | 1 | 2 |
| 1 | 1 | | 2 | 1 |
| 2 | 2 | +---+---+
+---+---+The tool rewrites queries to DELETE and REPLACE in this case.
This isautomatically handled after the first index violation, so you don't have toworry about it. REPLICATION SAFETYSynchronizing a replication master and slave safely is a non-trivial problem, ingeneral.
There are all sorts of issues to think about, such as other processeschanging data, trying to change data on the slave, whether the destination andsource 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 letthe 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 themaster.
REPLACE works only if there's a unique index on the table (otherwise itjust 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 dothe right thing.
When there is no unique key on the table, there is no choicebut to change the data on the slave, and mk-table-sync will detect that you'retrying 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-masterpair, you must change the data on the destination server.
Therefore, you needto 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 thesource 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 destinationserver.
You will also need to specify --no-check-slave to keepmk-table-sync from complaining that it is changing data on a slave. ALGORITHMSThis tool has a generic data-syncing framework, within which it is possible touse any number of different algorithms to actually find differences.
It choosesthe best algorithm automatically.
While I plan to add more algorithms in thefuture, the following are implemented now:ChunkFinds 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 entirechunk.
If the chunk differs on the source and destination, checksums eachchunk's rows individually to find the rows that differ.It is efficient when the column has sufficient cardinality to make the chunksend up about the right size.The initial per-chunk checksum is quite small and results in minimal networktraffic and memory consumption.
If a chunk's rows must be examined, only theprimary key columns and a checksum are sent over the network, not the entirerow.
If a row is found to be different, the entire row will be fetched, but notbefore. 和rsync类似哦，都是先检查checksum是否一致再判断是否需要进一步比较NibbleFinds an index and ascends the index in fixed-size nibbles of --chunk-sizerows, using a non-backtracking algorithm (see mk-archiver for more on thisalgorithm).
It is very similar to Chunk, but instead of pre-calculatingthe boundaries of each piece of the table based on index cardinality, it usesLIMIT to define each nibble's upper limit, and the previous nibble's upperlimit to define the lower limit.It works in steps: one query finds the row that will define the next nibble'supper boundary, and the next query checksums the entire nibble.
If the nibblediffers between the source and destination, it examines the nibble row-by-row,just as Chunk does.GroupBySelects the entire table grouped by all columns, with a COUNT(*) pares all columns, and if they're the same, compares the COUNT(*) column'svalue to determine how many rows to insert or delete into the destination.Works on tables with no primary key or unique index.StreamSelects the entire table in one big stream and compares all columns.
Selectsall columns.
Much less efficient than the other algorithms, but works whenthere is no suitable index for them to use.Future PlansPossibilities for future algorithms are TempTable (what I originally calledbottom-up in earlier versions of this tool), DrillDown (what I originalllycalled top-down), and GroupByPrefix (similar to how SqlYOG Job Agent works).Each algorithm has strengths and weaknesses.
If you'd like to implement yourfavorite technique for finding differences between two sources of data onpossibly different servers, I'm willing to help.
The algorithms adhere to asimple interface that makes it pretty easy to write your own. BIDIRECTIONAL SYNCINGBidirectional syncing is a new, experimental feature.
To make it workreliably 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 changesFor example, suppose we have three servers: c1, r1, r2.
c1 is the centralserver, a pseudo-master to the other servers (viz. r1 and r2 are not slavesto c1).
r1 and r2 are remote servers.
Rows in table foo are updated andinserted on all three servers and we want to synchronize all the changesbetween all the servers.
Table foo has columns:
int PRIMARY KEY
timestamp auto updated
varcharAuto-increment offsets are used so that new rows from any server do notcreate conflicting primary key (id) values.
In general, newer rows, asdetermined by the ts column, take precedence when a same but differing rowis found during the bidirectional sync.
"Same but differing" means thattwo rows have the same primary key (id) value but different values for someother column, like the name column in this example.
Same but differingconflicts are resolved by a "conflict".
A conflict compares some column ofthe competing rows to determine a "winner".
The winnning row becomes thesource and its values are used to update the other row.There are subtle differences between three columns used to achievebidirectional syncing that you should be familiar with: chunk column(--chunk-column), comparison column(s) (--columns), and conflictcolumn (--conflict-column).
The chunk column is only us e.g. "WHERE id &= 5 AND id & 10".
Chunks are checksummed and whenchunk checksums reveal a difference, the tool selects the rows in thatchunk and checksums the --columns for each row.
If a column checksumdiffers, the rows have one or more conflicting column values.
In atraditional unidirectional sync, the conflict is a moot point because it canbe resolved simply by updating the entire destination row with the sourcerow's values.
In a bidirectional sync, however, the --conflict-column(in accordance with other --conflict-* options list below) is comparedto determine which row is "correct" or "authoritative"; this row becomesthe "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 changesfrom r1.
At this point c1 and r2 are completely in sync, but r1 is missingany changes from r2 because c1 didn't have these changes when it and r1were synced.
So a second run is needed which syncs the servers in the sameorder, 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 DSNgiven on the command line and each subsequent DSN in turn.
So the tool inthis example would be ran twice like:
mk-table-sync --bidirectional h=c1 h=r1 h=r2The --bidirectional option enables this feature and causes varioussanity checks to be performed.
You must specify other options that tellmk-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 printedSQL statements will have comments saying on which host the statmentwould be executed if you used --execute.Technical side note: the first DSN is always the "left" server and the otherDSNs are always the "right" server.
Since either server can become the sourceor 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 rememberthis because the first DSN is always to the left of the other server DSNs onthe command line. EXIT STATUSExit status is as follows:
=======================================================
Internal error.
At least one table differed on the destination.
Combination of 1 and 2. OPTIONSSpecify at least one of --print, --execute, or --dry-run.dry-run 不执行！--where and --replicate are mutually exclusive.--algorithmstype: default: Chunk,Nibble,GroupBy,StreamAlgorithm to use when comparing the tables, in order of preference.For each table, mk-table-sync will check if the table can be synced withthe given algorithms in the order that they're given.
The first algorithmthat can sync the table is used.
See ALGORITHMS.--ask-passPrompt for a password when connecting to MySQL.--bidirectionalEnable bidirectional sync between first and subsequent hosts.See BIDIRECTIONAL SYNCING for more information.--[no]bin-logdefault: yesLog to the binary log (SET SQL_LOG_BIN=1).Specifying --no-bin-log will SET SQL_LOG_BIN=0.--buffer-in-mysqlInstruct 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 tableinternally before sending the results back to mk-table-sync.
The advantage ofthis strategy is that mk-table-sync can fetch rows as desired without using alot of memory inside the Perl process, while releasing locks on the MySQL table(to reduce contention with other queries).
The disadvantage is that it usesmore memory on the MySQL server instead.You probably want to leave --[no]buffer-to-client enabled too, becausebuffering into a temp table and then fetching it all into Perl's memory isprobably a silly thing to do.
This option is most useful for the GroupBy andStream algorithms, which may fetch a lot of data from the server.--[no]buffer-to-clientdefault: yesFetch rows one-by-one from MySQL while comparing.This is disabled by default.
If enabled, all rows will be fetched into memoryfor comparing.
This may result in the results "cursor" being held open for ashorter time on the server, but if the tables are large, it could take a longtime anyway, and eat all your memory.
For most non-trivial data sizes, youwant to leave this disabled.--charsetshort form: -A; type: stringDefault character set.
If the value is utf8, sets Perl's binmode onSTDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, andruns SET NAMES UTF8 after connecting to MySQL.
Any other value setsbinmode on STDOUT without the utf8 layer, and runs SET NAMES afterconnecting to MySQL.--[no]check-masterdefault: yesWith --sync-to-master, try to verify that the detectedmaster is the real master.--[no]check-privilegesdefault: yesCheck that user has all necessary privileges on source and destination table.--[no]check-slavedefault: yesCheck whether the destination server is a slave.If the destination server is a slave, it's generally unsafe to make changes onit.
However, s --replace won't work unless there's aunique index, for example, so you can't make changes on the master in thatscenario.
By default mk-table-sync will complain if you try to change data ona slave.
Specify --no-slave-check to disable this check.
Use it at your ownrisk.--[no]check-triggersdefault: yesCheck that no triggers are defined on the destination table.Triggers were introduced in MySQL v5.0.2, so for older versions this optionhas no effect because triggers will not be checked.--chunk-columntype: stringChunk the table on this column.--chunk-indextype: stringChunk the table using this index.--chunk-sizetype: default: 1000Number 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 arespecified with a suffix of k=kibibytes, M=mebibytes, G=gibibytes.
Data sizesare converted to a number of rows by dividing by the average row length.--columnsshort form: -c; type: arrayCompare this comma-separated list of columns.--configtype: ArrayRead this comma-separated
if specified, this must be thefirst option on the command line.--conflict-columntype: stringCompare this column when rows conflict during a --bidirectional sync.When a same but differing row is found the value of this column from eachrow is compared according to --conflict-comparison, --conflict-valueand --conflict-threshold to determine which row has the correct data andbecomes the source.
The column can be any type for which there is anappropriate --conflict-comparison (this is almost all types except, forexample, blobs).This option only works with --bidirectional.See BIDIRECTIONAL SYNCING for more information.--conflict-comparisontype: stringChoose the --conflict-column with this property as the source.The option affects how the --conflict-column values from the conflictingrows are compared.
Possible comparisons are one of these MAGIC_comparisons:
newest|oldest|greatest|least|equals|matches
COMPARISON
CHOOSES ROW WITH
==========
=========================================================
Newest temporal L&--conflict-column& value
Oldest temporal L&--conflict-column& value
Greatest numerical L&--conflict-column& value
Least numerical L&--conflict-column& value
L&--conflict-column& value equal to L&--conflict-value&
L&--conflict-column& value matching Perl regex pattern
L&--conflict-value&This option only works with --bidirectional.See BIDIRECTIONAL SYNCING for more information.--conflict-errortype: default: warnHow to report unresolvable conflicts and conflict errorsThis option changes how the user is notified when a conflict cannot beresolved or causes some kind of error.
Possible values are:
* warn: Print a warning to STDERR about the unresolvable conflict
Die, stop syncing, and print a warning to STDERRThis option only works with --bidirectional.See BIDIRECTIONAL SYNCING for more information.--conflict-thresholdtype: stringAmount by which one --conflict-column must exceed the other.The --conflict-threshold prevents a conflict from being resolved ifthe absolute difference between the two --conflict-column values isless than this amount.
For example, if two --conflict-column havetimestamp values " 12:00:00" and " 12:05:00" the differenceis 5 minutes.
If --conflict-threshold is set to "5m" the conflict willbe resolved, but if --conflict-threshold is set to "6m" the conflictwill fail to resolve because the difference is not greater than or equalto 6 minutes.
In this latter case, --conflict-error will reportthe failure.This option only works with --bidirectional.See BIDIRECTIONAL SYNCING for more information.--conflict-valuetype: stringUse this value for certain --conflict-comparison.This option gives the value for equals and matches--conflict-comparison.This option only works with --bidirectional.See BIDIRECTIONAL SYNCING for more information.--databasesshort form: -d; type: hashSync only this comma-separated list of databases.A common request is to sync tables from one database with tables from anotherdatabase on the same or different server.
This is not yet possible.--databases will not do it, and you can't do it with the D part of the DSNeither because in the absence of a table name it assumes the whole servershould be synced and the D part controls only the connection's default database.--defaults-fileshort form: -F; type: stringOnly read mysql options from the given file.
You must give an absolute pathname.--dry-runAnalyze, decide the sync algorithm to use, print and exit.Implies --verbose so you can see the results.
The results are in the sameoutput format that you'll see from actually running the tool, but there will bezeros for rows affected.
This is because the tool actually executes, but stopsbefore it compares any data and just returns zeros.
The zeros do not mean thereare no changes to be made.--enginesshort form: -e; type: hashSync only this comma-separated list of storage engines.--executeExecute queries to make the tables have identical data.This option makes mk-table-sync actually sync table data by executing allthe queries that it created to resolve table differences.
Therefore, thetables will be changed!
And unless you also specify --verbose, thechanges will be made silently.
If this is not what you want, see--print or --dry-run.--explain-hostsPrint connection information and exit.Print out a list of hosts to which mk-table-sync will connect, with allthe various connection options, and exit.--float-precisiontype: intPrecision for FLOAT and DOUBLE column comparisons.If you specify this option, FLOAT and DOUBLE columns will be rounded to thespecified number of digits after the decimal point for the checksum.
This canavoid mismatches due to different floating-point representations ofthe same values on different MySQL versions and hardware.--[no]foreign-key-checksdefault: yesEnable foreign key checks (SET FOREIGN_KEY_CHECKS=1).Specifying --no-foreign-key-checks will SET FOREIGN_KEY_CHECKS=0.--functiontype: stringWhich hash function you'd like to use for checksums.The default is CRC32.
Other good choices include MD5 and SHA1.
If youhave installed the FNV_64 user-defined function, mk-table-sync will detectit and prefer to use it, because it is much faster than the built-ins.
You canalso use MURMUR_HASH if you've installed that user-defined function.
Both ofthese are distributed with Maatkit.
See mk-table-checksum for moreinformation and benchmarks.--helpShow help and exit.--[no]hex-blobdefault: yesHEX() BLOB, TEXT and BINARY columns.When row data from the source is fetched to create queries to sync thedata (i.e. the queries seen with --print and exeucted by --execute),binary columns are wrapped in HEX() so the binary data does not producean invalid SQL statement.
You can disable this option but you probablyshouldn't.--hostshort form: -h; type: stringConnect to host.--ignore-columnstype: HashIgnore this comma-separated list of column names in comparisons.This option causes columns not to be compared.
However, if a row is determinedto differ between tables, all columns in that row will be synced, regardless.(It is not currently possible to exclude columns from the sync process itself,only from the comparison.)--ignore-databasestype: HashIgnore this comma-separated list of databases.--ignore-enginestype: H default: FEDERATED,MRG_MyISAMIgnore this comma-separated list of storage engines.--ignore-tablestype: HashIgnore this comma-separated list of tables.Table names may be qualified with the database name.--[no]index-hintdefault: yesAdd FORCE/USE INDEX hints to the chunk and row queries.By default mk-table-sync adds a FORCE/USE INDEX hint to each SQL statementto coerce MySQL into using the index chosen by the sync algorithm or specifiedby --chunk-index.
This is usually a good thing, but in rare cases theindex may not be the best for the query so you can suppress the index hintby specifying --no-index-hint and let MySQL choose the index.This does not affect the queries printed by -- it only affects thechunk and row queries that mk-table-sync uses to select and compare rows.--locktype: intLock tables: 0=none, 1=per sync cycle, 2=per table, or 3=globally.This uses LOCK TABLES.
This can help prevent tables being changed whileyou're examining them.
The possible values are as follows:
=======================================================
Never lock tables.
Lock and unlock one time per sync cycle (as implemented
by the syncing algorithm).
This is the most granular
level of locking available.
For example, the Chunk
algorithm will lock each chunk of C&N& rows, and then
unlock them if they are the same on the source and the
destination, before moving on to the next chunk.
Lock and unlock before and after each table.
Lock and unlock once for every server (DSN) synced, with
C&FLUSH TABLES WITH READ LOCK&.A replication slave is never locked if --replicate or --sync-to-masteris specified, since in theory locking the table on the master should prevent anychanges from taking place.
(You are not changing data on your slave, right?)If --wait is given, the master (source) is locked and then the tool waitsfor the slave to catch up to the master before continuing.If --transaction is specified, LOCK TABLES is not used.
Instead, lockand unlock are implemented by beginning and committing transactions.The exception is if --lock is 3.If --no-transaction is specified, then LOCK TABLES is used for anyvalue of --lock. See --[no]transaction.--lock-and-renameLock the source and destination table, sync, then swap names.
This is useful asa less-blocking ALTER TABLE, once the tables are reasonably in sync with eachother (which you may choose to accomplish via any number of means, includingdump and reload or even something like mk-archiver).
It requires exactly twoDSNs and assumes they are on the same server, so it does no waiting forreplication or the like.
Tables are locked with LOCK TABLES.--passwordshort form: -p; type: stringPassword to use when connecting.--pidtype: stringCreate the given PID file.
The file contains the process ID of the script.The PID file is removed when the script exits.
Before starting, the scriptchecks if the PID file already exists.
If it does not, then the script createsand writes its own PID to it.
If it does, then the script checks the following:if the file contains a PID and a process is running with that PID, or, if there is no process running with that PID, then thescript overwrites the file with its own PID else, if the filecontains no PID, then the script dies.--portshort form: -P; type: intPort number to use for connection.--printPrint queries that will resolve differences.If you don't trust mk-table-sync, or just want to see what it will do, thisis a good way to be safe.
These queries are valid SQL and you can run themyourself if you want to sync the tables manually.--recursion-methodtype: stringPreferred recursion method used to find slaves.Possible methods are:
===========
================
processlist
SHOW PROCESSLIST
SHOW SLAVE HOSTSThe processlist method is preferred because SHOW SLAVE HOSTS is not reliable.However, the hosts method is required if the server uses a non-standardport (not 3306).
Usually mk-table-sync does the right thing and findsthe slaves, but you may give a preferred method and it will be used first.If it doesn't find any slaves, the other methods will be tried.--replaceWrite all INSERT and UPDATE statements as REPLACE.This is automatically switched on as needed when there are unique indexviolations.--replicatetype: stringSync tables listed as different in this table.Specifies that mk-table-sync should examine the specified table to find datathat differs.
The table is exactly the same as the argument of the same name tomk-table-checksum.
That is, it contains records of which tables (and rangesof values) differ between the master and slave.For each table and range of values that shows differences between the master andslave, mk-table-checksum will sync that table, with the appropriate WHEREclause, to its master.This automatically sets --wait to 60 and causes changes to be made on themaster instead of the slave.If --sync-to-master is specified, the tool will assume the server youspecified is the slave, and connect to the master as usual to sync.Otherwise, it will try to use SHOW PROCESSLIST to find slaves of the serveryou specified.
If it is unable to find any slaves via SHOW PROCESSLIST, itwill inspect SHOW SLAVE HOSTS instead.
You must configure each slave'sreport-host, report-port and other options for this to work right.
Afterfinding slaves, it will inspect the specified table on each slave to find datathat needs to be synced, and sync it.The tool examines the master's copy of the table first, assuming that the masteris potentially a slave as well.
Any table that shows differences there willNOT be synced on the slave(s).
For example, suppose your replication is setup as A-&B, B-&C, B-&D.
Suppose you use this argument and specify server B.The tool will examine server B's copy of the table.
If it looks like server B'sdata in table test.tbl1 is different from server A's copy, the tool will notsync that table on servers C and D.--set-varstype: default: wait_timeout=10000Set these MySQL variables.
Immediately after connecting to MySQL, thisstring will be appended to SET and executed.--socketshort form: -S; type: stringSocket file to use for connection.--sync-to-masterTreat the DSN as a slave and sync it to its master.Treat the server you specified as a slave.
Inspect SHOW SLAVE STATUS,connect to the server's master, and treat the master as the source and the slaveas the destination.
Causes changes to be made on the master.
Sets --waitto 60 by default, sets --lock to 1 by default, and disables--[no]transaction by default.
See also --replicate, which changesthis option's behavior.--tablesshort form: -t; type: hashSync only this comma-separated list of tables.Table names may be qualified with the database name.--timeout-okKeep going if --wait fails.If you specify --wait and the slave doesn't catch up to the master'sposition before the wait times out, the default behavior is to abort.
Thisoption makes the tool keep going anyway.
Warning: if you are trying to get aconsistent comparision between the two servers, you probably don't want to keepgoing after a timeout.--[no]transactionUse transactions instead of LOCK TABLES.The granularity of beginning and committing transactions is controlled by--lock.
This is enabled by default, but since --lock is disabled bydefault, it has no effect.Most options that enable locking also disable transactions by default, so ifyou want to use transactional locking (via LOCK IN SHARE MODE and FORUPDATE, you must specify --transaction explicitly.If you don't specify --transaction explicitly mk-table-sync will decide ona per-table basis whether to use transactions or table locks.
It currentlyuses transactions on InnoDB tables, and table locks on all others.If --no-transaction is specified, then mk-table-sync will not usetransactions at all (not even for InnoDB tables) and locking is controlledby --lock.--trimTRIM() VARCHAR columns in BIT_XOR and ACCUM modes.
Helps whencomparing MySQL 4.1 to &= 5.0.This is useful when you don't care about the trailing space differences betweenMySQL versions which vary in their handling of trailing spaces. MySQL 5.0 and later all retain trailing spaces in VARCHAR, while previous versions would remove them.--[no]unique-checksdefault: yesEnable unique key checks (SET UNIQUE_CHECKS=1).Specifying --no-unique-checks will SET UNIQUE_CHECKS=0.--usershort form: -u; type: stringUser for login if not current user.--verboseshort form: -v; cumulative: yesPrint results of sync operations.See OUTPUT for more details about the output.--versionShow version and exit.--waitshort form: -w; type: timeHow long to wait for slaves to catch up to their master.Make the master wait for the slave to catch up in replication before comparingthe tables.
The value is the number of seconds to wait before timing out (seealso --timeout-ok).
Sets --lock to 1 and --[no]transaction to 0by default.
If you see an error such as the following,
MASTER_POS_WAIT returned -1It means the timeout was exceeded and you need to increase it.The default value of this option is influenced by other options.
To see whatvalue is in effect, run with --help.--wheretype: stringWHERE clause to restrict syncing to part of the table. DSN OPTIONSThese DSN options are used to create a DSN.
Each option is given likeoption=value.
The options are case-sensitive, so P and p are not thesame option.
There cannot be whitespace before or after the = andif the value contains whitespace it must be quoted.
DSN options arecomma-separated.
See the maatkit manpage for full details.Adsn: copy: yesDefault character set.Ddsn: copy: yesDatabase containing the table to be synced.Fdsn: mysql_read_default_ copy: yesOnly read default options from the given filehdsn: copy: yesConnect to host.pdsn: copy: yesPassword to use when connecting.Pdsn: copy: yesPort number to use for connection.Sdsn: mysql_ copy: yesSocket file to use for connection.tcopy: yesTable to be synced.udsn: copy: yesUser for login if not current user. DOWNLOADINGYou can download Maatkit from Google Code at/p/maatkit/, or you can get any of the toolseasily with a command like the following:
wget http://www.maatkit.org/get/toolname
wget http://www.maatkit.org/trunk/toolnameWhere toolname can be replaced with the name (or fragment of a name) of anyof the Maatkit tools.
Once downloaded, they' no installation isneeded.
The first URL gets the latest released version of the tool, and thesecond gets the latest trunk code from Subversion. ENVIRONMENTThe environment variable MKDEBUG enables verbose debugging output in all of theMaatkit tools:
MKDEBUG=1 mk-.... SYSTEM REQUIREMENTSYou need Perl, DBI, DBD::mysql, and some core packages that ought to beinstalled in any reasonably new version of Perl. BUGSFor a list of known bugs see: http://www.maatkit.org/bugs/mk-table-sync.Please use Google Code Issues and Groups to report bugs or request support:/p/maatkit/.
You can also join #maatkit on Freenode todiscuss Maatkit.Please include the complete command-line used to reproduce the problem you areseeing, the version of all MySQL servers involved, the complete output of thetool when run with --version, and if possible, debugging output produced byrunning with the MKDEBUG=1 environment variable. COPYRIGHT, LICENSE AND WARRANTYThis program is copyright
Baron Schwartz.Feedback and improvements are welcome.THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIEDWARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OFMERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.This prog you can redistribute it and/or modify it underthe terms of the GNU General Public License as published by the Free SoftwareFoundation, version 2; OR the Perl Artistic License.
On UNIX and similarsystems, you can issue `man perlgpl' or `man perlartistic' to read theselicenses.You should have received a copy of the GNU General Public License al if not, write to the Free Software Foundation, Inc., 59 TemplePlace, Suite 330, Boston, MA
USA. AUTHORBaron Schwartz ABOUT MAATKITThis tool is part of Maatkit, a toolkit for power users of MySQL.
Maatkitwas created by Baron S Baron and Daniel Nichter are the primarycode contributors.
Both are employed by Percona.
Financial support forMaatkit development is primarily provided by Percona and its clients. HISTORY AND ACKNOWLEDGEMENTSMy work is based in part on Giuseppe Maxia's work on distributed databases,/articles// and code derived from thatarticle.
There is more explanation, and a link to the code, athttp://www.perlmonks.org/.Another programmer extended Maxia's work even further.
Fabien Coelho changedand generalized Maxia's technique, introducing symmetry and avoiding someproblems that might have caused too-frequent checksum collisions.
This workgrew into pg_comparator, http://www.coelho.net/pg_comparator/.
Coelho alsoexplained the technique further in a paper titled "Remote Comparison of DatabaseTables" (http://cri.ensmp.fr/classement/doc/A-375.pdf).This existing literature mostly addressed how to find the differences betweenthe tables, not how to resolve them once found.
I needed a tool that would notonly find them efficiently, but would then resolve them.
I first began thinkingabout how to improve the technique further with my article/mysql-data-diff-algorithm,where I discussed a number of problems with the Maxia/Coelho "bottom-up"algorithm.
After writing that article, I began to write this tool.
I wanted toactually implement their algorithm with some improvements so I was sure Iunderstood it completely.
I discovered it is not what I thought it was, and isconsiderably more complex than it appeared to me at first.
Fabien Coelho waskind enough to address some questions over email.The first versions of this tool implemented a version of the Coelho/Maxiaalgorithm, which I called "bottom-up", and my own, which I called "top-down."Those algorithms are considerably more complex than the current algorithms andI have removed them from this tool, and may add them back later.
Theimprovements to the bottom-up algorithm are my original work, as is thetop-down algorithm.
The techniques to actually resolve the differences arealso my own work.Another tool that can synchronize tables is the SQLyog Job Agent from webyog.Thanks to Rohit Nadhani, SJA's author, for the conversations about the generaltechniques.
There is a comparison of mk-table-sync and SJA at/maatkit-vs-sqlyogThanks to the following people and organizations for helping in many ways:The Rimm-Kaufman Group /,MySQL AB /,Blue Ridge InternetWorks /,Percona /,Fabien Coelho,Giuseppe Maxia and others at MySQL AB,Kristian Koehntopp (MySQL AB),Rohit Nadhani
大小: 6.1 KB
最新教程周点击榜
微信扫一扫}