zabbix 之 mysql优化-阿里云开发者社区

开发者社区> 数据库> 正文

zabbix 之 mysql优化

简介:

    zabbix运行的性能怎么样,有很大一部分是由mysql数据库的性能决定的。因为zabbix实时收集监控数据,数据库一直在增长,因此,监控系统上线一段时间后,就会发现各类问题都与mysql数据库有关。

    在这里我记录了zabbix监控系统上线后对mysql数据库所做的一些操作,有些内容不是提高mysql运行性能,也就不讲究是否切题了,只是觉得经常用到,也就如实记录下来,后面碰到类似操作也好查阅和参考。

    

    

    1、修改mysql的root账号的密码(在忘记密码或者不知道密码的时候有用):

    先停止mysql服务: 

1
    service mysqld stop

    使用--skip-grant-tables忽略密码启动:

1
    mysqld_safe --skip-grant-tables &

    进入mysql修改密码:

1
2
3
    use mysql
    update user set password=password('mysql'where user='root';
    flush privileges;

    

    

    2、清理大表数据。

        1)查看表占用空间情况:

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

    

        2)备份表trends_uint、history_uint、history。我在实际生产环境中,只保留上次的备份,没有留存更早的备份,因为更早以前的数据,没有留存的价值。

    

1
2
3
        mysqldump -uroot -p zabbix history> history.sql
        mysqldump -uroot -p zabbix trends_uint> trends_uint.sql
        mysqldump -uroot -p zabbix history_uint> history_uint.sql

        

        3)清空表trends_uint、history_uint、history中的数据:

    

1
2
3
        truncate table trends_uint;
        truncate table history;
        truncate table history_uint;

    

    3、修改单独表空间:

        1)备份数据

1
    mysqldump -uroot -p zabbix > zabbix.sql

        2)删除数据库:

1
    drop database zabbix;

        3)停止数据库:

1
    service mysqld stop

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

1
2
    cd /var/lib/mysql
    rm ib*

        5)增加innodb_file_per_table参数

1
    vi /etc/my.cnf

        6)在[mysqld]下设置

    innodb_file_per_table=1

        7)重启数据库:

1
    service mysqld start

        8)重新创建数据库:

1
    mysql -uroot -p
1
2
3
    create database zabbix character set utf8;
    grant all privileges on zabbix.* to zabbix@localhost identified by 'zabbix';
    flush privileges;

    

        9)导入zabbix数据库:

1
    mysql -uzabbix -pzabbix zabbix<zabbix.sql

    其中,zabbix.sql是上面第一步备份的数据库备份文件。

    

    

    4、增大innodb_log_file_size的方法:

    有时候,zabbix在使用数据库的过程中,特别是删除历史数据的过程中,会涉及到大数据操作,如果逻辑日志文件太小,会造成执行不成功,日志回滚的问题。下面是操作方法:

        1)暂停mysql, 

1
        service mysqld stop

        2)转移ib_logfile0和ib_logfile1,   

1
        mkdir /home/backup/ && mv /var/lib/mysql/ib_logfile*   /home/backup/

        3)编辑my.cnf  ,  增加  

        innodb_log_file_size=20M

        4)启动mysql,  

