Zabbix-Server数据库mysql的libdata1mysqllog文件过大

        今天我们的zabbix-server机器上线半年时间,占用了500G空间,我一步步排查结果发现是/var/lib/MySQL/下的libdata1文件过大,已经达到了300G。我立即想到了zabbix的数据库原因,zabbix的数据库他的表模式是共享表空间模式,随着数据增长,ibdata1越来越大,性能方面会有影响,而且innodb把数据和索引都放在ibdata1下。

成都创新互联公司10多年企业网站建设服务;为您提供网站建设,网站制作,网页设计及高端网站定制服务,企业网站建设及推广,对成都餐厅设计等多个领域拥有多年的网站推广经验的网站建设公司。

共享表空间模式:

InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中:ibdata1,这样就感觉不爽,增删数据库的时候,ibdata1文件不会自动收缩,单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出,然后再导入解决这个问题。

独立表空间模式:

优点:

1.每个表都有自已独立的表空间。

2.每个表的数据和索引都会存在自已的表空间中。

3.可以实现单表在不同的数据库中移动。

4.空间可以回收(drop/truncate table方式操作表空间不能自动回收)

5.对于使用独立表空间的表,不管怎麽删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。

缺点:

单表增加比共享空间方式更大。

结论:

共享表空间在Insert操作上有一些优势,但在其它都没独立表空间表现好,所以我们要改成独立表空间。

当启用独立表空间时,请合理调整一下innodb_open_files 参数。

下面我们来讲下如何讲zabbix数据库修改成独立表空间模式

  1. 查看文件大小

  2. l 查看系统空间利用率

[root@zabbix ~]# df -h

Filesystem                     Size  Used Avail Use% Mounted on

/dev/mapper/vg_zabbix-lv_root 1000G  468G 482G  50% /

tmpfs                          7.8G     0 7.8G   0% /dev/shm

/dev/sda1                      485M   39M 421M   9% /boot

  • 查看文件空间占用情况

[root@zabbix ~]# du / -h --max-depth=1

12K     /.dbus

4.0K    /selinux

1.7M    /tmp

du: cannot access `/proc/7012/task/7012/fd/4': No suchfile or directory

du: cannot access `/proc/7012/task/7012/fdinfo/4': Nosuch file or directory

du: cannot access `/proc/7012/fd/4': No such file ordirectory

du: cannot access `/proc/7012/fdinfo/4': No such fileor directory

0       /proc

4.0K    /media

4.0K    /srv

8.0K    /mnt

1.2G    /root

0       /net

36K     /home

39M     /etc

462G    /var

29M     /boot

1.1G    /backup

7.7M    /bin

16K    /lost+found

3.5G    /usr

0       /misc

27M     /lib64

0       /sys

8.0K    /opt

15M     /sbin

164K    /dev

149M    /lib

468G    /

 

[root@zabbix ~]# cd /var/

[root@zabbix var]# du . -h --max-depth=1 

504K    ./spool

139G    ./log

4.0K    ./tmp

8.0K    ./db

103M    ./www

244K    ./run

16K     ./lock

4.0K    ./crash

4.0K   ./account

4.0K    ./local

12K     ./yp

33M     ./cache

4.0K   ./preserve

4.0K    ./cvs

4.0K    ./games

4.0K    ./gdm

4.0K    ./opt

8.0K    ./empty

324G    ./lib

4.0K    ./nis

463G    .

 

[root@zabbix var]# cd log/

[root@zabbix log]# ll

total 145373448

-rw-------. 1 root  root           2368 Oct 24  2016 anaconda.ifcfg.log

-rw-------. 1 root  root          35914 Oct 24  2016 anaconda.log

-rw-------. 1 root  root         181001 Oct 24  2016 anaconda.program.log

-rw-------. 1 root  root         300366 Oct 24  2016 anaconda.storage.log

-rw-------. 1 root  root         114857 Oct 24  2016 anaconda.syslog

-rw-------. 1 root  root          28547 Oct 24  2016 anaconda.xlog

