MYSQL 设置不同数据库在不同的磁盘目录上

MYSQL 设置不同数据库在不同的磁盘目录上

• 需求出发点
• 可查阅资料
• 采用方案
• ���结

  需求出发点

  在本地 win10 电脑上，已安装一个 mysql 服务，且已在使用中，现有数据库 database1、database2、database3，一般来说在配置文件中只设置有一个 datadir 参数，所有数据库均存放在这个目录下，假设 datadir = D:\data，现在需要将 database3 移动到 E:\data 存放，且在连接到 mysql 服务时，依旧可以同时访问到 database1、database2、database3

  可查阅资料

  截至发文时间，网上能查到的类似需求文章，亦或是询问 GPT 工具，大致可汇总一以下几种解决方案：

  1、在配置文件中设置两个 datadir，大致如下图。

  失败：重启后实际上只能识别到最下面的参数，即db2的路径，且db2文件夹中需要复制db1同款参数文件才能启动成功（ibdata等文件）。没有达到预期效果，本质上就是换了个数据目录。

  2、在配置文件中设置两个并行的 datadir ，大致如下图。

  失败：服务启动后自动停止。怀疑这种语法的有效性。

  3、查阅到 mysql 有表分区技术，但这个技术是将一个表按某规则拆分为不同部分，按逻辑关系存储在不同目录上，但我需要的是整个数据表存在另一个地方，与需求略有不符。参考链接：

  MySql分表、分库、分片和分区知识

  Mysql数据库表分区跨磁盘存储 表分区放到不同的磁盘 表分区存储到指定磁盘路径

  ---

  采用方案

  最后查到这么一篇帖子：mysql-data-on-multiple-drives，符合我的需求，帖子中给出的主要观点是：

  1、早期版本的mysql中（myisam版本），可以使用 symbolic link 链接到另外一个磁盘的数据，但是这么做在某些场景下存在弊端

  2、使用 create table 语句利用参数 data directory指定表建立在哪个磁盘上，具体可以参考官方文档：官方说明文档

  大致操作步骤如下：

  （1）停掉 mysql 服务

  （2）配置文件中设置 innodb_file_per_table 参数为1

  （3）配置文件中设置 innodb_directories='F:/new_data'，即 datadir 是主数据目录，这个是新的一个数据目录，并手动在对应路径下创建好这个空的目录

  原文：

  If the directory you want to use is unknown, add it to the innodb_directories setting before you create the table. The innodb_directories variable is read-only. Configuring it requires restarting the server.

  （4）启动 mysql 服务

  顺利的话可以成功启动。随便进入一个数据库下，执行以下测试语句

  CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = 'F:/new_data';

  如果成功，则可以在新文件夹 F:/new_data 下看到新建的 t.idb，并且在例如 navicat 的客户端中可以看到原本的表文件和新建的 t 表

  接下来就是怎么转移现有数据表到新目录下。直观上，似乎可以：

  CREATE TABLE t1 AS select * from old_table DATA DIRECTORY = 'F:/new_data';

  但是实测是不行的，会报语法错误，那如果把 create table 换成 alter table 呢，根据官方文件描述，DATA DIRECTORY 参数只在 create table 时有用，在 alter table 中虽然有这个参数但是会被忽略掉，原文：

  The DATA DIRECTORY clause, which is supported with CREATE TABLE ... TABLESPACE, is not supported with ALTER TABLE ... TABLESPACE, and is ignored if specified. 原文链接(页面中搜索 data directory)

  所以只能稍微麻烦一下，先创建表结构，再将原表数据插入到新表中，大致就是：

  （1）create table new_table () DATA DIRECTORY = 'F:/new_data';

  （2）insert into new_table () select from old_table;

  （3）等待完成

  至此就可以完成需要的新表创建，如果需要整个库里面的表都转移，似乎需要每个表都这么操作一遍？但我个人而言只需要把最频繁IO的表移过去即可，所以还算是基本解决了我的需求。

  操作完成后发现的一些细节：

  1、同样的表，打开后确定数据一致，表中数据量也一致，但是新旧目录下的 .ibd 文件的大小不一样

  2、虽然可以在连接到服务时同时访问到两个目录下的数据，但在 show table status; 的查询下，新目录的文件读取不到准确信息。如下图 0_trade_info 是旧表， t 是转移后的新表，t 表的行数、数据长度等均不准确

  总结

  目前虽然找到方法解决了需求，但是操作上还是存在繁琐之处，且未知在长期的使用上是否会在某些细节处存在缺陷。在查阅相关方法的过程中，我始终感觉 mysql 是可以更轻松的完成多磁盘分区存储的，但又奈何没有找到具体可行的操作方法。是否在 mysql 服务初始化的时候有更方便的办法也未可知，因为我无法舍弃现有数据重新初始化 mysql，所以未做尝试。如果确实有更简单易懂的方法，欢迎交流！