mysql 指定表数据位置_MySQL DDL操作--------指定表数据文件存放目录最佳实战

最新推荐文章于 2024-06-11 09:18:59 发布

那些都不懂

最新推荐文章于 2024-06-11 09:18:59 发布

阅读量461

点赞数

文章标签： mysql 指定表数据位置

本文链接：https://blog.csdn.net/weixin_35851508/article/details/113229082

版权

本文详细介绍了如何在MySQL中指定MyISAM和InnoDB引擎的表数据文件存放目录，包括创建目录、修改权限、创建表并指定数据文件位置。通过示例展示了在MyISAM中表数据文件和索引文件分开存放，而在InnoDB中合并存放的情况。

摘要由CSDN通过智能技术生成

1. 背景

* 在MYSQL中建立一张数据表，在其数据目录对应的数据库目录下都有对应表的.frm文件,.frm文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，.frm文件跟数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，命名方式为数据表名.frm，如user.frm. .frm文件可以用来在数据库崩溃时恢复表结构。

* MySQL文件包括MySQL所建数据库文件和MySQL所用引擎创建的数据库文件。

* .frm 文件与操作系统和数据库引擎无关，都有这么个与表名同名文件。

* MyISAM引擎的文件：

* .myd 即 my data，表数据文件

* .myi 即my index，索引文件

* .log 日志文件。

* InnoDB引擎的文件：

* 采用表空间(tablespace)来管理数据，存储表数据和索引，

* InnoDB数据库文件(即InnoDB文件集，ib-file set)。

* ibdata1、ibdata2等：系统表空间文件，当未开启innodb_file_per_table时，存储InnoDB系统信息和用户数据库表数据和索引，所有表共用。

* .ibd文件：当开启innodb_file_per_table时，单表表空间文件，每个表使用一个表空间文件(file per table)，存放用户数据库表数据和索引。

* 日志文件： ib_logfile1、ib_logfile2。

* 指定的目录必须是目录的完整路径名，而不是相对路径。

2. MySQL 环境Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.18 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'version';

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

| Variable_name | Value |

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

| version | 5.7.18 |

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

1 row in set (0.01 sec)

mysql> show variables like 'datadir';

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

| Variable_name | Value |

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

| datadir | /data/mysql_data/ |

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

1 row in set (0.04 sec)

mysql> show variables like 'innodb_file_per%';

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

| Variable_name | Value |

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

| innodb_file_per_table | ON |

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

1 row in set (0.02 sec)

3. MyISAM引擎指定表数据文件存放目录例子

* 创建表数据文件存放目录[root@MySQL ~]# mkdir -v /test_myisam

mkdir: created directory `/test_myisam'

* 查看mysqld 运行用户[root@MySQL ~]# ps aux | grep mysqld | grep -v grep

root 1468 0.0 0.0 110400 1532 ? S 16:00 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql_data --pid-file=/data/mysql_data/MySQL.pid

mysql 1614 0.2 4.9 1309380 194788 ? Sl 16:00 0:04 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql_data/error.log --pid-file=/data/mysql_data/MySQL.pid

* 修改目录所属用户与组为mysql运行用户 [ 此步必须 ][root@MySQL ~]# chown -v mysql.mysql /test_myisam

changed ownership of `/test_myisam' to mysql:mysql

* 创建表 test_myisam, 并指定数据文件与索引文件存放目录

[ MyISAM引擎中表数据文件与索引文件分开存放,，需要分别指定 ]CREATE TABLE test_myisam(

-> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,

-> name VARCHAR(64) NOT NULL

-> )ENGINE=MYISAM DATA DIRECTORY='/test_myisam' INDEX DIRECTORY='/test_myisam' DEFAULT CHARSET=utf8mb4;

* 查看表数据文件与表结构文件

[ 可以看到在MyISAM存储引擎中，指定表数据目录是通过软链接方式实现 ]mysql> select database();

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

| database() |

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

| mytest |

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

1 row in set (0.00 sec)

mysql> system ls -l /data/mysql_data/mytest

total 16

-rw-r----- 1 mysql mysql 67 Jul 5 16:30 db.opt

-rw-r----- 1 mysql mysql 8586 Jul 5 16:37 test_myisam.frm

lrwxrwxrwx 1 mysql mysql 28 Jul 5 16:37 test_myisam.MYD -> /test_myisam/test_myisam.MYD

lrwxrwxrwx 1 mysql mysql 28 Jul 5 16:37 test_myisam.MYI -> /test_myisam/test_myisam.MYI

mysql> system ls -l /test_myisam

total 4

-rw-r----- 1 mysql mysql 0 Jul 5 16:37 test_myisam.MYD

-rw-r----- 1 mysql mysql 1024 Jul 5 16:37 test_myisam.MYI

4. InnoDB引擎指定表数据文件存放目录例子

* 创建表数据文件存放目录[root@MySQL ~]# mkdir -v /test_innodb

mkdir: created directory `/test_innodb'

* 查看mysqld 运行用户[root@MySQL ~]# ps aux | grep mysqld | grep -v grep

root 1468 0.0 0.0 110400 1532 ? S 16:00 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql_data --pid-file=/data/mysql_data/MySQL.pid

mysql 1614 0.1 5.0 1309380 196576 ? Sl 16:00 0:04 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql_data/error.log --pid-file=/data/mysql_data/MySQL.pid

* 修改目录所属用户与组为mysql运行用户 [ 此步必须 ][root@MySQL ~]# chown -v mysql.mysql /test_innodb

changed ownership of `/test_innodb' to mysql:mysql

* 创建表 test_innodb, 并指定数据文件存放目录

[ InnoDB 引擎中表数据文件与索引文件合并存放,，指定数据文件存放目录即可 ]mysql> CREATE TABLE test_innodb(

-> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,

-> name VARCHAR(64) NOT NULL

-> )ENGINE=INNODB DATA DIRECTORY='/test_innodb' DEFAULT CHARSET=utf8mb4;

Query OK, 0 rows affected (0.06 sec)

* 查看表数据文件

[ 可以看到在InnoDB中会生成.isl文件，此文件记录表数据文件存放绝对路径 ]mysql> system ls -l /test_innodb

total 4

drwxr-x--- 2 mysql mysql 4096 Jul 5 16:47 mytest