Difference between datadir and innodb_data_home_dir

InnoDB has two modes of operation: If innodb_file_per_table = 0 all data is stored in one or more filespace tables central ibdata . If innodb_file_per_table = 1 the data is stored in .ibd files. The central file of tables must still exist, but only stores the administrative data and the undo record.

Central table space files are created in innodb_data_home_dir , unless they are specified with full paths. If innodb_data_home_dir is empty, then datadir

The individual table space files are specified with a magic syntax within innodb_data_file_path (see more details in the link at the end). The default string is " ibdata1: 10M: autoextend ", which will create 10 million files ibdata1 named in datadir .

You must be operating with innodb_file_per_table = 1 to get a file for each table. This allows you to recover disk space at the operating system level with OPTIMIZE TABLE .

If you need or want to use innodb_file_per_table = 0 you will have a very large ibdata1 file. Make sure that your operating system and backup tools can conveniently deal with such large files. If that is not the case, it may be necessary to have a more complicated innodb_data_file_path declaration indicating the size limit that those files should have.

datadir represents the MySQL data directory that was specified as a configuration option when compiling mysqld (typically /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation).

Example of advanced configuration

Assuming a computer with Linux, 2 GB of RAM and 60 GB of three hard drives (in directory paths / , /dr2 and /dr3 ). The following example shows possible configuration parameters in my.cnf for InnoDB:

[mysqld]
# You can write your other MySQL server options here
# ...
innodb_data_home_dir =
#
# Data files must be able to hold your data and indexes
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is < 2GB
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#
# innodb_log_arch_dir must be the same as innodb_log_group_home_dir
# (starting from 4.0.6, you can omit it)
innodb_log_arch_dir = /dr3/iblogs
set-variable = innodb_log_files_in_group=2
#
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=250M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#
# Uncomment the next lines if you want to use them
#innodb_flush_method=fdatasync
#set-variable = innodb_thread_concurrency=5

Notice that the example places the two data files on different disks. InnoDB will fill the table space starting with the first data file. In some cases, the performance of the database will be improved if all data is not placed on the same physical disk. Putting log files on a different disk of data is very often beneficial for performance. You can also use raw disk partitions (raw devices) as InnoDB data files, which can speed up the I / O. See the section " Use of basic devices for table spaces ".

• InnoDB Configuration: official documentation
• InnoDB Configuration, with examples
• Configuring InnoDB - An InnoDB tutorial

I hope it helps you.