Shulou(Shulou.com)06/01 Report--

About innodb_data_file_path setup

An error was reported when starting MySQL, which failed to start normally. A data file size error was sent through the log. The log screenshot is as follows:

[ERROR] InnoDB: auto-extending data file / usr/local/mysql/data/ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the. CNF file: initial 65536 pages, max 0 (relevant if non-zero) pages!

2018-05-25 14:27:04 6002 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!

Description:

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%'

+-+

| | 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:

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

Reference: https://blog.csdn.net/shaochenshuo/article/details/50973068

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.