Innodb_data_file_path Settin
Innodb_data_file_path is used to specify the innodb tablespace file, and if we do not specify innodb_data_home_dir and innodb_data_file_path in the My.cnf file, then ibdata1 will be created as the innodb tablespace in the datadir directory by default. The default values of the two parameters after 5.6.6 are as follows:
Show variables like 'innodb_data%'
[html] view plain copy
+-+
| | Variable_name | Value |
+-+
| | innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | > > note: although not specifying innodb_data_home_dir is the same as specifying it as empty, it does have the same effect. If you do not specify it, all innodb tablespace files can only be stored in the datadir directory. If the display is specified as empty, you can specify a different path for the innodb tablespace file
Since mysql 5.6.6, we can specify multiple innodb tablespace files (these files can be under the same path or under different paths). But there are a lot of considerations when specifying multiple files for innodb_data_file_path. Let's take a look at it.
1. Specify multiple innodb tablespace files under the same path
1)
Specify the following in the my.cnf file
[mysqld]
Innodb_data_file_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M
Note:The autoextend and max attributes can be used only for the last data file in the innodb_data_file_path line.
Note: because innodb_data_home_dir is not specified, both ibdata1 and ibdata2 are created in the datadir directory (innodb_data_home_dir default is datadir)
2)
Specify the following in the my.cnf file
[mysqld]
Innodb_data_home_dir = / data/mysql/mysql3306/data
Innodb_data_file_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M
Note: you can specify the path of the innodb tablespace file that can be displayed. If you specify innodb_data_home_dir, you cannot specify different paths for different innodb tablespace files.
two。 Specify innodb tablespace under different paths
Specify under the my.cnf file
[mysqld]
Innodb_data_home_dir =
Innodb_data_file_path = ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend
Note: if you want to specify files in a different directory for innodb tablespace, you must specify innodb_data_home_dir =. In this example, you will create an ibdata1 under datadir and an ibdata2 under the / data/mysql/mysql3306/data1/ directory
If I don't specify innodb_data_home_dir =, I'll just specify it in the my.cnf file as follows:
Innodb_data_file_path = ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend
Then, when you initialize the database with the my.cnf file, you will find that the initialization failed, and the error is as follows:
[html] view plain copy
2016-03-24 16:58:34 29559 [ERROR] InnoDB: File. / / data/mysql/mysql3306/data1/ibdata2: 'create' returned OS error 71. Cannot continue operation
# # so if you want to specify a different path for the innodb tablespace file, you must specify innodb_data_home_dir = in the my.cnf file
