MySQL MY.CNF configuration detailed

#配置多实例声明
[Mysqld_multi]
Mysqld =/data/mysql_bin/bin/mysqld_safe
Mysqladmin =/data/mysql_bin/bin/mysqladmin
user = user
Password = 123456

[Mysqld1]
#端口
Port = 3306
user = MySQL
#External-locking for locking MyISAM data tables for multi-process conditions skip
Skip-external-locking
Character_set_server = UTF8
#定时任务 Log Library use
Event_scheduler = On
#是否支持目录软连接
Symbolic-links = 0

#这个选项可以配置MYISAM表允许在创建索引的过程中, the ability to create a temporary index file size to disk
Myisam_max_sort_file_size = 10G
#如果该值大于1, MyISAM table indexes are created in parallel in repair by sorting (each index is in its own line range)
Myisam_repair_threads = 1
#自动检查和修复没有适当关闭的MyISAM表
Myisam_recover

#连接时间 units per second 10 hours
Wait_timeout = 36000
#交互式连接时间 units per second
interactive_timeout = 1200
#每次交互最大传输量 128M
Max_allowed_packet = 134217728

#慢查询日志 Log Library is set to 2
Long_query_time = 1
#文件目录
Datadir=/data/mysql_data/mysql1
Basedir=/data/mysql_bin
Socket=/tmp/mysql.sock1
#指定一个日志输出文件, add log information at the end of the file if the file exists
#log =/data/mysql_logs/logs1/mysqld.log
Pid-file=/data/mysql_data/mysql1/db-app1.pid
#慢查询日志
Log-slow-queries=/data/mysql_logs/logs1/slowquery.log
#binlog地址
Log-bin=/data/mysql_data/mysql1/app-net1_1-bin
Tmpdir=/data/mysql_data/mysql1/tmp

#设置每个主机的连接请求异常中断的最大次数, when this number of times is exceeded, the MySQL server will disallow host connection requests until the MySQL server restarts or clears the host information by flushing the hosts command.
Max_connect_errors = 10000000

# #binlog配置
#主从配置
Server-id = 1
#2进制模式
Binlog_format = MIXED
#文件大小
Max_binlog_size = 1G
#过期时间
Expire_logs_days = 1

#忽略的库
replicate-ignore-db = MySQL
Replicate-ignore-db = Information_schema

#内存相关

#允许的连接数
Max_connections = 500
#缓存表描述的数量
Table_cache = 2048
This value is only #未生效 mysql5.6
Table_open_cache = 4096
#要缓存的线程数量
Thread_cache_size = 300
#并发数量 Core Number X2
Thread_concurrency = 32

# #全局
#mysql查询缓存
Query_cache_size= 1024M
#临时表, the probability of exceeding the 2 average is slightly more than 5
Tmp_table_size = 256M
#key_buffer_size only works on MyISAM tables
Key_buffer_size = 1024M

# #线程

#默认值128M,
#innodb_buffer_pool_size = 256M

#binlog_cache_size default 32K. server 1 Size
Maximum cache memory size that #binlog can use
Max_binlog_cache_size = 64M

#MySql读入缓冲区大小 default value 2M optimized to 4M
Read_buffer_size = 8M
# Random Read data buffer 8M optimized to 32M using memory default value
Read_rnd_buffer_size = 128M
#排序缓存 default value 2M optimized to 4M
Sort_buffer_size = 8M
#联合查询 1M
Join_buffer_size = 1048576
Buffer size to use when #设置, restore, and modify tables
Myisam_sort_buffer_size = 512M
#批量插入 Default Value 8M
Bulk_insert_buffer_size = 8388608

[Mysqld2]
Port = 3307
user = MySQL
Skip-external-locking
Character_set_server = UTF8
Symbolic-links = 0

Myisam_max_sort_file_size = 10G
Myisam_repair_threads =1
Myisam_recover

Wait_timeout = 36000
interactive_timeout = 1200
Max_allowed_packet = 134217728

Long_query_time = 1
Datadir=/data/mysql_data/mysql2
Basedir=/data/mysql_bin
Socket=/tmp/mysql.sock2
#log =/data/mysql_logs/logs2/mysqld.log
Pid-file=/data/mysql_data/mysql2/db-app2.pid
Log-slow-queries=/data/mysql_logs/logs2/slowquery.log
Log-bin=/data/mysql_data/mysql2/app-net1_2-bin
Tmpdir=/data/mysql_data/mysql2/tmp

Max_connect_errors = 10000000

Server-id = 2
Binlog_format = MIXED
Max_binlog_size = 1G
Expire_logs_days = 1

replicate-ignore-db = MySQL
REPLICATE-IGNORE-DB = Test
Replicate-ignore-db = Information_schema

Max_connections = 2400
Table_cache = 2048
Table_open_cache = 4096
Thread_cache_size = 300
Thread_concurrency = 32

Query_cache_size= 1024M
Tmp_table_size = 256M
Key_buffer_size = 1024M

Max_binlog_cache_size = 64M

Read_buffer_size = 32M
Read_rnd_buffer_size = 128M
Sort_buffer_size = 24M
Join_buffer_size = 24M
Myisam_sort_buffer_size = 512M
bulk_insert_buffer_size=8388608

#只读模式
Read-only=1
#清除
Relay-log-purge=1

[Mysqldump]
Quick
#优化为64M
Max_allowed_packet = 32M

[MySQL]
#自动补全功能
No-auto-rehash

[Myisamchk]
#优化128M
Key_buffer_size = 256M
#优化128M
Sort_buffer_size = 256M
Read_buffer = 2M
Write_buffer = 2M

[Mysqlhotcopy]
Interactive-timeout

MySQL MY.CNF configuration detailed