0

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

Improve this question
7
  • Is this a new server? Which MariaDB version and distro package? Edit question to include the mariadb error log. If its a new server, you'd normally run mysql_install_db -u mysql after changing these settings to create the layout. The directories need to be created before running mysql_install_db.
    – danblack
    Commented Jan 23, 2022 at 0:30
  • I did not run mysql_install_db after changing the innodb_file_per_table setting. I only did a few ALTER TABLE tbl_name ENGINE=InnoDB to convert tables from myisam to innodb.
    – DanGer
    Commented Jan 23, 2022 at 0:35
  • changing innodb_file_per_table is ok after mysql_install_db. What exactly does 'didn't work for me' mean? innodb_data_home_dir for me contains ibtmp1, ib_buffer_pool and ibdata1.
    – danblack
    Commented Jan 23, 2022 at 0:45
  • I want the innodb data files to be stored at /var/db/mysql/innodb/<dbname>/table.ibd not in the datadir with other myisam tables. From the document I referenced it should be possible, but it doest work as described, or I did not understand it properly...
    – DanGer
    Commented Jan 23, 2022 at 0:49
  • 1
    I think you are right, needs to be better written. CREATE TABLE .. DATA DIRECTORY='/var/db/mysql/innodb', does work, but not for ALTER TABLE in my test. Happy to see bug reports for better documentation and if ALTER TABLE doesn't work for you (look at show warnings afterwards).
    – danblack
    Commented Jan 23, 2022 at 1:28

1 Answer 1

Reset to default
0

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
Improve this answer
5
  • 1
    I understand existing tabkes that were build with the setting off will not be affected by the change of the setting. I actually changed engine of a few tables from myisam to innodb after I modified the setting, and the innodb datafiles were created in the datadir :/
    – DanGer
    Commented Jan 23, 2022 at 16:35
  • root@localhost [(none)]> select @@datadir, @@innodb_file_per_table, @@innodb_data_home_dir; +---------------------+-------------------------+------------------------+ | @@datadir | @@innodb_file_per_table | @@innodb_data_home_dir | +---------------------+-------------------------+------------------------+ | /var/db/mysql/data/ | 1 | /var/db/mysql/innodb | +---------------------+-------------------------+------------------------+ I tried to drop one of the databases and import the new dump. The datafile is still in /var/db/mysql/data/table.ibd
    – DanGer
    Commented Jan 23, 2022 at 16:46
  • @DanGer - (I don't have an answer to your latest puzzle, but this might help answer it...) Dump a database that is already in the new directory; see if the dump contains anything about the directory.
    – Rick James
    Commented Jan 23, 2022 at 17:50
  • 2
    @DanGer as you found with Marko, its a documentation bug, datadir and not innodb_data_home_dir is used for table spaces where innodb_file_per_table=1.
    – danblack
    Commented Jan 24, 2022 at 2:26
  • 3
    The KB URL in the question has been updated to reflect the accurate information. Sorry it doesn't achieve what you wanted it to achieve, but at least the documentation is accurate now.
    – danblack
    Commented Jan 25, 2022 at 3:20

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.