mysql关于innodb_data_file_path设置

布满荆棘的人生 2022-05-27 01:16 406阅读 0赞

innodb\_data\_file\_path用来指定innodb tablespace文件，如果我们不在My.cnf文件中指定innodb\_data\_home\_dir和innodb\_data\_file\_path那么默认会在datadir目录下创建ibdata1 作为innodb tablespace。5.6.6之后 两个参数的默认值如下：  
show variables like 'innodb\_data%';

**\[html\]** [view plain][] [copy][view plain]

自mysql 5.6.6开始我们可以指定多个innodb tablespace 文件(这些文件可以在相同的路径下，也可以在不同的路径下)。但是在为innodb\_data\_file\_path 指定多个文件时还是有许多注意事项的，下面我来看一下  
1. 在同一个路径下指定多个innodb tablespace 文件  
1)  
在my.cnf文件指定如下  
\[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：因为没有指定innodb\_data\_home\_dir，所以ibdata1和ibdata2都在datadir(innodb\_data\_home\_dir默认值为datadir)目录下创建  
  
  
2)  
在my.cnf文件指定如下  
\[mysqld\]  
innodb\_data\_home\_dir = /data/mysql/mysql3306/data  
innodb\_data\_file\_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M  
note：可以显示的指定innodb tablespace文件的路径，如果指定innodb\_data\_home\_dir，那么就不能为innodb tablespace不同文件指定不同路径  
  
  
2. 在不同路径下指定innodb tablespace  
在my.cnf文件下指定  
\[mysqld\]  
innodb\_data\_home\_dir =  
innodb\_data\_file\_path = ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend

note：如果想为innodb tablespace指定不同目录下的文件，必须指定innodb\_data\_home\_dir =。这个例子中会在datadir下建立ibdata1，在/data/mysql/mysql3306/data1/目录下创建ibdata2

如果我不指定innodb\_data\_home\_dir =，只在my.cnf文件中做如下指定：

innodb\_data\_file\_path = ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend

然后使用该my.cnf文件进行数据库初始化的时候，会发现初始化失败，报错如下：

**\[html\]** [view plain][] [copy][view plain]

1.  2016-03-24 16:58:34 29559 \[ERROR\] InnoDB: File .//data/mysql/mysql3306/data1/ibdata2: 'create' returned OS error 71. Cannot continue operation

\#\#所以如果想为innodb tablespace文件指定不同路径，那么必须在my.cnf文件中指定innodb\_data\_home\_dir =;

=============================================

my.cnf文件中，默认配置为

innodb\_data\_file\_path = ibdata1:10M:autoextend

目前该文件已经扩大到了1g多：  
\-rw-r----- 1 mysql mysql 1470103552  4月 16 17:13 ibdata1  
修改my.cnf为  
innodb\_data\_file\_path = ibdata1:1470M;ibdata2:512M:autoextend

重启mysql，报错：

cat mysql.err  查看启动错误：

1.  InnoDB: Error: data file ./ibdata1 is of a different size  
    
2.  InnoDB: 89728 pages (rounded down to MB)  
    
3.  InnoDB: than specified in the .cnf file 89600 pages!

需要计算 89728/64=1402  
修改配置为  
innodb\_data\_file\_path = ibdata1:1402M;ibdata2:512M:autoextend  
启动mysql，成功！  
计算公式：64pages相当于1M

89728/64=1402M

如果不清楚默认文件page大小，可以先 du -h ibdata1 查看下，再去设置；

在安装数据库的时候忘记配置innodb\_data\_file\_path 参数，后续想新设置该参数，也要参考该方法解决

========================================================

## mysql的innodb扩容 ##

为了添加一个数据文件到表空间中，首先要关闭 MySQL 数据库，编辑 my.cnf 文件，确认innodb ibdata文件的实际情况和my.cnf的配置是否一致，这里有两种情况：

my.cnf的配置

**\[plain\]** [view plain][view plain 1] [copy][view plain 1]

