1

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).

2
  • Perhaps you meant mysqld.cnf.
    – Rick James
    Apr 19, 2022 at 18:42
  • Yes, was just a typo here. Corrected it.
    – dr0i
    Apr 22, 2022 at 10:55

2 Answers 2

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 
...
7
  • Mine too - and in /etc/mysql/my.cnf it says ''!includedir /etc/mysql/mysql.conf.d/ '' where two files resist :"default-auth-override.cnf" and "mysqld.cnf".
    – dr0i
    Apr 21, 2022 at 12:53
  • Interstingly, ` /etc/mysql/mysql.conf.d/mysqld.cnf` is ignored - I have copied it to /etc/mysql/my.cnf and is now taking into account. So thx for insisting!
    – dr0i
    Apr 21, 2022 at 13:50
  • However, mysql refuses to start when my.cnf has the entry innodb_temporary=$nfs3Dir. Unfortunately no further messages are shown besides " mysql.service: Main process exited, code=exited, status=1/FAILURE" . Is it not allowed to point innodb_temporary to an nfs (version3) mount ?
    – dr0i
    Apr 21, 2022 at 14:00
  • Hm. I had to assign to innodb_temp_data_file_path not only a path but also the size, e.g like=$path/ibtmp1:50M:autoextend:max:50G . Now at least mysql> SELECT @@innodb_temp_data_file_path; shows the new location $path/ibtmp1 while mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G still onyl references ./ibtmp1. Gonna test this.
    – dr0i
    Apr 21, 2022 at 14:43
  • Those dollar signs smell like something to fill in -- was a script supposed to replace them?
    – Rick James
    Apr 21, 2022 at 20:05
0

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.