数据库5.6升级到5.7后乱码

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

kingshard这个中间件怎么样?

回复

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

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

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

一个SQL问题

MySQLqidan3500 回复了问题 • 2 人关注 • 2 个回复 • 342 次浏览 • 2018-04-24 14:26 • 来自相关话题

关于fpmmm与pmm的监控数据不一致的问题

MySQLarron 回复了问题 • 3 人关注 • 2 个回复 • 449 次浏览 • 2018-04-24 14:14 • 来自相关话题

MYSQL 8.0 认证变化

回复

MySQLwubx 回复了问题 • 1 人关注 • 1 个回复 • 451 次浏览 • 2018-04-23 17:01 • 来自相关话题

sql_mode 客户端软件登陆和本地登录值不同

MySQLwubx 回复了问题 • 2 人关注 • 1 个回复 • 269 次浏览 • 2018-04-21 11:52 • 来自相关话题

关于jdbc 和java 连接池的问题

MySQLzhiquan 回复了问题 • 3 人关注 • 2 个回复 • 628 次浏览 • 2018-04-08 13:30 • 来自相关话题

使用GTID复制的限制条件--手册章节翻译

MySQLliyh 发表了文章 • 2 个评论 • 371 次浏览 • 2018-03-14 10:52 • 来自相关话题

最近想把传统复制转移到GTID复制,翻看了一下官方手册的,做了简单翻译,有些地方翻译的很蹩脚,但是大体可以帮助理解下本章节。如有大神,请帮忙提供点意见,有的地方真的理解不上去。
 
原文:16.1.3.4 Restrictions on Replication with GTIDs

Because GTID-based replication is dependent on transactions, some features otherwise available in MySQL are not supported when using it. This section provides information about restrictions on and limitations of replication with GTIDs.

Updates involving nontransactional storage engines.  When using GTIDs, updates to tables using nontransactional storage engines such as MyISAM cannot be made in the same statement or transaction as updates to tables using transactional storage engines such as InnoDB.

This restriction is due to the fact that updates to tables that use a nontransactional storage engine mixed with updates to tables that use a transactional storage engine within the same transaction can result in multiple GTIDs being assigned to the same transaction.

Such problems can also occur when the master and the slave use different storage engines for their respective versions of the same table, where one storage engine is transactional and the other is not.

In any of the cases just mentioned, the one-to-one correspondence between transactions and GTIDs is broken, with the result that GTID-based replication cannot function correctly.

CREATE TABLE ... SELECT statements.  CREATE TABLE ... SELECT is not safe for statement-based replication. When using row-based replication, this statement is actually logged as two separate events—one for the creation of the table, and another for the insertion of rows from the source table into the new table just created. When this statement is executed within a transaction, it is possible in some cases for these two events to receive the same transaction identifier, which means that the transaction containing the inserts is skipped by the slave. Therefore, CREATE TABLE ... SELECT is not supported when using GTID-based replication.

Temporary tables.  CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions when using GTIDs (that is, when the server was started with the --enforce-gtid-consistency option). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

Preventing execution of unsupported statements.  To prevent execution of statements that would cause GTID-based replication to fail, all servers must be started with the --enforce-gtid-consistency option when enabling GTIDs. This causes statements of any of the types discussed previously in this section to fail with an error.

Note that --enforce-gtid-consistency only takes effect if binary logging takes place for a statement. If binary logging is disabled on the server, or if statements are not written to the binary log because they are removed by a filter, GTID consistency is not checked or enforced for the statements that are not logged.

For information about other required startup options when enabling GTIDs, see Section 16.1.3.2, “Setting Up Replication Using GTIDs”.

sql_slave_skip_counter is not supported when using GTIDs. If you need to skip transactions, use the value of the master's gtid_executed variable instead; see Injecting empty transactions, for more information.

GTID mode and mysqldump.  It is possible to import a dump made using mysqldump into a MySQL Server running with GTID mode enabled, provided that there are no GTIDs in the target server's binary log.

GTID mode and mysql_upgrade.  When the server is running with global transaction identifiers (GTIDs) enabled (gtid_mode=ON), do not enable binary logging by mysql_upgrade (the --write-binlog option).
来源: https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-restrictions.html 

