起因
最近迁移了服务器,运维同学给导了数据库,数据库也跟着一起迁移到了新机房。今天,有用户发现自己创建的一条记录不见了,我去新库查询确实没有,去旧库查询到了这条记录,确定了是数据丢失。
排查过程
起初,怀疑迁移到新库已经有几天了,是否是这几天用户手动删除了这条记录?还好对用户的改动做了记录,经过查询发现,用户并没有删除动作。
所以,大概率是数据库迁移引起的数据库丢失,具体是哪个环节出错,还要继续排查。
运维同学给出了当时从老库里导出的sql,发现是存在丢失的这条记录的,这证明了从老库导出没有问题,问题出现在往新库导入的时候。
找到了这条丢失记录的导入sql,由于有一个字段用了 longtext 类型,这个字段内存入了大量的json数据,将这条sql保存成txt后,竟然有1.7M大小。把这条语句通过命令行单独导入,报错 MySQL server has gone away
。
问题
通过上面的排查,基本上可以确定是由于sql语句过大,导致插入失败,引起迁移时数据丢失的问题。
解决
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 导得数据,如果数据迁移时能有一个失败日志,那么就能及时发现这个问题。另外,迁移库后,如果我立即比较一下表的记录数,或许也能早点发现这个问题。
[参考]
MySQL server has gone away 问题的解决方法
来自 https://mr-houzi.github.io/20...
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。