一个SQL问题

qidan3500 回复了问题 • 2 人关注 • 2 个回复 • 85 次浏览 • 2 天前 • 来自相关话题

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

arron 回复了问题 • 3 人关注 • 2 个回复 • 47 次浏览 • 2 天前 • 来自相关话题

MYSQL 8.0 认证变化

回复

wubx 回复了问题 • 1 人关注 • 1 个回复 • 19 次浏览 • 3 天前 • 来自相关话题

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

wubx 回复了问题 • 2 人关注 • 1 个回复 • 87 次浏览 • 5 天前 • 来自相关话题

关于jdbc 和java 连接池的问题

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

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

liyh 发表了文章 • 2 个评论 • 181 次浏览 • 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选项)

数字和字母编码问题

回复

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

关于GTID模式下备份时 --set-gtid-purged=OFF 参数的实验

回复

liyh 发起了问题 • 1 人关注 • 0 个回复 • 360 次浏览 • 2018-02-28 17:50 • 来自相关话题

关于redo log的内容

yejr 回复了问题 • 2 人关注 • 1 个回复 • 307 次浏览 • 2018-01-30 14:00 • 来自相关话题

关于insert buffer的效果

yejr 回复了问题 • 2 人关注 • 1 个回复 • 374 次浏览 • 2018-01-30 13:56 • 来自相关话题