MySQL工具包Maaikit之mk-table-sync解析
MySQL工具包Maaikit之mk-table-sync解析
mk-table-sync解析01.Usage: /home/falcon/perl5/bin/mk-table-sync ... DSN ...
02.
03.Options:
04.
05. --algorithms Algorithm to use when comparing the tables, in order
06. of preference (default Chunk,Nibble,GroupBy,Stream)
07. --ask-pass Prompt for a password when connecting to MySQL
08. --bin-log Log to the binary log (SET SQL_LOG_BIN=1) (default
09. yes)
10. --buffer-in-mysql Instruct MySQL to buffer queries in its memory
11. --buffer-to-client Fetch rows one-by-one from MySQL while comparing
12. (default yes)
13. --charset -A Default character set
14. --check-master With --sync-to-master, try to verify that the
15. detected master is the real master (default yes)
16. --check-privileges Check that user has all necessary privileges on
17. source and destination table (default yes)
18. --check-slave Check whether the destination server is a slave
19. (default yes)檢測目標伺服器是否為從
20. --check-triggers Check that no triggers are defined on the
21. destination table (default yes)檢測是否在目標伺服器上有觸發器
22. --chunk-column Chunk the table on this column
23. --chunk-index Chunk the table using this index
24. --chunk-size Number of rows or data size per chunk (default 1000)
25. --columns -c Compare this comma-separated list of columns
26. --config Read this comma-separated list of config files; if
27. specified, this must be the first option on the
28. command line
29. --databases -d Sync only this comma-separated list of databases
30. --defaults-file -F Only read mysql options from the given file
31. --dry-run Analyze, decide the sync algorithm to use, print and
32. exit
33. --engines -e Sync only this comma-separated list of storage
34. engines
35. --execute Execute queries to make the tables have identical
36. data
37. --explain-hosts Print connection information and exit
38. --float-precision Precision for FLOAT and DOUBLE column comparisons
39. --foreign-key-checks Enable foreign key checks (SET FOREIGN_KEY_CHECKS=1)
40. (default yes)
41. --function Which hash function you'd like to use for checksums
42. --help Show help and exit
43. --host -h Connect to host
44. --ignore-columns Ignore this comma-separated list of column names in
45. comparisons
46. --ignore-databases Ignore this comma-separated list of databases
47. --ignore-engines Ignore this comma-separated list of storage engines
48. (default FEDERATED,MRG_MyISAM)
49. --ignore-tables Ignore this comma-separated list of tables
50. --index-hint Add FORCE/USE INDEX hints to the chunk and row
51. queries (default yes)
52. --lock Lock tables: 0=none, 1=per sync cycle, 2=per table,
53. or 3=globally
54. --lock-and-rename Lock the source and destination table, sync, then
55. swap names
56. --password -p Password to use when connecting
57. --pid Create the given PID file
58. --port -P Port number to use for connection
59. --print Print queries that will resolve differences
60. --recursion-method Preferred recursion method used to find slaves
61. --replace Write all INSERT and UPDATE statements as REPLACE
62. --replicate Sync tables listed as different in this table
63. --set-vars Set these MySQL variables (default
64. wait_timeout=10000)
65. --socket -S Socket file to use for connection
66. --sync-to-master Treat the DSN as a slave and sync it to its master
67. --tables -t Sync only this comma-separated list of tables
68. --timeout-ok Keep going if --wait fails
69. --transaction Use transactions instead of LOCK TABLES
70. --trim TRIM() VARCHAR columns in BIT_XOR and ACCUM modes
71. --unique-checks Enable unique key checks (SET UNIQUE_CHECKS=1)
72. (default yes)是否進行唯一性檢測
73. --user -u User for login if not current user
74. --verbose -v Print results of sync operations
75. --version Show version and exit
76. --wait -w How long to wait for slaves to catch up to their
77. master. Optional suffix s=seconds, m=minutes,
78. h=hours, d=days; if no suffix, s is used.
79. --where WHERE clause to restrict syncing to part of the
80. table
81.
82.Rules:
83.
84. Specify at least one of --print, --execute, or --dry-run.
85. --where and --replicate are mutually exclusive.
86.
87.DSN syntax is key=value[,key=value...] Allowable DSN keys:
88.
89. KEY COPY MEANING
90. === ==== =============================================
91. A yes Default character set
92. D yes Database containing the table to be synced
93. F yes Only read default options from the given file
94. P yes Port number to use for connection
95. S yes Socket file to use for connection
96. h yes Connect to host
97. p yes Password to use when connecting
98. t yes Table to be synced
99. u yes User for login if not current user
100.
101. If the DSN is a bareword, the word is treated as the 'h' key.
102.
103.Options and values after processing arguments:
104.
105. --algorithms Chunk,Nibble,GroupBy,Stream
106. --ask-pass FALSE
107. --bin-log TRUE
108. --buffer-in-mysql FALSE
109. --buffer-to-client TRUE
110. --charset (No value)
111. --check-master TRUE
112. --check-privileges TRUE
113. --check-slave TRUE
114. --check-triggers TRUE
115. --chunk-column (No value)
116. --chunk-index (No value)
117. --chunk-size 1000
118. --columns (No value)
119. --config /etc/maatkit/maatkit.conf,/etc/maatkit/mk-table-sync.conf,/home/falcon/.maatkit.conf,/home/falcon/.mk-table-sync.conf
120. --databases (No value)
121. --defaults-file (No value)
122. --dry-run FALSE
123. --engines (No value)
124. --execute FALSE
125. --explain-hosts FALSE
126. --float-precision (No value)
127. --foreign-key-checks TRUE
128. --function (No value)
129. --help TRUE
130. --host (No value)
131. --ignore-columns
132. --ignore-databases
133. --ignore-engines FEDERATED,MRG_MyISAM
134. --ignore-tables
135. --index-hint TRUE
136. --lock (No value)
137. --lock-and-rename FALSE
138. --password (No value)
139. --pid (No value)
140. --port (No value)
141. --print FALSE
142. --recursion-method (No value)
143. --replace FALSE
144. --replicate (No value)
145. --set-vars wait_timeout=10000
146. --socket (No value)
147. --sync-to-master FALSE
148. --tables (No value)
149. --timeout-ok FALSE
150. --transaction FALSE
151. --trim FALSE
152. --unique-checks TRUE
153. --user (No value)
154. --verbose 0
155. --version FALSE
156. --wait (No value)
157. --where (No value)
複製代碼同步2個mysql庫的相同表，只需要指定--databases參數即可，如：01.$mk-table-sync --execute --print --databases mooo\h=192.168.2.5:3306,u=falcon,p=falcon,P=3306 \h=192.168.2.5:3310,u=falcon,p=falcon,P=3310
複製代碼同步主從伺服器上非主從同步範圍的庫，用上面方法就會報REPLICATION SAFETY複製安全性的錯誤提示，解決辦法是添加 --no-check-slave 參數01.$mk-table-sync --execute --print --no-check-slave --databases mooo \h=192.168.2.5:3306,u=falcon,p=falcon,P=3306 \ （主）h=192.168.2.5:3307,u=falcon,p=falcon,P=3307 （從）
複製代碼當主從數據不同步時，這裡有一個技巧可以讓主同步從的數據即調換主從同步的DSN參數順序，注意：這時需要關閉主的binlog日誌，否則當從的數據同步到主后，同步的數據寫入主的binlog會反過來同步到從伺服器導致從的數據重複，如：01.$mk-table-sync slave-DSN master-DSN$mk-table-sync --execute --print --no-bin-log --databases test --tables tb01 \h=192.168.2.5:3307,u=falcon,p=falcon,P=3307 (從)h=192.168.2.5:3306,u=falcon,p=falcon,P=3306（主）
複製代碼
《解決方案》
謝謝分享 希望於樓主多多交流