译文:
由于基于GTID的复制是依赖于事物,另外一些特性在MySQL中允许而使用GTID就不支持了。本章节提供了使用GTID复制的限制条件和局限性的一些信息。
涉及非实物存储引擎的变更。当使用GTID时,对于非事务引擎表的更新,如MyISAM,不能与事物存储引擎表更新,如innodb ,在同一陈述(语句)或事物中。
这个限制是由于,在同一事物混合更新非事务存储引擎表和事务存储引擎表,会导致多个GTID被分配到同一个事务。
这个问题也会发生在,主库和从库同一张表分别使用不同的存储引擎的各自版本,一个存储引擎是事务的另一个不是。
上述任意一种情况,事务和GTID之间的一一对应关系都被破坏,从而导致基于GTID的复制不能正常运行。
create table...select 语句。create table ...select 在基于语句的复制,是不安全的。当使用基于行的复制,这个语句实际上被记录成两个独立的事件--一个是表的创建,另一个是(从原表到新建表)的行的插入。当这样的语句在事务里执行,两个事件会获得同一个事务标识,这就意味着包含插入语句的事务会被从库忽略。因此,create table...select .在基于GTID的复制中是不支持的。
临时表。create temporary table 和drop temporary table 语句在GTID 内部事务中是不支持的(当服务开启--enforce-gtid-consistency 选项时)。GTID开启时,可以使用这些语句,当只能是outside 任何事务,并且仅当autocommit=1时。
防止执行不支持的语句。为了防止执行导致基于GTID复制的失败的语句,当开启GTID时,所有的服务都要开启 --enforce-gtid-consistency 这个选项。这是导致(本章之前讨论的任一类型的)语句失败或者错误。
注意 --enforce-gtid-consistency 只在二进制日志开启的语句生效。如果服务禁止了二进制日志,或者语句被过滤器删除没有写入二进制日志,GTID一致性无法检查或者强制没有写入日志语句。

关于开启GTID时,其他开启选项要求信息,参考章节16.1.3.2 “使用GTID设置复制”


当使用GTID时,sql_slave_skip_counter 是不支持的。如果需要跳过事务,使用主库的gtid_executed变量值来替代。参考注入空事务,获取更多信息


GTID 模式和mysqldump。可以导入一个用mysqldump做的备份到开启GTID的MySQL服务中,目标服务没有GTID的二进制日志(就是说可以导入由mysqldump做的备份到开启GTID的MySQL中,但是这个MySQL日志中必须没有任何gtid事务,就是gtid事务为空)


GTID 模式和mysql_upgrad.当正在运行的服务开启全局事务标识(GTID)(gtid_mode=on),不要开启二进制日志mysql_upgrade(--write-binlog选项) 查看全部
最近想把传统复制转移到GTID复制,翻看了一下官方手册的,做了简单翻译,有些地方翻译的很蹩脚,但是大体可以帮助理解下本章节。如有大神,请帮忙提供点意见,有的地方真的理解不上去。
 
原文:16.1.3.4 Restrictions on Replication with GTIDs

Because GTID-based replication is dependent on transactions, some features otherwise available in MySQL are not supported when using it. This section provides information about restrictions on and limitations of replication with GTIDs.

Updates involving nontransactional storage engines.  When using GTIDs, updates to tables using nontransactional storage engines such as MyISAM cannot be made in the same statement or transaction as updates to tables using transactional storage engines such as InnoDB.

This restriction is due to the fact that updates to tables that use a nontransactional storage engine mixed with updates to tables that use a transactional storage engine within the same transaction can result in multiple GTIDs being assigned to the same transaction.

Such problems can also occur when the master and the slave use different storage engines for their respective versions of the same table, where one storage engine is transactional and the other is not.

In any of the cases just mentioned, the one-to-one correspondence between transactions and GTIDs is broken, with the result that GTID-based replication cannot function correctly.

CREATE TABLE ... SELECT statements.  CREATE TABLE ... SELECT is not safe for statement-based replication. When using row-based replication, this statement is actually logged as two separate events—one for the creation of the table, and another for the insertion of rows from the source table into the new table just created. When this statement is executed within a transaction, it is possible in some cases for these two events to receive the same transaction identifier, which means that the transaction containing the inserts is skipped by the slave. Therefore, CREATE TABLE ... SELECT is not supported when using GTID-based replication.

