read_only 模式下 mysqldump 报错

回复

MySQLliyh 发起了问题 • 1 人关注 • 0 个回复 • 40 次浏览 • 2018-06-11 15:25 • 来自相关话题

Consul服务域名访问时好时坏

MySQLpytoday 回复了问题 • 2 人关注 • 1 个回复 • 137 次浏览 • 2018-06-04 18:02 • 来自相关话题

MHA 0.58 check_repl 缺少对配置文件关键参数的正确性检查.

MySQLk2865 发表了文章 • 0 个评论 • 112 次浏览 • 2018-05-31 20:12 • 来自相关话题

       本菜在对MHA进行测试时意外发现check_repl没有对配置文件masterha_default.cnf中的repl_user和password正确性进行检查.特别记录以下内容,不对之处欢迎批评指正.
1.环境node1(manger) 192.168.99.183
node2:192.168.99.184
node3:192.168.99.185
vip:192.168.99.253
MHA::MasterMonitor version 0.58.
 
2.当masterha_default.cnf配置中slave 同步账号密码填写错误时,通过masterha_check_repl无法检查到相应账号问题(只会检查slave的同步状态):
#masterha_check_repl --conf=/etc/masterha/app1.conf
Thu May 31 19:50:35 2018 - [info] Reading default configuration from /etc/masterha_default.cnf..
Thu May 31 19:50:35 2018 - [info] Reading application default configuration from /etc/masterha/app1.conf..
Thu May 31 19:50:35 2018 - [info] Reading server configuration from /etc/masterha/app1.conf..
Thu May 31 19:50:35 2018 - [info] MHA::MasterMonitor version 0.58.
Thu May 31 19:50:37 2018 - [info] GTID failover mode = 1
Thu May 31 19:50:37 2018 - [info] Dead Servers:
Thu May 31 19:50:37 2018 - [info] Alive Servers:
Thu May 31 19:50:37 2018 - [info]   192.168.99.183(192.168.99.183:3307)
Thu May 31 19:50:37 2018 - [info]   192.168.99.184(192.168.99.184:3307)
Thu May 31 19:50:37 2018 - [info]   192.168.99.185(192.168.99.185:3307)
Thu May 31 19:50:37 2018 - [info] Alive Slaves:
Thu May 31 19:50:37 2018 - [info]   192.168.99.183(192.168.99.183:3307)  Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Thu May 31 19:50:37 2018 - [info]     GTID ON
Thu May 31 19:50:37 2018 - [info]     Replicating from 192.168.99.185(192.168.99.185:3307)
Thu May 31 19:50:37 2018 - [info]   192.168.99.184(192.168.99.184:3307)  Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Thu May 31 19:50:37 2018 - [info]     GTID ON
Thu May 31 19:50:37 2018 - [info]     Replicating from 192.168.99.185(192.168.99.185:3307)
Thu May 31 19:50:37 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 31 19:50:37 2018 - [info] Current Alive Master: 192.168.99.185(192.168.99.185:3307)
Thu May 31 19:50:37 2018 - [info] Checking slave configurations..
Thu May 31 19:50:37 2018 - [info]  read_only=1 is not set on slave 192.168.99.183(192.168.99.183:3307).
Thu May 31 19:50:37 2018 - [info]  read_only=1 is not set on slave 192.168.99.184(192.168.99.184:3307).
Thu May 31 19:50:37 2018 - [info] Checking replication filtering settings..
Thu May 31 19:50:37 2018 - [info]  binlog_do_db= , binlog_ignore_db=
Thu May 31 19:50:37 2018 - [info]  Replication filtering check ok.
Thu May 31 19:50:37 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Thu May 31 19:50:37 2018 - [info] Checking SSH publickey authentication settings on the current master..
Thu May 31 19:50:37 2018 - [info] HealthCheck: SSH to 192.168.99.185 is reachable.
Thu May 31 19:50:37 2018 - [info]
192.168.99.185(192.168.99.185:3307) (current master)
+--192.168.99.183(192.168.99.183:3307)
+--192.168.99.184(192.168.99.184:3307)

