mysqldiff

mysqldiff来比较对象之间不同,有点类似mysqldbcompare

$ ./mysqldiff  --help
Usage: mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4
 
mysqldiff - compare object definitions among objects where the difference is
how db1.obj1 differs from db2.obj2
 
Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --server1=SERVER1     connection information for first server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  --server2=SERVER2     connection information for second server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  --width=WIDTH         display width
  --force               do not abort when a diff test fails
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.
  -d DIFFTYPE, --difftype=DIFFTYPE
                        display differences in context format in one of the
                        following formats: [unified|context|differ|sql]
                        (default: unified).
  --changes-for=CHANGES_FOR
                        specify the server to show transformations to match
                        the other server. For example, to see the
                        transformation for transforming server1 to match
                        server2, use --changes-for=server1. Valid values are
                        'server1' or 'server2'. The default is 'server1'.
  --show-reverse        produce a transformation report containing the SQL
                        statements to conform the object definitions specified
                        in reverse. For example if --changes-for is set to
                        server1, also generate the transformation for server2.
                        Note: the reverse changes are annotated and marked as
                        comments.

eg1:比较两个库下面的表及表结构

$ ./mysqldiff --server1=root@localhost:3406  --server2=root@localhost:3506 luoxuan:luoxuan
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing luoxuan to luoxuan                                     [PASS]
# Comparing luoxuan.test_lx to luoxuan.test_lx                     [PASS]
Success. All objects are the same.

eg2:在server1中加个e字段,没有设置–changes-for,以server2为准

root@luoxuan 06:29:16>alter table test_lx add e int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
$ ./mysqldiff --server1=root@localhost:3406  --server2=root@localhost:3506 luoxuan:luoxuan
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing luoxuan to luoxuan                                     [PASS]
# Comparing luoxuan.test_lx to luoxuan.test_lx                     [FAIL]
# Object definitions differ. (--changes-for=server1)
#
--- luoxuan.test_lx
+++ luoxuan.test_lx
@@ -3,6 +3,5 @@
   `b` bigint(20) NOT NULL,
   `c` varchar(1000) DEFAULT NULL,
   `d` varchar(1000) DEFAULT NULL,
-  `e` int(11) DEFAULT NULL,
   PRIMARY KEY (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=gbk
Compare failed. One or more differences found.

eg3:产生一致性的表结构的sql,以server2为标准

$ ./mysqldiff --server1=root@localhost:3406  --server2=root@localhost:3506 luoxuan:luoxuan  --difftype=sql
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing luoxuan to luoxuan                                     [PASS]
# Comparing luoxuan.test_lx to luoxuan.test_lx                     [FAIL]
# Transformation for --changes-for=server1:
#
 
ALTER TABLE luoxuan.test_lx 
  DROP COLUMN e;
 
Compare failed. One or more differences found.
 
eg4:可以server1为标准,产生修改语句
$ ./mysqldiff --server1=root@localhost:3406  --server2=root@localhost:3506 luoxuan:luoxuan  --changes-for=server2 --difftype=sql
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing luoxuan to luoxuan                                     [PASS]
# Comparing luoxuan.test_lx to luoxuan.test_lx                     [FAIL]
# Transformation for --changes-for=server2:
#
 
ALTER TABLE luoxuan.test_lx 
  ADD COLUMN e int(11) NULL AFTER d;
 
Compare failed. One or more differences found.

Post a Comment