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

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 一般只有5G.查询生产的文件写满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