1MYSQLDIFF(1) MySQL Utilities MYSQLDIFF(1)
2
6 mysqldiff - Identify Differences Among Database Objects
7
9 mysqldiff [options] {db1[:db1] | {db1.obj1[:db2.obj2]} ...
10
12 This utility reads the definitions of objects and compares them using a
13 diff-like method to determine whether they are the same. The utility
14 displays the differences for objects that are not the same.
15 Use the notation db1:db2 to name two databases to compare, or,
17 alternatively just db1 to compare two databases with the same name. The
18 latter case is a convenience notation for comparing same-named
19 databases on different servers.
20 The comparison may be executed against two databases of different names
22 on a single server by specifying only the --server1 option. The user
23 can also connect to another server by specifying the --server2 option.
24 In this case, db1 is taken from server1 and db2 from server2.
25 When a database pair is specified, all objects in one database are
27 compared to the corresponding objects in the other. Objects not
28 appearing in either database produce an error.
29 To compare a specific pair of objects, add an object name to each
31 database name using the db.obj format. For example, use the
32 db1.obj1:db2.obj2 format to compare two named objects, or db1.obj1 to
33 compare an object with the same name in databases with the same name.
34 It is not legal to mix a database name with an object name. For
35 example, db1.obj1:db2 and db1:db2.obj2 are illegal formats.
36 The comparison may be run against a single server for comparing two
38 databases of different names on the same server by specifying only the
39 --server1 option. Alternatively, you can also connect to another server
40 by specifying the --server2 option. In this case, the first object to
41 compare is taken from server1 and the second from server2.
42 By default, the utility generates object differences as a difference
44 report. However, you can generate a transformation report containing
45 SQL statements for transforming the objects for conformity instead. Use
46 the 'sql' value for the --difftype option to produce a listing that
47 contains the appropriate ALTER commands to conform the object
48 definitions for the object pairs specified. If a transformation cannot
49 be formed, the utility reports the diff of the object along with a
50 warning statement. See important limitations in the NOTES section.
51 To specify how to display the diff styled output, use one of the
53 following values with the --difftype option:
54 · unified (default)
56 Display unified format output.
58 · context
60 Display context format output.
62 · differ
64 Display differ-style format output.
66 · sql
68 Display SQL transformation statement output.
70 The --changes-for option controls the direction of the difference (by
72 specifying the object to be transformed) in either the difference
73 report (default) or the transformation report (designated with the
74 --difftype=sql option). Consider the following command:
75 shell> mysqldiff --server1=root@host1 --server2=root@host2 --difftype=sql \
77 db1.table1:dbx.table3
78 The leftmost database (db1) exists on the server designated by the
80 --server1 option (host1). The rightmost database (dbx) exists on the
81 server designated by the --server2 option (host2).
82 · --changes-for=server1: Produces output that shows how to make the
84 definitions of objects on server1 like the definitions of the
85 corresponding objects on server2.
86 · --changes-for=server2: Produces output that shows how to make the
88 definitions of objects on server2 like the definitions of the
89 corresponding objects on server1.
90 The default direction is server1.
92 For the sql difference format, you can also see the reverse
94 transformation by specifying the --show-reverse option.
95 The utility stops at the first occurrence of missing objects or when an
97 object does not match. To override this behavior, specify the --force
98 option to cause the utility to attempt to compare all objects listed as
99 arguments. OPTIONS.PP mysqldiff accepts the following command-line
100 options:
101 · --help
103 Display a help message and exit.
105 · --license
107 Display license information and exit.
109 · --changes-for=<direction>
111 Specify the server to show transformations to match the other
113 server. For example, to see the transformation for transforming
114 object definitions on server1 to match the corresponding
115 definitions on server2, use --changes-for=server1. Permitted values
116 are server1 and server2. The default is server1.
117 · --character-set=<charset>
119 Sets the client character set. The default is retrieved from the
121 server variable character_set_client.
122 · --difftype=<difftype>, -d<difftype>
124 Specify the difference display format. Permitted format values are
126 unified (default), context, differ, and sql.
127 · --compact
129 Compacts the output by reducing the control lines that are
131 displayed in the diff results. This option should be used together
132 with one of the following difference types: unified or context.
133 · --force
135 Do not halt at the first difference found. Process all objects to
137 find all differences.
138 · --quiet, -q
140 Do not print anything. Return only an exit code of success or
142 failure.
143 · --server1=<source>
145 Connection information for the first server.
147 To connect to a server, it is necessary to specify connection
149 parameters such as user name, host name, password, and either a
150 port or socket. MySQL Utilities provides a number of ways to
151 provide this information. All of the methods require specifying
152 your choice via a command-line option such as --server, --master,
153 --slave, etc. The methods include the following in order of most
154 secure to least secure.
155 · Use login-paths from your .mylogin.cnf file (encrypted, not
157 visible). Example : <login-path>[:<port>][:<socket>]
158 · Use a configuration file (unencrypted, not visible) Note:
160 available in release-1.5.0. Example :
161 <configuration-file-path>[:<section>]
162 · Specify the data on the command-line (unencrypted, visible).
164 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
165 · --server2=<source>
168 Connection information for the second server.
170 To connect to a server, it is necessary to specify connection
172 parameters such as user name, host name, password, and either a
173 port or socket. MySQL Utilities provides a number of ways to
174 provide this information. All of the methods require specifying
175 your choice via a command-line option such as --server, --master,
176 --slave, etc. The methods include the following in order of most
177 secure to least secure.
178 · Use login-paths from your .mylogin.cnf file (encrypted, not
180 visible). Example : <login-path>[:<port>][:<socket>]
181 · Use a configuration file (unencrypted, not visible) Note:
183 available in release-1.5.0. Example :
184 <configuration-file-path>[:<section>]
185 · Specify the data on the command-line (unencrypted, visible).
187 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
188 · --show-reverse
191 Produce a transformation report containing the SQL statements to
193 conform the object definitions specified in reverse. For example,
194 if --changes-for is set to server1, also generate the
195 transformation for server2.
196 Note
198 The reverse changes are annotated and marked as comments.
199 · --skip-table-options
201 Ignore the differences between all table options, such as
203 AUTO_INCREMENT, ENGINE, CHARSET, etc.). A warning is issued if the
204 --skip-table-options option is used and table option differences
205 are found.
206 · --ssl-ca
208 The path to a file that contains a list of trusted SSL CAs.
210 · --ssl-cert
212 The name of the SSL certificate file to use for establishing a
214 secure connection.
215 · --ssl-cert
217 The name of the SSL key file to use for establishing a secure
219 connection.
220 · --ssl
222 Specifies if the server connection requires use of SSL. If an
224 encrypted connection cannot be established, the connection attempt
225 fails. Default setting is 0 (SSL not required).
226 · --verbose, -v
228 Specify how much information to display. Use this option multiple
230 times to increase the amount of information. For example, -v =
231 verbose, -vv = more verbose, -vvv = debug.
232 · --version
234 Display version information and exit.
236 · --width=<number>
238 Change the display width of the test report. The default is 75
240 characters.
241 NOTES.PP You must provide connection parameters (user, host, password,
242 and so forth) for an account that has the appropriate privileges to
243 access all objects to be compared.
244 The SQL transformation feature has these known limitations:
246 · When tables with partition differences are encountered, the utility
248 generates the ALTER TABLE statement for all other changes but
249 prints a warning and omits the partition differences.
250 · If the transformation detects table options in the source table
252 (specified with the --changes-for option) that are not changed or
253 do not exist in the target table, the utility generates the ALTER
254 TABLE statement for all other changes but prints a warning and
255 omits the table option differences.
256 · Rename for events is not supported. This is because mysqldiff
258 compares objects by name. In this case, depending on the direction
259 of the diff, the event is identified as needing to be added or a
260 DROP EVENT statement is generated.
261 · Changes in the definer clause for events are not supported.
263 · SQL extensions specific to MySQL Cluster are not supported.
265 For the --difftype option, the permitted values are not case sensitive.
267 In addition, values may be specified as any unambiguous prefix of a
268 valid value. For example, --difftype=d specifies the differ type. An
269 error occurs if a prefix matches more than one valid value.
270 The path to the MySQL client tools should be included in the PATH
272 environment variable in order to use the authentication mechanism with
273 login-paths. This will allow the utility to use the my_print_defaults
274 tools which is required to read the login-path values from the login
275 configuration file (.mylogin.cnf).
276 If any database object identifier specified as an argument contains
278 special characters or is a reserved word, then it must be appropriately
279 quoted with backticks (`). In turn, names quoted with backticks must
280 also be quoted with single or double quotes depending on the operating
281 system, i.e. (") in Windows or (') in non-Windows systems, in order for
282 the utilities to read backtick quoted identifiers as a single argument.
283 For example, to show the difference between table weird`table1 from
284 database weird`db.name and table weird`table2 from database
285 other:weird`db.name, the objects pair must be specified using the
286 following syntax (in non-Windows):
287 '`weird``db.name`.`weird``table1`:`other:weird``db.name`.`weird``table2`'.
288 EXAMPLES.PP To compare the employees and emp databases on the local
289 server, use this command:
290 shell> mysqldiff --server1=root@localhost employees:emp1
292 # server1 on localhost: ... connected.
293 WARNING: Objects in server1:employees but not in server2:emp1:
294 EVENT: e1
295 Compare failed. One or more differences found.
296 shell> mysqldiff --server1=root@localhost \
297 employees.t1:emp1.t1 employees.t3:emp1.t3
298 # server1 on localhost: ... connected.
299 # Comparing employees.t1 to emp1.t1 [PASS]
300 # server1 on localhost: ... connected.
301 # Comparing employees.t3 to emp1.t3 [PASS]
302 Success. All objects are the same.
303 shell> mysqldiff --server1=root@localhost \
304 employees.salaries:emp1.salaries --differ
305 # server1 on localhost: ... connected.
306 # Comparing employees.salaries to emp1.salaries [FAIL]
307 # Object definitions are not the same:
308 CREATE TABLE `salaries` (
309 `emp_no` int(11) NOT NULL,
310 `salary` int(11) NOT NULL,
311 `from_date` date NOT NULL,
312 `to_date` date NOT NULL,
313 PRIMARY KEY (`emp_no`,`from_date`),
314 KEY `emp_no` (`emp_no`)
315 - ) ENGINE=InnoDB DEFAULT CHARSET=latin1
316 ? ^^^^^
317 + ) ENGINE=MyISAM DEFAULT CHARSET=latin1
318 ? ++ ^^^
319 Compare failed. One or more differences found.
320 The following examples show how to generate a transformation report.
322 Assume the following object definitions:
323 Host1:
325 CREATE TABLE db1.table1 (num int, misc char(30));
327 Host2:
329 CREATE TABLE dbx.table3 (num int, notes char(30), misc char(55));
331 To generate a set of SQL statements that transform the definition of
333 db1.table1 to dbx.table3, use this command:
334 shell> mysqldiff --server1=root@host1 --server2=root@host2 \
336 --changes-for=server1 --difftype=sql \
337 db1.table1:dbx.table3
338 # server1 on host1: ... connected.
339 # server2 on host2: ... connected.
340 # Comparing db1.table1 to dbx.table3 [FAIL]
341 # Transformation statements:
342 ALTER TABLE db1.table1
343 ADD COLUMN notes char(30) AFTER a,
344 CHANGE COLUMN misc misc char(55);
345 Compare failed. One or more differences found.
346 To generate a set of SQL statements that transform the definition of
348 dbx.table3 to db1.table1, use this command:
349 shell> mysqldiff --server1=root@host1 --server2=root@host2 \
351 --changes-for=server2 --difftype=sql \
352 db1.table1:dbx.table3
353 # server1 on host1: ... connected.
354 # server2 on host2: ... connected.
355 # Comparing db1.table1 to dbx.table3 [FAIL]
356 # Transformation statements:
357 ALTER TABLE dbx.table3
358 DROP COLUMN notes,
359 CHANGE COLUMN misc misc char(30);
360 Compare failed. One or more differences found.
361 To generate a set of SQL statements that transform the definitions of
363 dbx.table3 and db1.table1 in both directions, use this command:
364 shell> mysqldiff --server1=root@host1 --server2=root@host2 \
366 --show-reverse --difftype=sql \
367 db1.table1:dbx.table3
368 # server1 on host1: ... connected.
369 # server2 on host2: ... connected.
370 # Comparing db1.table1 to dbx.table3 [FAIL]
371 # Transformation statements:
372 # --destination=server1:
373 ALTER TABLE db1.table1
374 ADD COLUMN notes char(30) AFTER a,
375 CHANGE COLUMN misc misc char(55);
376 # --destination=server2:
377 # ALTER TABLE dbx.table3
378 # DROP COLUMN notes,
379 # CHANGE COLUMN misc misc char(30);
380 Compare failed. One or more differences found.
381 PERMISSIONS REQUIRED.PP The user must have SELECT privileges for both
383 objects on both servers as well as SELECT on the mysql database.
384
386 Copyright © 2006, 2015, Oracle and/or its affiliates. All rights
387 reserved.
388 This documentation is free software; you can redistribute it and/or
390 modify it only under the terms of the GNU General Public License as
391 published by the Free Software Foundation; version 2 of the License.
392 This documentation is distributed in the hope that it will be useful,
394 but WITHOUT ANY WARRANTY; without even the implied warranty of
395 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
396 General Public License for more details.
397 You should have received a copy of the GNU General Public License along
399 with the program; if not, write to the Free Software Foundation, Inc.,
400 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
401 http://www.gnu.org/licenses/.
402
405 For more information, please refer to the MySQL Utilities and Fabric
406 documentation, which is available online at
407 http://dev.mysql.com/doc/index-utils-fabric.html
408
410 Oracle Corporation (http://dev.mysql.com/).
411MySQL 1.5.6 09/15/2015 MYSQLDIFF(1)