Thu May 31 19:50:37 2018 - [info] Checking replication health on 192.168.99.183..
Thu May 31 19:50:37 2018 - [info]  ok.
Thu May 31 19:50:37 2018 - [info] Checking replication health on 192.168.99.184..
Thu May 31 19:50:37 2018 - [info]  ok.
Thu May 31 19:50:37 2018 - [info] Checking master_ip_failover_script status:
Thu May 31 19:50:37 2018 - [info]   /etc/masterha/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.99.185 --orig_master_ip=192.168.99.185 --orig_master_port=3307  --orig_master_ssh_port=3322
Thu May 31 19:50:37 2018 - [info]  OK.
Thu May 31 19:50:37 2018 - [warning] shutdown_script is not defined.
Thu May 31 19:50:37 2018 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.


3.但在节点切换测试时,新主节点和VIP都可以正常启动,但主从同步没办法正常:
主节点切换日志:
192.168.99.184(192.168.99.184:3307): OK: Applying all logs succeeded.
192.168.99.184(192.168.99.184:3307): OK: Activated master IP address.
192.168.99.185(192.168.99.185:3307): ERROR: Starting slave failed.
Master failover to 192.168.99.184(192.168.99.184:3307) done, but recovery on slave partially failed.
 
slave节点show slave status\G;提示:
Last_IO_Error: error connecting to master 'repl@192.168.99.184:3307' - retry-time: 60  retries: 13

4.在新主节点error.log中找到关键提示:
2018-05-30T13:16:21.305305Z 63 [Note] Access denied for user 'repl'@'db5' (using password: YES)
2018-05-30T13:17:21.307134Z 64 [Note] Access denied for user 'repl'@'db5' (using password: YES)
2018-05-30T13:18:21.308872Z 65 [Note] Access denied for user 'repl'@'db5' (using password: YES)

5.将masterha_default.cnf的密码填写正确,就正常了. 查看全部
       本菜在对MHA进行测试时意外发现check_repl没有对配置文件masterha_default.cnf中的repl_user和password正确性进行检查.特别记录以下内容,不对之处欢迎批评指正.
1.环境node1(manger) 192.168.99.183
node2:192.168.99.184
node3:192.168.99.185
vip:192.168.99.253
MHA::MasterMonitor version 0.58.
 
2.当masterha_default.cnf配置中slave 同步账号密码填写错误时,通过masterha_check_repl无法检查到相应账号问题(只会检查slave的同步状态):
#masterha_check_repl --conf=/etc/masterha/app1.conf
Thu May 31 19:50:35 2018 - [info] Reading default configuration from /etc/masterha_default.cnf..
Thu May 31 19:50:35 2018 - [info] Reading application default configuration from /etc/masterha/app1.conf..
Thu May 31 19:50:35 2018 - [info] Reading server configuration from /etc/masterha/app1.conf..
Thu May 31 19:50:35 2018 - [info] MHA::MasterMonitor version 0.58.
Thu May 31 19:50:37 2018 - [info] GTID failover mode = 1
Thu May 31 19:50:37 2018 - [info] Dead Servers:
Thu May 31 19:50:37 2018 - [info] Alive Servers:
Thu May 31 19:50:37 2018 - [info]   192.168.99.183(192.168.99.183:3307)
Thu May 31 19:50:37 2018 - [info]   192.168.99.184(192.168.99.184:3307)
Thu May 31 19:50:37 2018 - [info]   192.168.99.185(192.168.99.185:3307)
Thu May 31 19:50:37 2018 - [info] Alive Slaves:
Thu May 31 19:50:37 2018 - [info]   192.168.99.183(192.168.99.183:3307)  Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Thu May 31 19:50:37 2018 - [info]     GTID ON
Thu May 31 19:50:37 2018 - [info]     Replicating from 192.168.99.185(192.168.99.185:3307)
Thu May 31 19:50:37 2018 - [info]   192.168.99.184(192.168.99.184:3307)  Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Thu May 31 19:50:37 2018 - [info]     GTID ON
Thu May 31 19:50:37 2018 - [info]     Replicating from 192.168.99.185(192.168.99.185:3307)
Thu May 31 19:50:37 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 31 19:50:37 2018 - [info] Current Alive Master: 192.168.99.185(192.168.99.185:3307)
Thu May 31 19:50:37 2018 - [info] Checking slave configurations..
Thu May 31 19:50:37 2018 - [info]  read_only=1 is not set on slave 192.168.99.183(192.168.99.183:3307).
Thu May 31 19:50:37 2018 - [info]  read_only=1 is not set on slave 192.168.99.184(192.168.99.184:3307).
Thu May 31 19:50:37 2018 - [info] Checking replication filtering settings..
Thu May 31 19:50:37 2018 - [info]  binlog_do_db= , binlog_ignore_db=
Thu May 31 19:50:37 2018 - [info]  Replication filtering check ok.
Thu May 31 19:50:37 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Thu May 31 19:50:37 2018 - [info] Checking SSH publickey authentication settings on the current master..
Thu May 31 19:50:37 2018 - [info] HealthCheck: SSH to 192.168.99.185 is reachable.
Thu May 31 19:50:37 2018 - [info]
192.168.99.185(192.168.99.185:3307) (current master)
+--192.168.99.183(192.168.99.183:3307)
+--192.168.99.184(192.168.99.184:3307)