-rw-------. 1 root  root         115596 Oct 24  2016 anaconda.yum.log

drwxr-x---. 2 root  root           4096 May  5 19:50 audit

-rw-r--r--  1root   root           2607 Jun  5 07:00 boot.log

-rw-------  1root   utmp              0 Jun  1 03:31 btmp

-rw-------  1root   utmp            768 May  5 18:38 btmp-20170601

drwxr-xr-x. 2 root  root           4096 Oct 24  2016 ConsoleKit

-rw-------  1root   root          24280 Jun  5 10:30 cron

-rw-------  1root   root         128516 May 14 03:23 cron-20170514

-rw-------  1root   root         129746 May 21 03:40 cron-20170521

-rw-------  1root   root         128931 May 28 03:12 cron-20170528

-rw-------  1root   root         129139 Jun  4 03:31 cron-20170604

drwxr-xr-x. 2 lp    sys            4096 Aug 17  2013 cups

-rw-r--r--  1root   root          69891 Jun  5 07:00 dmesg

-rw-r--r--  1root   root          69891 May 29 07:00 dmesg.old

-rw-------  1root   root              0 Jan  1 03:32 dracut.log

-rw-r--r--. 1 root  root         345367 Oct 24  2016 dracut.log-20170101

drwxrwx--T. 2 root  gdm            4096 Oct 24  2016 gdm

drwx------. 2 root  root           4096 Jun  4 03:31 httpd

-rw-r--r--. 1 root  root         145708 Jun  5 10:03 lastlog

-rw-------  1root   root           1629 Jun  5 07:00 maillog

-rw-------  1root   root           4465 May 13 08:19 maillog-20170514

-rw-------  1root   root           5153 May 20 06:42 maillog-20170521

-rw-------  1root   root           5153 May 27 06:30 maillog-20170528

-rw-------  1root   root           5158 Jun  3 06:28 maillog-20170604

-rw-------  1root   root          78017 Jun  5 10:05 messages

-rw-------  1root   root        1317112 May 14 03:05 messages-20170514

-rw-------  1root   root        1068946 May 21 03:06 messages-20170521

-rw-------  1root   root          86245 May 28 03:05 messages-20170528

-rw-------  1root   root          86065 Jun  4 03:06 messages-20170604

-rw-r-----. 1 mysql  mysql 148857106048 Jun  5 10:30mysqld.log

drwxr-xr-x. 2 ntp   ntp            4096 Nov 24  2013 ntpstats

-rw-r--r--. 1 root  root             89 Oct 24  2016 pm-powersave.log

drwx------. 2 root  root           4096 Aug 23  2010 ppp

drwxr-xr-x. 2 root  root           4096 Oct 24  2016 prelink

drwxr-xr-x. 2 root  root           4096 Jun  5 00:00 sa

drwx------. 3 root  root           4096 Oct 24  2016 samba

-rw-------  1root   root            855 Jun  5 10:03 secure

-rw-------  1root   root           1855 May 12 18:54 secure-20170514

-rw-------  1root   root            207 May 15 07:00 secure-20170521

-rw-------  1root   root           1455 May 27 19:02 secure-20170528

-rw-------  1root   root           1503 May 31 23:22 secure-20170604

-rw-------. 1 root  root              0 Oct 24  2016 spice-vdagent.log

-rw-------  1root   root              0 Jun  4 03:31 spooler

-rw-------  1root   root              0 May  7 03:47 spooler-20170514

-rw-------  1root   root              0 May 14 03:23 spooler-20170521

-rw-------  1root   root              0 May 21 03:40 spooler-20170528

-rw-------  1root   root              0 May 28 03:12 spooler-20170604

drwxr-x---. 2 root  root           4096 Nov 23  2013 sssd

-rw-------. 1 root  root              0 Oct 24  2016 tallylog

-rw-r--r--. 1 root  root              0 Oct 24  2016 wpa_supplicant.log

-rw-rw-r--. 1 root  utmp         325632 Jun  5 10:03 wtmp

