MySQL DDL操作--------指定表資料檔案存放目錄最佳實戰

標籤：mysql dba   data directory   

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 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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引擎指定表資料檔案存放目錄例子

   * 建立表資料檔案存放目錄

[[email protected] ~]# mkdir -v /test_myisammkdir: created directory `/test_myisam‘

　 * 查看mysqld 運行使用者

[[email protected] ~]# ps aux | grep mysqld | grep -v greproot       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.pidmysql      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運行使用者 [ 此步必須 ]

[[email protected] ~]# 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/mytesttotal 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.frmlrwxrwxrwx 1 mysql mysql   28 Jul  5 16:37 test_myisam.MYD -> /test_myisam/test_myisam.MYDlrwxrwxrwx 1 mysql mysql   28 Jul  5 16:37 test_myisam.MYI -> /test_myisam/test_myisam.MYImysql> system ls -l /test_myisamtotal 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引擎指定表資料檔案存放目錄例子

　 * 建立表資料檔案存放目錄

[[email protected] ~]# mkdir -v /test_innodbmkdir: created directory `/test_innodb‘

   * 查看mysqld 運行使用者

[[email protected] ~]# ps aux | grep mysqld | grep -v greproot       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.pidmysql      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運行使用者 [ 此步必須 ]

[[email protected] ~]# chown -v mysql.mysql /test_innodbchanged 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中會產生<table_name>.isl檔案，此檔案記錄表資料檔案存放絕對路徑 ]

mysql> system ls -l /test_innodbtotal 4drwxr-x--- 2 mysql mysql 4096 Jul  5 16:47 mytestmysql> system ls -l /test_innodb/mytesttotal 96-rw-r----- 1 mysql mysql 98304 Jul  5 16:47 test_innodb.ibdmysql> select database();+------------+| database() |+------------+| mytest     |+------------+1 row in set (0.00 sec)mysql> system ls -l /data/mysql_data/mytesttotal 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.islmysql> system cat /data/mysql_data/mytest/test_innodb.isl/test_innodb/mytest/test_innodb.ibd

5. 總結

以需求驅動技術，技術本身沒有優略之分，只有業務之分。

本文出自 “sea” 部落格，請務必保留此出處http://lisea.blog.51cto.com/5491873/1945444

MySQL DDL操作--------指定表資料檔案存放目錄最佳實戰