1.  innodb\_data\_file\_path=ibdata1:10G;ibdata2:10G:autoextend

如果当前数据库正在使用ibdata1，或者使用ibdata2，但ibdata2没有超过10G，则对my.cnf配置直接改成：

**\[plain\]** [view plain][view plain 1] [copy][view plain 1]

1.  innodb\_data\_file\_path=ibdata1:10G;ibdata2:10G;ibdata3:10G:autoextend

如果设置了最后一个ibdata自动扩展时，有可能最后一个ibdata的占用空间大于my.cnf的配置空间。例如：

**\[plain\]** [view plain][view plain 1] [copy][view plain 1]

1.  mysql@test:/data1/mysqldata/innodb/data> ls -lh  
2.  \-rw-rw---- 1 mysql mysql 10737418240 2010-01-26 16:34 ibdata1  
3.  \-rw-rw---- 1 mysql mysql 16106127360 2010-01-26 16:34 ibdata2

这时，需要精确的计算ibdata2的大小 15360M，修改：

**\[plain\]** [view plain][view plain 1] [copy][view plain 1]

1.  innodb\_data\_file\_path=ibdata1:10G;ibdata2:15360M;ibdata3:10G:autoextend

重启mysql。

注意：

1、扩容前注意磁盘空间是否足够。  
2、restart后关注是否生成了新的ibdata。

更多说明：

如果，最后一个文件以关键字 autoextend 来描述，那么编辑 my.cnf 的过程中，必须检查最后一个文件的尺寸，并使它向下接近于 1024 \* 1024 bytes (= 1 MB) 的倍数（比方说现在autoextend 的/ibdata/ibdata1为18.5M，而在旧的my.ini中为10M，则需要修改为innodb\_data\_file\_path = /ibdata/ibdata1:19M; 且必须是19M，如果指定20M，就会报错。），并在 innodb\_data\_file\_path 中明确指定它的尺寸。然后你可以添加另一个数据文件。记住只有 innodb\_data\_file\_path 中的最后一个文件可以被指定为 auto-extending。

一个例子：假设起先仅仅只有一个 auto-extending 数据文件 ibdata1 ，这个文件接近于 988 MB。下面是添加了另一个 auto-extending 数据文件后的可能示例 。

**\[plain\]** [view plain][view plain 1] [copy][view plain 1]

1.  innodb\_data\_home\_dir =  
2.  innodb\_data\_file\_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

## ibdata1 瘦身 ##

通常不能移除 InnoDB 的数据文件。为了减小数据文件的大小，你必须使用 mysqldump 来转储(dump)所有的数据表，再重新建立一个新的数据库，并将数据导入新的数据库中。具体步骤如下：

1、备份数据库

**\[plain\]** [view plain][view plain 1] [copy][view plain 1]

1.  mysqldump -uroot -p123456 --default-character-set=utf8 --opt --extended-insert=true --triggers -R --hex-blob --single-transaction --no-autocommit  test > db\_name.sql

2、停止数据库

**\[plain\]** [view plain][view plain 1] [copy][view plain 1]

1.  service mysqld stop

3、删除相关文件

**\[plain\]** [view plain][view plain 1] [copy][view plain 1]

1.  ibdata1  
2.  ib\_logfile\*  
3.  mysql-bin.index

4、手动删除除Mysql之外所有数据库文件夹，然后启动数据库

**\[plain\]** [view plain][view plain 1] [copy][view plain 1]

1.  service mysqld start

5、还原数据

**\[plain\]** [view plain][view plain 1] [copy][view plain 1]

1.  /usr/local/mysql/bin/mysql -uroot -phigkoo < /data/bkup/mysqldump.sql

主要是使用Mysqldump时的一些参数，建议在使用前看一个说明再操作。另外备份前可以先查看一下当前数据库里哪些表占用空间大，把一些不必要的给truncate table掉。这样省些空间和时间

## 改变 InnoDB 的日志文件数目 ##

