MySQL Tablespace Configuration on Linux
MySQL uses tablespaces to store and manage data efficiently. A tablespace is a storage structure that allows better organization. This guide provides a step-by-step explanation of how to configure MySQL tablespaces on a Linux system.
Steps to Configure Tablespace in MySQL
1) Log in to MySQL
Use the root user and enter your MySQL root password to proceed.
[root@RHEL-OEL23 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 9.2.0-commercial MySQL Enterprise Server - Commercial
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2) Create a New Database
This command create and select a new database named discover.
mysql> create database discover;
Query OK, 1 row affected (0.00 sec)
mysql> use discover
Database changed
3) Create a Table
By default, when you create a table, MySQL assigns it to the general tablespace of the InnoDB storage engine. Running SHOW CREATE TABLE confirms the table structure and its assigned tablespace.
mysql> create table test1 (id INT);
Query OK, 0 rows affected (0.03 sec)
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> create tablespace test_tbs add datafile 'test_tbs.ibd';
Query OK, 0 rows affected (0.01 sec)
4) Create Tablespace
This command creates a custom tablespace named test_tbs, and the data for the tables stored in this tablespace will be written to test_tbs.ibd.
mysql> create tablespace test_tbs add datafile 'test_tbs.ibd';
Query OK, 0 rows affected (0.01 sec)
5) Verify Tablespace File Location
After creating the tablespace, check the directory where MySQL stores tablespace files: This command lists files in the MySQL directory, where you should see test_tbs.ibd. This confirms that the tablespace was successfully created and assigned to a physical file.
ls -lrt /var/lib/mysql/6) Create a Table Using the New Tablespace
This ensures that the table test2 is explicitly assigned to the newly created test_tbs tablespace. Running SHOW CREATE TABLE confirms the assignment
mysql> create table test2 (number INT) tablespace=test_tbs;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test2\G
*************************** 1. row ***************************
Table: test2
Create Table: CREATE TABLE `test2` (
`number` int DEFAULT NULL
) /*!50100 TABLESPACE `test_tbs` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
7) Configure a Custom Data Directory for Tablespaces
a) Create a Custom Directory
This creates a new directory /tablespace where custom tablespaces will be stored and assigns ownership to the MySQL user to ensure proper access control
[root@RHEL-OEL23 ~]# mkdir /tablespace
[root@RHEL-OEL23 ~]# chown mysql:mysql /tablespace/
b) Modify MySQL Configuration
Edit the MySQL configuration file /etc/my.cnf to specify the custom tablespace directory:
[root@RHEL-OEL23 ~]# vi /etc/my.cnf
[root@RHEL-OEL23 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/9.2/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
innodb_directories="/tablespace"
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/lib/mysql/mysql.sock
c) Restart MySQL Server
To apply the configuration changes, restart MySQL:
[root@RHEL-OEL23 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
Active: active (running) since Wed 2025-03-05 00:21:31 EST; 4h 15min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 1352 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 2190 (mysqld)
Status: "Server is operational"
Tasks: 36 (limit: 203176)
Memory: 540.8M
CPU: 24.642s
CGroup: /system.slice/mysqld.service
└─2190 /usr/sbin/mysqld
Mar 05 00:21:29 RHEL-OEL23 systemd[1]: Starting MySQL Server...
Mar 05 00:21:31 RHEL-OEL23 systemd[1]: Started MySQL Server.
[root@RHEL-OEL23 ~]# systemctl restart mysqld
[root@RHEL-OEL23 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
Active: active (running) since Wed 2025-03-05 04:38:08 EST; 6s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 3030688 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 3031171 (mysqld)
Status: "Server is operational"
Tasks: 36 (limit: 203176)
Memory: 447.2M
CPU: 1.173s
CGroup: /system.slice/mysqld.service
└─3031171 /usr/sbin/mysqld
Mar 05 04:38:07 RHEL-OEL23 systemd[1]: Starting MySQL Server...
Mar 05 04:38:08 RHEL-OEL23 systemd[1]: Started MySQL Server.
[root@RHEL-OEL23 ~]#
8) Create a Tablespace in a Custom Directory
This command creates a tablespace named test2_tbs inside the /tablespace directory.
mysql> create tablespace test2_tbs add datafile '/tablespace/test2_tbs.ibd';
Query OK, 0 rows affected (0.01 sec)
a) Create a Table Using the New Tablespace
This assigns the new table test_tb to test2_tbs.
mysql> create table test_tb (rollno int) tablespace=test2_tbs;
Query OK, 0 rows affected (0.00 sec)
b) Verify the Table’s Creation
This command checks if the table test_tb is correctly assigned to test2_tbs.
mysql> show create table test_tb\G
*************************** 1. row ***************************
Table: test_tb
Create Table: CREATE TABLE `test_tb` (
`rollno` int DEFAULT NULL
) /*!50100 TABLESPACE `test2_tbs` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
c) Confirm File Creation
The output should list test2_tbs.ibd, confirming that the tablespace is successfully stored in /tablespace.
[root@RHEL-OEL23 ~]# cd /tablespace/
[root@RHEL-OEL23 tablespace]# ls -lrt
total 112
-rw-r----- 1 mysql mysql 114688 Mar 5 04:43 test2_tbs.ibd
[root@RHEL-OEL23 tablespace]#
9) Dropping a Tablespace
a) Ensure the Tablespace is Empty
Before removing a tablespace, delete all associated tables:
mysql> drop table test3;
Query OK, 0 rows affected (0.00 sec)
If you attempt to drop a tablespace before removing its tables, MySQL will return the following error:
mysql> drop tablespace test2_tbs;
ERROR 3120 (HY000): Tablespace `test2_tbs` is not empty.
b) Drop the Tablespace
Once the tables have been removed, you can delete the tablespace:
mysql> drop tablespace test2_tbs;
Query OK, 0 rows affected (0.01 sec)
This will successfully remove test2_tbs and its corresponding data file.
Alert: The information provided on this website is for educational purposes only. It has been tested internally, but there is no guarantee it will work in every environment. It is strongly recommended to first test the process in a non-production environment, such as a User Acceptance Testing (UAT) setup. This allows you to verify configurations, identify any issues, and ensure a smooth transition to production, minimizing the risk of disruptions in your live environment. Always conduct thorough testing before applying changes to production systems.
