MySQL数据一致性验证方式:
mysqldbcompare是官方提供一个可以实现多库或单库比较数据一致(无法基于表级别使用)工具,对于表数据很大情况下,测试运行效率并不是很高,有时候会报异常超时等。
官方文档:http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcompare.html
工具下载地址:http://downloads.mysql.com/snapshots/pb/mysql-connector-utilities-fabric/mysql-utilities-1.4.0-labs-fabric.tar.gz
二、安装
解压安装后,需要使用python命令进行安装
cd mysql-utilities-1.4.0-labs-fabric
python setup.py install
安装完成后,mysqldbcompare命令在/usr/local/bin下面
用法:
mysqldbcompare --server1=dlan:root123@172.16.32.10:3307 --server2=dlan:root123@172.16.32.11:3307 --run-all-test --changes-for=server1 --difftype=sql ms:ms
对于差异的展现能通过参数--difftype调控
① unified (default)
② context
③ differ
④ sql
参数选项
--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.
如果要比较数据库表结构就使用mysqldiff,并生成差异SQL语句
如果要比较数据库主从一致使用pt-table-checksum,生成差异报告,在利用pt-table-sync 数据修复
mysqldbcompare是官方提供一个可以实现多库或单库比较数据一致(无法基于表级别使用)工具,对于表数据很大情况下,测试运行效率并不是很高,有时候会报异常超时等。
官方文档:http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcompare.html
工具下载地址:http://downloads.mysql.com/snapshots/pb/mysql-connector-utilities-fabric/mysql-utilities-1.4.0-labs-fabric.tar.gz
二、安装
解压安装后,需要使用python命令进行安装
cd mysql-utilities-1.4.0-labs-fabric
python setup.py install
安装完成后,mysqldbcompare命令在/usr/local/bin下面
用法:
mysqldbcompare --server1=dlan:root123@172.16.32.10:3307 --server2=dlan:root123@172.16.32.11:3307 --run-all-test --changes-for=server1 --difftype=sql ms:ms
对于差异的展现能通过参数--difftype调控
① unified (default)
② context
③ differ
④ sql
参数选项
--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.