Example Analysis of non-datadir Directory of Table data Storage path in mysql
mysql table data storage path non-datadir directory example analysis, for this problem, this article details the corresponding analysis and solution, hoping to help more want to solve this problem of small partners to find a simpler and easier way.
Suppose you create a new table and store it in a path other than the default/path/to/datadir/dbname. But what should be done with the specified storage location?
method one
shell> mkdir /Generalt1
shell> chown mysql.mysql /Generalt1
mysql> create table test_ger1 (a int) data directory='/Generalt1';
Query OK, 0 rows affected (0.15 sec)
shell> cd /Generalt1
shell> ll test_ger1* #in the test directory of datadir
-rw-r-----. 1 mysql mysql 8554 Jan 3 16:41 test_ger1.frm
-rw-r-----. 1 mysql mysql 36 Jan 3 16:41 test_ger1.isl #This is the link file, link to the ibd file above
shell> cat test_ger1.isl #A text file containing the path to the idb file
/Generalt1/test/test_ger1.ibd
methodology II
After mysql 5.7, you can use `universal tablespaces`
Grammar:
CREATE TABLESPACE tablespace_name ADD DATAFILE 'file_name' [FILE_BLOCK_SIZE = value] [ENGINE [=] engine_name]
-- 1: Create a common table space
mysql> create tablespace ger_space add datafile '/Generalt1/ger_space.ibd' file_block_size=8192;
Query OK, 0 rows affected (0.07 sec)
-- datafile After specifying the storage path, an isl file will be generated under datadir. The content of this file is the path of the ibd file of General Space.
--If the datafile does not specify a path, the ibd file is stored in the datadir directory by default, and the isl file is not required.
mysql> create tablespace ger_space2 add datafile 'ger_space2.ibd' file_block_size=8192;
Query OK, 0 rows affected (0.06 sec)
shell> ll ger*
-rw-r-----. 1 mysql mysql 32768 Jan 3 16:51 ger_space2.ibd #path not specified, stored in datadir directory
-rw-r-----. 1 mysql mysql 26 Jan 3 16:50 ger_space.isl #Other path specified, isl link file exists
shell> cat ger_space.isl
/Generalt1/ger_space.ibd #Path to the actual ibd file
mysql> select * from information_schema.innodb_sys_tablespaces where name='ger_space'\G
*************************** 1. row ***************************
SPACE: 96
NAME: ger_space
FLAG: 2304
FILE_FORMAT: Any
ROW_FORMAT: Any
PAGE_SIZE: 8192 -- page_size is 8k
ZIP_PAGE_SIZE: 0
SPACE_TYPE: General -- General Type
FS_BLOCK_SIZE: 0
FILE_SIZE: 18446744073709551615
ALLOCATED_SIZE: 2
COMPRESSION: None
1 row in set (0.00 sec)
-- 2: Create table
mysql> create table test_ger2 (a int) tablespace=ger_space;
Query OK, 0 rows affected (0.11 sec)
shell> ll test_ger* #in the test directory of datadir
-rw-r-----. 1 mysql mysql 8554 Jan 3 16:41 test_ger1.frm
-rw-r-----. 1 mysql mysql 36 Jan 3 16:41 test_ger1.isl
-rw-r-----. 1 mysql mysql 8554 Jan 3 17:09 test_ger2.frm #Only one frm file
shell> ll /Generalt1/
total 52
drwxr-x---. 2 mysql mysql 4096 Jan 3 16:41 test
-rw-r-----. 1 mysql mysql 49152 Jan 3 17:09 ger_space.ibd # test_ger2 ibd file is actually stored in ger_space.ibd universal table space
mysql> create table test_ger3 (a int) tablespace=ger_space; -- test_ger3 is also stored in ger_space.ibd
Query OK, 0 rows affected (0.09 sec)
About mysql table data storage path non-datadir directory example analysis questions to share here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.
