关于mysql:故障分析-库表名大小写不规范运维两行泪

作者:刘聪

爱可生华东交付服务部 DBA 成员,专职 MySQL 故障解决及相干技术支持。座右铭:好好学习,天天向上。

本文起源:原创投稿

*爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。


一、问题形容

客户须要做一套库的迁徙,因为库的数据量不大,40G左右,并且须要到近程机器下来做全量复原。所以第一工夫想到的天然是 mysqldump 工具来做。然而没想到会产生这种“惨案”。

mysqldump 备份失败,报错表不存在。

查看 MySQL 客户端去查看表信息以及表的物理文件包含环境信息(是否严格辨别大小写),整顿的景象如下:

  • mysqldump 报错:table doesn’t exist;
  • show tables 察看:db 存在,报错的 table 也存在;
  • select 查看表数据:报错 table doesn’t exist ;
  • 察看物理文件:db.opt 文件不存在;报错的 table,idb 文件不存在,仅有 frm 文件;
  • 查看 mysql-error.log :有 DROP database 的提醒,且记录了报错 table 的 frm 文件 was lost ;
  • MySQL 环境信息:lower_case_table_names = 1 。

二、剖析诊断

首先,咱们看一下 MySQL 官网提供的信息(官网链接:https://dev.mysql.com/doc/ref…)。

依据上面截图信息能够确定 Unix 平台上,lower_case_table_names 默认 = 0 ,是大小写敏感的。

从 mysqldump 报错所提醒的表名中蕴含了大写,不难推断出:是在 lower_case_table_names = 0 条件下,创立了该表,所以表名和物理文件名也都蕴含大写。而以后的 MySQL 环境是 lower_case_table_names = 1(也就是不管 sql 中是否明确了表名的大小写,均按小写去匹配),能够确定此环境变量有做过变更。

联合 mysql-error.log 的报错信息提醒,可能有 DROP database 动作执行过。且数据库目录中的 db.opt 文件缺失这点,更加加强了 DROP database 的可能性。

那么,咱们无妨做出如下猜测:

在 lower_case_table_names = 1 环境下,下发了 DROP database 操作。因为操作系统 Linux 是大小写敏感的,MySQL 应用小写字母去匹配须要删除的库表文件,而.frm文件名中蕴含了大写,无奈匹配,导致文件残留(mysql-error.log 此时记录,在删库过程中,无奈找到对应.frm文件)。

三、场景模仿

测试环境:

操作系统 CentOS Linux release 7.5.1804 (Core)
MySQL版本 5.7.25, for linux-glibc2.12 (x86_64)

为了证实第二节的猜测是否精确,设计如下试验,模仿复现 mysqldump 报错。

  • 在 lower_case_table_names=0 ,严格辨别大小写条件下创立测试库‘test_database’和测试表‘Test_table’;
  • 批改配置文件 lower_case_table_names=1 ,并重启 MySQL ;
  • 在 lower_case_table_names=1 条件下,模仿删除数据库:test_database ;
  • 查看物理文件信息以及 mysql-error.log 信息;
  • 应用 mysqldump 触发备份动作,复现报错。

通过以上试验,能够论证第二节的揣测是精确的,并总结如下论断:

  • 操作系统 Linux 是大小写敏感的,在 lower_case_table_names=0(默认值)条件下,库表的物理文件会明确辨别大小写;
  • 在 lower_case_table_names=1 条件下,MySQL 应用小写字母(不管 sql 语句里是否明确应用大写表名)去匹配须要删除的库表文件;
  • 在 lower_case_table_names=1 条件下,下发 Drop database 操作,因为表 frm 文件名蕴含大写,无奈匹配,因而残留,而 idb 文件不管大小写都会被删除。

四、报错解决方案

通过第三节的场景模仿能够揣测出,利用人员其实本意是要将库下的数据全副删除掉,然而因 MySQL 的环境因素,以及运维人员的操作不当,导致遗留下.frm文件未被清理掉。那么咱们能够间接跳过相干库的备份,从而绕过此报错。

五、运维倡议

运维中,不免有库表的迁徙和革新的需要,这时须要特地留神 lower_case_table_names 的值以及库、表名的大小写,稍不留神就报错库或者表不存在。对此,整顿如下两个场景以供大家运维参考。

场景1:将 MySQL 的环境变量 lower_case_table_names 从默认的 0 ,批改为1

  • 先将库名和表名转换为小写;
  • 编辑配置文件,增加配置:lower_case_table_names = 1 ;
  • 重启 MySQL 。

场景2:将大写的表名、库名标准改成小写的:

  • 表名革新:能够间接应用 RENAME TABLE 语句;
  • 库名革新:须要先应用 mysqldump ,将数据全副导出后,重建库名,再将数据导入进去。

致谢:感激爱可生DBA巨佬操盛春、晏泽的鼎力相助!

【腾讯云】轻量 2核2G4M,首年65元

阿里云限时活动-云数据库 RDS MySQL  1核2G配置 1.88/月 速抢

本文由乐趣区整理发布,转载请注明出处,谢谢。

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据