- A+
统由于新旧系的一些参数有一些差异，下面这几个参数，如果参数不同，肯能在做主从切换的时候会有些问题。
- 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
HH@test 19:45:58>show variables like '%explicit_defaults_for_timestamp%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | explicit_defaults_for_timestamp | OFF | +---------------------------------+-------+ 1 row in set (0.00 sec) HH@test 19:49:52>CREATE TABLE timestamp_1( -> id INT NOT NULL AUTO_INCREMENT COMMENT 'ID', -> a TIMESTAMP, -> b TIMESTAMP, -> c TIMESTAMP, -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (0.03 sec) HH@test 19:50:13>show create table timestamp_1 \G *************************** 1. row *************************** Table: timestamp_1 Create Table: CREATE TABLE `timestamp_1` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) |
设置成: 1
结论: 在创建表的时候无论有多少个TIMESTAMP字段并且没有默认值那么就自动为 NULL DEFAULT NULL,
测试如下:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
HH@test 20:10:30>show session variables like '%explicit_defaults_for_timestamp%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | explicit_defaults_for_timestamp | ON | +---------------------------------+-------+ 1 row in set (0.00 sec) -- 创建测试表 HH@test 20:11:31>CREATE TABLE timestamp_2( -> id INT NOT NULL AUTO_INCREMENT COMMENT 'ID', -> a TIMESTAMP, -> b TIMESTAMP, -> c TIMESTAMP, -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (1.03 sec) HH@test 20:12:03>show create table timestamp_2 \G *************************** 1. row *************************** Table: timestamp_2 Create Table: CREATE TABLE `timestamp_2` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `a` timestamp NULL DEFAULT NULL, `b` timestamp NULL DEFAULT NULL, `c` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) |
sql_mode
该参数的值主要关注一些严格限制的值：
- STRICT_TRANS_TABLES
结论：添加了上面的值在做 insert、 update的时候如果碰到一些无效值，或者一些值操过了定义的范围，直接就一报错结局并且回滚事物。没有设置该值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
结论: 在对数据库进行修改的时候对值进行了严格的校验，只风险直接报错
实验过程:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
HH@localhost 11:25:31 [test]>SHOW SESSION VARIABLES LIKE "%sql_mode%"; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | sql_mode | STRICT_TRANS_TABLES,... | +---------------+-------------------------+ CREATE TABLE test_1( id INT NOT NULL AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) DEFAULT NULL, age int DEFAULT NULL, PRIMARY KEY(id) ); HH@localhost 11:27:25 [test]>INSERT INTO test_1 VALUES(NULL, 'aaaaaaaaaaa', 1); ERROR 1406 (22001): Data too long for column 'name' at row 1 |
我们可以看到MySQL直接报错，说name字段给的值太长了
|
1 2 |
HH@localhost 11:27:37 [test]>INSERT INTO test_1 VALUES(NULL, 'aaaaaaaaaa', 'a'); ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'age' at row 1 |
我们可以看到将一个字符串插入到一个 INT 类型中 MySQL 也直接就报错了
无 STRICT_TRANS_TABLES
结论：出现非法的值，只是会有 Warning 但是还是会执行成功，但是 MySQL 会在内部默认的处理这些值。
实验过程:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
HH@localhost 11:34:47 [test]>SHOW SESSION VARIABLES LIKE "%sql_mode%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+ 1 row in set (0.00 sec) HH@localhost 11:34:51 [test]>INSERT INTO test_1 VALUES(NULL, 'aaaaaaaaaabbb', 1); Query OK, 1 row affected, 1 warning (0.00 sec) HH@localhost 11:36:48 [test]>show warnings; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1265 | Data truncated for column 'name' at row 1 | +---------+------+-------------------------------------------+ 1 row in set (0.00 sec) HH@localhost 11:36:57 [test]>select * from test_1; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | aaaaaaaaaa | 1 | +----+------------+------+ 1 row in set (0.00 sec) |
从上面可以看到在向name插入值的时候，如果插入的值超过了定义的长度MySQL会截取字符串并且插入成功。
唠叨:
MySQL这种默认的行为是比较危险的，因为这样会造成一些业务数据执行成功，但是其实数据变成了无用数据了，因为数据被截取了。这也是我为什么说其实严格的模式是好的原因了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
HH@localhost 11:43:45 [test]>INSERT INTO test_1 VALUES(NULL, 'aaaaaaaaaa', 'a'); Query OK, 1 row affected, 1 warning (0.02 sec) HH@localhost 11:43:53 [test]>show warnings; +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1366 | Incorrect integer value: 'a' for column 'age' at row 1 | +---------+------+--------------------------------------------------------+ 1 row in set (0.00 sec) HH@localhost 11:44:07 [test]>SELECT * FROM test_1; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | aaaaaaaaaa | 0 | +----+------------+------+ 1 row in set (0.00 sec) |
从上面实验我们可以看到向 INT 类型中插入一个字符串执行成功并产生一个Warning，并且在MySQL会将值自动变为0
和我想的不一样: 我觉得默认值应该会是一个
NULL才对，毕竟我们设置的DEFAULT NULL
log_slave_updates
该参数我只做一个解释，具体实验就不做了。
解释：在做主从的时候，从库在应用主库的binlog的时候，是否也需要写入Slave的binlog中
结论: 如果Slave也需要充当Master类似M->S1->S2的架构那S1就需要开启log_slave_updates参数了
建议: 个人认为还是不论是什么环境，只要有MySQL实例的情况下还是统一开启的好。毕竟现在的机器的综合能力的很强。没必要为节省一点点I/O把这个参数给关闭了。统一开启主要也是为了方便我们的统一管理。毕竟机器如此的多要一个个去判断，去修改也不是太明智。
binlog_row_image
该参数主要是用来决定如何记录binlog 的方式的有三种选项:
- full
- minimal
- noblob
Tips: 这边使用了
python-mysql-replication作为解析binlog工具来观察相关内容
结论: 只有full才会完整的记录修改记录的所有字段的前后值
建议: 就使用默认的full就是最安全最好的。不要为了节省那一点空间而放弃使用full, 毕竟现在我们的架构部单单是MySQL。还有其他消息队列等等系统的接入。他们会需要每一行的所有值的。特别是如果有使用到想kylin这样的分析型数据库的时候他们就需要有较完整的数据。
还有一点就是在做解析binlog做回滚的时候如果没有做成full的话将没发做回滚了。这个是相当致命的。
full
该选项是数据库默认的，无论你修改了记录的哪个字段，都会记录下所有的字段的修改前的值和修改后的值。
实验过程:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
HH@localhost 02:10:53 [test]>SHOW SESSION VARIABLES LIKE '%binlog_row_image%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | binlog_row_image | FULL | +------------------+-------+ 1 row in set (0.00 sec) HH@localhost 02:15:42 [test]>show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(10) NOT NULL DEFAULT '', `html` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) HH@localhost 02:16:11 [test]>SELECT * FROM t1; +----+------+---------------+ | id | name | html | +----+------+---------------+ | 1 | HH | <html></html> | +----+------+---------------+ 1 row in set (0.00 sec) UPDATE t1 SET name = 'CC'; === binlog解析结果 === Date: 2017-07-07T14:17:13 Log position: 2712 Event size: 59 Read bytes: 13 Table: test.t1 Affected columns: 3 Changed rows: 1 Affected columns: 3 Values: -- *html:<html></html>=><html></html> *id:1=>1 *name:HH=>CC |
从上面可以看到我们解析出来了。我们只修改了name字段，但是html字段修改前后的值也都会一起记录。
minimal
在binlog中只保存了修改了的字段的前后值，没有改的字段就不记录其前后值。但是会记录下主键的修改前的值。
该选项抱着能不记录就不多余记录的原则活着。
实验过程:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
HH@localhost 02:19:45 [test]>SHOW SESSION VARIABLES LIKE '%binlog_row_image%'; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | binlog_row_image | MINIMAL | +------------------+---------+ 1 row in set (0.00 sec) HH@localhost 02:22:02 [test]>UPDATE t1 SET id = 2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 === 解析binlog的值 === Date: 2017-07-07T14:22:04 Log position: 3020 Event size: 23 Read bytes: 13 Table: test.t1 Affected columns: 3 Changed rows: 1 Affected columns: 3 Values: -- *html:None=>None *id:1=>2 *name:None=>None |
从上面结果可以看到，我们修改了id的值，但是在binlog中只记录了id字段的修改前后值, name、html这两个字段的值压根没有记录
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
HH@localhost 02:22:04 [test]>UPDATE t1 SET name = 'AA'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 === 解析binlog的输出 === Date: 2017-07-07T14:32:18 Log position: 3332 Event size: 22 Read bytes: 13 Table: test.t1 Affected columns: 3 Changed rows: 1 Affected columns: 3 Values: -- *html:None=>None *id:2=>None *name:None=>AA |
从上面可以看到我们修改了name字段的值，只记录下了主键id和name修改后的值。
noblob
在某些表中存在BLOB和TEXT字段，但是修改字段值的时候这两种值不会被记录。当然如果有修改这两种类型字段。修改的值还是会记录的。
实验过程:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
HH@localhost 02:41:26 [test]>SHOW SESSION VARIABLES LIKE '%binlog_row_image%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | binlog_row_image | NOBLOB | +------------------+--------+ 1 row in set (0.00 sec) HH@localhost 02:49:26 [test]>UPDATE t1 SET name = 'BB'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 === 解析binlog的输出 === Date: 2017-07-07T14:49:31 Log position: 3651 Event size: 29 Read bytes: 13 Table: test.t1 Affected columns: 3 Changed rows: 1 Affected columns: 3 Values: -- *html:None=>None *id:2=>2 *name:AA=>BB |
从上面我们可以看到我们修改了name字段除了html字段没有被记录外其他的字段会被全部记录
auto_increment_increment & auto_increment_offset
这两个参数其实算是大家比较熟悉的了。和自增ID有关的。一般会去设置这个值的会是业务上的需要。比如在双主 M1<-->M2 结构。并且需要支持双写，这样的情况就需要设置auto_increment_increment(步长)和auto_increment_offset了。
一般情况下两个实例直接的自增ID需要交叉着使用：
M1的自增ID为1, 3, 5, 7, 9M2的自增ID为2, 4, 6, 8, 10
注意：对于必要的系统，在主从切换或数据迁移的时候就需要特别注意这两个值是否相等。如果不相等那就很可能会出现数据同步的问题。
07/10/2017 下午 3:11 沙发
谢谢博主分享
17/07/2017 下午 8:34 板凳
具体分析吧
11/07/2017 下午 1:36 地板
。额
11/07/2017 上午 5:01 4楼
查询内容或信息图片等。