合 mysql-utilities工具之MySQL数据库比对工具mysqldiff和mysqldbcompare
Tags： MySQLmysqldbcomparemysqldiff
👉 本文共约4366个字，系统预计阅读时间或需17分钟。
目录
简介
如果要比较数据库表结构就使用mysqldiff，并生成差异SQL语句
如果要比较数据库数据是否一致就使用mysqldbcompare，并生成差异SQL语句(全能比较，也可以比较数据库结构)
如果要比较数据库主从一致使用pt-table-checksum，生成差异报告，在利用pt-table-sync数据修复
mysqldbcompare
mysqldbcompare从两个数据库比较对象和数据的不同。显示差异可以使用不同风格，如GRID, CSV, TAB, 或 VERTICAL。
在比较过程中数据不可以改变，否则出现错误。
数据库中的对象包括：表、视图、触发器、存储过程、函数和事件。每一个对象类型计数可以使用-vv选项显示。
通过一系列步骤检查进行测试，默认情况下，一旦测试失败就终止检测。可以指定 --run-all-tests 选项来进行所有的测试。
比较检测的内容包括：
- 数据库定义的检查
一个数据库存在的前提是检测确保数据库都存在。如果不存在，不需要进行下一步检测， --run-all-tests选项也被忽略的。
- 检测数据库的对象
检测两者数据库中的对象是否丢失。剩余的检测是针对这些数据库对象的。可以使用--skip-object-compare跳过该测试。当你已知缺少某些对象这个选项是有用的。
- 比较对象的定义
对对象的定义（CREATE语句）进行比较和显示不同。可以使用 --skip-diff 选项跳过这步。当你已知对象不同想忽略可用这个选项。
- 检测表的行数
这个检查确保这两个表有相同的行数，但这并不确保表的数据是一致性的。只是粗略性的检查表数据行数的缺失与否，识别丢失的行。可以使用 --skip-row-count选项跳过这步。
- 检查表数据的一致性
这个检查包括检查第四步的行数，同时也检查数据是否一致。行不同显示不同的风格报告，缺失的行也是如此，默认是GRID。
这个检查分为两步：首先对全表进行checksum校验，然后如果这步失败了算法将找出不同。
可以使用--skip-checksum-table 选项跳过表校验，使用--skip-data-check选项跳过数据检查。
如果你不想进行所有的检查，可以使用 --skip-xxx 选项来只进行某个测试。这在只想同步避免执行所有的测试非常有用。
测试的状态信息：
- pass测试成功
- FAIL测试失败
- SKIP缺少或跳过测试
- WARN测试时出现不寻常的但不是错误
- -测试不适用于该对象
指定如何显示 diff-style输出：
可以通过--difftype选项来定义输出风格。
- unified (default)统一的格式输出
- context上下文格式输出
- differdiffer-style格式输出
- sqlSQL转换语句输出
指定如何显示行丢失或改变的输出：
可以通过使用--format选项来定义。
- grid (default)网格化输出，类似于mysql命令行客户端输出
- csv逗号分隔格式输出
- tab制表符格式输出
- vertical类似于mysql命令行客户端\G执行的输出
--changes-for选项控制着差异报告（默认）还是转换报告（需要--difftype=sql选项），以谁为参照物。如：
1 | mysqldbcompare --server1=root@host1 --server2=root@host2 --difftype=sql db1:dbx |
最左边的db1对应于--server1选项，最右边的dbx对应于--server2选项。
--changes-for=server1: 针对server1，以server2为参照物（默认）--changes-for=server2: 针对server2，以server1为参照物
需要指定连接参数同时还需要一定的访问权限。
如果服务器上启用二进制日志，不想比较日志这步，可以使用 --disable-binary-logging 选项来禁用。
参数选项：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | --version show program's version number and exit --help 帮助信息 --license 版权信息 --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>]. --character-set=CHARSET 设置客户端字符集。默认是以'character_set_client'变量值. -f FORMAT, --format=FORMAT 指定缺失或改变行的显示格式，有grid (default), tab, csv, or vertical --skip-checksum-table skip CHECKSUM TABLE step in data consistency check. --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. --skip-table-options skip check of all table options (e.g., AUTO_INCREMENT, ENGINE, CHARSET, etc.). --width=WIDTH display width -t, --run-all-tests 首次发现差异时不中止 -a, --all 检测所有库，1.4.0版本引入 -x EXCLUDE, --exclude=EXCLUDE 排除一个或多个特定的数据库。1.4.0版本引入 exclude one or more databases from the operation using either a specific name (e.g. db1), a LIKE pattern (e.g. db%) or a REGEXP search pattern. To use a REGEXP search pattern for all exclusions, you must also specify the --regexp option. Repeat the --exclude option for multiple exclusions. -c, --compact compact output from a diff. --disable-binary-logging 防止比较操作被写入二进制日志，如果二进制启用 (SQL_LOG_BIN=1).需要SUPER权限. Prevents compare operations from being written to the binary log. --span-key-size=SPAN_KEY_SIZE changes the size of the key used for compare table contents. A higher value can help to get more accurate results comparing large databases, but may slow the algorithm. Default value is 8. --use-indexes=USE_INDEXES for each table, indicate which index to use as if were a primary key (each of his columns must not allow null values). -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 指定不同的显示格式。: [unified|context|differ|sql] (default: unified). --changes-for=CHANGES_FOR 执行要显示的转换与其他服务器匹配。如, 要看到server1的对象定义 与server2相匹配,使用 --changes-for=server1。合法的值有 'server1' or 'server2'。默认'server1'。 --show-reverse produce a transformation report containing the SQL statements to transform 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. -G, --basic-regexp, --regexp use 'REGEXP' operator to match pattern. Default is to use 'LIKE'. --ssl-ca=SSL_CA The path to a file that contains a list of trusted SSL CAs. --ssl-cert=SSL_CERT The name of the SSL certificate file to use for establishing a secure connection. --ssl-key=SSL_KEY The name of the SSL key file to use for establishing a secure connection. |
所需权限
所要连接比较的数据库需要有 SELECT, CREATE TEMPORARY TABLES 和 INSERT 权限。
需要对mysql数据库有SELECT权限。
如果二进制启用同时使用到 --disable-binary-logging选项，该用户必须要有SUPER权限。
比较实例
使用下面的命令来比较本地服务器上的emp1和emp2数据库。进行所有的测试，不管是否失败。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | # mysqldbcompare --server1=root@localhost emp1:emp2 --run-all-tests # server1 on localhost: ... connected. # Checking databases emp1 on server1 and emp2 on server2 # # WARNING: Objects in server2:emp2 but not in server1:emp1: # TRIGGER: trg # PROCEDURE: p1 # TABLE: t1 # VIEW: v1 # # Defn Row Data # Type Object Name Diff Count Check # --------------------------------------------------------------------------- # FUNCTION f1 pass - - # TABLE departments pass pass - # - Compare table checksum FAIL # - Find row differences FAIL # # Data differences found among rows: --- emp1.departments +++ emp2.departments @@ -1,4 +1,4 @@ ************************* 1. row ************************* dept_no: d002 - dept_name: dunno + dept_name: Finance 1 rows. # Rows in emp1.departments not in emp2.departments ************************* 1. row ************************* dept_no: d008 dept_name: Research 1 rows. # Rows in emp2.departments not in emp1.departments ************************* 1. row ************************* dept_no: d100 dept_name: stupid 1 rows. # TABLE dept_manager pass pass - # - Compare table checksum pass # Database consistency check failed. # # ...done |
db1和db2表结构一样，表数据不一致，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> select * from db1.t1; +---+---------------+ | a | b | +---+---------------+ | 1 | Test 789 | | 2 | Test 456 | | 3 | Test 123 | | 4 | New row - db1 | +---+---------------+ 4 rows in set (0.00 sec) mysql> select * from db2.t1; +---+---------------+ | a | b | +---+---------------+ | 1 | Test 123 | | 2 | Test 456 | | 3 | Test 789 | | 5 | New row - db2 | +---+---------------+ 4 rows in set (0.00 sec) |
生产转换的SQL语句，使db1.t1数据与db2.t1一致，使用--changes-for=server1 选项。同时还必需使用-a选项来确保数据的一致性检查。如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | shell> mysqldbcompare --server1=root:root@localhost \ --server2=root:root@localhost db1:db2 --changes-for=server1 -a \/ --difftype=sql [...] # Defn Row Data # Type Object Name Diff Count Check #------------------------------------------------------------------------- # TABLE t1 pass pass - # - Compare table checksum FAIL # - Find row differences FAIL # # Transformation for --changes-for=server1: # # Data differences found among rows: UPDATE db1.t1 SET b = 'Test 123' WHERE a = '1'; UPDATE db1.t1 SET b = 'Test 789' WHERE a = '3'; DELETE FROM db1.t1 WHERE a = '4'; INSERT INTO db1.t1 (a, b) VALUES('5', 'New row - db2'); # Database consistency check failed. # # ...done |
相反的，针对server2，以server1为参照物。如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | shell> mysqldbcompare --server1=root:root@localhost \ --server2=root:root@localhost db1:db2 --changes-for=server2 -a \ --difftype=sql [...] # Defn Row Data # Type Object Name Diff Count Check #------------------------------------------------------------------------- # TABLE t1 pass pass - # - Compare table checksum FAIL # - Find row differences FAIL # # Transformation for --changes-for=server2: # # Data differences found among rows: UPDATE db2.t1 SET b = 'Test 789' WHERE a = '1'; UPDATE db2.t1 SET b = 'Test 123' WHERE a = '3'; DELETE FROM db2.t1 WHERE a = '5'; INSERT INTO db2.t1 (a, b) VALUES('4', 'New row - db1'); # Database consistency check failed. # # ...done |
使用 --difftype=sql 选项生产差异的SQL语句，同时，使用 --show-reverse选项可以显示出双方的SQL语句。如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | shell> mysqldbcompare --server1=root:root@localhost \ --server2=root:root@localhost db1:db2 --changes-for=server1 \ --show-reverse -a --difftype=sql [...] # Defn Row Data # Type Object Name Diff Count Check # ------------------------------------------------------------------------- # TABLE t1 pass pass - # - Compare table checksum FAIL # - Find row differences FAIL # # Transformation for --changes-for=server1: # # Data differences found among rows: UPDATE db1.t1 SET b = 'Test 123' WHERE a = '1'; UPDATE db1.t1 SET b = 'Test 789' WHERE a = '3'; DELETE FROM db1.t1 WHERE a = '4'; INSERT INTO db1.t1 (a, b) VALUES('5', 'New row - db2'); # # Transformation for reverse changes (--changes-for=server2): # # # Data differences found among rows: # UPDATE db2.t1 SET b = 'Test 789' WHERE a = '1'; # UPDATE db2.t1 SET b = 'Test 123' WHERE a = '3'; # DELETE FROM db2.t1 WHERE a = '5'; # INSERT INTO db2.t1 (a, b) VALUES('4', 'New row - db1'); # Database consistency check failed. # # ...done |
生成差异SQL语句来同步表数据
在生产环境中，为了数据的安全，往往会布置多台，来保障数据冗余。然而，有时会因各种原因导致数据的不一致。我们不仅仅想知道哪些数据是不同的，相反，更要知道如何去修复这些不一致的数据来保障冗余数据是一样的。比如，主从，数据不一致重新做主从，效率太低了。此外还不能确定哪个库上的数据是最新的。因此想要看到两个方向的SQL转换语句。在这种情况下，mysqldbcompare工具非常实用。
这节来说说如何使用mysqldbcompare来保证数据一致性。
比较生成差异性语句
需要对menagerie数据库有SELECT权限。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | shell> mysqldbcompare --server1=root:root@localhost:13001 --server2=root:root@localhost:13002 \ menagerie -a --difftype=SQL --show-reverse --quiet # Checking databases menagerie on server1 and menagerie on server2 # # # Row counts are not the same among `menagerie`.`pet` and `menagerie`.`pet`. # # Transformation for --changes-for=server1: # DELETE FROM `menagerie`.`pet` WHERE `pet_num` = '10'; DELETE FROM `menagerie`.`pet` WHERE `pet_num` = '12'; INSERT INTO `menagerie`.`pet` (`pet_num`, `name`, `owner`, `species`, `sex`, `birth`, `death`) VALUES('11', 'Violet', 'Annette', 'dog', 'f', '2010-10-20', NULL); # # Transformation for reverse changes (--changes-for=server2): # # DELETE FROM `menagerie`.`pet` WHERE `pet_num` = '11'; # INSERT INTO `menagerie`.`pet` (`pet_num`, `name`, `owner`, `species`, `sex`, `birth`, `death`) # VALUES('10', 'JonJon', 'Annette', 'dog', 'm', '2010-10-20', '2012-07-01'); # INSERT INTO `menagerie`.`pet` (`pet_num`, `name`, `owner`, `species`, `sex`, `birth`, `death`) # VALUES('12', 'Charlie', 'Annette', 'dog', 'f', '2010-10-20', NULL); # |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