-rw-r--r--. 1 root  root          45892 Oct 25  2016 Xorg.0.log

-rw-r--r--. 1 root  root          44521 Oct 24  2016 Xorg.0.log.old

-rw-r--r--. 1 root  root          26974 Oct 24  2016 Xorg.9.log

-rw-------  1root   root           1663 Apr 12 15:04 yum.log

-rw-------. 1 root  root           6936 Dec  2  2016yum.log-20170101

drwxr-xr-x. 2 zabbix zabbix         4096 Oct 28  2016 zabbix

 

  • mysql.log日志文件占用空间过大,查看详细日志内容

[root@zabbix log]# tail mysqld.log

InnoDB: Your database may be corrupt or you may havecopied the InnoDB

InnoDB: tablespace but not the InnoDB log files. See

InnoDB:http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

InnoDB: for more information.

170605 10:30:29 InnoDB: Error: page 14091764 log sequence number 462 2253153679

InnoDB: is in the future! Current system log sequencenumber 76 3553747713.

InnoDB: Your database may be corrupt or you may havecopied the InnoDB

InnoDB: tablespace but not the InnoDB log files. See

InnoDB:http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

InnoDB: for more information.

 

  • 查看数据库文件夹硬盘空间使用情况

[root@zabbix log]# cd /var/lib/mysql

[root@zabbix mysql]# ll -h

total 324G

-rw-rw----. 1 mysql mysql 324G Jun  5 15:15 ibdata1

-rw-rw----  1mysql mysql 5.0M Jun  5 15:15 ib_logfile0

-rw-rw----  1mysql mysql 5.0M Jun  5 15:15 ib_logfile1

drwx------. 2 mysql mysql 4.0K Oct 28  2016 mysql

srwxrwxrwx  1mysql mysql    0 Jun  5 14:54 mysql.sock

drwx------. 2 mysql mysql 4.0K Jun  5 12:39 zabbix

-rw-r--r--  1root  root  251M Jun 5 15:15 zabbix02.sql

-rw-r--r--  1root  root     0 Jun 5 15:06 zabbix.sql

                  

发现ibdata1 文件占用空间过大

2.清除zabbix数据库历史数据

1)查看哪些表的历史数据比较多

mysql> SELECT table_name AS "Tables",round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"FROM information_schema.TABLES WHERE table_schema = 'zabbix' ORDER BY(data_length + index_length) DESC;

+----------------------------+------------+

| Tables                     | Size in MB |

+----------------------------+------------+

| events                     |  181851.00 |

| history_uint               |   92227.30 |

| history                    |    4590.27|

| trends_uint                |    3851.88 |

| history_str                |     309.39 |

| trends                     |     156.75 |

| history_log                |      89.17 |

| items                      |      36.41 |

| item_discovery             |       6.23 |

| items_applications         |      6.17 |

| alerts                     |       3.45 |

| triggers                   |       1.69 |

| p_w_picpaths                     |       1.53 |

| application_template       |      0.63 |

| auditlog                   |       0.55 |

| acknowledges               |       0.55 |

| functions                  |       0.48 |

| graphs                     |       0.47 |

| graphs_items               |       0.44 |

| applications               |       0.41 |

| hosts                      |       0.33 |

| hosts_groups               |       0.27 |

| hostmacro                  |      0.22 |

| history_text               |       0.22 |

| trigger_discovery          |       0.20 |

| screens_items              |       0.16 |

| profiles                   |       0.16 |

| graph_discovery            |       0.13 |

| auditlog_details          |       0.13 |

| hosts_templates            |       0.11 |

| sysmaps_elements           |       0.09 |

| host_discovery             |       0.09 |

| interface                  |       0.09 |

| httptest                   |       0.08 |

| sysmaps                    |       0.08 |

| sysmaps_links              |       0.06 |

| scripts                    |       0.06 |

| group_prototype            |       0.06 |

| interface_discovery        |      0.06 |

| drules                     |       0.05 |

| icon_mapping               |       0.05 |

| users_groups               |       0.05 |

| slideshows                 |       0.05 |