1
        service mysqld start

    5、生产环境中mysql配置文件my.cnf相关参数的配置。下面是my.cnf配置文件,参考了一些资料,最后形成了实际生产环境使用的参数。所在主机的配置是CPU 8核,内存16G。具体参数的含义我这里就不详细介绍了,参考相关资料。

    [root@oracle etc]# cat my.cnf

    [mysqld]

    datadir=/var/lib/mysql

    socket=/var/lib/mysql/mysql.sock

    user=mysql

    # Disabling symbolic-links is recommended to prevent assorted security risks

    symbolic-links=0

    innodb_buffer_pool_size=3G

    slow_query_log=/var/log/mysql.slow.log

    

    back_log               =500

    connect_timeout        =5

    delayed_insert_timeout =  300

    delayed_insert_limit   =100

    delayed_queue_size     =5000

    flush_time             =0

    interactive_timeout    =28800

    join_buffer_size       =1048540

    key_buffer_size        =1048540

    lower_case_table_names =  0

    long_query_time        =1

    max_allowed_packet     =1048576

    max_connections        =1000

    max_connect_errors     =10

    max_delayed_threads    =20

    max_heap_table_size    =256M

    max_join_size          =4294967295

    max_sort_length        =1024

    max_tmp_tables         =32

    max_write_lock_count   =4294967295

    net_buffer_length      =16384

    read_buffer_size       =134217728

    sort_buffer            =10M

    table_cache            =64

    thread_concurrency     =10

    tmp_table_size         =1048576

    thread_stack           =1M

    wait_timeout           =28800

    innodb-file-per-table=1

    innodb_log_file_size=20M

    innodb_flush_log_at_trx_commit=0

    [mysqld_safe]

    log-error=/var/log/mysqld.log

    pid-file=/var/run/mysqld/mysqld.pid

    

    

    6、移动mysql数据目录的方法:

        在有些情况下,由于当初没有规划好,mysql数据库安装目录所在的文件系统空间不足,这时需要将程序目录(包含数据文件目录)迁移到新的文件系统中。方法比较狡猾,是通过链接文件的方式解决的。下面是操作步骤:

        1)关闭mysql: 

1
        service mysqld stop

        2)复制文件:

1
        cp -R /var/lib/mysql /home/mysql

        其中/var/lib/mysql 目录是原来的目录 ,/home/mysql目录是新目录

        3)重命名目录: 

1
        mv /var/lib/mysql /var/lib/mysql-backup

        4)链接文件: 

1
        ln -s /home/mysql /var/lib/mysql

        5)修改目录权限:

1
2
        chown mysql:mysql /home/mysql -R
        chown -h mysql:mysql /var/lib/mysql

        6)重新启动mysql: 

1
        service mysqld start

    


    7、每月1日定时清理3个zabbix数据库的大表。

    这一块内容与第2条的内容有些重复,但这一块更加彻底,自动实现对大数据的清理,保持mysql数据库性能的持续性。

    增加定时配置,每月1日凌晨3点01分执行mysql_trunc_mysql.sh:

1
    crontab -e
1
    01 03 1 * * /backup/mysql_trunc_mysql.sh &>/dev/null


    下面是mysql_trunc_mysql.sh文件内容:

1
    vim /backup/mysql_trunc_zabbix.sh

   

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 #!/bin/bash
    #truncate 3 big tables :trends_uint,history,history_unit .
    #Every month's 1th 3:00 excute this script
    #Writen by yuweibing ,phonenumber:18080116652
    service zabbix_server stop
    echo "truncate table trends_uint;"   |mysql -uzabbix -pzabbix zabbix
    echo "truncate table history;"       |mysql -uzabbix -pzabbix zabbix
    echo "truncate table history_uint;"  |mysql -uzabbix -pzabbix zabbix
    sleep 1
    mysqldump -uroot -pmysql zabbix > /backup/zabbix.sql
    if [ $? -eq 0 ]
    then 
    echo "drop database zabbix;"|mysql  -uroot -pmysql
    else
    exit 1
    fi
     
    echo "create database zabbix character set utf8;"|mysql  -uroot -pmysql
    echo "grant all privileges on zabbix.* to zabbix@localhost identified by 'zabbix';"|mysql  -uroot -pmysql
    echo "flush privileges;"|mysql  -uroot -pmysql
     
    mysql -uzabbix -pzabbix zabbix</backup/zabbix.sql
    sleep 1
    service zabbix_server start
















本文转自yuweibing51CTO博客,原文链接:http://blog.51cto.com/yuweibing/1656425 ,如需转载请自行联系原作者










版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章