mysql优化-针对zabbix数据库

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 zabbix@localhost 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

[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

移动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 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

本文出自 “yuweibing的技术博客” 博客，请务必保留此出处http://yuweibing.blog.51cto.com/3879355/1656425