MySQL 5.7 sys库里的视图不能访问

回复

MySQLliyh 回复了问题 • 1 人关注 • 1 个回复 • 304 次浏览 • 2018-06-29 16:56 • 来自相关话题

实验--修改MySQL提示符生效实验

MySQLliyh 发表了文章 • 2 个评论 • 106 次浏览 • 2018-06-29 11:41 • 来自相关话题

背景:
物理机器上安装了三个实例
3306    /etc/my.cnf                                      /tmp/mysql3306.sock
3307    /data/3307/mysql/my3307.cnf        /tmp/mysql3307.sock
3308    /data/3308/mysql/my3308.cnf        /tmp/mysql3308.sock

实验过程:
1.准备好命令提示符语句
[mysql]
prompt="\\u@\\h:\\p [\\d]>
2.想修改3308 实例的命令提示符。
 vi /data/3308/mysql/my3308.cnf
……
[mysql]
prompt="\\u@\\h:\\p [\\d]>
……
在3308 实例的配置文件中加上了,命令提示符语句
3.查看效果
 [root@g173x 3308]# mysql -uroot -S /tmp/mysql3308.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1211
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
说好的提示呢?没出现。
4.查看mysql 帮助命令
 mysql --help|less
……
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
……
原来 mysql 命令默认读取配置文件的顺序为上述的5个。
(其实我一直有个疑问,我们初始化实例,和启动实例的时候已经指定了 --defaults-file=xxxx,启动都正常读取了我们自定义配置文件,怎么msyql 就是读不到呢?
这个疑问,我多次沉淀,多次疑惑,时而设置成功,时而设置失败,突然有一天我明白了!我们初始化和启动MySQL时确实指定了 --defaults-file=xxx  但是我们 进入客户端时
mysql -uroot -p -S /tmp/mysql3308.sock  并没有指定--defaults-file!!!【其实是吴老师说的】)
得知以上问题的原因,就很容易实现我们设置MySQL提示符了

5.修改默认位置的配置文件 /etc/my.cnf
 vi /etc/my.cnf
……
[mysql]
prompt="\\u@\\h:\\p [\\d]>
……
每个位置,每个实例配置文件都不同,mysql 命令是每次都指定 --defaults-file= xxxx  太麻烦了,所以直接修改默认位置的,这样所有实例的都读取/etc/my.cnf 这个默认配置下的
【mysql】标签下的内容就好。
这是其他配置文件可以不设【mysql】标签,设置了也不会读到,不过为了避免混乱,就在一处设置就好了!

 
# 只在 /data/3308/mysql/my3308.cnf 设置了提示符修改 
# 直接mysql 登录 提示符未生效[root@g173x 3308]# mysql -uroot -S /tmp/mysql3308.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1213
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \q

 
# 加 --defaults-file 后生效,注意:这里 --defaults-file 要放在最前面才可以,放后面不识别,跟初始化的时候一样。[root@g173x 3308]# mysql --defaults-file=/data/3308/mysql/my3308.cnf -uroot -S /tmp/mysql3308.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1214
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
"root@localhost:mysql3308.sock [(none)]> 查看全部

背景:
物理机器上安装了三个实例
3306    /etc/my.cnf                                      /tmp/mysql3306.sock
3307    /data/3307/mysql/my3307.cnf        /tmp/mysql3307.sock
3308    /data/3308/mysql/my3308.cnf        /tmp/mysql3308.sock

实验过程:
1.准备好命令提示符语句
[mysql]
prompt="\\u@\\h:\\p [\\d]>
2.想修改3308 实例的命令提示符。
 
vi /data/3308/mysql/my3308.cnf
……
[mysql]
prompt="\\u@\\h:\\p [\\d]>
……

在3308 实例的配置文件中加上了,命令提示符语句
3.查看效果
 
[root@g173x 3308]# mysql -uroot -S /tmp/mysql3308.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1211
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

说好的提示呢?没出现。
4.查看mysql 帮助命令
 
mysql --help|less
……
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
……

原来 mysql 命令默认读取配置文件的顺序为上述的5个。
(其实我一直有个疑问,我们初始化实例,和启动实例的时候已经指定了 --defaults-file=xxxx,启动都正常读取了我们自定义配置文件,怎么msyql 就是读不到呢?
这个疑问,我多次沉淀,多次疑惑,时而设置成功,时而设置失败,突然有一天我明白了!我们初始化和启动MySQL时确实指定了 --defaults-file=xxx  但是我们 进入客户端时
mysql -uroot -p -S /tmp/mysql3308.sock  并没有指定--defaults-file!!!【其实是吴老师说的】)
得知以上问题的原因,就很容易实现我们设置MySQL提示符了

5.修改默认位置的配置文件 /etc/my.cnf
 
vi /etc/my.cnf
……
[mysql]
prompt="\\u@\\h:\\p [\\d]>
……

每个位置,每个实例配置文件都不同,mysql 命令是每次都指定 --defaults-file= xxxx  太麻烦了,所以直接修改默认位置的,这样所有实例的都读取/etc/my.cnf 这个默认配置下的
【mysql】标签下的内容就好。
这是其他配置文件可以不设【mysql】标签,设置了也不会读到,不过为了避免混乱,就在一处设置就好了!

 
# 只在 /data/3308/mysql/my3308.cnf 设置了提示符修改 
# 直接mysql 登录 提示符未生效
[root@g173x 3308]# mysql -uroot -S /tmp/mysql3308.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1213
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \q

 
# 加 --defaults-file 后生效,注意:这里 --defaults-file 要放在最前面才可以,放后面不识别,跟初始化的时候一样。
[root@g173x 3308]# mysql --defaults-file=/data/3308/mysql/my3308.cnf -uroot -S /tmp/mysql3308.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1214
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
"root@localhost:mysql3308.sock [(none)]>

Consul服务域名访问时好时坏

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

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

MySQLk2865 发表了文章 • 0 个评论 • 326 次浏览 • 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 发表了文章 • 1 个评论 • 174 次浏览 • 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 个评论 • 244 次浏览 • 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 个回复 • 333 次浏览 • 2018-05-19 12:31 • 来自相关话题

数据库5.6升级到5.7后乱码

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

kingshard这个中间件怎么样?

回复

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

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

MySQLUest 发表了文章 • 0 个评论 • 283 次浏览 • 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 ~]#
善于思考,善于搜索,不要期盼别人把结果原封不动地送到嘴里~.~
吐槽一下,本来想将代码中的关键字加粗,编辑时显示正常,一发布就乱套了