General tablespaces are moved to the data directory after restore
General
Escalation
General
Escalation
Description
Initialize, start PS8.0.18 with --innodb-directories="$HOME/PS050320_8_0_18_9_debug" Create the tablespace directory in PS050320_8_0_18_9_debug directory and add a general tablespace in database
CREATE TABLESPACE ts1 ADD DATAFILE '/home/mchawla/PS050320_8_0_18_9_debug/tablespace/tablesp1.ibd';8.0.18>SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%.ibd%' ORDER BY FILE_ID;+------------+---------------------------------------------------------------+| FILE_ID | FILE_NAME |+------------+---------------------------------------------------------------+|1|./sys/sys_config.ibd ||2|/home/mchawla/PS050320_8_0_18_9_debug/tablespace/tablesp1.ibd ||4294967294|./mysql.ibd |+------------+---------------------------------------------------------------+
Initialize, start PS8.0.18 with --innodb-directories="$HOME/PS050320_8_0_18_9_debug"
Create the tablespace directory in PS050320_8_0_18_9_debug directory and add a general tablespace in database
CREATE TABLESPACE ts1 ADD DATAFILE '/home/mchawla/PS050320_8_0_18_9_debug/tablespace/tablesp1.ibd'; 8.0.18>SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%.ibd%' ORDER BY FILE_ID; +------------+---------------------------------------------------------------+ | FILE_ID | FILE_NAME | +------------+---------------------------------------------------------------+ | 1 | ./sys/sys_config.ibd | | 2 | /home/mchawla/PS050320_8_0_18_9_debug/tablespace/tablesp1.ibd | | 4294967294 | ./mysql.ibd | +------------+---------------------------------------------------------------+Take full backup
./xtrabackup --user=root --password='' --backup --target-dir=$HOME/dbbackup_PS8 -S $HOME/PS050320_8_0_18_9_debug/socket.sock --datadir=$HOME/PS050320_8_0_18_9_debug/dataPrepare the backup
./xtrabackup --prepare --target_dir=$HOME/dbbackup_PS8Stop PS server and move the data directories to another location
Restore the backup
./xtrabackup --copy-back --target-dir=$HOME/dbbackup_PS8 --datadir=$HOME/PS050320_8_0_18_9_debug/dataStart the PS server with --innodb-directories="$HOME/PS050320_8_0_18_9_debug"
The external tablespace file is restored to the data directory instead of its original location:
8.0.18>SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%.ibd%' ORDER BY FILE_ID; +------------+---------------------------------------------------------+ | FILE_ID | FILE_NAME | +------------+---------------------------------------------------------+ | 1 | ./sys/sys_config.ibd | | 2 | /home/mchawla/PS050320_8_0_18_9_debug/data/tablesp1.ibd | | 4294967294 | ./mysql.ibd | +------------+---------------------------------------------------------+