如何收缩Mysql的ibdata1文件

修改表引擎

成都创新互联专注于企业全网整合营销推广、网站重做改版、站前网站定制设计、自适应品牌网站建设、H5高端网站建设、商城网站制作、集团公司官网建设、成都外贸网站建设公司、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为站前等各大城市提供网站开发制作服务。

1.对每个InnoDB表执行 ALTER TABLE table_name ENGINE=MyISAM;

2.停止Mysql服务；

3.移除InnoDB相关文件ibdata1等；

4.修改my.cnf中的参数，添加innodb_file_per_table；

在my.cnf中[mysqld]下设置

innodb_file_per_table=1

5.启动Mysql服务；

6.将刚才修改后的那些表改回InnoDB：ALTER TABLE table_name ENGINE=InnoDB；

导出InnoDB表

1.使用mysqldump命令导出所有的InnoDB表，例如: mysqldump –add-drop-table –extended-insert –disable-keys –quick ‘db_name’ –tables ‘tbl_name’ ‘db_name.tbl_name.sql’

2.删掉这些表：

◦SET FOREIGN_KEY_CHECKS=0;

◦DROP TABLE db_name.tbl_name;

◦DROP TABLE db_name1.tbl_name1;

◦–– DROP other tables here…

◦SET FOREIGN_KEY_CHECKS=1;

3.停止Mysql服务；

4.移除InnoDB相关文件ibdata1等；

5.修改my.cnf中的参数，添加innodb_file_per_table；

6.启动Mysql服务；

7.在Mysql Console下导入表：

◦SET FOREIGN_KEY_CHECKS=0;

◦SOURCE db_name.tbl_name.sql;

◦SOURCE db_name1.tbl_name1.sql;

◦–– SOURCE other files here…

◦SET FOREIGN_KEY_CHECKS=1;

导出整个数据库

这个是我常用的，虽然他和耗磁盘和时间，但是确实是最简便的：

1.导出所有的数据： /usr/bin/mysqldump ––extended-insert ––all-databases ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers all-databases.sql

2.停止Mysql服务；

3.重命名mysql数据文件夹；

4.修改my.cnf中的参数，添加innodb_file_per_table；

5.mysql_install_db重新初始化mysqld；

6.开启Mysql服务;

7.进入Mysql Console执行：

◦SET FOREIGN_KEY_CHECKS=0;

◦SOURCE all-databases.sql;

◦SET FOREIGN_KEY_CHECKS=1;

8.重启数据库测试OK就领赏去吧。

如果因为断电或者直接关机导致idb文件出错，就需要重构这些文件

[mysqld]

加 innodb_force_recovery=1

mysql怎么通过frm和ibd文件还原数据

1、找回表结构，如果表结构没有丢失直接到下一步

a、先创建一个数据库，这个数据库必须是没有表和任何操作的。