| media                      |       0.05 |

| icon_map                   |       0.05 |

| service_alarms             |       0.05 |

| opgroup                    |       0.05 |

| slideshow_usrgrp           |       0.05 |

| httptestitem               |       0.05 |

| sysmaps_link_triggers      |      0.05 |

| item_application_prototype |       0.05 |

| slideshow_user             |       0.05 |

| application_prototype      |      0.05 |

| maintenances_windows       |      0.05 |

| slides                     |       0.05 |

| application_discovery      |      0.05 |

| maintenances_hosts         |      0.05 |

| httpstepitem              |       0.05 |

| screens                    |       0.05 |

| config                     |       0.05 |

| trigger_depends            |       0.05 |

| optemplate                 |       0.05 |

| maintenances_groups        |      0.05 |

| sysmap_usrgrp              |       0.05 |

| screen_usrgrp              |       0.05 |

| opcommand_hst              |       0.05 |

| opmessage_usr              |       0.05 |

| actions                    |       0.05 |

| maintenances               |       0.05 |

| sysmap_user                |       0.05 |

| screen_user                |       0.05 |

| opcommand_grp              |       0.05 |

| services_links             |       0.05 |

| dservices                  |       0.05 |

| opmessage_grp              |       0.05 |

| rights                     |       0.05 |

| services                   |       0.03 |

| opmessage                  |       0.03 |

| usrgrp                     |       0.03 |

| sysmap_element_url         |      0.03 |

| regexps                   |       0.03 |

| media_type                 |       0.03 |

| dhosts                     |       0.03 |

| proxy_history              |       0.03 |

| item_condition             |       0.03 |

| users                      |       0.03 |

| globalmacro                |       0.03 |

| proxy_dhistory             |       0.03 |

| mappings                   |       0.03 |

| groups                     |       0.03 |

| dchecks                    |       0.03 |

| proxy_autoreg_host         |      0.03 |

| operations                 |       0.03 |

| expressions                |       0.03 |

| group_discovery            |       0.03 |

| opconditions               |       0.03 |

| sessions                   |       0.03 |

| httpstep                   |       0.03 |

| conditions                 |       0.03 |

| services_times             |       0.03 |

| escalations                |       0.03 |

| autoreg_host               |       0.03 |

| valuemaps                  |       0.03 |

| sysmap_url                 |       0.03 |

| graph_theme                |       0.03 |

| opcommand                  |       0.03 |

| timeperiods                |       0.02 |

| opinventory                |       0.02 |

| globalvars                 |       0.02 |

| host_inventory             |      0.02 |

| dbversion                  |       0.02 |

| housekeeper                |       0.02 |

| ids                        |       0.02 |

+----------------------------+------------+

113 rows in set (7.31 sec)

可以看到events、history和history_uint这三个表的历史数据最多。

由于数据量太大,按照普通的方式delete数据的话基本上不太可能。

所以决定直接采用truncatetable的方式来快速清空这些表的数据,再使用mysqldump导出数据,删除共享表空间数据文件,重新导入数据。其中events空间过大,尝试truncatetable无果,果断抛弃

2)停止相关服务,避免写入数据

[root@zabbix ~]#/etc/init.d/zabbix_server stop

[root@zabbix ~]#/etc/init.d/httpd stop

3)清空历史数据

[root@zabbix ~]#mysql -uroot -p

mysql > use zabbix;

Database changed

 

mysql > truncate table history;

Query OK, 123981681 rows affected (0.23 sec)

 

mysql > optimize table history;

1 row in set (0.02 sec)

 

mysql > truncate table history_uint;

Query OK, 57990562 rows affected (0.12 sec)

 

mysql > optimize table history_uint;

1 row in set (0.03 sec)

3.备份数据库

[root@zabbix~]#mysqldump -uroot -p zabbix > /data/zabbix.sql

4.停止数据库并删除共享表空间数据文件

1)停止数据库

[root@zabbix ~]#/etc/init.d/mysqld stop

2)删除共享表空间数据文件

