mysql的innodb擴(kuò)容
為了添加一個(gè)數(shù)據(jù)文件到表空間中，首先要關(guān)閉 MySQL 數(shù)據(jù)庫，編輯 my.cnf 文件，確認(rèn)innodb ibdata文件的實(shí)際情況和my.cnf的配置是否一致，這里有兩種情況：
1.my.cnf的配置

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

如果當(dāng)前數(shù)據(jù)庫正在使用ibdata1，或者使用ibdata2，但ibdata2沒有超過10G，則對my.cnf配置直接改成：

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

2.如果設(shè)置了最后一個(gè)ibdata自動(dòng)擴(kuò)展時(shí)，有可能最后一個(gè)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 

這時(shí)，需要精確的計(jì)算ibdata2的大小 15360M，修改：

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

重啟mysql。
注意：
1、擴(kuò)容前注意磁盤空間是否足夠。
2、restart后關(guān)注是否生成了新的ibdata。
更多說明：
如果，最后一個(gè)文件以關(guān)鍵字 autoextend 來描述，那么編輯 my.cnf 的過程中，必須檢查最后一個(gè)文件的尺寸，并使它向下接近于 1024 * 1024 bytes (= 1 MB) 的倍數(shù)（比方說現(xiàn)在autoextend 的/ibdata/ibdata1為18.5M，而在舊的my.ini中為10M，則需要修改為innodb_data_file_path = /ibdata/ibdata1:19M; 且必須是19M，如果指定20M，就會(huì)報(bào)錯(cuò)。），并在 innodb_data_file_path 中明確指定它的尺寸。然后你可以添加另一個(gè)數(shù)據(jù)文件。記住只有 innodb_data_file_path 中的最后一個(gè)文件可以被指定為 auto-extending。
一個(gè)例子：假設(shè)起先僅僅只有一個(gè) auto-extending 數(shù)據(jù)文件 ibdata1 ，這個(gè)文件接近于 988 MB。下面是添加了另一個(gè) auto-extending 數(shù)據(jù)文件后的可能示例 。

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

ibdata1 瘦身

0. ibdata1里存了什么

當(dāng)你啟用了 innodb_file_per_table，表被存儲(chǔ)在他們自己的表空間里，但是共享表空間仍然在存儲(chǔ)其它的 InnoDB 內(nèi)部數(shù)據(jù)：
（1）數(shù)據(jù)字典，也就是 InnoDB 表的元數(shù)據(jù)
（2）變更緩沖區(qū)
（3）雙寫緩沖區(qū)
（4）撤銷日志

其中的一些在 Percona 服務(wù)器上可以被配置來避免增長過大的。例如你可以通過 innodb_ibuf_max_size 設(shè)置最大變更緩沖區(qū)，或設(shè)置 innodb_doublewrite_file 來將雙寫緩沖區(qū)存儲(chǔ)到一個(gè)分離的文件。

MySQL 5.6 版中你也可以創(chuàng)建外部的撤銷表空間，所以它們可以放到自己的文件來替代存儲(chǔ)到 ibdata1。

1. 什么引起 ibdata1 增長迅速？

當(dāng) MySQL 出現(xiàn)問題通常我們需要執(zhí)行的第一個(gè)命令是：

SHOW ENGINE INNODB STATUS/G

這將展示給我們一些很有價(jià)值的信息。我們從** TRANSACTION（事務(wù)）**部分開始檢查，然后我們會(huì)發(fā)現(xiàn)這個(gè)：

---TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id >= 36F, sees < 36F

這是一個(gè)最常見的原因，一個(gè)14天前創(chuàng)建的相當(dāng)老的事務(wù)。這個(gè)狀態(tài)是活動(dòng)的，這意味著 InnoDB 已經(jīng)創(chuàng)建了一個(gè)數(shù)據(jù)的快照，所以需要在撤銷日志中維護(hù)舊頁面，以保障數(shù)據(jù)庫的一致性視圖，直到事務(wù)開始。如果你的數(shù)據(jù)庫有大量的寫入任務(wù)，那就意味著存儲(chǔ)了大量的撤銷頁。

