
平台论坛博客文库

› 论坛 › IT运维 › 监控及自动化运维技术 › MySQL工具包Maaikit之mk-table-sync解析

MySQL工具包Maaikit之mk-table-sync解析 [复制链接]

凝望长空

家境小康

论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2011-12-10 23:36 |只看该作者 |倒序浏览

MySQL工具包Maaikit之mk-table-sync解析

mk-table-sync解析

01.Usage: /home/falcon/perl5/bin/mk-table-sync [OPTION]... DSN [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. --[no]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. --[no]buffer-to-client Fetch rows one-by-one from MySQL while comparing
12. (default yes)
13. --charset -A Default character set
14. --[no]check-master With --sync-to-master, try to verify that the
15. detected master is the real master (default yes)
16. --[no]check-privileges Check that user has all necessary privileges on
17. source and destination table (default yes)
18. --[no]check-slave Check whether the destination server is a slave
19. (default yes)检测目标服务器是否为从
20. --[no]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. --[no]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. --[no]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. --[no]transaction Use transactions instead of LOCK TABLES
70. --trim TRIM() VARCHAR columns in BIT_XOR and ACCUM modes
71. --[no]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 [OPTION] 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（主）
复制代码

复制代码

运维