Change location of innodb_temporary

Question

I want to change the path where the ibtmp1 is stored in mysql 8.0.25 on debian 11. How to do this? I see:

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G
*************************** 1. row ***************************
             FILE_ID: 4294967293
             FILE_NAME: ./ibtmp1

I changed mysqld.cnf:

[mysqld]
innodb_tmpdir=/$newDir/
innodb_temporary=/$newDir/
innodb_temp_tablespaces_dir=/$newDir/
innodb_temp_data_file_path=/$newDir/

restarted mysqld but that doesn't change a thing. Same with:

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

Only if I change in the init.d script:

mysqld_safe --innodb-tmpdir=/$newDir/

I see with mysql> SELECT @@innodb_tmpdir; that the path is set (which unfortunately doesn't affect the path of ibtmp1.

I am unsure if the mysqld.cnf is read properly, but /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options" reveals one of the conf files is the correct one.

Is there a way to do this via a mysql statement - and how looks it like? The most easiest way, making a symbolic link in the filesystem, doesn't work (anymore).

Answer 1

mysqld --help --verbose

At about line 13, it will say where the config file is. It is probably not spelled the way you spelled it. Mine says

...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
...

Answer 2

Rick James' answer made me aware that (whyever) the conf file was not read. So I copied /etc/mysql/mysql.conf.d/mysqld.cnf to /etc/mysql/my.cnf. That one is read fine. I then had to define (in my.cnf):

[mysqld]
innodb_tmpdir=$yourNewTmpPath
innodb_temp_data_file_path=$yourNewTmpPath/ibtmp1:50M:autoextend:max:50G
innodb_temp_tablespaces_dir=$yourNewTmpPath
...

Substitute $yourNewTmpPath with your new temp directory. (Adopt your innodb_temp_data_file_path parameters at your need (you may not need 50G for autoextend)).

Restart mysqld.

You can evaluate if the settings are properly consumed by:

mysql> SELECT @@innodb_temp_data_file_path;
+----------------------------------------------------------+
| @@innodb_temp_data_file_path                             |
+----------------------------------------------------------+
| $yourNewTmpPath/ibtmp1:50M:autoextend:max:50G |
+----------------------------------------------------------+

Now I could alter a 160GB table with ... CONVERT TO CHARACTER SET utf8mb4;. On my NFS mount it produces some temporary .nfs0000... files, which are removed from time to time and created again. I think at most they needed around 50GB at a time. I also watched IO operations of mysqld (using iotop -oPa) which amounts to >1TB for write operations and again >1TB for read operations.

So, key here was to copy the config file to a place where it is indeed consumed - and use the proper variables and values for them.