0

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 the innodb_directories variable. Directories defined by innodb_data_home_dir, innodb_undo_directory, and datadir are automatically appended to the innodb_directories value regardless of whether the innodb_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 and innodb_undo_002) created when the MySQL instance is initialized must reside in the directory defined by the innodb_undo_directory variable. If the innodb_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 the innodb_undo_directory variable configured to the new directory.

Based on this, it seems like the following ought to be the proper procedure:

  1. Stop the mysql server.
  2. Move the files undo_001 and undo_002 from the datadir into the destination directory (/mnt/e/.mysql).
  3. Set innodb_undo_directory to /mnt/e/.mysql in the config file (e.g. /etc/mysql/my.cnf).
  4. 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...)

asked Aug 24 at 5:35

0

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.