mysql 5.6 优化配置_mysql 5.6 常规优化配置

这是继续上一篇的my.cnf配置文件，只是常规的参数，里面加入了部分优化参数。硬件配置10核32G内存。优化是个大工程，后面会陆续补充。比如mysql的sync-binlog=1等mysql的配置双一会专门抽出来写。以后会从DML语句开始写，sql语句毕竟是数据库的灵魂。

client]

port    = 42116

socket  = /database/mysql/3306/mysql.sock

default-character-set=utf8

[mysql]

pager="more"

no-auto-rehash

[mysqld]

core-file

#### Baes dir ####

user    = mysql

port    = 42116

socket  = /database/mysql/3306/mysql.sock

basedir = /usr/local/mysql

datadir = /database/mysql/3306/data

pid-file = /database/mysql/3306/mysql.pid

#### Base configure info ####

skip-name-resolve

old_passwords = 0

lower_case_table_names = 1

open_files_limit = 65535

max_allowed_packet = 24M

max_connect_errors = 50000

max_connections = 2100

max_user_connections=2000

thread_cache_size=256

table_definition_cache = 2048

thread_stack = 524288

transaction_isolation = READ-COMMITTED

#### Log info ####

slow_query_log = 1

long_query_time  = 0.1

slow_query_log_file = /database/mysql/3306/logs/slow.log

log-error = /database/mysql/3306/logs/error.log

#### Binary log && Relay log ####

server-id = 204

log-bin = /data/mysql/3306/logs/binlog/mysql-bin

relay-log=/data/mysql/3306/logs/relaylog/relay-bin

relay-log-index=/data/mysql/3306/logs/relaylog/relay-log.index

relay-log-info-file=/data/mysql/3306/logs/relaylog/relay-log.info

max_binlog_size = 500M

max_binlog_cache_size = 2G

binlog_format = ROW

binlog_cache_size = 2M

log_bin_trust_function_creators =1

replicate_wild_ignore_table= mysql.%

replicate_wild_ignore_table= test.%

slave-skip-errors     = all

concurrent_insert = 2

key_buffer = 256

sort_buffer_size = 100k

join_buffer_size = 100K

read_buffer_size = 1M

read_rnd_buffer_size=5M

myisam_sort_buffer_size = 100M

#innodb plugin

innodb_file_format=Barracuda

innodb_strict_mode=1

innodb_thread_concurrency = 36

#innodb

default-storage-engine = INNODB

innodb_additional_mem_pool_size = 20M

innodb_log_buffer_size= 200M

innodb_log_file_size = 256M

innodb_buffer_pool_size = 28G

innodb_max_dirty_pages_pct = 60

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

innodb_lock_wait_timeout = 100

innodb_log_files_in_group = 2

innodb_open_files=60000

innodb_support_xa=1

###InnoDB IO

innodb_write_io_threads=8

innodb_read_io_threads=8

# set 0 just for test

innodb_flush_log_at_trx_commit = 2

innodb_data_file_path = ibdata1:1G:autoextend

innodb_io_capacity = 1000

[mysqldump]quickmax_allowed_packet = 32M