Optimized configuration of MySQL performance optimization my.cnf file

[Client]
#password = Your_password
Port= 3306
socket=/opt/mysql/dbdata/mysql.sock

# The MySQL server
[Mysqld]
Port= 3306
socket=/opt/mysql/dbdata/mysql.sock
Skip-external-locking
Key_buffer_size = 384M
Max_allowed_packet = 1M
Table_open_cache = 512

#sort_buffer_size = 2M
Sort_buffer_size = 256M

#read_buffer_size = 2M
Read_buffer_size = 32M

#read_rnd_buffer_size = 8M
Read_rnd_buffer_size = 32M

Myisam_sort_buffer_size = 64M

Thread_cache_size = 8
Query_cache_size= 32M

#开启慢查询日志
Log_slow_admin_statements=on
Log_slow_slave_statements=on
Slow_query_log=1

#慢查询日志文件存放路径
Slow_query_log_file=/opt/mysql/mysqllog/logfile/slow-query.log

#执行的时间大于多少秒的SQL语句记录到慢查询日志
long_query_time=2

#未使用索引的查询也记录到文件
Log_queries_not_using_indexes = 1

#日志记录写到日志文件
log_output= ' FILE '

#数据连接等待时间, per second
interactive_timeout = 1800
wait_timeout = 1800

Basedir =/opt/mysql/product
DataDir =/opt/mysql/dbdata
max_binlog_size=100m
Log_bin=/opt/mysql/mysqllog/binlog/binlog.bin
Log-error=/opt/mysql/mysqllog/logfile/mysql-err.log

Binlog_format=mixed
Expire_logs_days=7
Binlog_cache_size=4mb

Pid-file =/opt/mysql/dbdata/mysql.pid
Default-storage-engine=myisam

user = MySQL

Group_concat_max_len = 10240
max_connections=3000

# twice times the number of (cpu+ disks)
#thread_concurrency = 8
Thread_concurrency = 24

# The following paths to a dedicated disk
#tmpdir =/tmp/
#skip-networking

Log-bin=mysql-bin

Server-id= 1

#server-id = 2
#
# The replication master for this slave-required
#master-host = #
# The username the slave would use for authentication when connecting
# to the master-required
#master-user = <username>
#
# The password the slave would authenticate with when connecting to
# The Master-required
#master-password = <password>
#
# The Port the master is listening on.
# Optional-defaults to 3306
#master-port = <port>
#
# binary Logging-not required for slaves, but recommended
#log-bin=mysql-bin
#
# Binary Logging format-mixed recommended
Binlog_format=mixed

#使用 InnoDB engine, you can turn on the following configuration items
# Uncomment the following if you is using InnoDB tables
#innodb_data_home_dir =/opt/mysql/dbdata
#innodb_data_file_path = Ibdata1:10m:autoextend
#innodb_log_group_home_dir =/opt/mysql/dbdata

#数据和索引缓冲区的大小
#innodb_buffer_pool_size = 256M
#innodb_buffer_pool_size = 10240M

#数据字段和其他数据结构的大小
#innodb_additional_mem_pool_size = 20M
#innodb_additional_mem_pool_size = 20M

# Set: _log_file_size to% of buffer pool size
#redo日志大小
#innodb_log_file_size = 64M
#innodb_log_file_size = 2560M

#日志缓冲区的大小
#innodb_log_buffer_size = 8M
#innodb_log_buffer_size = 16M

#0 commit things do not write to the log, log files write to and flush disk per second
Log files are written to the flush disk #1 per second or every time something is committed
#2 every time a thing is committed, log files are written, flush disk per second
#innodb_flush_log_at_trx_commit = 1
#innodb_flush_log_at_trx_commit = 1

#innodb_lock_wait_timeout = 50

[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash
# Remove The next comment character if you is not a familiar with SQL
#safe-updates

[Myisamchk]
#key_buffer_size = 128M
#sort_buffer_size = 128M
#read_buffer = 2M
#write_buffer = 2M

Key_buffer_size = 256M
Sort_buffer_size = 256M
Read_buffer = 32M
Write_buffer = 32M

[Mysqlhotcopy]
Interactive-timeout

[Mysqlhotcopy]
Interactive-timeout

Optimized configuration of MySQL performance optimization my.cnf file