Change location of innodb_temporary



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



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



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2