如果希望改变 InnoDB 的日志文件数目，必须先关闭 MySQL 并确定完全关闭而没有发生任何错误。将旧的日志文件复制到其它安全的地方，以防在关闭服务时发生了错误而需要恢复数据库。删除所有日志文件，编辑 my.cnf，再重新启动 MySQL。InnoDB 在启动时将会提示它在建立新的日志文件。

=================================================================

今天研究下关于mysql数据的相关参数以及innodb引擎下的共享表空间和独立表空间介绍，参数主要包括innodb\_data\_file\_path、innodb\_data\_home\_dir、innodb\_buffer\_pool\_size、innodb\_buffer\_pool\_instances四个参数。

**一：首先介绍innodb\_buffer\_pool\_size**

我们知道mysql数据库在操作数据页的时候，会先把数据页pin到内存中，之后再做相关的处理，那么mysql可以使用的mysql的内存大小就是由innodb\_buffer\_pool\_size参数控制的，这个参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲。在高并发高I/O时正确的配置非常重要，可能带来很大的性能提升，这是InnoDB最重要的设置，对InnoDB性能有决定性的影响。默认的设置只有8M，所以默认的数据库设置下面InnoDB性能很差。在只有 InnoDB存储引擎的数据库服务器上面，可以设置60-80%的内存。这个参数是非动态的，要修改这个值，需要重启mysqld服务

\# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

\# row data. The bigger you set this the less disk I/O is needed to

\# access data in tables. On a dedicated database server you may set this

\# parameter up to 80% of the machine physical memory size. Do not set it

\# too large, though, because competition of the physical memory may

\# cause paging in the operating system. Note that on 32bit systems you

\# might be limited to 2-3.5G of user level memory per process, so do not

\# set it too high.

\#innodb，不像myisam，使用缓冲池来缓存索引和

\#行数据。您设置的越大，那么访问表中的数据所需的磁盘I /o就越少。

\#在专用的数据库服务器上，您可以设置这个

\#参数最多80%的计算机物理内存大小。不要设置它

\#太大了，因为物理内存的竞争可能

\#导致操作系统中的分页。注意，在32位系统上

\#可能仅限于每个进程的2-3.5g用户级内存，所以不要

把它定得太高了。

**二：innodb\_data\_home\_dir和innodb\_data\_file\_path参数**

innodb\_data\_file\_path用来指定innodb 共享 表空间文件，如果我们不在My.cnf文件中指定innodb\_data\_home\_dir和innodb\_data\_file\_path那么默认会在datadir目录下创建ibdata1 作为innodb tablespace。

虽然不指定innodb\_data\_home\_dir和指定它为空显示是一样的，但是作用确不一样，如果不指定那么所有的innodb tablespace 文件只能存放在datadir目录下。如果显示指定为空，则可以为innodb tablespace 文件指定不同的路径

1. 在同一个路径下指定多个innodb tablespace 文件

在my.cnf文件指定如下

