1MYSQLDBCOMPARE(1) MySQL Utilities MYSQLDBCOMPARE(1)
2
6 mysqldbcompare - Compare Two Databases and Identify Differences
7
9 mysqldbcompare [options] db1 [:db2] ...
10
12 This utility compares the objects and data from two databases to find
13 differences. It identifies objects having different definitions in the
14 two databases and presents them in a diff-style format of choice.
15 Differences in the data are shown using a similar diff-style format.
16 Changed or missing rows are shown in a standard format of GRID, CSV,
17 TAB, or VERTICAL.
18 Use the notation db1:db2 to name two databases to compare, or,
20 alternatively just db1 to compare two databases with the same name. The
21 latter case is a convenience notation for comparing same-named
22 databases on different servers.
23 The comparison may be run against two databases of different names on a
25 single server by specifying only the --server1 option. The user can
26 also connect to another server by specifying the --server2 option. In
27 this case, db1 is taken from server1 and db2 from server2.
28 All databases between two servers can also be compared using the --all
30 option. In this case, only the databases in common (with the same name)
31 between the servers are successively compared. Therefore, no databases
32 need to be specified but the --server1 and --server2 options are
33 required. Users can skip the comparison of some of the databases using
34 the --exclude option.
35 Note
37 The data must not be changed during the comparison. Unexpected
38 errors may occur if data is changed during the comparison.
39 The objects considered in the database include tables, views, triggers,
41 procedures, functions, and events. A count for each object type can be
42 shown with the -vv option.
43 The check is performed using a series of steps called tests. By
45 default, the utility stops on the first failed test, but you can
46 specify the --run-all-tests option to cause the utility to run all
47 tests regardless of their end state.
48 Note
50 Using --run-all-tests may produce expected cascade failures. For
51 example, if the row counts differ among two tables being compared,
52 the data consistency will also fail.
53 The tests include the following:
55 1. Check database definitions
57 A database existence precondition check ensures that both databases
59 exist. If they do not, no further processing is possible and the
60 --run-all-tests option is ignored.
61 2. Check existence of objects in both databases
63 The test for objects in both databases identifies those objects
65 missing from one or another database. The remaining tests apply
66 only to those objects that appear in both databases. To skip this
67 test, use the --skip-object-compare option. That can be useful when
68 there are known missing objects among the databases.
69 3. Compare object definitions
71 The definitions (the CREATE statements) are compared and
73 differences are presented. To skip this test, use the --skip-diff
74 option. That can be useful when there are object name differences
75 only that you want to ignore.
76 4. Check table row counts
78 This check ensures that both tables have the same number of rows.
80 This does not ensure that the table data is consistent. It is
81 merely a cursory check to indicate possible missing rows in one
82 table or the other. The data consistency check identifies the
83 missing rows. To skip this test, use the --skip-row-count option.
84 5. Check table data consistency
86 This check identifies both changed rows as well as missing rows
88 from one or another of the tables in the databases. Changed rows
89 are displayed as a diff-style report with the format chosen (GRID
90 by default) and missing rows are also displayed using the format
91 chosen. This check is divided in two steps: first the full table
92 checksum is compared between the tables, then if this step fails
93 (or is skipped) the algorithm to find rows differences is executed.
94 To skip the preliminary checksum table step in this test, use the
95 --skip-checksum-table option. To skip this full test, use the
96 --skip-data-check option.
97 You may want to use the --skip-xxx options to run only one of the
99 tests. This might be helpful when working to bring two databases into
100 synchronization, to avoid running all of the tests repeatedly during
101 the process.
102 Each test completes with one of the following states:
104 · pass
106 The test succeeded.
108 · FAIL
110 The test failed. Errors are displayed following the test state
112 line.
113 · SKIP
115 The test was skipped due to a missing prerequisite or a skip
117 option.
118 · WARN
120 The test encountered an unusual but not fatal error.
122 · -
124 The test is not applicable to this object.
126 To specify how to display diff-style output, use one of the following
128 values with the --difftype option:
129 · unified (default)
131 Display unified format output.
133 · context
135 Display context format output.
137 · differ
139 Display differ-style format output.
141 · sql
143 Display SQL transformation statement output.
145 To specify how to display output for changed or missing rows, use one
147 of the following values with the --format option:
148 · grid (default)
150 Display output in grid or table format like that of the mysql
152 client command-line tool.
153 · csv
155 Display output in comma-separated values format.
157 · tab
159 Display output in tab-separated format.
161 · vertical
163 Display output in single-column format like that of the \G command
165 for the mysql client command-line tool.
166 The --changes-for option controls the direction of the difference (by
168 specifying the object to be transformed) in either the difference
169 report (default) or the transformation report (designated with the
170 --difftype=sql option). Consider the following command:
171 shell> mysqldbcompare --server1=root@host1 --server2=root@host2 --difftype=sql db1:dbx
173 The leftmost database (db1) exists on the server designated by the
175 --server1 option (host1). The rightmost database (dbx) exists on the
176 server designated by the --server2 option (host2).
177 · --changes-for=server1: Produce output that shows how to make the
179 definitions of objects on server1 like the definitions of the
180 corresponding objects on server2.
181 · --changes-for=server2: Produce output that shows how to make the
183 definitions of objects on server2 like the definitions of the
184 corresponding objects on server1.
185 The default direction is server1.
187 You must provide connection parameters (user, host, password, and so
189 forth) for an account that has the appropriate privileges to access all
190 objects in the operation.
191 If the utility is to be run on a server that has binary logging
193 enabled, and you do not want the comparison steps logged, use the
194 --disable-binary-logging option. OPTIONS.PP mysqldbcompare accepts the
195 following command-line options:
196 · --all, -a
198 Compare all database in common (with the same name) between two
200 servers.
201 The --all option ignores the following databases:
203 INFORMATION_SCHEMA, PERFORMANCE_SCHEMA, mysql, and sys.
204 Note
206 The sys database is ignored as of Utilities 1.5.5.
207 · --help
209 Display a help message and exit.
211 · --license
213 Display license information and exit.
215 · --changes-for=<direction>
217 Specify the server to show transformations to match the other
219 server. For example, to see the transformation for transforming
220 object definitions on server1 to match the corresponding
221 definitions on server2, use --changes-for=server1. Permitted values
222 are server1 and server2. The default is server1.
223 · --character-set=<charset>
225 Sets the client character set. The default is retrieved from the
227 server variable character_set_client.
228 · --difftype=<difftype>, -d<difftype>
230 Specify the difference display format. Permitted format values are
232 unified, context, differ, and sql. The default is unified.
233 · --disable-binary-logging
235 If binary logging is enabled, disable it during the operation to
237 prevent comparison operations from being written to the binary log.
238 Note: Disabling binary logging requires the SUPER privilege.
239 · --exclude=<exclude>, -x<exclude>
241 Exclude one or more databases from the operation using either a
243 specific name such as db1 or a search pattern. Use this option
244 multiple times to specify multiple exclusions. By default, patterns
245 use database patterns such as LIKE. With the --regexp option,
246 patterns use regular expressions for matching names.
247 Added in release-1.4.0.
249 · --format=<format>, -f<format>
251 Specify the display format for changed or missing rows. Permitted
253 format values are grid, csv, tab, and vertical. The default is
254 grid.
255 · --compact
257 Compacts the output by reducing the number of control lines that
259 are displayed in the diff results. This option should be used
260 together with one of the following difference types: unified or
261 context. It is most effective when used with the unified difference
262 type and the grid format.
263 · --quiet, -q
265 Do not print anything. Return only an exit code of success or
267 failure.
268 · --regexp, --basic-regexp, -G
270 Perform pattern matches using the REGEXP operator. The default is
272 to use LIKE for matching.
273 Added in release-1.4.0.
275 · --run-all-tests, -t
277 Do not halt at the first difference found. Process all objects.
279 · --server1=<source>
281 Connection information for the first server.
283 To connect to a server, it is necessary to specify connection
285 parameters such as user name, host name, password, and either a
286 port or socket. MySQL Utilities provides a number of ways to
287 provide this information. All of the methods require specifying
288 your choice via a command-line option such as --server, --master,
289 --slave, etc. The methods include the following in order of most
290 secure to least secure.
291 · Use login-paths from your .mylogin.cnf file (encrypted, not
293 visible). Example : <login-path>[:<port>][:<socket>]
294 · Use a configuration file (unencrypted, not visible) Note:
296 available in release-1.5.0. Example :
297 <configuration-file-path>[:<section>]
298 · Specify the data on the command-line (unencrypted, visible).
300 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
301 · --server2=<source>
304 Connection information for the second server.
306 To connect to a server, it is necessary to specify connection
308 parameters such as user name, host name, password, and either a
309 port or socket. MySQL Utilities provides a number of ways to
310 provide this information. All of the methods require specifying
311 your choice via a command-line option such as --server, --master,
312 --slave, etc. The methods include the following in order of most
313 secure to least secure.
314 · Use login-paths from your .mylogin.cnf file (encrypted, not
316 visible). Example : <login-path>[:<port>][:<socket>]
317 · Use a configuration file (unencrypted, not visible) Note:
319 available in release-1.5.0. Example :
320 <configuration-file-path>[:<section>]
321 · Specify the data on the command-line (unencrypted, visible).
323 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
324 · --show-reverse
327 Produce a transformation report containing the SQL statements to
329 conform the object definitions specified in reverse. For example,
330 if --changes-for is set to server1, also generate the
331 transformation for server2. Note: The reverse changes are annotated
332 and marked as comments.
333 · --skip-checksum-table
335 Skip the CHECKSUM TABLE step in the data consistency check. Added
337 in release-1.4.3.
338 · --skip-data-check
340 Skip the data consistency check.
342 · --skip-diff
344 Skip the object definition difference check.
346 · --skip-object-compare
348 Skip the object comparison check.
350 · --skip-row-count
352 Skip the row count check.
354 · --span-key-size=<number of bytes to use for key>
356 Change the size of the key used for compare table contents. A
358 higher value can help to get more accurate results comparing large
359 databases, but may slow the algorithm.
360 Default value is 8.
362 · --ssl-ca
364 The path to a file that contains a list of trusted SSL CAs.
366 · --ssl-cert
368 The name of the SSL certificate file to use for establishing a
370 secure connection.
371 · --ssl-cert
373 The name of the SSL key file to use for establishing a secure
375 connection.
376 · --ssl
378 Specifies if the server connection requires use of SSL. If an
380 encrypted connection cannot be established, the connection attempt
381 fails. Default setting is 0 (SSL not required).
382 · --verbose, -v
384 Specify how much information to display. Use this option multiple
386 times to increase the amount of information. For example, -v =
387 verbose, -vv = more verbose, -vvv = debug.
388 · --version
390 Display version information and exit.
392 · --use-indexes
394 List the index to use. Use this option to select the index to use
396 if the table has no primary key or it has more than one unique
397 index without null columns. Use this option in the format:
398 --use-indexes="<table1>.<indexA>[;<table2>.<indexB>;]"
399 · --width=<number>
401 Change the display width of the test report. The default is 75
403 characters.
404 NOTES.PP The login user must have the appropriate permissions to read
405 all databases and tables listed.
406 For the --difftype option, the permitted values are not case sensitive.
408 In addition, values may be specified as any unambiguous prefix of a
409 valid value. For example, --difftype=d specifies the differ type. An
410 error occurs if a prefix matches more than one valid value.
411 The path to the MySQL client tools should be included in the PATH
413 environment variable in order to use the authentication mechanism with
414 login-paths. This will allow the utility to use the my_print_defaults
415 tools which is required to read the login-path values from the login
416 configuration file (.mylogin.cnf).
417 If any database identifier specified as an argument contains special
419 characters or is a reserved word, then it must be appropriately quoted
420 with backticks (`). In turn, names quoted with backticks must also be
421 quoted with single or double quotes depending on the operating system,
422 i.e. (") in Windows or (') in non-Windows systems, in order for the
423 utilities to read backtick quoted identifiers as a single argument. For
424 example, to compare a database with the name weird`db.name with
425 other:weird`db.name, the database pair must be specified using the
426 following syntax (in non-Windows):
427 '`weird``db.name`:`other:weird``db.name`'. EXAMPLES.PP Use the
428 following command to compare the emp1 and emp2 databases on the local
429 server, and run all tests even if earlier tests fail:
430 shell> mysqldbcompare --server1=root@localhost emp1:emp2 --run-all-tests
432 # server1 on localhost: ... connected.
433 # Checking databases emp1 on server1 and emp2 on server2
434 #
435 # WARNING: Objects in server2:emp2 but not in server1:emp1:
436 # TRIGGER: trg
437 # PROCEDURE: p1
438 # TABLE: t1
439 # VIEW: v1
440 #
441 # Defn Row Data
442 # Type Object Name Diff Count Check
443 # ---------------------------------------------------------------------------
444 # FUNCTION f1 pass - -
445 # TABLE departments pass pass -
446 # - Compare table checksum FAIL
447 # - Find row differences FAIL
448 #
449 # Data differences found among rows:
450 --- emp1.departments
451 +++ emp2.departments
452 @@ -1,4 +1,4 @@
453 ************************* 1. row *************************
454 dept_no: d002
455 - dept_name: dunno
456 + dept_name: Finance
457 1 rows.
458 # Rows in emp1.departments not in emp2.departments
459 ************************* 1. row *************************
460 dept_no: d008
461 dept_name: Research
462 1 rows.
463 # Rows in emp2.departments not in emp1.departments
464 ************************* 1. row *************************
465 dept_no: d100
466 dept_name: stupid
467 1 rows.
468 # TABLE dept_manager pass pass -
469 # - Compare table checksum pass
470 # Database consistency check failed.
471 #
472 # ...done
473 Given: two databases with the same table layout. Data for each table
475 contains:
476 mysql> select * from db1.t1;
478 +---+---------------+
479 | a | b |
480 +---+---------------+
481 | 1 | Test 789 |
482 | 2 | Test 456 |
483 | 3 | Test 123 |
484 | 4 | New row - db1 |
485 +---+---------------+
486 4 rows in set (0.00 sec)
487 mysql> select * from db2.t1;
488 +---+---------------+
489 | a | b |
490 +---+---------------+
491 | 1 | Test 123 |
492 | 2 | Test 456 |
493 | 3 | Test 789 |
494 | 5 | New row - db2 |
495 +---+---------------+
496 4 rows in set (0.00 sec)
497 To generate the SQL statements for data transformations to make db1.t1
499 the same as db2.t1, use the --changes-for=server1 option. We must also
500 include the -a option to ensure that the data consistency test is run.
501 The following command illustrates the options used and an excerpt from
502 the results generated:
503 shell> mysqldbcompare --server1=root:root@localhost \
505 --server2=root:root@localhost db1:db2 --changes-for=server1 -a \/
506 --difftype=sql
507 [...]
508 # Defn Row Data
509 # Type Object Name Diff Count Check
510 #-------------------------------------------------------------------------
511 # TABLE t1 pass pass -
512 # - Compare table checksum FAIL
513 # - Find row differences FAIL
514 #
515 # Transformation for --changes-for=server1:
516 #
517 # Data differences found among rows:
518 UPDATE db1.t1 SET b = 'Test 123' WHERE a = '1';
519 UPDATE db1.t1 SET b = 'Test 789' WHERE a = '3';
520 DELETE FROM db1.t1 WHERE a = '4';
521 INSERT INTO db1.t1 (a, b) VALUES('5', 'New row - db2');
522 # Database consistency check failed.
523 #
524 # ...done
525 Similarly, when the same command is run with --changes-for=server2 and
527 --difftype=sql, the following report is generated:
528 shell> mysqldbcompare --server1=root:root@localhost \
530 --server2=root:root@localhost db1:db2 --changes-for=server2 -a \
531 --difftype=sql
532 [...]
533 # Defn Row Data
534 # Type Object Name Diff Count Check
535 #-------------------------------------------------------------------------
536 # TABLE t1 pass pass -
537 # - Compare table checksum FAIL
538 # - Find row differences FAIL
539 #
540 # Transformation for --changes-for=server2:
541 #
542 # Data differences found among rows:
543 UPDATE db2.t1 SET b = 'Test 789' WHERE a = '1';
544 UPDATE db2.t1 SET b = 'Test 123' WHERE a = '3';
545 DELETE FROM db2.t1 WHERE a = '5';
546 INSERT INTO db2.t1 (a, b) VALUES('4', 'New row - db1');
547 # Database consistency check failed.
548 #
549 # ...done
550 With the --difftype=sql SQL generation option set, --show-reverse shows
552 the object transformations in both directions. Here is an excerpt of
553 the results:
554 shell> mysqldbcompare --server1=root:root@localhost \
556 --server2=root:root@localhost db1:db2 --changes-for=server1 \
557 --show-reverse -a --difftype=sql
558 [...]
559 # Defn Row Data
560 # Type Object Name Diff Count Check
561 # -------------------------------------------------------------------------
562 # TABLE t1 pass pass -
563 # - Compare table checksum FAIL
564 # - Find row differences FAIL
565 #
566 # Transformation for --changes-for=server1:
567 #
568 # Data differences found among rows:
569 UPDATE db1.t1 SET b = 'Test 123' WHERE a = '1';
570 UPDATE db1.t1 SET b = 'Test 789' WHERE a = '3';
571 DELETE FROM db1.t1 WHERE a = '4';
572 INSERT INTO db1.t1 (a, b) VALUES('5', 'New row - db2');
573 #
574 # Transformation for reverse changes (--changes-for=server2):
575 #
576 # # Data differences found among rows:
577 # UPDATE db2.t1 SET b = 'Test 789' WHERE a = '1';
578 # UPDATE db2.t1 SET b = 'Test 123' WHERE a = '3';
579 # DELETE FROM db2.t1 WHERE a = '5';
580 # INSERT INTO db2.t1 (a, b) VALUES('4', 'New row - db1');
581 # Database consistency check failed.
582 #
583 # ...done
584 PERMISSIONS REQUIRED.PP The user must have the SELECT, CREATE TEMPORARY
586 TABLES and INSERT privileges for the databases being compared on both
587 connections. The user must also have SELECT privilege on the mysql
588 database. If the binary log is enabled and the --disable-binary-logging
589 option is used, the user must also have the SUPER privilege.
590
592 Copyright © 2006, 2015, Oracle and/or its affiliates. All rights
593 reserved.
594 This documentation is free software; you can redistribute it and/or
596 modify it only under the terms of the GNU General Public License as
597 published by the Free Software Foundation; version 2 of the License.
598 This documentation is distributed in the hope that it will be useful,
600 but WITHOUT ANY WARRANTY; without even the implied warranty of
601 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
602 General Public License for more details.
603 You should have received a copy of the GNU General Public License along
605 with the program; if not, write to the Free Software Foundation, Inc.,
606 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
607 http://www.gnu.org/licenses/.
608
611 For more information, please refer to the MySQL Utilities and Fabric
612 documentation, which is available online at
613 http://dev.mysql.com/doc/index-utils-fabric.html
614
616 Oracle Corporation (http://dev.mysql.com/).
617MySQL 1.5.6 09/15/2015 MYSQLDBCOMPARE(1)