# restart:--innodb-directories=MYSQL_TMP_DIR ## create all kinds of tables including absolute paths CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB; CREATE TABLE tab1(c1 int, c2 varchar(10)) TABLESPACE=ts1; INSERT INTO tab1 VALUES(1, 'VISH'); CREATE TABLE tab2(c1 int, c2 varchar(10)) Engine=InnoDB; INSERT INTO tab2 VALUES(2, 'VISH'); CREATE INDEX ix1 ON tab1(c2) USING BTREE; CREATE INDEX ix1 ON tab2(c2) ; # Create a table with absolute path. CREATE TABLE tab3( empno INT, ename VARCHAR(30),sal NUMERIC(3)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED PARTITION by hash(empno) ( PARTITION P0 DATA DIRECTORY 'MYSQL_TMP_DIR/db_directory1', PARTITION P1 DATA DIRECTORY 'MYSQL_TMP_DIR/db_directory1'); # Create a tablespace in a known location with absolute path. CREATE TABLESPACE ts2 ADD DATAFILE 'MYSQL_TMP_DIR/db_directory1/ts2.ibd' Engine=InnoDB; DROP TABLESPACE ts2; # Create a tablespace in a known location with relative path. CREATE TABLESPACE ts2 ADD DATAFILE '../data/ts2.ibd' Engine=InnoDB; DROP TABLESPACE ts2; # Try to create a tablespace in an unknown location. CREATE TABLESPACE ts2 ADD DATAFILE '../ts2.ibd' Engine=InnoDB; ERROR HY000: CREATE TABLESPACE data file must be in one of these directories './;MYSQL_TMP_DIR/'. SHOW WARNINGS; Level Code Message Error 3121 CREATE TABLESPACE data file must be in one of these directories './;MYSQL_TMP_DIR/'. Error 1528 Failed to create TABLESPACE ts2 Error 3121 Incorrect File Name '../ts2.ibd'. CREATE INDEX ix1 ON tab3(ename) USING BTREE; INSERT INTO tab3 VALUES (100,'VISWANATH',100); INSERT INTO tab3 VALUES (300,'VISWANATH',100); SHOW CREATE TABLE tab1; Table Create Table tab1 CREATE TABLE `tab1` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL, KEY `ix1` (`c2`) USING BTREE ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SHOW CREATE TABLE tab2; Table Create Table tab2 CREATE TABLE `tab2` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL, KEY `ix1` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SHOW CREATE TABLE tab3; Table Create Table tab3 CREATE TABLE `tab3` ( `empno` int(11) DEFAULT NULL, `ename` varchar(30) DEFAULT NULL, `sal` decimal(3,0) DEFAULT NULL, KEY `ix1` (`ename`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED /*!50100 PARTITION BY HASH (`empno`) (PARTITION P0 DATA DIRECTORY = 'MYSQL_TMP_DIR/db_directory1/' ENGINE = InnoDB, PARTITION P1 DATA DIRECTORY = 'MYSQL_TMP_DIR/db_directory1/' ENGINE = InnoDB) */ CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu'; CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu'; SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%'; FILE_NAME FILE_TYPE TABLESPACE_NAME ./undo_001 UNDO LOG innodb_undo_001 ./undo_002 UNDO LOG innodb_undo_002 ./undo_003.ibu UNDO LOG undo_003 ./undo_004.ibu UNDO LOG undo_004 SELECT space,path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path; SPACE PATH 4294967279 ./undo_001 4294967278 ./undo_002 4294967277 undo_003.ibu 4294967276 undo_004.ibu ## Stop DB server # Copy whole --datadir files into new locations # Copy absolute path *.ibd files into new location # Remove the old datadir files and *.ibd files # Start with --innodb-directories along with other initDB options # Check new datadir SELECT @@datadir; @@datadir NEW_DATADIR/ SELECT @@innodb_undo_directory; @@innodb_undo_directory MYSQL_TMP_DIR/undo_directory1 SELECT @@innodb_data_home_dir; @@innodb_data_home_dir DATA_HOME_DIR/data_home_dir SELECT @@innodb_log_group_home_dir; @@innodb_log_group_home_dir DATA_HOME_DIR/data_home_dir SHOW CREATE TABLE tab3; Table Create Table tab3 CREATE TABLE `tab3` ( `empno` int(11) DEFAULT NULL, `ename` varchar(30) DEFAULT NULL, `sal` decimal(3,0) DEFAULT NULL, KEY `ix1` (`ename`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED /*!50100 PARTITION BY HASH (`empno`) (PARTITION P0 DATA DIRECTORY = 'MYSQL_TMP_DIR/undo_directory2' ENGINE = InnoDB, PARTITION P1 DATA DIRECTORY = 'MYSQL_TMP_DIR/undo_directory2' ENGINE = InnoDB) */ SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%'; FILE_NAME FILE_TYPE TABLESPACE_NAME MYSQL_TMP_DIR/undo_directory1/undo_001 UNDO LOG innodb_undo_001 MYSQL_TMP_DIR/undo_directory1/undo_002 UNDO LOG innodb_undo_002 MYSQL_TMP_DIR/undo_directory1/undo_003.ibu UNDO LOG undo_003 MYSQL_TMP_DIR/undo_directory2/undo_004.ibu UNDO LOG undo_004 SELECT space,path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path; SPACE PATH 4294967279 MYSQL_TMP_DIR/undo_directory1/undo_001 4294967278 MYSQL_TMP_DIR/undo_directory1/undo_002 4294967277 MYSQL_TMP_DIR/undo_directory1/undo_003.ibu 4294967276 MYSQL_TMP_DIR/undo_directory2/undo_004.ibu # Stop DB server # Start with new --datadir only # restart: --datadir=NEW_DATADIR --innodb_undo_directory=MYSQL_TMP_DIR/undo_directory1 --innodb_data_home_dir=MYSQL_TMP_DIR/data_home_dir --innodb_log_group_home_dir=MYSQL_TMP_DIR/data_home_dir --innodb-directories=MYSQL_TMP_DIR # Check with new --datadir SELECT @@datadir; @@datadir NEW_DATADIR/ SELECT @@innodb_undo_directory; @@innodb_undo_directory MYSQL_TMP_DIR/undo_directory1 SELECT @@innodb_data_home_dir; @@innodb_data_home_dir DATA_HOME_DIR/data_home_dir SELECT @@innodb_log_group_home_dir; @@innodb_log_group_home_dir DATA_HOME_DIR/data_home_dir # Check the tables with SHOW SHOW CREATE TABLE tab1; Table Create Table tab1 CREATE TABLE `tab1` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL, KEY `ix1` (`c2`) USING BTREE ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SHOW CREATE TABLE tab2; Table Create Table tab2 CREATE TABLE `tab2` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL, KEY `ix1` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SHOW CREATE TABLE tab3; Table Create Table tab3 CREATE TABLE `tab3` ( `empno` int(11) DEFAULT NULL, `ename` varchar(30) DEFAULT NULL, `sal` decimal(3,0) DEFAULT NULL, KEY `ix1` (`ename`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED /*!50100 PARTITION BY HASH (`empno`) (PARTITION P0 DATA DIRECTORY = 'MYSQL_TMP_DIR/undo_directory2' ENGINE = InnoDB, PARTITION P1 DATA DIRECTORY = 'MYSQL_TMP_DIR/undo_directory2' ENGINE = InnoDB) */ SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%'; FILE_NAME FILE_TYPE TABLESPACE_NAME MYSQL_TMP_DIR/undo_directory1/undo_001 UNDO LOG innodb_undo_001 MYSQL_TMP_DIR/undo_directory1/undo_002 UNDO LOG innodb_undo_002 MYSQL_TMP_DIR/undo_directory1/undo_003.ibu UNDO LOG undo_003 MYSQL_TMP_DIR/undo_directory2/undo_004.ibu UNDO LOG undo_004 SELECT space,path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path; SPACE PATH 4294967279 MYSQL_TMP_DIR/undo_directory1/undo_001 4294967278 MYSQL_TMP_DIR/undo_directory1/undo_002 4294967277 MYSQL_TMP_DIR/undo_directory1/undo_003.ibu 4294967276 MYSQL_TMP_DIR/undo_directory2/undo_004.ibu ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE; ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE; # Check with DML & DDL operations SELECT * FROM tab1; c1 c2 1 VISH SELECT * FROM tab2; c1 c2 2 VISH SELECT * FROM tab3; empno ename sal 100 VISWANATH 100 300 VISWANATH 100 DELETE FROM tab1; DELETE FROM tab2; DELETE FROM tab3; ALTER TABLE tab3 ADD COLUMN c3 VARCHAR(15); INSERT INTO tab1 VALUES(1, 'VISH'); INSERT INTO tab2 VALUES(2, 'VISH'); INSERT INTO tab3 VALUES (100,'VISWANATH',100,'New Column'); INSERT INTO tab3 VALUES (300,'VISWANATH',100,'New Column'); SELECT * FROM tab1; c1 c2 1 VISH SELECT * FROM tab2; c1 c2 2 VISH SELECT * FROM tab3; empno ename sal c3 100 VISWANATH 100 New Column 300 VISWANATH 100 New Column SELECT name,space_type,state FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE space_type = 'Undo'; name space_type state innodb_undo_001 Undo empty innodb_undo_002 Undo empty undo_003 Undo active undo_004 Undo active ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE; ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE; SELECT name,space_type,state FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE space_type = 'Undo'; name space_type state innodb_undo_001 Undo active innodb_undo_002 Undo active undo_003 Undo active undo_004 Undo active # Creating general tablespace in known DIR location CREATE TABLESPACE ts2 ADD DATAFILE 'DATA_HOME_DIR/data_home_dir/ts2.ibd' Engine=InnoDB; CREATE TABLE tab4(c1 int, c2 varchar(10)) TABLESPACE=ts2; SHOW CREATE TABLE tab4; Table Create Table tab4 CREATE TABLE `tab4` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL ) /*!50100 TABLESPACE `ts2` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO tab4 VALUES(1, 'VISH'); # Creating general tablespace in known DIR location CREATE TABLESPACE ts3 ADD DATAFILE 'MYSQL_TMP_DIR/undo_directory1/ts3.ibd' Engine=InnoDB; CREATE TABLE tab5(c1 int, c2 varchar(10)) TABLESPACE=ts3; SHOW CREATE TABLE tab5; Table Create Table tab5 CREATE TABLE `tab5` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL ) /*!50100 TABLESPACE `ts3` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO tab5 VALUES(1, 'VISH'); # Clean up new datadir DROP TABLE tab1; DROP TABLE tab2; DROP TABLESPACE ts1; # Stop DB server with new datadir # Copy back --datadir and *.ibd files into old location # Cleanup the new DATA DIRECTORY *.ibd files # Test by providing the relative path # Re-start with old --datadir with --innodb-directories as remote *.ibd files are existing. # restart: --datadir=OLD_DATADIR --innodb-directories=MYSQL_TMP_DIR # Check --datadir started with old SELECT @@datadir; @@datadir OLD_DATADIR SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%'; FILE_NAME FILE_TYPE TABLESPACE_NAME ./undo_001 UNDO LOG innodb_undo_001 ./undo_002 UNDO LOG innodb_undo_002 ./undo_003.ibu UNDO LOG undo_003 ./undo_004.ibu UNDO LOG undo_004 SELECT space,path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path; SPACE PATH 4294967152 ./undo_001 4294967151 ./undo_002 4294967277 ./undo_003.ibu 4294967276 ./undo_004.ibu SHOW CREATE TABLE tab3; Table Create Table tab3 CREATE TABLE `tab3` ( `empno` int(11) DEFAULT NULL, `ename` varchar(30) DEFAULT NULL, `sal` decimal(3,0) DEFAULT NULL, `c3` varchar(15) DEFAULT NULL, KEY `ix1` (`ename`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED /*!50100 PARTITION BY HASH (`empno`) (PARTITION P0 DATA DIRECTORY = 'MYSQL_TMP_DIR/db_directory1' ENGINE = InnoDB, PARTITION P1 DATA DIRECTORY = 'MYSQL_TMP_DIR/db_directory1' ENGINE = InnoDB) */ SHOW CREATE TABLE tab4; Table Create Table tab4 CREATE TABLE `tab4` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL ) /*!50100 TABLESPACE `ts2` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SHOW CREATE TABLE tab5; Table Create Table tab5 CREATE TABLE `tab5` ( `c1` int(11) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL ) /*!50100 TABLESPACE `ts3` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ALTER UNDO TABLESPACE undo_003 SET INACTIVE; ALTER UNDO TABLESPACE undo_004 SET INACTIVE; SELECT name,space_type,state FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE space_type = 'Undo'; name space_type state innodb_undo_001 Undo active innodb_undo_002 Undo active undo_003 Undo empty undo_004 Undo empty SHOW VARIABLES LIKE '%directories%'; Variable_name Value innodb_directories MYSQL_TMP_DIR # Creating general tablespace in known DIR location (DATA DIRECTORY) CREATE TABLESPACE ts4 ADD DATAFILE 'MYSQL_TMP_DIR/db_directory1/ts4.ibd' Engine=InnoDB; CREATE TABLE tab6(c1 int, c2 varchar(10)) TABLESPACE=ts4; INSERT INTO tab6 VALUES(1, 'VISH'); # Stop DB server # Re-start with old --datadir only # restart: --datadir=OLD_DATADIR DELETE FROM tab3; DELETE FROM tab4; DELETE FROM tab5; DELETE FROM tab6; SELECT TABLESPACE_NAME,TABLE_NAME,ENGINE FROM INFORMATION_SCHEMA.FILES; TABLESPACE_NAME TABLE_NAME ENGINE # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB # NULL InnoDB DROP TABLE tab3; DROP TABLE tab4; DROP TABLE tab5; DROP TABLE tab6; DROP UNDO TABLESPACE undo_003; DROP UNDO TABLESPACE undo_004;