mysql最佳化-針對zabbix資料庫

標籤：zabbix mysql

mysql最佳化：

修改mysql的root帳號的密碼：

先停止mysql服務： 

service mysqld stop

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

mysqld_safe --skip-grant-tables &

進入mysql修改密碼：

use mysql

update user set password=password(‘mysql‘) where user=‘root‘;

flush privileges;

查看錶佔用空間情況：

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

備份表trends_uint、history_uint、history

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

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

truncate table trends_uint;

truncate table history;

truncate table history_uint;

修改單獨資料表空間：

備份資料

mysqldump -uroot -p zabbix > zabbix.sql

刪除資料庫：

drop database zabbix;

停止資料庫：

service mysqld stop

刪除共用資料表空間資料檔案

$ cd /var/lib/mysql

$ rm ib*

.增加innodb_file_per_table參數

vi /etc/my.cnf

在[mysqld]下設定

innodb_file_per_table=1

重啟資料庫：

service mysqld start

重新建立資料庫：

mysql -uroot -p

create database zabbix character set utf8;

grant all privileges on zabbix.* to [email protected] identified by ‘zabbix‘;

flush privileges;

匯入zabbix資料庫：

mysql -uzabbix -pzabbix zabbix<zabbix.sql

增大innodb_log_file_size的方法:

暫停mysql, 

service mysqld stop

轉移ib_logfile0和ib_logfile1,   

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

編輯my.cnf  ,  增加  

innodb_log_file_size=20M

啟動mysql,  

service mysqld start

[[email protected] 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

移動mysql資料目錄的方法：

關閉mysql: service mysqld stop

複製檔案：cp -R /var/lib/mysql /home/mysql

重新命名目錄： mv /var/lib/mysql /var/lib/mysql-backup

連結檔案： ln -s /home/mysql /var/lib/mysql

修改目錄許可權：

chown mysql:mysql /home/mysql -R

chown -h mysql:mysql /var/lib/mysql

重新啟動mysql: service mysqld start

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

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

vim /backup/mysql_trunc_zabbix.sh

#!/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 [email protected] 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

本文出自 “yuweibing的技術部落格” 部落格，請務必保留此出處http://yuweibing.blog.51cto.com/3879355/1656425

mysql最佳化-針對zabbix資料庫