如果你找不到任何長時(shí)間運(yùn)行的事務(wù)，你也可以監(jiān)控INNODB STATUS 中的其他的變量，“History list length（歷史記錄列表長度）”展示了一些等待清除操作。這種情況下問題經(jīng)常發(fā)生，因?yàn)榍宄€程（或者老版本的主線程）不能像這些記錄進(jìn)來的速度一樣快地處理撤銷。

2. 我怎么檢查什么被存儲(chǔ)到了 ibdata1 里了？

很不幸，MySQL 不提供查看什么被存儲(chǔ)到 ibdata1 共享表空間的信息，但是有兩個(gè)工具將會(huì)很有幫助。第一個(gè)是馬克·卡拉漢制作的一個(gè)修改版 innochecksum ，它發(fā)布在這個(gè)漏洞報(bào)告里。

它相當(dāng)易于使用：

# ./innochecksum /var/lib/mysql/ibdata1
0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 max index_id

全部的 20608 中有 19272 個(gè)撤銷日志頁。這占用了表空間的 93%。

第二個(gè)檢查表空間內(nèi)容的方式是杰里米·科爾制作的 InnoDB Ruby 工具。它是個(gè)檢查 InnoDB 的內(nèi)部結(jié)構(gòu)的更先進(jìn)的工具。例如我們可以使用 space-summary 參數(shù)來得到每個(gè)頁面及其數(shù)據(jù)類型的列表。我們可以使用標(biāo)準(zhǔn)的 Unix 工具來統(tǒng)計(jì)撤銷日志頁的數(shù)量：

# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l
19272

盡管這種特殊的情況下，innochedcksum 更快更容易使用，但是我推薦你使用杰里米的工具去了解更多的 InnoDB 內(nèi)部的數(shù)據(jù)分布及其內(nèi)部結(jié)構(gòu)。

好，現(xiàn)在我們知道問題所在了。

3. ibdata1 瘦身方案
其中的一些在 Percona 服務(wù)器上可以被配置來避免增長過大的。例如你可以通過 innodb_ibuf_max_size 設(shè)置最大變更緩沖區(qū)，或設(shè)置 innodb_doublewrite_file 來將雙寫緩沖區(qū)存儲(chǔ)到一個(gè)分離的文件。

MySQL 5.6 版中你也可以創(chuàng)建外部的撤銷表空間，所以它們可以放到自己的文件來替代存儲(chǔ)到 ibdata1。

通常不能移除 InnoDB 的數(shù)據(jù)文件。為了減小數(shù)據(jù)文件的大小，你必須使用 mysqldump 來轉(zhuǎn)儲(chǔ)(dump)所有的數(shù)據(jù)表，再重新建立一個(gè)新的數(shù)據(jù)庫，并將數(shù)據(jù)導(dǎo)入新的數(shù)據(jù)庫中。具體步驟如下：
（1）備份數(shù)據(jù)庫
mysqldump -uroot -p123456 --default-character-set=utf8 --opt --extended-insert=true --triggers -R --hex-blob --single-transaction --no-autocommit  test > db_name.sql 
（2）停止數(shù)據(jù)庫

service mysqld stop 

（3）刪除相關(guān)文件

ibdata1 
ib_logfile* 
mysql-bin.index 

（4）手動(dòng)刪除除Mysql之外所有數(shù)據(jù)庫文件夾，然后啟動(dòng)數(shù)據(jù)庫 

service mysqld start 

（5）還原數(shù)據(jù)

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

主要是使用Mysqldump時(shí)的一些參數(shù)，建議在使用前看一個(gè)說明再操作。另外備份前可以先查看一下當(dāng)前數(shù)據(jù)庫里哪些表占用空間大，把一些不必要的給truncate table掉。這樣省些空間和時(shí)間

最新評論