리눅스, 자료실, 성경검색, 추억의게임, 고전게임, 오락실게임, rootman, http://www.rootman.co.kr
* 54.221.9.6 *
| Home | Profile | Linux | 자료실 | zabbix | Mysql 5.6 | 갤러리 | 성경검색 | 해피니스 | 자유게시판 | 게시물검색 | L | O | R |    

 
[mysql] [mysql] Creating a File-Per-Table Tablespace Outside the Data Directory
 작성자 : rootman
Date : 2015-08-03 23:59  |  Hit : 3,739  
 
To create a new InnoDB file-per-table tablespace in a specific location outside the MySQL data directory, use the DATA DIRECTORY = absolute_path_to_directory clause of the CREATE TABLE statement.
Plan the location in advance, because you cannot use the DATA DIRECTORY clause with the ALTER TABLE statement. The directory you specify could be on another storage device with particular performance or capacity characteristics, such as a fast SSD or a high-capacity HDD.
Within the destination directory, MySQL creates a subdirectory corresponding to the database name, and within that a .ibd file for the new table. In the database directory beneath the MySQL DATADIR directory, MySQL creates a table_name.isl file containing the path name for the table. The .isl file is treated by MySQL like a symbolic link. (Using actual symbolic links has never been supported for InnoDB tables.)
The following example demonstrates creating a file-per-table tablespace outside the MySQL data directory. It shows the .ibd created in the specified directory, and the .isl created in the database directory beneath the MySQL data directory.
mysql> USE test;
Database changed

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/alternative/directory';
Query OK, 0 rows affected (0.03 sec)

# MySQL creates a .ibd file for the new table in a subdirectory that corresponding  
# to the database name

[email protected]:~/alternative/directory/test$ ls
t1.ibd

# MySQL creates a .isl file containing the path name for the table in a directory 
# beneath the MySQL data directory

[email protected]:~/mysql/data/test$ ls
db.opt  t1.frm  t1.isl

Usage Notes:

  • MySQL initially holds the .ibd file open, preventing you from dismounting the device, but might eventually close the table if the server is busy. Be careful not to accidentally dismount an external device while MySQL is running, or to start MySQL while the device is disconnected. Attempting to access a table when the associated .ibd file is missing causes a serious error that requires a server restart.
    A server restart might fail if the .ibd file is still not at the expected path. In this case, manually remove the table_name.isl file in the database directory, and after restarting perform a DROP TABLE to delete the .frm file and remove the information about the table from the data dictionary.
  • Do not put MySQL tables on an NFS-mounted volume. NFS uses a message-passing protocol to write to files, which could cause data inconsistency if network messages are lost or received out of order.
  • If you use an LVM snapshot, file copy, or other file-based mechanism to back up the .ibd file, always use the FLUSH TABLES ... FOR EXPORT statement first to make sure all changes that were buffered in memory are flushed to disk before the backup occurs.
  • The DATA DIRECTORY clause is a supported alternative to using symbolic links, which has always been problematic and was never supported for individual InnoDB tables.
 

 
 

Total. 645
번호 분류 제목 작성자 등록일 조회수
645 기초강좌 NFS server unable to open connection to tcp6/udp6 networks rootman 09-27 2322
644 php [php] mssql ms-sql 한글 변환 끄적임.. 정리 rootman 03-29 3353
643 기초강좌 java vim 에서 자바 환경 만들기 rootman 02-16 2517
642 삭제예정 [xencenter] xenserver tool 설치 rootman 08-24 3560
641 mysql [mysql] Creating a File-Per-Table Tablespace Outside the Dat… rootman 08-03 3740
640 기초강좌 Centos Xwindow 설치 rootman 06-16 4599
639 php [php] highlight 함수 rootman 04-01 4485
638 qmail [qmail] qmail-scanner에 filtering 된 이메일 처리 rootman 02-14 4864
637 Shell [날짜] awk를 이용하여 unixtime의 시간 차 구하기 rootman 01-27 5790
636 기초강좌 centos에 그놈(gnome) 설치하기 rootman 01-10 5739
635 쉘(awk) [awk] shell 변수 사용하기 rootman 12-27 5375
634 기초강좌 서버 캐시 메모리 초기화하기 (drop_caches) rootman 12-11 6401
633 windows tips 그리드 제거 배치 스크립트 rootman 11-28 6000
632 기초강좌 패스워드 lockgin control rootman 09-06 4
631 windows tips win7 자동 로그인 설정하기 rootman 08-18 5991
 1  2  3  4  5  6  7  8  9  10    
AND OR