\[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：因为没有指定innodb\_data\_home\_dir，所以ibdata1和ibdata2都在datadir(innodb\_data\_home\_dir默认值为datadir)目录下创建

在my.cnf文件指定如下

\[mysqld\]

innodb\_data\_home\_dir = /data/mysql/mysql3306/data

innodb\_data\_file\_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M

note：可以显示的指定innodb tablespace文件的路径，如果指定innodb\_data\_home\_dir，那么就不能为innodb tablespace不同文件指定不同路径

2. 在不同路径下指定innodb tablespace

在my.cnf文件下指定

\[mysqld\]

innodb\_data\_home\_dir =

innodb\_data\_file\_path = ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend

如果我不指定innodb\_data\_home\_dir =，只在my.cnf文件中做如下指定：

innodb\_data\_file\_path =/data/data/ibdata1:18m;/data/data/1ibdata2:100m:autoextend:max:2000M

bogon:root@~>service mysqld start

Starting MySQL. ERROR! The server quit without updating PID file (/tmp/mysql.pid).

查看错误日志。。。如下

bogon:root@/>tail -n 100 /data/log/error.log

171127 21:08:16 InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: If you are installing InnoDB, remember that you must create

InnoDB: directories yourself, InnoDB does not create them.

InnoDB: File name .//data/data/ibdata1

InnoDB: File operation call: 'create'.

InnoDB: Cannot continue operation.

\#\#所以如果想为innodb tablespace文件指定不同路径，那么必须在my.cnf文件中指定innodb\_data\_home\_dir =

注意：

1）设定文件大小时一定要注意你的OS是否有最大文件尺寸为2GB的限制！InnoDB是不会注意你的OS文件尺寸限制的， 在一些文件系统中你可能要设定最大容量限制：

innodb\_data\_home\_dir =

innodb\_data\_file\_path = /ibdata/ibdata1:100M:autoextend:max:2000M

2）如果是修改共享表空间的文件个数，注意在描述原来文件的时候，一定要找到该文件，查看他当前的实际大小，然后写在innodb\_data\_file\_path =哪个文件的后面。否则会报错。

**三：innodb\_buffer\_pool\_instances**

innodb缓冲池被划分为多个内存缓冲池的数量。对于具有多GB范围的缓冲池的系统，将缓冲池划分为多个缓冲池可以提高并发性，减少对不同线程读取和写入到缓存时页面的争用。从缓冲池中存储或读取的每个页面都随机分配给其中一个缓冲池实例，使用哈希函数。每个缓冲池管理自己的自由列表、刷新列表、LRU和连接到缓冲池的所有其他数据结构，并由自己的buffer pool mutex（互斥体）保护。

只有当您将innodb \_ buffer \_ pool \_size设置为1gb或更高的大小时，此选项才会生效。所有缓冲池的大小之和是您指定的总的缓存池大小。为了获得最佳效率，请指定innodb \_ buffer \_ pool \_instances和innodb \_ buffer \_ pool \_size的组合，以便每个缓冲池实例至少为1gb。

在MySQL 5.6.6之前,默认是1， 默认值 在MySQL 5.6.6和更高版本在32位系统取决于innodb\_buffer\_pool\_size的值

1 innodb\_buffer\_pool\_instances可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。

2 innodb\_buffer\_pool\_instances 参数显著的影响测试结果，特别是非常高的 I/O 负载时。

3 实验环境下， innodb\_buffer\_pool\_instances=8 在很小的 buffer\_pool 大小时有很大的提升，而使用大的 buffer\_pool 时，innodb\_buffer\_pool\_instances=1 的表现最棒。

最后说说mysql的innodb引擎下的表空间：

一、针对innodb引擎，分为两种情况一种是共享表空间存储方式，还有一种是独享表空间存储方式。

共享表空间： Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。

独立表空间：

而当使用独享表空间来存放Innodb的表的时候，每个表的数据以一个单独的文件来存放，这个时候的单表限制，又变成文件系统的大小限制了

针对myasam引擎：

MySQL单表最大限制就已经扩大到了64PB了（官方文档显示）。也就是说，从目前的技术环境来看，MySQL数据库的MyISAM存储 引擎单表大小限制已经不是有MySQL数据库本身来决定，而是由所在主机的OS上面的文件系统来决定了。

二、查看数据库的表空间

MySQL \[(none)\]> show variables like 'innodb\_data%';  
\+-----------------------+----------------------------------------------------------------------+  
| Variable\_name         | Value                                                                |  
\+-----------------------+----------------------------------------------------------------------+  
| innodb\_data\_file\_path | /data/data/ibdata1:100m;/data/data1/ibdata2:100m:autoextend:max:2000M |  
| innodb\_data\_home\_dir  |                                                                      |  
\+-----------------------+----------------------------------------------------------------------+

表空间有四个文件组成：ibdata1、ibdata2每个文件的大小为100M，当 ibdata1文件满了的时候，ibdata2会自动扩展；

当前的存储空间满的时候，可以在其他的磁盘添加数据文件，语法如下：语法如下所示：

pathtodatafile:sizespecification;pathtodatafile:sizespec;.;pathtodatafile:sizespec\[:autoextend\[:max:sizespecification\]\]

如果用 autoextend 选项描述最后一个数据文件，当 InnoDB 用尽所有表自由空间后将会自动扩充最后一个数据文件，每次增量为 8 MB。示例：

不管是共享表空间和独立表空间，都会存在innodb\_data\_file文件，因为这些文件不仅仅要存放数据，而且还要充当着类似于ORACLE的UNDO表空间等一些角色。

三、共享表空间优缺点

既然Innodb有共享表空间和独立表空间两种类型，那么这两种表空间存在肯定都有时候自己的应用的场景，存在即合理。以下是摘自mysql官方的一些介绍：

3.1 共享表空间的优点

表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制，方便扩容，放到多个磁盘磁盘上可以分散io,提高性能。

3.2 共享表空间的缺点

所有的数据和索引存放到一个文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，当数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日志系统这类应用最不适合用共享表空间，对于经常删除操作的这类应用最不适合用共享表空间。

共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了（可以理解为oracle的表空间10G，但是才使用10M，但是操作系统显示的表空间为10G），进行数据库的冷备很慢；

四、独立表空间的优缺点

4.1 独立表空间的优点

每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。

空间可以回收（除drop table操作外，表空不能自已回收）

Drop table操作自动回收表空间，如果对于统计分析或是日志表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能；

4.2 独立表空间的缺点

单表增加过大，当单表占用空间过大时，存储空间不足，只能从操作系统层面思考解决方法，比如做软连接；

五、共享表空间和独立表空间之间的转换

5.1 查看当前数据库的表空间管理类型

mysql> show variables like "innodb\_file\_per\_table";

ON代表独立表空间管理，OFF代表共享表空间管理；（查看单表的表空间管理方式，需要查看每个表是否有单独的数据文件）

5.2 修改数据库的表空间管理方式

修改innodb\_file\_per\_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间；

innodb\_file\_per\_table=1 为使用独占表空间

innodb\_file\_per\_table=0 为使用共享表空间

**题外话**

碎片的产生：

MySQL具有相当多不同种类的存储引擎来实现列表中的数据存储功能。每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。

针对oracle行链接和行迁移：新insert的时候，可能发生行链接；update已经存在的行时，可能发生行迁移；

行链接产生在第一次插入数据的时候如果一个block不能存放一行记录的情况下。这种情况下，Oracle将使用链接一个或者多个在这个段中保留的block存储这一行记录，行链接比较容易发生在比较大的行上，例如行上有LONG、LONG RAW、LOB等数据类型的字段，这种时候行链接是不可避免的会产生的。

当一行记录初始插入的时候事可以存储在一个block中的，由于更新操作导致行长增加了，而block的自由空间已经完全满了，这个时候就产生了行迁移。在这种情况下，Oracle将会迁移整行数据到一个新的block中（假设一个block中可以存储下整行数据），Oracle会保留被迁移行的原始指针指向新的存放行数据的block，这就意味着被迁移行的ROW ID是不会改变的。

当发生了行迁移或者行链接，对这行数据操作的性能就会降低，因为Oracle必须要扫描更多的block来获得这行的信息。

**总结：**原来mysql也有类似于oracle的undo表空间，也就是innodb共享表空间（ibdata1），默认就是自动扩展的，每次扩展8M，共享表空间类似于oracle的表空间，可以实现把mysql的数据和索引放到不同的磁盘下，分散io，进而提高性能，就是不像oracle那样可以动态的给表空间添加文件，mysql如果想要添加共享表空间的文件，需要重启mysql服务才能生效。

[view plain]: https://blog.csdn.net/shaochenshuo/article/details/50973068#
[view plain 1]: https://blog.csdn.net/zm2714/article/details/8479974/#