Thu May 31 19:50:37 2018 - [info] Checking replication health on 192.168.99.183..
Thu May 31 19:50:37 2018 - [info]  ok.
Thu May 31 19:50:37 2018 - [info] Checking replication health on 192.168.99.184..

Thu May 31 19:50:37 2018 - [info]  ok.
Thu May 31 19:50:37 2018 - [info] Checking master_ip_failover_script status:
Thu May 31 19:50:37 2018 - [info]   /etc/masterha/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.99.185 --orig_master_ip=192.168.99.185 --orig_master_port=3307  --orig_master_ssh_port=3322
Thu May 31 19:50:37 2018 - [info]  OK.
Thu May 31 19:50:37 2018 - [warning] shutdown_script is not defined.
Thu May 31 19:50:37 2018 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.


3.但在节点切换测试时,新主节点和VIP都可以正常启动,但主从同步没办法正常:
主节点切换日志:
192.168.99.184(192.168.99.184:3307): OK: Applying all logs succeeded.
192.168.99.184(192.168.99.184:3307): OK: Activated master IP address.
192.168.99.185(192.168.99.185:3307): ERROR: Starting slave failed.
Master failover to 192.168.99.184(192.168.99.184:3307) done, but recovery on slave partially failed.
 
slave节点show slave status\G;提示:
Last_IO_Error: error connecting to master 'repl@192.168.99.184:3307' - retry-time: 60  retries: 13

4.在新主节点error.log中找到关键提示:
2018-05-30T13:16:21.305305Z 63 [Note] Access denied for user 'repl'@'db5' (using password: YES)
2018-05-30T13:17:21.307134Z 64 [Note] Access denied for user 'repl'@'db5' (using password: YES)
2018-05-30T13:18:21.308872Z 65 [Note] Access denied for user 'repl'@'db5' (using password: YES)

5.将masterha_default.cnf的密码填写正确,就正常了.

mysql 5.7 utf8 alter table 扩容varchar讨论

MySQLMJ 发表了文章 • 0 个评论 • 55 次浏览 • 2018-05-30 08:51 • 来自相关话题

