如何收縮Mysql的ibdata1文件

修改表引擎

成都創(chuàng)新互聯(lián)專注于企業(yè)全網(wǎng)整合營銷推廣、網(wǎng)站重做改版、站前網(wǎng)站定制設(shè)計、自適應(yīng)品牌網(wǎng)站建設(shè)、H5高端網(wǎng)站建設(shè)、商城網(wǎng)站制作、集團(tuán)公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)公司、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計等建站業(yè)務(wù)，價格優(yōu)惠性價比高，為站前等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。

1.對每個InnoDB表執(zhí)行 ALTER TABLE table_name ENGINE=MyISAM;

2.停止Mysql服務(wù)；

3.移除InnoDB相關(guān)文件ibdata1等；

4.修改my.cnf中的參數(shù)，添加innodb_file_per_table；

在my.cnf中[mysqld]下設(shè)置

innodb_file_per_table=1

5.啟動Mysql服務(wù)；

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

導(dǎo)出InnoDB表

1.使用mysqldump命令導(dǎo)出所有的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服務(wù)；

4.移除InnoDB相關(guān)文件ibdata1等；

5.修改my.cnf中的參數(shù)，添加innodb_file_per_table；

6.啟動Mysql服務(wù)；

7.在Mysql Console下導(dǎo)入表：

?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;

導(dǎo)出整個數(shù)據(jù)庫

這個是我常用的，雖然他和耗磁盤和時間，但是確實(shí)是最簡便的：

1.導(dǎo)出所有的數(shù)據(jù)： /usr/bin/mysqldump ––extended-insert ––all-databases ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers all-databases.sql

2.停止Mysql服務(wù)；

3.重命名mysql數(shù)據(jù)文件夾；

4.修改my.cnf中的參數(shù)，添加innodb_file_per_table；

5.mysql_install_db重新初始化mysqld；

6.開啟Mysql服務(wù);

7.進(jìn)入Mysql Console執(zhí)行：

?SET FOREIGN_KEY_CHECKS=0;

?SOURCE all-databases.sql;

?SET FOREIGN_KEY_CHECKS=1;

8.重啟數(shù)據(jù)庫測試OK就領(lǐng)賞去吧。

如果因?yàn)閿嚯娀蛘咧苯雨P(guān)機(jī)導(dǎo)致idb文件出錯，就需要重構(gòu)這些文件

[mysqld]

加 innodb_force_recovery=1

mysql怎么通過frm和ibd文件還原數(shù)據(jù)

1、找回表結(jié)構(gòu)，如果表結(jié)構(gòu)沒有丟失直接到下一步

a、先創(chuàng)建一個數(shù)據(jù)庫，這個數(shù)據(jù)庫必須是沒有表和任何操作的。

