今天我們的zabbix-server機器根空間不夠了，我一步步排查結果發現是/var/lib/mysql/下的libdata1檔案過大，已經達到了41G。我立即想到了zabbix的資料庫原因，随後百度、谷歌才知道zabbix的資料庫他的表模式是共享表空間模式，随着資料增長，ibdata1 越來越大，性能方面會有影響，而且innodb把資料和索引都放在ibdata1下。
共享表空間模式：
InnoDB 預設會将所有的資料庫InnoDB引擎的表資料存儲在一個共享空間中：ibdata1，這樣就感覺不爽，增删資料庫的時候，ibdata1檔案不會自動收縮，單個資料庫的備份也将成為問題。通常隻能将資料使用mysqldump 導出，然後再導入解決這個問題。
獨立表空間模式：
優點：
1.每個表都有自已獨立的表空間。
2.每個表的資料和索引都會存在自已的表空間中。
3.可以實作單表在不同的資料庫中移動。
4.空間可以回收（drop/truncate table方式操作表空間不能自動回收）
5.對于使用獨立表空間的表，不管怎麼删除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。
缺點：
單表增加比共享空間方式更大。
結論：
共享表空間在Insert操作上有一些優勢，但在其它都沒獨立表空間表現好，是以我們要改成獨立表空間。
當啟用獨立表空間時，請合理調整一下 innodb_open_files 參數。
下面我們來講下如何講zabbix資料庫修改成獨立表空間模式
1.檢視檔案大小
1
2
3
4
5
6
<code>[root@localhost ~]</code><code>#cd /var/lib/mysql</code>
<code>[root@localhost ~]</code><code>#ls -lh</code>
<code>-rw-rw---- 1 mysql mysql 41G Nov 24 13:31 ibdata1</code>
<code>-rw-rw---- 1 mysql mysql 5.0M Nov 24 13:31 ib_logfile0</code>
<code>-rw-rw---- 1 mysql mysql 5.0M Nov 24 13:31 ib_logfile1</code>
<code>drwx------ 2 mysql mysql 1.8M Nov 24 13:31 zabbix</code>
大家可以看到這是沒修改之前的共享表資料空間檔案ibdata1大小已經達到了41G
2.清除zabbix資料庫曆史資料
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<code>1)檢視哪些表的曆史資料比較多</code>
<code>[root@localhost ~]</code><code>#mysql -uroot -p</code>
<code>mysql > </code><code>select</code> <code>table_name, (data_length+index_length)</code><code>/1024/1024</code> <code>as total_mb, table_rows from information_schema.tables where table_schema=</code><code>'zabbix'</code><code>;</code>
<code>+-----------------------+---------------+------------+</code>
<code>| table_name | total_mb | table_rows |</code>
<code>| acknowledges | 0.06250000 | 0 |</code>
<code>....</code>
<code>| help_items | 0.04687500 | 103 |</code>
<code>| </code><code>history</code> <code>| 1020.00000000 | 123981681 |</code>
<code>| history_log | 0.04687500 | 0 |</code>
<code>...</code>
<code>| history_text | 0.04687500 | 0 |</code>
<code>| history_uint | 3400.98437500 | 34000562 |</code>
<code>| history_uint_sync | 0.04687500 | 0 |</code>
<code>可以看到</code><code>history</code><code>和history_uint這兩個表的曆史資料最多。</code>
<code>另外就是trends,trends_uint中也存在一些資料。</code>
<code>由于資料量太大，按照普通的方式delete資料的話基本上不太可能。</code>
<code>是以決定直接采用truncate table的方式來快速清空這些表的資料，再使用mysqldump導出資料，删除共享表空間資料檔案，重新導入資料。</code>
<code>2）停止相關服務，避免寫入資料</code>
<code>[root@localhost ~]</code><code>#/etc/init.d/zabbix_server stop</code>
<code>[root@localhost ~]</code><code>#/etc/init.d/httpd stop</code>
<code>3）清空曆史資料</code>
<code>mysql > use zabbix;</code>
<code>Database changed</code>
<code>mysql > truncate table </code><code>history</code><code>;</code>
<code>Query OK, 123981681 rows affected (0.23 sec) </code>
<code>mysql > optimize table </code><code>history</code><code>; </code>
<code>1 row </code><code>in</code> <code>set</code> <code>(0.02 sec)</code>
<code>mysql > truncate table history_uint;</code>
<code>Query OK, 57990562 rows affected (0.12 sec) </code>
<code>mysql > optimize table history_uint;</code>
<code>1 row </code><code>in</code> <code>set</code> <code>(0.03 sec)</code>
3.備份資料庫由于我/下的空間不足是以我挂載了一個NFS過來
<code>[root@localhost ~]</code><code>#mysqldump -uroot -p zabbix > /data/zabbix.sql</code>
4.停止資料庫并删除共享表空間資料檔案
<code>1)停止資料庫</code>
<code>[root@localhost ~]</code><code>#/etc/init.d/mysqld stop</code>
<code>2）删除共享表空間資料檔案</code>
<code>[root@localhost ~]</code><code>#rm -rf ib*</code>
5.增加innodb_file_per_table參數
<code>[root@localhost ~]</code><code>#vi /etc/my.cnf</code>
<code>在[mysqld]下設定</code>
<code>innodb_file_per_table=1</code>
6.啟動mysql
<code>[root@localhost ~]</code><code>#/etc/init.d/mysqld start</code>
7.檢視innodb_file_per_table參數是否生效
<code>mysql> show variables like </code><code>'%per_table%'</code><code>;</code>
<code>+-----------------------+-------+</code>
<code>| Variable_name | Value |</code>
<code>| innodb_file_per_table | ON |</code>
<code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
8.重新導入資料庫
<code>[root@localhost ~]</code><code>#mysqldump -uroot -p zabbix < /data/zabbix.sql</code>
9.最後，恢複相關服務程序
<code>[root@localhost ~]</code><code>#/etc/init.d/zabbix_server start</code>
<code>[root@localhost ~]</code><code>#/etc/init.d/httpd start</code>
恢複完服務之後，檢視/分區的容量就下去了，之前是99%，處理完之後變成了12%。可見其成效
本文轉自Devin 51CTO部落格，原文連結:http://blog.51cto.com/devingeng/1582033
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)