二、mysql 5.7 utf8  alter table 扩容varchar大小:
优先使用的算法 in-place,其次是copy
1> 扩容前字段的大小如果小于256字节 && 扩容后大小小于256字节,则通过in-place算法,采用默认的lock mode,不会阻塞dml操作
2> 扩容前字段的大小如果小于256字节 && 扩容后大小大于256字节,则通过copy算法,采用默认的lock mode,会阻塞dml操作(系统层可以看到拷贝临时表)
3> 扩容前字段的大小如果大于等于256字节, 则通过in-place算法,采用默认的lock mode,不会阻塞dml操作
4> 缩容仅支持copy算法
也就是说VARCHAR列所需的长度字节数必须保持不变,如果发生长度字节的数量从1更改为2,则仅支持copy算法
https://dev.mysql.com/doc/refm ... .html
https://dev.mysql.com/doc/refm ... .html 查看全部
二、mysql 5.7 utf8  alter table 扩容varchar大小:
优先使用的算法 in-place,其次是copy
1> 扩容前字段的大小如果小于256字节 && 扩容后大小小于256字节,则通过in-place算法,采用默认的lock mode,不会阻塞dml操作
2> 扩容前字段的大小如果小于256字节 && 扩容后大小大于256字节,则通过copy算法,采用默认的lock mode,会阻塞dml操作(系统层可以看到拷贝临时表)
3> 扩容前字段的大小如果大于等于256字节, 则通过in-place算法,采用默认的lock mode,不会阻塞dml操作
4> 缩容仅支持copy算法
也就是说VARCHAR列所需的长度字节数必须保持不变,如果发生长度字节的数量从1更改为2,则仅支持copy算法
https://dev.mysql.com/doc/refm ... .html
https://dev.mysql.com/doc/refm ... .html

mysql 5.7/5.6 gtid环境建立主从,从库没有数据

MySQLMJ 发表了文章 • 0 个评论 • 95 次浏览 • 2018-05-30 08:49 • 来自相关话题

一、mysql 5.7/5.6  gtid环境:
通过mysqldump方式建立主从,发现从库没有表结构/数据,原因: 
mysqldump 添加参数 --set-gtid-purged=on/auto,会在dump的文件中添加 SET @@SESSION.SQL_LOG_BIN= 0;
This option enables control over global transaction ID (GTID) information written to the dump file, by indicating whether to add a SET @@global.gtid_purged statement to the output. This option may also cause a statement to be written to the output that disables binary logging while the dump file is being reloaded.
https://bugs.mysql.com/bug.php?id=77845
https://dev.mysql.com/doc/refm ... urged 查看全部
一、mysql 5.7/5.6  gtid环境:
通过mysqldump方式建立主从,发现从库没有表结构/数据,原因: 
mysqldump 添加参数 --set-gtid-purged=on/auto,会在dump的文件中添加 SET @@SESSION.SQL_LOG_BIN= 0;
This option enables control over global transaction ID (GTID) information written to the dump file, by indicating whether to add a SET @@global.gtid_purged statement to the output. This option may also cause a statement to be written to the output that disables binary logging while the dump file is being reloaded.
https://bugs.mysql.com/bug.php?id=77845
https://dev.mysql.com/doc/refm ... urged

mysql 5.6单实例下有两万多个库导致启动和备份都很慢,有加速的方法吗?

MySQLyejr 回复了问题 • 2 人关注 • 1 个回复 • 160 次浏览 • 2018-05-19 12:31 • 来自相关话题

mysql-router

回复

MySQLA673 发起了问题 • 1 人关注 • 0 个回复 • 86 次浏览 • 2018-05-18 17:04 • 来自相关话题

数据库5.6升级到5.7后乱码

MySQLzhagyilig 回复了问题 • 2 人关注 • 5 个回复 • 217 次浏览 • 2018-05-18 11:21 • 来自相关话题

kingshard这个中间件怎么样?

回复

MySQLFan 发起了问题 • 1 人关注 • 0 个回复 • 69 次浏览 • 2018-05-12 11:10 • 来自相关话题

pt-table-checksum检测不出主从差异处理

MySQLUest 发表了文章 • 0 个评论 • 154 次浏览 • 2018-05-10 10:18 • 来自相关话题

