mysql优化-针对zabbix数据库

ewsd12

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

[iyunv@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