在数据库主从切换或数据迁移需要注意的几个参数

  • A+
所属分类:MySQL

统由于新旧系的一些参数有一些差异,下面这几个参数,如果参数不同,肯能在做主从切换的时候会有些问题。

  • explicit_defaults_for_timestamp
  • sql_mode
  • log_slave_updates
  • binlog_row_image
  • auto_increment_increment
  • auto_increment_offset

这边主要对上面参数进行测试。

注意: 如果是设计到新旧系统的主从切换,或者数据迁移需要着重关注这些。并且让参数和原来库的参数保持一致.


explicit_defaults_for_timestamp

建议: 在所有的环境都设置成 1, 就不会出现奇怪的现象, 也避免了在做数据迁移的时候会影响到业务的sql执行。毕竟5.7也已经是强制要求这个参数设置为1了。我们也就从了吧。

Tips: 该参数在5.6不能在Session级别设置,在5.7环境下能。

设置成: 0

结论: 在创建表的时候如果有多个TIMESTAMP字段并且没有默认值, 表现为:
第一个 TIMESTAMP 字段的定义: NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
第二个 TIMESTAMP 字段的定义: NOT NULL DEFAULT '0000-00-00 00:00:00'
之后的 TIMESTAMP 字段的定义: NOT NULL DEFAULT '0000-00-00 00:00:00'

测试如下:

 

设置成: 1

结论: 在创建表的时候无论有多少个TIMESTAMP字段并且没有默认值那么就自动为 NULL DEFAULT NULL,

测试如下:

 

sql_mode

该参数的值主要关注一些严格限制的值:

  1. STRICT_TRANS_TABLES

结论:添加了上面的值在做 insertupdate的时候如果碰到一些无效值,或者一些值操过了定义的范围,直接就一报错结局并且回滚事物。没有设置该值MySQL只是产生一个Warning

注意: 如果在原数据库中没有设置了这些值, 可是到了迁移的目标数据库中设置了这些值, 在开发新库使用后可能会对业务的SQL有影响从而导致SQL执行失败的现象。
当然,严格限制肯定是好的,这会让我们的及时知道数据库发生了什么。不会默默的让数据库把一些错误吞掉。但是我们还是需要在保证业务正常走通的情况下使用。
除非,你项目一开始就使用了这参数那之后就不会有相关的问题了。如果一开始没有使用的话。那就不要画蛇添足了。该怎样还怎样吧。

Tips:MySQL 5.7.7以后sql_mode的默认值为: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION所以从5.6升级到5.7的时候需要手动配置参数和5.6保持一致了。

有 STRICT_TRANS_TABLES

结论: 在对数据库进行修改的时候对值进行了严格的校验,只风险直接报错

实验过程:

 

我们可以看到MySQL直接报错,说name字段给的值太长了

 

我们可以看到将一个字符串插入到一个 INT 类型中 MySQL 也直接就报错了

无 STRICT_TRANS_TABLES

结论:出现非法的值,只是会有 Warning 但是还是会执行成功,但是 MySQL 会在内部默认的处理这些值。

实验过程:

 

从上面可以看到在向name插入值的时候,如果插入的值超过了定义的长度MySQL会截取字符串并且插入成功。

唠叨: MySQL这种默认的行为是比较危险的,因为这样会造成一些业务数据执行成功,但是其实数据变成了无用数据了,因为数据被截取了。这也是我为什么说其实严格的模式是好的原因了。


 

从上面实验我们可以看到向 INT 类型中插入一个字符串执行成功并产生一个Warning,并且在MySQL会将值自动变为0

和我想的不一样: 我觉得默认值应该会是一个 NULL 才对,毕竟我们设置的DEFAULT NULL

log_slave_updates

该参数我只做一个解释,具体实验就不做了。

解释:在做主从的时候,从库在应用主库的binlog的时候,是否也需要写入Slavebinlog

结论: 如果Slave也需要充当Master类似M->S1->S2的架构那S1就需要开启log_slave_updates参数了

建议: 个人认为还是不论是什么环境,只要有MySQL实例的情况下还是统一开启的好。毕竟现在的机器的综合能力的很强。没必要为节省一点点I/O把这个参数给关闭了。统一开启主要也是为了方便我们的统一管理。毕竟机器如此的多要一个个去判断,去修改也不是太明智。

binlog_row_image

该参数主要是用来决定如何记录binlog 的方式的有三种选项:

  1. full
  2. minimal
  3. noblob

Tips: 这边使用了 python-mysql-replication 作为解析binlog工具来观察相关内容

结论: 只有full才会完整的记录修改记录的所有字段的前后值
建议: 就使用默认的full就是最安全最好的。不要为了节省那一点空间而放弃使用full, 毕竟现在我们的架构部单单是MySQL。还有其他消息队列等等系统的接入。他们会需要每一行的所有值的。特别是如果有使用到想kylin这样的分析型数据库的时候他们就需要有较完整的数据。
还有一点就是在做解析binlog做回滚的时候如果没有做成full的话将没发做回滚了。这个是相当致命的。

full

该选项是数据库默认的,无论你修改了记录的哪个字段,都会记录下所有的字段的修改前的值和修改后的值。

实验过程:

 

从上面可以看到我们解析出来了。我们只修改了name字段,但是html字段修改前后的值也都会一起记录。

minimal

在binlog中只保存了修改了的字段的前后值,没有改的字段就不记录其前后值。但是会记录下主键的修改前的值。
该选项抱着能不记录就不多余记录的原则活着。

实验过程:

 

从上面结果可以看到,我们修改了id的值,但是在binlog中只记录了id字段的修改前后值, namehtml这两个字段的值压根没有记录

 

从上面可以看到我们修改了name字段的值,只记录下了主键idname修改后的值。

noblob

在某些表中存在BLOBTEXT字段,但是修改字段值的时候这两种值不会被记录。当然如果有修改这两种类型字段。修改的值还是会记录的。

实验过程:

 

从上面我们可以看到我们修改了name字段除了html字段没有被记录外其他的字段会被全部记录

auto_increment_increment & auto_increment_offset

这两个参数其实算是大家比较熟悉的了。和自增ID有关的。一般会去设置这个值的会是业务上的需要。比如在双主 M1<-->M2 结构。并且需要支持双写,这样的情况就需要设置auto_increment_increment(步长)auto_increment_offset了。

一般情况下两个实例直接的自增ID需要交叉着使用:

  • M1 的自增ID为1, 3, 5, 7, 9
  • M2 的自增ID为2, 4, 6, 8, 10

注意:对于必要的系统,在主从切换或数据迁移的时候就需要特别注意这两个值是否相等。如果不相等那就很可能会出现数据同步的问题。

weinxin
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~
HH

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:4   其中:访客  4   博主  0

    • 优选汇 1

      谢谢博主分享

      • 深蓝 2

        具体分析吧

        • 匿名 9

          。额

          • 二锅头 0

            查询内容或信息图片等。