【MVS】MySQL创建指定目录下表空间报错ERROR 3121 (HY000):

## 创建表空间报错

mysql> CREATE TABLESPACE my_general_tbs16k

    ->     ADD DATAFILE '/data/dbfile/my_general_tbs16k1.ibd'

    ->     FILE_BLOCK_SIZE = 16384

    ->     ENGINE = InnoDB;

ERROR 3121 (HY000): The DATAFILE location must be in a known directory.

## 创建表空间报错，是因为没有在参数文件中设置可信目录。

mysql> CREATE TABLESPACE my_general_tbs16k

    ->     ADD DATAFILE '/data/dbfile/my_general_tbs16k1.ibd'

    ->     FILE_BLOCK_SIZE = 16384

    ->     ENGINE = InnoDB;

ERROR 3121 (HY000): The DATAFILE location must be in a known directory.

如需创建mysql指定目录表，则需在参数中设置目录，或启动时临时设置。

参数文件永久设置：MySQL option file:

[mysqld]

innodb_directories="directory_path_1;directory_path_2"

注意：建议关闭实例设置参数，后启动校验。

或在启动临时设置：Startup command:

mysqld --innodb-directories="directory_path_1;directory_path_2"

### 在配置文件中修改参数，后重启实例，查看参数配置情况

mysql> show variables like '%innodb_directories%';

+--------------------+--------------+

| Variable_name      | Value        |

+--------------------+--------------+

| innodb_directories | /data/dbfile |

+--------------------+--------------+

1 row in set (0.01 sec)

 

mysql>

##创建指定目录表空间

CREATE TABLESPACE my_general_tbs16k

ADD DATAFILE '/data/dbfile/my_general_tbs16k1.ibd'

FILE_BLOCK_SIZE = 16384

ENGINE = InnoDB;

 

# 查询表空间情况

mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES;

+--------------------------+-------------------------------------+

| TABLESPACE_NAME          | FILE_NAME                           |

+--------------------------+-------------------------------------+

| abc/my_independent_table | ./abc/my_independent_table.ibd      |

| abc/t1                   | ./abc/t1.ibd                        |

| employees/departments    | ./employees/departments.ibd         |

| employees/dept_emp       | ./employees/dept_emp.ibd            |

| employees/dept_manager   | ./employees/dept_manager.ibd        |

| employees/employees      | ./employees/employees.ibd           |

| employees/salaries#p#p01 | ./employees/salaries#p#p01.ibd      |

| innodb_undo_001          | ./undo_001                          |

| innodb_undo_002          | ./undo_002                          |

| my_general_space         | ./my_general_space.ibd              |

| my_general_tbs16k        | /data/dbfile/my_general_tbs16k1.ibd |

| mysql                    | ./mysql.ibd                         |

| mysql/backup_history     | ./mysql/backup_history.ibd          |

| mysql/backup_progress    | ./mysql/backup_progress.ibd         |

| sys/sys_config           | ./sys/sys_config.ibd                |

+--------------------------+-------------------------------------+

54 rows in set (0.00 sec)

#创建表并插入数据测试

mysql> CREATE TABLE tab1_gtbs (c1 INT PRIMARY KEY) TABLESPACE my_general_tbs16k;

mysql> insert into tab1_gtbs ('1');

mysql> INSERT INTO tab1_gtbs (c1) VALUES (1);

mysql> commit;

mysql> select * from tab1_gtbs;

+----+

| c1 |

+----+

|  1 |

+----+

1 row in set (0.01 sec)

 

备注：

innodb 表空间不能设置最大大小，是由文件系统决定的。