zabbix 之 mysql優化

zabbix運作的性能怎麼樣，有很大一部分是由mysql資料庫的性能決定的。因為zabbix實時收集監控資料，資料庫一直在增長，是以，監控系統上線一段時間後，就會發現各類問題都與mysql資料庫有關。

在這裡我記錄了zabbix監控系統上線後對mysql資料庫所做的一些操作，有些内容不是提高mysql運作性能，也就不講究是否切題了，隻是覺得經常用到，也就如實記錄下來，後面碰到類似操作也好查閱和參考。

1、修改mysql的root賬号的密碼（在忘記密碼或者不知道密碼的時候有用）：

先停止mysql服務：

<code> </code><code>service mysqld stop</code>

使用--skip-grant-tables忽略密碼啟動：

<code> </code><code>mysqld_safe --skip-grant-tables &</code>

進入mysql修改密碼：

<code> </code><code>use mysql</code>

<code> </code><code>update</code> <code>user</code> <code>set</code> <code>password</code><code>=</code><code>password</code><code>(</code><code>'mysql'</code><code>) </code><code>where</code> <code>user</code><code>=</code><code>'root'</code><code>;</code>

<code> </code><code>flush </code><code>privileges</code><code>;</code>

2、清理大表資料。

1）檢視表占用空間情況：

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

2）備份表trends_uint、history_uint、history。我在實際生産環境中，隻保留上次的備份，沒有留存更早的備份，因為更早以前的資料，沒有留存的價值。

<code> </code><code>mysqldump -uroot -p zabbix history> history.sql</code>

<code> </code><code>mysqldump -uroot -p zabbix trends_uint> trends_uint.sql</code>

<code> </code><code>mysqldump -uroot -p zabbix history_uint> history_uint.sql</code>

3）清空表trends_uint、history_uint、history中的資料：

<code> </code><code>truncate</code> <code>table</code> <code>trends_uint;</code>

<code> </code><code>truncate</code> <code>table</code> <code>history;</code>

<code> </code><code>truncate</code> <code>table</code> <code>history_uint;</code>

3、修改單獨表空間：

1）備份資料

<code> </code><code>mysqldump -uroot -p zabbix > zabbix.sql</code>

2）删除資料庫：

<code> </code><code>drop</code> <code>database</code> <code>zabbix;</code>

3）停止資料庫：

4）删除共享表空間資料檔案

<code> </code><code>$ </code><code>cd</code> <code>/var/lib/mysql</code>

5）增加innodb_file_per_table參數

6）在[mysqld]下設定

innodb_file_per_table=1

7）重新開機資料庫：

<code> </code><code>service mysqld start</code>

8）重新建立資料庫：

<code> </code><code>mysql -uroot -p</code>

<code> </code><code>create</code> <code>database</code> <code>zabbix </code><code>character</code> <code>set</code> <code>utf8;</code>

<code> </code><code>grant</code> <code>all</code> <code>privileges</code> <code>on</code> <code>zabbix.* </code><code>to</code> <code>zabbix@localhost identified </code><code>by</code> <code>'zabbix'</code><code>;</code>

9）導入zabbix資料庫：

<code> </code><code>mysql -uzabbix -pzabbix zabbix<zabbix.sql</code>

其中，zabbix.sql是上面第一步備份的資料庫備份檔案。

4、增大innodb_log_file_size的方法:

有時候，zabbix在使用資料庫的過程中，特别是删除曆史資料的過程中，會涉及到大資料操作，如果邏輯日志檔案太小，會造成執行不成功，日志復原的問題。下面是操作方法：

1）暫停mysql,

<code> </code><code>service mysqld stop</code>

2）轉移ib_logfile0和ib_logfile1,

<code> </code><code>mkdir</code> <code>/home/backup/</code> <code>&& </code><code>mv</code> <code>/var/lib/mysql/ib_logfile</code><code>* </code><code>/home/backup/</code>

3）編輯my.cnf , 增加

innodb_log_file_size=20M

4）啟動mysql,

<code> </code><code>service mysqld start</code>

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:

2）複制檔案：

<code> </code><code>cp</code> <code>-R </code><code>/var/lib/mysql</code> <code>/home/mysql</code>

其中/var/lib/mysql 目錄是原來的目錄，/home/mysql目錄是新目錄

3）重命名目錄：

<code> </code><code>mv</code> <code>/var/lib/mysql</code> <code>/var/lib/mysql-backup</code>

4）連結檔案：

<code> </code><code>ln</code> <code>-s </code><code>/home/mysql</code> <code>/var/lib/mysql</code>

5）修改目錄權限：

<code> </code><code>chown</code> <code>mysql:mysql </code><code>/home/mysql</code> <code>-R</code>

<code> </code><code>chown</code> <code>-h mysql:mysql </code><code>/var/lib/mysql</code>

6）重新啟動mysql:

7、每月1日定時清理3個zabbix資料庫的大表。

這一塊内容與第2條的内容有些重複，但這一塊更加徹底，自動實作對大資料的清理，保持mysql資料庫性能的持續性。

增加定時配置，每月1日淩晨3點01分執行mysql_trunc_mysql.sh：

<code> </code><code>crontab</code> <code>-e</code>

<code> </code><code>01 03 1 * * </code><code>/backup/mysql_trunc_mysql</code><code>.sh &></code><code>/dev/null</code>

下面是mysql_trunc_mysql.sh檔案内容：

<code> </code><code>vim </code><code>/backup/mysql_trunc_zabbix</code><code>.sh</code>

<code> </code><code>#truncate 3 big tables :trends_uint,history,history_unit .</code>

<code> </code><code>#Every month's 1th 3:00 excute this script</code>

<code> </code><code>#Writen by yuweibing ,phonenumber:18080116652</code>

<code> </code><code>service zabbix_server stop</code>

<code> </code><code>echo</code> <code>"truncate table trends_uint;"</code> <code>|mysql -uzabbix -pzabbix zabbix</code>

<code> </code><code>echo</code> <code>"truncate table history;"</code> <code>|mysql -uzabbix -pzabbix zabbix</code>

<code> </code><code>echo</code> <code>"truncate table history_uint;"</code> <code>|mysql -uzabbix -pzabbix zabbix</code>

<code> </code><code>sleep</code> <code>1</code>

<code> </code><code>mysqldump -uroot -pmysql zabbix > </code><code>/backup/zabbix</code><code>.sql</code>

<code> </code><code>echo</code> <code>"drop database zabbix;"</code><code>|mysql -uroot -pmysql</code>

<code> </code><code>echo</code> <code>"create database zabbix character set utf8;"</code><code>|mysql -uroot -pmysql</code>

<code> </code><code>echo</code> <code>"grant all privileges on zabbix.* to zabbix@localhost identified by 'zabbix';"</code><code>|mysql -uroot -pmysql</code>

<code> </code><code>echo</code> <code>"flush privileges;"</code><code>|mysql -uroot -pmysql</code>

<code> </code><code>mysql -uzabbix -pzabbix zabbix<</code><code>/backup/zabbix</code><code>.sql</code>

<code> </code><code>service zabbix_server start</code>

本文轉自yuweibing51CTO部落格，原文連結：http://blog.51cto.com/yuweibing/1656425 ，如需轉載請自行聯系原作者

zabbix 之 mysql優化

繼續閱讀

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

ACS基本配置-權限等級管理

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Ambari介紹和架構原理

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

win10本地scala和spark安裝安裝scala安裝spark