mariadb innodb file per table data location



  • I have the following settings:

    datadir                         = /var/db/mysql/data
    innodb_data_home_dir            = /var/db/mysql/innodb
    innodb_file_per_table           = ON
    

    My intention is to store the innodb data in /var/db/mysql/innodb but it is still being stored under /var/db/mysql/data/database_name/. I would expect that my settings would achieve my desired behavior as per https://mariadb.com/kb/en/innodb-file-per-table-tablespaces/ which reads:

    "By default, InnoDB's file-per-table tablespaces are created in the system's data directory, which is defined by the datadir system variable. If you want to store InnoDB data separate from the data for other storage engines, then you change this by setting the the innodb_data_home_dir system variable."

    It doesn't work for me though. Any ideas? Thanks!

    Server version: 10.5.13-MariaDB-log

    There are no errors in the log related to this "issue".



  • after changing the innodb_file_per_table setting

    Messing with directory settings does not move files around.

    That setting takes affect only at the time of CREATE TABLE or ALTER TABLE. Existing tables that were built with the setting OFF will stay in ibdata1.

    It is not easy to shrink ibdata1. Furthermore it cannot be removed, not made "empty"; things other than tables go in there.

    What is your ultimate goal? If it is to shrink ibdata1, then there are lots of Q&A here that go through the steps to achieve that.

    Or, perhaps, the new directory is on a separate physical drive. (This is unlikely to make much difference in performance.)

    If you are simply "moving" things to a different directory, then the following may work. (Caution! I don't know for sure.)

    1. Backup! (Either with mariadump or by copying the entire directory tree)
    2. Stop MariaDB
    3. Change the dir setting as you did.
    4. Manually move ib* and any database directories to the new location.
    5. Start MariaDB



Suggested Topics

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