mysqldbcompare

mysqldbcompare主要是用来比较两个数据库的不同:库的不同、表的不同、表结构不同、记录不同、值不同,并可以生成同步的脚本。

$ ./mysqldbcompare --help
Usage: mysqldbcompare --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1:db2
 
mysqldbcompare - compare databases for consistency
 
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>].
  -f FORMAT, --format=FORMAT
                        display the output in either grid (default), tab, csv,
                        or vertical format
  --skip-object-compare
                        skip object comparison step
  --skip-row-count      skip row count step
  --skip-diff           skip the object diff step
  --skip-data-check     skip data consistency check
  --width=WIDTH         display width
  -a, --run-all-tests   do not abort when a diff test fails
  --disable-binary-logging
                        turn binary logging off during operation if enabled
                        (SQL_LOG_BIN=1). Note: may require SUPER privilege.
                        Prevents compare operations from being written to the
                        binary log.
  -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:3306上的luoxuan库下有test_1,test_2,test_3表,3506上的lijie库下没有表

./mysqldbcompare --server1=root@localhost:3306 --server2=root@localhost:3506 luoxuan:lijie --run-all-tests
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases luoxuan on server1 and lijie on server2
#
# Object definitions differ. (--changes-for=server1)
#
 
--- luoxuan
+++ lijie
@@ -1,1 +1,1 @@
-CREATE DATABASE `luoxuan` /*!40100 DEFAULT CHARACTER SET gbk */
+CREATE DATABASE `lijie` /*!40100 DEFAULT CHARACTER SET gbk */
 
# WARNING: Objects in server1.luoxuan but not in server2.lijie:
#        TABLE: test_2
#        TABLE: test_1
#        TABLE: test_3
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# -------------------------------------------------------------------------
 
# Database consistency check failed.

eg2:3306上的luoxuan库下有test_1,test_2,test_3表,3506上的luoxuan库下test_1、test_2,但3306:test_1和3506:test_1的表结构不一样,但3306:test_2和3506:test_2的记录不一样

# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases luoxuan on server1 and luoxuan on server2
#
# WARNING: Objects in server1.luoxuan but not in server2.luoxuan:
#        TABLE: test_3
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     test_1                                  FAIL    pass    FAIL    
#
# Object definitions differ. (--changes-for=server1)
#
 
--- luoxuan.test_1
+++ luoxuan.test_1
@@ -1,7 +1,6 @@
 CREATE TABLE `test_1` (
   `a` int(11) NOT NULL,
   `b` varchar(20) NOT NULL,
-  `c` varchar(20) NOT NULL,
   PRIMARY KEY (`a`),
   UNIQUE KEY `uk_b` (`b`)
 ) ENGINE=InnoDB DEFAULT CHARSET=gbk
# Data differences found among rows:
--- luoxuan.test_1
+++ luoxuan.test_1
@@ -1,5 +1,5 @@
-+----+----+----+
-| a  | b  | c  |
-+----+----+----+
-| 1  | b  | c  |
-+----+----+----+
++----+----+
+| a  | b  |
++----+----+
+| 1  | b  |
++----+----+
 
# TABLE     test_2                                  pass    pass    FAIL    
#
# Data differences found among rows:
--- luoxuan.test_2
+++ luoxuan.test_2
@@ -1,5 +1,5 @@
-+----+----+----+----+
-| a  | b  | c  | d  |
-+----+----+----+----+
-| 1  | b  | c  | d  |
-+----+----+----+----+
++----+----+----+------+
+| a  | b  | c  | d    |
++----+----+----+------+
+| 1  | b  | c  | ddd  |
++----+----+----+------+
 
 
# Database consistency check failed.
#
# ...done

注意:表一定要有pk,不然它会认为库与库不一致。mysqldbcompare比较行记录值算法是对行记录做MD5 HASH,再写到临时表处理。如果在高并行情况下,主备存在延迟可能,会导致比结果不一致,因为它不进行锁表动作。


Post a Comment