In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-20 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >
Share
Shulou(Shulou.com)06/01 Report--
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.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.