根据课堂上吴老师和各位同学提供的思路整理而成~
最近几版pt-table-checksum(3.0.4和3.0.9)在binlog_format='row',且主从存在差异数据时,却检测不出主从差异。原因就是处理过程中主库没有SET binlog_format = 'STATEMENT',导致下面两个核心语句不是以statement格式记录,从库不会进行CRC32相关运算,主从永远一致~# 查看pt版本
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --version
pt-table-checksum 3.0.4
[root@ZST2 ~]#
# pt-table-checksum 3.0.4检测不出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:41:15 0 0 8 1 0 0.257 replcrash.py_user
05-08T09:41:16 0 0 67740 5 0 1.056 replcrash.py_user_innodb

# 两个核心语句
REPLACE INTO `replcrash`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'replcrash', 'py_user', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `uid`, convert(`name` using utf8mb4), `add_time`, convert(`server_id` using utf8mb4), CONCAT(ISNULL(`name`), ISNULL(`add_time`), ISNULL(`server_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `replcrash`.`py_user` /*checksum table*/
UPDATE `replcrash`.`checksums` SET chunk_time = '0.004092', master_crc = '5abbd632', master_cnt = '8' WHERE db = 'replcrash' AND tbl = 'py_user' AND chunk = '1'方法一:修改代码# pt-table-checksum 3.0.4 只需注释掉第9335行和9364行,这层逻辑应该应用于任何情况下,因此不需要使用if判断
[root@ZST2 ~]# vim /usr/local/bin/pt-table-checksum
...
9335 #if ( $o->get('check-binlog-format') ) {
9336 # https://bugs.launchpad.net/per ... 19352
9337 # The tool shouldn't blindly attempt to change binlog_format;
9338 # instead, it should check if it's already set to STATEMENT.
9339 # This is becase starting with MySQL 5.1.29, changing the format
9340 # requires a SUPER user.
9341 if ( VersionParser->new($dbh) >= '5.1.5' ) {
9342 $sql = 'SELECT @@binlog_format';
9343 PTDEBUG && _d($dbh, $sql);
9344 my ($original_binlog_format) = $dbh->selectrow_array($sql);
9345 PTDEBUG && _d('Original binlog_format:', $original_binlog_format);
9346 if ( $original_binlog_format !~ /STATEMENT/i ) {
9347 $sql = q{/*!50108 SET @@binlog_format := 'STATEMENT'*/};
9348 eval {
9349 PTDEBUG && _d($dbh, $sql);
9350 $dbh->do($sql);
9351 };
9352 if ( $EVAL_ERROR ) {
9353 die "Failed to $sql: $EVAL_ERROR\n"
9354 . "This tool requires binlog_format=STATEMENT, "
9355 . "but the current binlog_format is set to "
9356 ."$original_binlog_format and an error occurred while "
9357 . "attempting to change it. If running MySQL 5.1.29 or newer, "
9358 . "setting binlog_format requires the SUPER privilege. "
9359 . "You will need to manually set binlog_format to 'STATEMENT' "
9360 . "before running this tool.\n";
9361 }
9362 }
9363 }
9364 #}
...

# 修改后检测出主从不一致
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:51:32 0 1 8 1 0 0.030 replcrash.py_user
05-08T09:51:33 0 0 67740 5 0 0.561 replcrash.py_user_innodb
[root@ZST2 ~]# 方法二、命令行带上--set-vars参考
详细说明参考:pt-table-checksum not detecting diffs# pt-table-checksum 3.0.4不修改代码,带上--set-vars参数检测出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721 --set-vars binlog_format='statement'
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-10T09:25:01 0 1 11 1 0 0.024 replcrash.py_user
05-10T09:25:01 0 0 67740 5 0 0.695 replcrash.py_user_innodb
[root@ZST2 ~]#善于思考,善于搜索,不要期盼别人把结果原封不动地送到嘴里~.~
吐槽一下,本来想将代码中的关键字加粗,编辑时显示正常,一发布就乱套了 查看全部
根据课堂上吴老师和各位同学提供的思路整理而成~
最近几版pt-table-checksum(3.0.4和3.0.9)在binlog_format='row',且主从存在差异数据时,却检测不出主从差异。原因就是处理过程中主库没有SET binlog_format = 'STATEMENT',导致下面两个核心语句不是以statement格式记录,从库不会进行CRC32相关运算,主从永远一致~
# 查看pt版本
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --version
pt-table-checksum 3.0.4
[root@ZST2 ~]#
# pt-table-checksum 3.0.4检测不出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:41:15 0 0 8 1 0 0.257 replcrash.py_user
05-08T09:41:16 0 0 67740 5 0 1.056 replcrash.py_user_innodb

# 两个核心语句
REPLACE INTO `replcrash`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'replcrash', 'py_user', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `uid`, convert(`name` using utf8mb4), `add_time`, convert(`server_id` using utf8mb4), CONCAT(ISNULL(`name`), ISNULL(`add_time`), ISNULL(`server_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `replcrash`.`py_user` /*checksum table*/
UPDATE `replcrash`.`checksums` SET chunk_time = '0.004092', master_crc = '5abbd632', master_cnt = '8' WHERE db = 'replcrash' AND tbl = 'py_user' AND chunk = '1'
方法一:修改代码
# pt-table-checksum 3.0.4 只需注释掉第9335行和9364行,这层逻辑应该应用于任何情况下,因此不需要使用if判断
[root@ZST2 ~]# vim /usr/local/bin/pt-table-checksum
...
9335 #if ( $o->get('check-binlog-format') ) {
9336 # https://bugs.launchpad.net/per ... 19352
9337 # The tool shouldn't blindly attempt to change binlog_format;
9338 # instead, it should check if it's already set to STATEMENT.
9339 # This is becase starting with MySQL 5.1.29, changing the format
9340 # requires a SUPER user.
9341 if ( VersionParser->new($dbh) >= '5.1.5' ) {
9342 $sql = 'SELECT @@binlog_format';
9343 PTDEBUG && _d($dbh, $sql);
9344 my ($original_binlog_format) = $dbh->selectrow_array($sql);
9345 PTDEBUG && _d('Original binlog_format:', $original_binlog_format);
9346 if ( $original_binlog_format !~ /STATEMENT/i ) {
9347 $sql = q{/*!50108 SET @@binlog_format := 'STATEMENT'*/};
9348 eval {
9349 PTDEBUG && _d($dbh, $sql);
9350 $dbh->do($sql);
9351 };
9352 if ( $EVAL_ERROR ) {
9353 die "Failed to $sql: $EVAL_ERROR\n"
9354 . "This tool requires binlog_format=STATEMENT, "
9355 . "but the current binlog_format is set to "
9356 ."$original_binlog_format and an error occurred while "
9357 . "attempting to change it. If running MySQL 5.1.29 or newer, "
9358 . "setting binlog_format requires the SUPER privilege. "
9359 . "You will need to manually set binlog_format to 'STATEMENT' "
9360 . "before running this tool.\n";
9361 }
9362 }
9363 }
9364 #}
...

# 修改后检测出主从不一致
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:51:32 0 1 8 1 0 0.030 replcrash.py_user
05-08T09:51:33 0 0 67740 5 0 0.561 replcrash.py_user_innodb
[root@ZST2 ~]#
方法二、命令行带上--set-vars参考
详细说明参考:pt-table-checksum not detecting diffs
# pt-table-checksum 3.0.4不修改代码,带上--set-vars参数检测出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721 --set-vars binlog_format='statement'
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-10T09:25:01 0 1 11 1 0 0.024 replcrash.py_user
05-10T09:25:01 0 0 67740 5 0 0.695 replcrash.py_user_innodb
[root@ZST2 ~]#
善于思考,善于搜索,不要期盼别人把结果原封不动地送到嘴里~.~
吐槽一下,本来想将代码中的关键字加粗,编辑时显示正常,一发布就乱套了