I am trying to move innodb_undo_directory
files to a different device mounted on Linux (WSL 2.0). (NB: Specifically, I am trying to move all high-write-load files away from the default datadir on my SSD and onto a HDD to avoid blowing away my SSD again, and I can't put the datadir on the other drive due to pem permissions because it's NTFS for visibility in both Windows and WSL 2.)
The documentation says:
Moving Undo Tablespaces
Undo tablespaces created with
CREATE UNDO TABLESPACE
syntax can be moved while the server is offline to any known directory. Known directories are those defined by theinnodb_directories
variable. Directories defined byinnodb_data_home_dir
,innodb_undo_directory
, anddatadir
are automatically appended to theinnodb_directories
value regardless of whether theinnodb_directories
variable is defined explicitly. Those directories and their subdirectories are scanned at startup for undo tablespaces files. An undo tablespace file moved to any of those directories is discovered at startup and assumed to be the undo tablespace that was moved.The default undo tablespaces (
innodb_undo_001
andinnodb_undo_002
) created when the MySQL instance is initialized must reside in the directory defined by theinnodb_undo_directory
variable. If theinnodb_undo_directory
variable is undefined, default undo tablespaces reside in the data directory. If default undo tablespaces are moved while the server is offline, the server must be started with theinnodb_undo_directory
variable configured to the new directory.
Based on this, it seems like the following ought to be the proper procedure:
- Stop the mysql server.
- Move the files
undo_001
andundo_002
from thedatadir
into the destination directory (/mnt/e/.mysql
). - Set
innodb_undo_directory
to/mnt/e/.mysql
in the config file (e.g./etc/mysql/my.cnf
). - Start the mysql server.
However, after this, mysqld
consistently fails to startup twice in a row, succeeds the third time, and then permanently fails as long as innodb_undo_directory
is set this way.
The first time, it complains:
[InnoDB] Can't create UNDO tablespace innodb_undo_001 since '/mnt/e/.mysql/undo_001' already exists.
and then creates undo_1_trunc.log
in the desired directory.
The second time, it complains: [InnoDB] Can't create UNDO tablespace innodb_undo_002 since '/mnt/e/.mysql/undo_002' already exists.
, deletes undo_001
and creates undo_2_trunc.log
in the desired directory.
The third time, it deletes both trunc logs, creates the undo files back in the datadir
, and succeeds.
The fourth and subsequent times it fails with [InnoDB] Plugin initialization aborted with error Invalid Filename.
which deeper searching indicates is caused by having the undo tablespace files in the datadir
when innodb_undo_directory
points elsewhere.
My config file /etc/mysql/my.cnf
looks like this:
[mysqld]
plugin-load-add=auth_socket.so
innodb_directories=/mnt/e/.mysql
cte_max_recursion_depth=100000
disable_log_bin
innodb_data_home_dir=/mnt/e/.mysql/
innodb_doublewrite_dir=/mnt/e/.mysql/
innodb_log_group_home_dir=/mnt/e/.mysql
innodb_temp_tablespaces_dir=/mnt/e/.mysql/innodb_temp
innodb_undo_directory=/mnt/e/.mysql
What am I missing or doing wrong? Do I need to scrap the server install and start over?
(Did I miss an initialization step somewhere and it's constantly recreating the undo tablespace? I had to recover my datadir from a backup after failing to reinstall it and reinitialize with a different value for lower_case_table_names
but restoring the directory entirely and scrapping that idea should have been enough... but it does seem like that aborted attempt changed the defaults file from mysql.cnf
to my.cnf
and also my error log isn't being filled anymore...)