mysql生产系统_mysql生产系统的配置优化(7W用户mailserver)

在业务数据库中遇到的几个问题解决：

1.ibdata过大问题，ibdata30G左右，做备份时存储写入速度非常慢

增加innodb_file_per_table=1 可以修改InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间。避免ibdata增长过大的问题

2.mysql查询过大，磁盘io瓶颈时，导致服务器负载过高拖死服务器，造成业务无法访问数据库中断。

innodb_buffer_pool_size=25G 调整缓存大小，64G内存给到25G，因为服务器还要承载其他业务，如果是独立服务器可以分配更大内存给mysql。

3.当前端数据对50W数据的表进行遍历查询时，mysql会用到/tmp 目录，由于服务器独立分区tmp 一般只有5Ｇ．查询生产的文件写满tmp就无法执行了

所以修改mysql tmp目录一个比较大的目录，这里用tmpdir=/home/tmp

my.cnf

[client]

#password       = your_password

port            = 3308

socket          = /home/richmail/var/mysql/mysql.sock

default-character-set=UTF8

# The MySQL server

[mysqld]

server-id  = 1

log-bin    = mysql-bin

log-bin-trust-function-creators = 1

port            = 3308

socket          = /home/richmail/var/mysql/mysql.sock

datadir         = /hwdata/mysql/

tmpdir=/home/tmp

#key_buffer = 256M

max_allowed_packet = 5M

table_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

myisam_sort_buffer_size = 64M

#thread_cache = 8

#query_cache_size= 16M

#thread_concurrency = 8

basedir = /home/richmail/mysql

user = richmail

###max_connections=5120

max_connections=1024

max_connect_errors=10240

#####2016-04-01###

#wait_timeout=100

####2015-12-11 add####

skip-name-resolve

#20160406

innodb_buffer_pool_size=25G   #

innodb_file_per_table=1

innodb_log_file_size = 500M

innodb_log_files_in_group = 4

innodb_flush_log_at_trx_commit = 0

sync_binlog=0

query_cache_size = 0

query_cache_type = 0

transaction_isolation = READ-COMMITTED

binlog_format=mixed

max_binlog_size=512M

max_binlog_cache_size = 1G

thread_concurrency=32

thread_cache_size = 64

table_open_cache = 4096

max_heap_table_size = 96M

tmp_table_size = 96M

key_buffer = 1G

slow_query_log = 1

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[isamchk]

key_buffer = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[myisamchk]

key_buffer = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

原文：http://daijh.blog.51cto.com/762214/1768495