b、创建一个表结构，和要恢复的表名是一样的。表里的字段无所谓。一定要是innodb引擎的。CREATE TABLE `weibo_qq0`( `weiboid` bigint(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

c、关闭mysql， service mysqld stop;

d、用需要恢复的frm文件覆盖刚新建的frm文件;

e、修改my.ini 里 innodb_force_recovery=1 ， 如果不成修改为 2,3,4,5,6。

f、 启动mysql，service mysqld start;show create table weibo_qq0 就能li到表结构信息了。

2、找回数据。记得上面把 innodb_force_recovery改掉了，需要注释掉，不然恢复模式不好操作。 这里有个关键的问题，就是innodb里的任何数据操作都是一个日志的记录点。也就是如果我们需要数据恢复，必须把之前的表的数据的日志记录点添加到一致。

a、建立一个数据库，根据上面导出的创建表的sql执行创建表。

b、找到记录点。先要把当前数据库的表空间废弃掉，使当前ibd的数据文件和frm分离。 ALTER TABLE weibo_qq0 DISCARD TABLESPACE;

c、把之前要恢复的 .ibd文件复制到新的表结构文件夹下。 使当前的ibd 和frm发生关系。ALTER TABLE weibo_qq0 IMPORT TABLESPACE; 结果不出意外肯定会报错。就和我们开展数据开始说的那样，数据记录点不一致。我们看看之前ibd记录的点在什么位置。开始执行 import tablespace，报错 ERROR 1030 (HY000): Got error -1 from storage engine。找到mysql的错误日志，InnoDB: Error: tablespace id in file ‘.testweibo_qq0.ibd’ is 112, but in the InnoDB InnoDB: data dictionary it is 1. 因为 weibo_qq0 之前的记录点在112，当前的表只创建一次，所以记录点是1.

d、那怎么从1记录到112。for ($1=1; $i=111; $1++) {CREATE TABLE t# (id int) ENGINE=InnoDB;} 也许很奇怪，为什么是循环111，不是112。因为在a执行创建表结构的时候已经记录增加了一次。

e、修改表结构 alter table weibo_qq0 discard tablespace;使当前的表结构和ibd脱离关系。复制.ibd到当前的目录结构。

f、使原来数据的ibd和当前frm建立关系。 ALTER TABLE product IMPORT TABLESPACE; 这个时候没有错误，说明已经建立好了。但是查询数据还是查不出来。

g、相比这里大家已经知道为什么了，这个模式也不是说改了数据库就可以在生产环境使用。更改 innodb_force_recovery=1 ， 如果不成修改为 2,3,4,5,6。直到可以 查询出数据为止，然后dump出来。数据就备份出来了。

h、把所有数据导出后，在新的数据库导入。所有数据就生成了。

扩展问题，很多时候我们是分表表结构怎么批量操作，提高速度呢。用循环!循环把表的空间废弃掉。

for i in `seq 0 111`; do mysql -uroot -P33061 -h127.0.0.1 -Dtestdd -e “CREATE TABLE inv_crawl_weibo_qq$i (id bigint(20) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id)) ENGINE=innodb “; done

ALTER TABLE inv_crawl_weibo_qq0 DISCARD TABLESPACE;

从备份数据把ibd复制cp到dd数据库下，注意复制过来的文件权限。

循环导入表空间。

ALTER TABLE inv_crawl_weibo_qq0 IMPORT TABLESPACE;

没有报错就导入成功了

windows mysql 怎么释放ibdata的空间

1，备份数据库

从命令行进入MySQL Server 5.1/bin

备份全部数据库，执行命令mysqldump -q -uusername -pyourpassword --add-drop-table --all-databases /backup/all.sql

除数据库

drop database db1;

drop database db2;

此处不删除应用数据库，在全库恢复数据库时，就会报异常错误，一些表的*.ibd会报不存在。

解决方式：

movo db1 /backup/db1_bak

movo db2 /backup/db2_bak

做完此步后，然后停止数据库。

2，修改mysql配置文件

修改my.cnf文件，增加下面配置

innodb_file_per_table

对每张表使用单独的innoDB文件, 修改/etc/my.cnf文件

3，删除原数据文件

删除原来的ibdata1文件及日志文件ib_logfile*，删除data目录下的应用数据库文件夹(mysql,test,information_schema数据库本身文件夹不要删除)

4，还原数据库

启动数据库服务

从命令行进入MySQL Server 5.1/bin

还原全部数据库，执行命令mysql -uusername -pyourpassword /all.sql

大公告成。

浅谈MySQL中的ibdata1

Data dictionary

Double write buffer

Insert buffer

Rollback segments

UNDO space

Forign key constraint system tables

大量事务，产生大量的undo log

有旧事物长时间未提交，产生大量旧undo log

file I/O性能差，purge进展慢

32bit系统下bug

升级到5.6以上（64-bit），采用独立undo表空间

增加purge线程数 innodb_purge_threads

升高file I/O能力

事务及时提交，不要积压

默认打开autocommit=1

检查开发框架，确认autocommit=0的地方，事务结束后都有提交或回滚

名称栏目：mysql怎么利用ib mysqli用法
文章来源：http://chengdu.cdxwcx.cn/article/dddjsjd.html