Production library MySQL configuration file my.cnf detailed

os:centos6.3

db:5.6.16

[Client] #客户端
Port = 3306 #数据库端口3306
Socket =/my/log/mysql.sock #MySQL套接字, socket connection with multiple instances

[MySQL]
Default-character-set = UTF8 #字符集

[Mysqld]
# file
DataDir =/my/data #数据文件路径
pid-file=/my/log/mysql.pid #MySQL进程
Log_error=/my/log/mysql.err #MySQL错误日志路径
Port = 3306 #数据库端口3306
Socket =/my/log/mysql.sock #MySQL套接字, socket connection with multiple instances
Character_set_server = UTF8 #字符集
#skip-grant-tables #忘记root密码时, you can connect MySQL and reset your password by--skip-grant-tables startup
Slow_query_log = 1 #开启慢查询日志
Slow_query_log_file =/my/log/slowq.log #慢查询日志路径
Long_query_time = 1 #慢查询时间1秒

# Temp
Max_tmp_tables = #打开临时表的最大数量
Max_heap_table_size = 1G #创建内存表的的大小
Tmp_table_size = 512M #内存临时表的最大值
Slave_load_tmpdir =/my/log #Slave临时目录路径
Tmpdir =/my/log #临时目录路径

# session
Back_log = 8192 #MySQL停止新请求前表示有多少请求可以暂时堆栈
Skip-name-resolve #跳过反向解析过程
Max_allowed_packet = 32M #客户端接收最大字节会话值
Max_connections = 16000 #最大连接数
Max_connect_errors = 10000 #最大错误连接数, full of need to clear through flush hosts
Wait_timeout = 28800 #等待超时时间
Binlog_cache_size = 1M #binlog缓存大小

Table_open_cache = 1024x768 #表缓冲区大小
Thread_concurrency = 4 #线程并发数
Thread_cache_size = #线程缓冲区大小
thread_handling = Pool-of-threads #开启线程池
Thread_pool_high_prio_mode = None #新的连接根据thread_pool_high_prio_mode分出优先级
Thread_pool_idle_timeout = 28800 #线程闲置超时时间
Thread_pool_oversubscribe = #支持运行的最大任务数
#max_statement_time = #控制查询在MySQL的最长执行时间 per millisecond

Query_cache_type = 0 #查询缓冲区是否开启 0: off 1: Open 2:demand
query_cache_size = 0 #查询缓冲区大小
Key_buffer_size = 128M #索引缓冲区大小
Myisam_sort_buffer_size = 16M #MyISAM排序缓冲大小
Read_buffer_size = 8M #顺序读缓区冲大小
Read_rnd_buffer_size = 4M #随机读缓冲区大小
Sort_buffer_size = 16M #排序缓冲区大小
Join_buffer_size = 16M #join缓冲区大小

# InnoDB
Default_storage_engine = InnoDB #默认存储引擎
Innodb_data_home_dir =/my/log #innodb存储引擎共享表空间路径, i.e.: ibdata
Innodb_data_file_path = Ibdata1:256m:autoextend #innodb存储引擎大小, self-increasing
Innodb_log_group_home_dir =/my/log #ib_logfile日志路径
Innodb_log_files_in_group = 2 #ib_logfile两组, two per group
Innodb_log_file_size = 512M #ib_logfile大小
Innodb_log_buffer_size = 8M #日志缓冲区大小
Innodb_flush_log_at_trx_commit = 2 #等于2时, write to the system cache without writing the hard disk, and the log will still write to the hard disk every second
Innodb_flush_method = O_direct #向文件写入数据, only the data is written to the disk, the write operation is complete (write returned successfully)
Innodb_lock_wait_timeout = #innodb引擎锁等待超时时间
#innodb_thread_concurrency = #innodb线程并发数
Innodb_buffer_pool_size = 44G #innodb存储引擎缓冲区大小
Innodb_additional_mem_pool_size = 20M #用来设置 The memory pool size of InnoDB stored data directory information and other internal data structures
Innodb_io_capacity = IO capability at #控制Innodb checkpoint
Innodb_use_native_aio = 1 #控制是否启用Native aio, enabled by default. Official tests show that with native AIO enabled, the recovery speed can be increased by 75%
innodb_file_per_table = 1 #innodb引擎使用独立的表空间
Innodb_open_files = #innodb打开文件数
Innodb_print_all_deadlocks = 1 #在error中打印锁信息

# others
Memlock = 1 #MySQL是否使用交换分区

# replication
Server-id = 1137 #server-id must be configured and unique when building master-slave
Log-bin =/my/log/mysql-bin #二进制日志文件路径
#binlog-ignore-db=mysql #过滤mysql库的二进制日志
Binlog_format = mixed #二进制日志模式 three species, respectively: row,statement,mixed
Expire_logs_days = 7 #删除过期日志时间
Relay_log =/my/log/relay-bin #relay-log file path
#replicate_ignore_db = MySQL #复制过滤MySQL库
#slave_skip_errors = 1062 #主从同步出现问题, ignoring all type 1062 errors from the library
Log_slave_updates = 1 #级联复制使用的参数, to meet M-s-s
Skip-slave-start #Slave不会随MySQL的启动而启动
#read_only = 1 #只读
Sync_binlog = 1 #将binlog_cache中的数据强制写入磁盘

[Mysqldump]
Default-character-set = UTF8 #数据库字符集

Production library MySQL configuration file my.cnf detailed