b、創(chuàng)建一個表結(jié)構(gòu)，和要恢復(fù)的表名是一樣的。表里的字段無所謂。一定要是innodb引擎的。CREATE TABLE `weibo_qq0`( `weiboid` bigint(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

c、關(guān)閉mysql， service mysqld stop;

d、用需要恢復(fù)的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到表結(jié)構(gòu)信息了。

2、找回數(shù)據(jù)。記得上面把 innodb_force_recovery改掉了，需要注釋掉，不然恢復(fù)模式不好操作。 這里有個關(guān)鍵的問題，就是innodb里的任何數(shù)據(jù)操作都是一個日志的記錄點(diǎn)。也就是如果我們需要數(shù)據(jù)恢復(fù)，必須把之前的表的數(shù)據(jù)的日志記錄點(diǎn)添加到一致。

a、建立一個數(shù)據(jù)庫，根據(jù)上面導(dǎo)出的創(chuàng)建表的sql執(zhí)行創(chuàng)建表。

b、找到記錄點(diǎn)。先要把當(dāng)前數(shù)據(jù)庫的表空間廢棄掉，使當(dāng)前ibd的數(shù)據(jù)文件和frm分離。 ALTER TABLE weibo_qq0 DISCARD TABLESPACE;

c、把之前要恢復(fù)的 .ibd文件復(fù)制到新的表結(jié)構(gòu)文件夾下。 使當(dāng)前的ibd 和frm發(fā)生關(guān)系。ALTER TABLE weibo_qq0 IMPORT TABLESPACE; 結(jié)果不出意外肯定會報錯。就和我們開展數(shù)據(jù)開始說的那樣，數(shù)據(jù)記錄點(diǎn)不一致。我們看看之前ibd記錄的點(diǎn)在什么位置。開始執(zhí)行 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. 因?yàn)?weibo_qq0 之前的記錄點(diǎn)在112，當(dāng)前的表只創(chuàng)建一次，所以記錄點(diǎn)是1.

d、那怎么從1記錄到112。for ($1=1; $i=111; $1++) {CREATE TABLE t# (id int) ENGINE=InnoDB;} 也許很奇怪，為什么是循環(huán)111，不是112。因?yàn)樵赼執(zhí)行創(chuàng)建表結(jié)構(gòu)的時候已經(jīng)記錄增加了一次。

e、修改表結(jié)構(gòu) alter table weibo_qq0 discard tablespace;使當(dāng)前的表結(jié)構(gòu)和ibd脫離關(guān)系。復(fù)制.ibd到當(dāng)前的目錄結(jié)構(gòu)。

f、使原來數(shù)據(jù)的ibd和當(dāng)前frm建立關(guān)系。 ALTER TABLE product IMPORT TABLESPACE; 這個時候沒有錯誤，說明已經(jīng)建立好了。但是查詢數(shù)據(jù)還是查不出來。

g、相比這里大家已經(jīng)知道為什么了，這個模式也不是說改了數(shù)據(jù)庫就可以在生產(chǎn)環(huán)境使用。更改 innodb_force_recovery=1 ， 如果不成修改為 2,3,4,5,6。直到可以 查詢出數(shù)據(jù)為止，然后dump出來。數(shù)據(jù)就備份出來了。

h、把所有數(shù)據(jù)導(dǎo)出后，在新的數(shù)據(jù)庫導(dǎo)入。所有數(shù)據(jù)就生成了。

擴(kuò)展問題，很多時候我們是分表表結(jié)構(gòu)怎么批量操作，提高速度呢。用循環(huán)!循環(huán)把表的空間廢棄掉。

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;

從備份數(shù)據(jù)把ibd復(fù)制cp到dd數(shù)據(jù)庫下，注意復(fù)制過來的文件權(quán)限。

循環(huán)導(dǎo)入表空間。

ALTER TABLE inv_crawl_weibo_qq0 IMPORT TABLESPACE;

沒有報錯就導(dǎo)入成功了

windows mysql 怎么釋放ibdata的空間

1，備份數(shù)據(jù)庫

從命令行進(jìn)入MySQL Server 5.1/bin

備份全部數(shù)據(jù)庫，執(zhí)行命令mysqldump -q -uusername -pyourpassword --add-drop-table --all-databases /backup/all.sql

除數(shù)據(jù)庫

drop database db1;

drop database db2;

此處不刪除應(yīng)用數(shù)據(jù)庫，在全庫恢復(fù)數(shù)據(jù)庫時，就會報異常錯誤，一些表的*.ibd會報不存在。

解決方式：

movo db1 /backup/db1_bak

movo db2 /backup/db2_bak

做完此步后，然后停止數(shù)據(jù)庫。

2，修改mysql配置文件

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

innodb_file_per_table

對每張表使用單獨(dú)的innoDB文件, 修改/etc/my.cnf文件

3，刪除原數(shù)據(jù)文件

刪除原來的ibdata1文件及日志文件ib_logfile*，刪除data目錄下的應(yīng)用數(shù)據(jù)庫文件夾(mysql,test,information_schema數(shù)據(jù)庫本身文件夾不要刪除)

4，還原數(shù)據(jù)庫

啟動數(shù)據(jù)庫服務(wù)

從命令行進(jìn)入MySQL Server 5.1/bin

還原全部數(shù)據(jù)庫，執(zhí)行命令mysql -uusername -pyourpassword /all.sql

大公告成。

淺談MySQL中的ibdata1

Data dictionary

Double write buffer

Insert buffer

Rollback segments

UNDO space

Forign key constraint system tables

大量事務(wù)，產(chǎn)生大量的undo log

有舊事物長時間未提交，產(chǎn)生大量舊undo log

file I/O性能差，purge進(jìn)展慢

32bit系統(tǒng)下bug

升級到5.6以上（64-bit），采用獨(dú)立undo表空間

增加purge線程數(shù) innodb_purge_threads

升高file I/O能力

事務(wù)及時提交，不要積壓

默認(rèn)打開autocommit=1

檢查開發(fā)框架，確認(rèn)autocommit=0的地方，事務(wù)結(jié)束后都有提交或回滾