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'

测试如下:

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)

28
 )

 

设置成: 1

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

测试如下:

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)

29
 )

 

sql_mode

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

  1. 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

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

实验过程:

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

16
 1

 

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

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
2
 1

 

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

无 STRICT_TRANS_TABLES

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

实验过程:

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)

26
 )

 

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

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

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)

18
 )



 

从上面实验我们可以看到向 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 的方式的有三种选项:

  1. full
  2. minimal
  3. noblob

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

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

full

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

实验过程:

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

43
 CC

 

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

minimal

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

实验过程:

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

26
 None

 

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

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

18
 AA

 

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

noblob

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

实验过程:

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

26
 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, 9
  • M2 的自增ID为2, 4, 6, 8, 10

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


数据运维技术 » 在数据库主从切换或数据迁移需要注意的几个参数
分享到：

相关推荐