[root@zabbix ~]#cd /var/lib/mysql

[root@zabbix ~]#rm -rf ib*

[root@zabbix ~]#mkdir /tmp/zabbix

 

[root@zabbix ~]#mv zabbix/* /tmp/zabbix

 

5.增加innodb_file_per_table参数

[root@zabbix ~]#vi /etc/my.cnf

在[mysqld]下设置

innodb_file_per_table=1

6.启动mysql

[root@zabbix ~]#/etc/init.d/mysqld start

7.查看innodb_file_per_table参数是否生效

[root@zabbix ~]#mysql -uroot -p

mysql> show variables like '%per_table%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

8.重新导入数据库

mysql >use zabbix;

mysql >source mysql.sql

9.最后,恢复相关服务进程

[root@zabbix ~]#/etc/init.d/zabbix_server start

[root@zabbix ~]#/etc/init.d/httpd start

恢复完服务之后,查看/分区的容量就下去了,之前是50%,处理完之后变成了2%。可见其成效

[root@zabbix ~]# df -h

Filesystem                     Size  Used Avail Use% Mounted on

/dev/mapper/vg_zabbix-lv_root 1000G   13G 936G   2% /

tmpfs                          7.8G     0 7.8G   0% /dev/shm

/dev/sda1                      485M   39M 421M   9% /boot

 

 

mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb,table_rows from information_schema.tables where table_schema='zabbix' order bytable_rows;

+----------------------------+---------------+------------+

| table_name                 | total_mb      | table_rows |

+----------------------------+---------------+------------+

| slideshows                 |    0.04687500 |          0 |

| globalvars                |    0.01562500 |          0 |

| proxy_history              |    0.03125000 |          0 |

| icon_map                   |    0.04687500 |          0 |

| sysmaps_links              |    0.06250000 |          0 |

| service_alarms             |    0.04687500 |          0 |

| opinventory                |    0.01562500 |          0 |

| slideshow_usrgrp           |   0.04687500 |          0 |

| proxy_dhistory             |    0.03125000 |          0 |

| sysmaps_link_triggers      |   0.04687500 |          0 |

| proxy_autoreg_host         |   0.03125000 |          0 |

| application_prototype      |   0.04687500 |          0 |

| maintenances_windows       |   0.04687500 |          0 |

| item_application_prototype |    0.04687500 |          0 |

| host_inventory             |    0.01562500 |          0 |

| slideshow_user             |    0.04687500 |          0 |

| application_discovery      |   0.04687500 |          0 |

| maintenances_hosts         |   0.04687500 |          0 |

| opconditions               |    0.03125000 |          0 |

| slides                     |    0.04687500 |          0 |

| maintenances_groups        |   0.04687500 |          0 |

| sysmap_usrgrp              |    0.04687500 |          0 |

| screen_usrgrp              |   0.04687500 |          0 |

| opcommand_hst              |    0.04687500 |          0 |

| maintenances               |    0.04687500 |          0 |

| housekeeper                |    0.01562500 |          0 |

| sysmap_user                |    0.04687500 |          0 |

| screen_user                |    0.04687500 |          0 |

| opcommand_grp              |    0.04687500 |          0 |

| services_times             |    0.03125000 |          0 |

| escalations                |    0.03125000 |          0 |

| sysmap_url                 |    0.03125000 |          0 |

| opcommand                  |    0.03125000 |          0 |

| services_links             |    0.04687500 |          0 |

| sysmap_element_url         |   0.03125000 |          0 |

| icon_mapping              |    0.04687500 |          0 |

| timeperiods                |    0.01562500 |          0 |

| services                   |    0.03125000 |          0 |

| httptest                   |    0.07812500 |          1 |

| sysmaps_elements           |   0.09375000 |          1 |

| dbversion                  |    0.01562500 |          1 |

| sysmaps                    |    0.07812500 |          1 |

| config                     |    0.04687500 |          1 |

| autoreg_host               |    0.03125000 |          1 |

| globalmacro                |    0.03125000 |          2 |

| optemplate                 |    0.04687500 |          2 |

