今天我们的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=)