328 lines
12 KiB
Plaintext
328 lines
12 KiB
Plaintext
# 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;
|