| opmessage_usr              |    0.04687500 |          2 |

| graph_theme                |    0.03125000 |          2 |

| media                      |    0.04687500 |          3 |

| users_groups               |    0.04687500 |          3 |

| httptestitem               |    0.04687500 |          3 |

| scripts                    |    0.06250000 |          3 |

| dchecks                    |    0.03125000 |          3 |

| opgroup                    |    0.04687500 |          3 |

| users                      |    0.03125000 |          3 |

| httpstep                   |    0.03125000 |          3 |

| regexps                    |    0.03125000 |          3 |

| media_type                 |    0.03125000 |          3 |

| drules                     |    0.04687500 |          3 |

| expressions                |    0.03125000 |          4 |

| opmessage_grp              |    0.04687500 |          4 |

| usrgrp                     |    0.03125000 |          5 |

| opmessage                  |    0.03125000 |          6 |

| httpstepitem               |    0.04687500 |          9 |

| actions                   |    0.04687500 |          9 |

| dhosts                     |    0.03125000 |         12 |

| operations                 |    0.03125000 |         14 |

| dservices                  |    0.04687500 |         14 |

| group_prototype            |   0.06250000 |         15 |

| conditions                 |    0.03125000 |         17 |

| rights                     |    0.04687500 |         17 |

| group_discovery            |   0.03125000 |         19 |

| valuemaps                  |    0.03125000 |         21 |

| screens                    |    0.04687500 |         24 |

| groups                     |    0.03125000 |         31 |

| item_condition             |    0.03125000 |         31 |

| sessions                   |    0.03125000 |         39 |

| ids                        |    0.01562500 |         46 |

| trigger_depends            |   0.04687500 |         51 |

| mappings                   |    0.03125000 |        100 |

| hosts_templates            |   0.12500000 |        181 |

| p_w_picpaths                     |    1.53125000 |        192 |

| host_discovery             |    0.09375000 |        361 |

| interface_discovery        |   0.06250000 |        515 |

| hosts                      |    0.32812500 |        516 |

| profiles                   |    0.17187500 |        659 |

| interface                  |    0.09375000 |        691 |

| auditlog_details           |   0.12500000 |        707 |

| events                     |    0.17187500 |        759 |

| screens_items              |    0.15625000 |        805 |

| history_text               |    0.18750000 |       1201 |

| graph_discovery            |   0.12500000 |       1217 |

| graphs                     |    0.46875000 |       1330 |

| hosts_groups              |    0.26562500 |       1381 |

| hostmacro                  |    0.21875000 |       1569 |

| trigger_discovery          |   0.20312500 |       2017 |

| acknowledges               |    0.54687500 |       2408 |

| auditlog                   |    0.53125000 |       2546 |

| graphs_items               |    0.43750000 |       2706 |

| triggers                   |    0.68750000 |       2900 |

| functions                  |    0.50000000 |       3134 |

| application_template       |   0.51562500 |       4364 |

| applications               |    0.42187500 |       4512 |

| history                    |    0.46875000 |       4780 |

| alerts                     |    3.46875000 |       6242 |

| item_discovery             |    5.54687500 |      26567 |

| items_applications         |   6.54687500 |      30181 |

| items                      |   24.12500000 |      34425 |

| history_uint               |   12.03125000 |     123327 |


新闻标题:Zabbix-Server数据库mysql的libdata1mysqllog文件过大
网页网址:http://bzwzjz.com/article/goccee.html

其他资讯

Copyright © 2007-2020 广东宝晨空调科技有限公司 All Rights Reserved 粤ICP备2022107769号
友情链接: 网站制作 网站制作 移动手机网站制作 成都网站设计 温江网站设计 成都定制网站建设 成都网站制作 网站制作 响应式网站设计 阿坝网站设计 成都网站设计 营销网站建设 定制网站设计 成都企业网站制作 成都网站建设 高端网站设计推广 重庆手机网站建设 成都响应式网站建设 营销型网站建设 企业网站建设 定制网站制作 成都h5网站建设