innodb引擎，共享表空间存储方式下的，ibdata文件扩容和瘦身

参看网址：

原理性的东西；

 http://www.linuxidc.com/Linux/2015-01/111241.htm

操作步骤：

http://blog.csdn.net/zm2714/article/details/8479974/

 

Innodb共享空间存储方式不适合有大量删除的业务使用，对于查询还可以。但现在基本上采用的是innodb独立表空间存储的方式。

 

 查看数据库”innodb引擎表共享存储方式“的表空间有多少个ibdata文件：mysql> show variables like 'innodb_data%';

 

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

当前的存储空间满的时候，可以在其他的磁盘添加数据文件，语法如下：语法如下所示：第二个网址中有具体实现

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

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

 

共享表空间的优点

表空间可以分成多个文件存放到各个磁盘，所以表也就分别存放在了多个文件，多个磁盘上了，表的大小不受磁盘大小的限制。所以磁盘容量扩充很方便。

 

共享表空间的缺点

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

共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了

 

独立表空间的优点

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

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

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

 

独立表空间的缺点

单表增加过大，当单表占用空间过大时，存储空间不足，只能从操作系统层面思考解决方法；（具体方案就是使用lvm技术实现此盘扩容，也不是很简单的。具体请参看“lvm磁盘扩容-磁盘分区-磁盘挂载卸载-自动挂载.doc”）。不过搞一次就是永久用的。

 

mysql的innodb扩容

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

my.cnf的配置

innodb_data_file_path=ibdata1:10G;ibdata2:10G:autoextend  

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

innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G:autoextend  

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

mysql@test:/data1/mysqldata/innodb/data> ls -lh  

-rw-rw---- 1 mysql mysql 10737418240 2010-01-26 16:34 ibdata1  

-rw-rw---- 1 mysql mysql 16106127360 2010-01-26 16:34 ibdata2  

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

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 数据文件后的可能示例 。

innodb_data_home_dir =  

innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend  

ibdata1 瘦身

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

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、停止数据库

service mysqld stop  

3、删除相关文件

ibdata1  

ib_logfile*  

mysql-bin.index  

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

service mysqld start  

5、还原数据

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