Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Example Analysis of non-datadir Directory of Table data Storage path in mysql

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.

Share To

Servers

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report