MySQL迁移丢失数据的问题的可能因素

1.sql语句过大，导致插入失败，引起迁移时数据丢失的问题

迁移数据时发现数据丢失现象，排查后找到了丢失的数据，检查数据时发现有一个字段用了 longtext 类型，这个字段内存入了大量的json数据，将这条sql保存成txt后，竟然有1.7M大小。把这条语句通过命令行单独导入，报错 MySQL server has gone away

MySQL 中有一个 max_allowed_packet 参数，用来控制一次插入语句的大小，像Blob、longtext类型的字段很容易导致sql语句过长，而达到 max_allowed_packet的限制

max_allowed_packet 当前大小是 1048576 （1024 X 1024 X 1），也就是 1M 大小，而我那条语句竟然达到了1.7M ，显然超过了上限。

mysql> show global variables like 'max_allowed_packet';

+--------------------+---------+

| Variable_name      | Value   |

+--------------------+---------+

| max_allowed_packet | 1048576 |

+--------------------+---------+

1 row in set (0.01 sec)
调大 max_allowed_packet 值

mysql> set global max_allowed_packet=1024*1024*16;

Query OK, 0 rows affected (0.00 sec)

对比新老两库后，发现了总共丢失了两条记录，在调大 max_allowed_packet 值后，分别重新插入，恢复了丢失的数据。

反思

经历了这次事件后的几个小反思：

1. Text 字段是个坑。

Text对数据库性能就已经有明显影响了。更何况了是LongText。LongText 最大能存 (2^32 -1)个字节，即 4GiB。使用LongText字段便是给自己和运维同学留下了一个坑。由于这个字段存储的是大文本 json ，日后可以考虑将此字段放入 MongoDB 。

• TEXT | 65,535（2 16 -1）个字节= 64个KiB
• MEDIUMTEXT | 16,777,215（2 24 -1）字节= 16 MiB
• LONGTEXT | 4,294,967,295（2 32 -1）个字节= 4个GiB

2. 数据迁移失败日志。

运维同学说使用 mysqldump 导得数据，如果数据迁移时能有一个失败日志，那么就能及时发现这个问题。另外，迁移库后，如果我立即比较一下表的记录数，或许也能早点发现这个问题。