Symbolic Links in PXC

MySQL & MariaDB Percona XtraDB Cluster 8.x
,
1

We have the file per table option set in our PXC setup. We are trying to move a few of these tables off to a different disk; are there any precautions we should take when setting up a symbolic link to the tables we move? Does SST and innobackupex handle these setups?

Thank you.

1 Like
2

Hi @jhbremer1,
Firstly, innobackupex is essentially dead. All scripts should be using xtrabackup binary directly. Secondly, the SST process uses xtrabackup to perform this action, so if xtrabackup supports symlinks, so would SST.

I’m not sure if xtrabackup can follow symlinks. I cannot find anything in the manual that says no, but also nothing that says yes. Ideally, instead of creating symlinks, you should use the native InnoDB method:

CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';

I believe you can ALTER TABLE to move. You should add the path to innodb_directories variable in my.cnf as well before moving the table.

1 Like
3

I was curious so I just tested this.

systemctl stop mysql
mkdir /opt/mysql2/world
mv /var/lib/mysql/world/country.ibd /opt/mysql2/world/
ln -s /opt/mysql2/world/country.ibd /var/lib/mysql/world/country.ibd
chown -R mysql:mysql /var/lib/mysql/world/country.ibd
systemctl start mysql
...
xtrabackup --backup --parallel 4 --target-dir /var/backup/

md5sum /var/backup/world/country.ibd
2d6684cc3447a28c379fe0adbed448fc  /var/backup/world/country.ibd

md5sum /opt/mysql2/world/country.ibd
2d6684cc3447a28c379fe0adbed448fc  /opt/mysql2/world/country.ibd

It appears to have worked. xtrabackup followed the symlink. Just be aware that if you ever need to restore, none of the symlink stuff will be reapplied by any tools. xtrabackup --prepare/–copyback will not honor the symlink and restore to the new location. Everything will get copied back to $datadir

1 Like
4

Excellent, thank you for the information! I will be referencing this in the coming months as we begin testing these changes.