Temporary tables.  CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions when using GTIDs (that is, when the server was started with the --enforce-gtid-consistency option). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

Preventing execution of unsupported statements.  To prevent execution of statements that would cause GTID-based replication to fail, all servers must be started with the --enforce-gtid-consistency option when enabling GTIDs. This causes statements of any of the types discussed previously in this section to fail with an error.

Note that --enforce-gtid-consistency only takes effect if binary logging takes place for a statement. If binary logging is disabled on the server, or if statements are not written to the binary log because they are removed by a filter, GTID consistency is not checked or enforced for the statements that are not logged.

For information about other required startup options when enabling GTIDs, see Section 16.1.3.2, “Setting Up Replication Using GTIDs”.

sql_slave_skip_counter is not supported when using GTIDs. If you need to skip transactions, use the value of the master's gtid_executed variable instead; see Injecting empty transactions, for more information.

GTID mode and mysqldump.  It is possible to import a dump made using mysqldump into a MySQL Server running with GTID mode enabled, provided that there are no GTIDs in the target server's binary log.

GTID mode and mysql_upgrade.  When the server is running with global transaction identifiers (GTIDs) enabled (gtid_mode=ON), do not enable binary logging by mysql_upgrade (the --write-binlog option).
来源: https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-restrictions.html 

译文:
由于基于GTID的复制是依赖于事物,另外一些特性在MySQL中允许而使用GTID就不支持了。本章节提供了使用GTID复制的限制条件和局限性的一些信息。
涉及非实物存储引擎的变更。当使用GTID时,对于非事务引擎表的更新,如MyISAM,不能与事物存储引擎表更新,如innodb ,在同一陈述(语句)或事物中。
这个限制是由于,在同一事物混合更新非事务存储引擎表和事务存储引擎表,会导致多个GTID被分配到同一个事务。
这个问题也会发生在,主库和从库同一张表分别使用不同的存储引擎的各自版本,一个存储引擎是事务的另一个不是。
上述任意一种情况,事务和GTID之间的一一对应关系都被破坏,从而导致基于GTID的复制不能正常运行。
create table...select 语句。create table ...select 在基于语句的复制,是不安全的。当使用基于行的复制,这个语句实际上被记录成两个独立的事件--一个是表的创建,另一个是(从原表到新建表)的行的插入。当这样的语句在事务里执行,两个事件会获得同一个事务标识,这就意味着包含插入语句的事务会被从库忽略。因此,create table...select .在基于GTID的复制中是不支持的。
临时表。create temporary table 和drop temporary table 语句在GTID 内部事务中是不支持的(当服务开启--enforce-gtid-consistency 选项时)。GTID开启时,可以使用这些语句,当只能是outside 任何事务,并且仅当autocommit=1时。
防止执行不支持的语句。为了防止执行导致基于GTID复制的失败的语句,当开启GTID时,所有的服务都要开启 --enforce-gtid-consistency 这个选项。这是导致(本章之前讨论的任一类型的)语句失败或者错误。
注意 --enforce-gtid-consistency 只在二进制日志开启的语句生效。如果服务禁止了二进制日志,或者语句被过滤器删除没有写入二进制日志,GTID一致性无法检查或者强制没有写入日志语句。

关于开启GTID时,其他开启选项要求信息,参考章节16.1.3.2 “使用GTID设置复制”


当使用GTID时,sql_slave_skip_counter 是不支持的。如果需要跳过事务,使用主库的gtid_executed变量值来替代。参考注入空事务,获取更多信息


GTID 模式和mysqldump。可以导入一个用mysqldump做的备份到开启GTID的MySQL服务中,目标服务没有GTID的二进制日志(就是说可以导入由mysqldump做的备份到开启GTID的MySQL中,但是这个MySQL日志中必须没有任何gtid事务,就是gtid事务为空)


GTID 模式和mysql_upgrad.当正在运行的服务开启全局事务标识(GTID)(gtid_mode=on),不要开启二进制日志mysql_upgrade(--write-binlog选项)

数字和字母编码问题

回复

MySQLlxzkenney 发起了问题 • 1 人关注 • 0 个回复 • 401 次浏览 • 2018-03-08 17:16 • 来自相关话题