MySQL中指定表數(shù)據(jù)文件實(shí)現(xiàn)存放目錄的最佳方法

不知道大家之前對類似MySQL中指定表數(shù)據(jù)文件實(shí)現(xiàn)存放目錄的最佳方法的文章有無了解，今天我在這里給大家再簡單的講講。感興趣的話就一起來看看正文部分吧，相信看完MySQL中指定表數(shù)據(jù)文件實(shí)現(xiàn)存放目錄的最佳方法你一定會(huì)有所收獲的。

1. 背景

   * 在MYSQL中建立一張數(shù)據(jù)表，在其數(shù)據(jù)目錄對應(yīng)的數(shù)據(jù)庫目錄下都有對應(yīng)表的.frm文件,.frm文件是用來保存每個(gè)數(shù)據(jù)表的元數(shù)據(jù)(meta)信息，包括表結(jié)構(gòu)的定義等，.frm文件跟數(shù)據(jù)庫存儲引擎無關(guān)，也就是任何存儲引擎的數(shù)據(jù)表都必須有.frm文件，命名方式為數(shù)據(jù)表名.frm，如user.frm. .frm文件可以用來在數(shù)據(jù)庫崩潰時(shí)恢復(fù)表結(jié)構(gòu)。

   * MySQL文件包括MySQL所建數(shù)據(jù)庫文件和MySQL所用引擎創(chuàng)建的數(shù)據(jù)庫文件。

   * .frm 文件與操作系統(tǒng)和數(shù)據(jù)庫引擎無關(guān)，都有這么個(gè)與表名同名文件。

   * MyISAM引擎的文件：

     * .myd 即 my data，表數(shù)據(jù)文件

     * .myi 即my index，索引文件

     * .log 日志文件。

   * InnoDB引擎的文件：

     * 采用表空間（tablespace）來管理數(shù)據(jù)，存儲表數(shù)據(jù)和索引，

     * InnoDB數(shù)據(jù)庫文件（即InnoDB文件集，ib-file set)。

     * ibdata1、ibdata2等：系統(tǒng)表空間文件，當(dāng)未開啟innodb_file_per_table時(shí)，存儲InnoDB系統(tǒng)信息和用戶數(shù)據(jù)庫表數(shù)據(jù)和索引，所有表共用。

     * .ibd文件：當(dāng)開啟innodb_file_per_table時(shí)，單表表空間文件，每個(gè)表使用一個(gè)表空間文件（file per table），存放用戶數(shù)據(jù)庫表數(shù)據(jù)和索引。

     * 日志文件： ib_logfile1、ib_logfile2。

   * 指定的目錄必須是目錄的完整路徑名，而不是相對路徑。

2. MySQL 環(huán)境

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)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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引擎指定表數(shù)據(jù)文件存放目錄例子

   * 創(chuàng)建表數(shù)據(jù)文件存放目錄

[root@MySQL ~]# mkdir -v /test_myisam
mkdir: created directory `/test_myisam'

　 * 查看mysqld 運(yùn)行用戶

[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運(yùn)行用戶 [ 此步必須 ]

[root@MySQL ~]# chown -v  mysql.mysql /test_myisam 
changed ownership of `/test_myisam' to mysql:mysql

   * 創(chuàng)建表 test_myisam, 并指定數(shù)據(jù)文件與索引文件存放目錄 

     [ MyISAM引擎中表數(shù)據(jù)文件與索引文件分開存放,，需要分別指定 ]

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;

   * 查看表數(shù)據(jù)文件與表結(jié)構(gòu)文件

　　 [ 可以看到在MyISAM存儲引擎中，指定表數(shù)據(jù)目錄是通過軟鏈接方式實(shí)現(xiàn) ]

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引擎指定表數(shù)據(jù)文件存放目錄例子

　 * 創(chuàng)建表數(shù)據(jù)文件存放目錄

[root@MySQL ~]# mkdir -v /test_innodb
mkdir: created directory `/test_innodb'

   * 查看mysqld 運(yùn)行用戶

[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運(yùn)行用戶 [ 此步必須 ]

[root@MySQL ~]# chown -v mysql.mysql /test_innodb
changed ownership of `/test_innodb' to mysql:mysql

   * 創(chuàng)建表 test_innodb, 并指定數(shù)據(jù)文件存放目錄 

     [ InnoDB 引擎中表數(shù)據(jù)文件與索引文件合并存放,，指定數(shù)據(jù)文件存放目錄即可 ]

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)

    * 查看表數(shù)據(jù)文件

　　　[ 可以看到在InnoDB中會(huì)生成<table_name>.isl文件，此文件記錄表數(shù)據(jù)文件存放絕對路徑 ]

mysql> system ls -l /test_innodb
total 4
drwxr-x--- 2 mysql mysql 4096 Jul  5 16:47 mytest
mysql> system ls -l /test_innodb/mytest
total 96
-rw-r----- 1 mysql mysql 98304 Jul  5 16:47 test_innodb.ibd

mysql> select database();
+------------+
| database() |
+------------+
| mytest     |
+------------+
1 row in set (0.00 sec)

mysql> system ls -l /data/mysql_data/mytest
total 20
-rw-r----- 1 mysql mysql   67 Jul  5 16:30 db.opt
-rw-r----- 1 mysql mysql 8586 Jul  5 16:47 test_innodb.frm
-rw-r----- 1 mysql mysql   35 Jul  5 16:47 test_innodb.isl
mysql> system cat /data/mysql_data/mytest/test_innodb.isl

/test_innodb/mytest/test_innodb.ibd

5. 總結(jié)

以需求驅(qū)動(dòng)技術(shù)，技術(shù)本身沒有優(yōu)略之分，只有業(yè)務(wù)之分。

看完MySQL中指定表數(shù)據(jù)文件實(shí)現(xiàn)存放目錄的最佳方法這篇文章，大家覺得怎么樣？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。