《mysql性能優(yōu)化之優(yōu)化配置my.cnf文件MYSQL數(shù)據(jù)庫(kù)》要點(diǎn)：
本文介紹了mysql性能優(yōu)化之優(yōu)化配置my.cnf文件MYSQL數(shù)據(jù)庫(kù)，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

[client]

MYSQL學(xué)習(xí)#password = your_password
port = 3306
socket = /opt/mysql/dbdata/mysql.sock

MYSQL學(xué)習(xí)# 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

MYSQL學(xué)習(xí)#sort_buffer_size = 2M
sort_buffer_size = 256M

MYSQL學(xué)習(xí)#read_buffer_size = 2M
read_buffer_size = 32M

MYSQL學(xué)習(xí)#read_rnd_buffer_size = 8M
read_rnd_buffer_size = 32M

MYSQL學(xué)習(xí)myisam_sort_buffer_size = 64M

MYSQL學(xué)習(xí)thread_cache_size = 8
query_cache_size= 32M

MYSQL學(xué)習(xí)#開(kāi)啟慢查詢?nèi)罩?br style="box-sizing: border-box;"> log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log=1

MYSQL學(xué)習(xí)#慢查詢?nèi)罩疚募娣怕窂?br style="box-sizing: border-box;"> slow_query_log_file=/opt/mysql/mysqllog/logfile/slow-query.log

MYSQL學(xué)習(xí)#執(zhí)行的時(shí)間大于多少秒的SQL語(yǔ)句記錄到慢查詢?nèi)罩?br style="box-sizing: border-box;"> long_query_time=2

MYSQL學(xué)習(xí)#未使用索引的查詢也記錄到文件
log_queries_not_using_indexes = 1

MYSQL學(xué)習(xí)#日志記錄寫到日志文件
log_output='FILE'

MYSQL學(xué)習(xí)
#數(shù)據(jù)連接等待時(shí)間,單位秒
interactive_timeout = 1800
wait_timeout = 1800

MYSQL學(xué)習(xí)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

MYSQL學(xué)習(xí)binlog_format=mixed
expire_logs_days=7
binlog_cache_size=4MB

MYSQL學(xué)習(xí)pid-file = /opt/mysql/dbdata/mysql.pid
default-storage-engine=MyISAM

MYSQL學(xué)習(xí)user = mysql

MYSQL學(xué)習(xí)group_concat_max_len = 10240
max_connections=3000

MYSQL學(xué)習(xí)#(cpu+磁盤)數(shù)量的2倍
#thread_concurrency = 8
thread_concurrency = 24

MYSQL學(xué)習(xí)# Point the following paths to a dedicated disk
#tmpdir = /tmp/
#skip-networking

MYSQL學(xué)習(xí)log-bin=mysql-bin

MYSQL學(xué)習(xí)server-id = 1

MYSQL學(xué)習(xí)#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will 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

MYSQL學(xué)習(xí)
#使用 InnoDB 引擎,可開(kāi)啟以下配置項(xiàng)
# Uncomment the following if you are 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

MYSQL學(xué)習(xí)#數(shù)據(jù)和索引緩沖區(qū)的大小
#innodb_buffer_pool_size = 256M
#innodb_buffer_pool_size = 10240M

MYSQL學(xué)習(xí)#數(shù)據(jù)字段和其他數(shù)據(jù)結(jié)構(gòu)的大小
#innodb_additional_mem_pool_size = 20M
#innodb_additional_mem_pool_size = 20M

MYSQL學(xué)習(xí)# Set .._log_file_size to 25 % of buffer pool size
#redo日志大小
#innodb_log_file_size = 64M
#innodb_log_file_size = 2560M

MYSQL學(xué)習(xí)#日志緩沖區(qū)的大小
#innodb_log_buffer_size = 8M
#innodb_log_buffer_size = 16M

MYSQL學(xué)習(xí)#0 提交事物不寫入日志,每秒日志文件寫入和flush磁盤
#1 每秒或每次事物提交時(shí),日志文件寫入 flush磁盤
#2 每次事物提交時(shí),日志文件寫入,每秒flush磁盤
#innodb_flush_log_at_trx_commit = 1
#innodb_flush_log_at_trx_commit = 1

MYSQL學(xué)習(xí)#innodb_lock_wait_timeout = 50

MYSQL學(xué)習(xí)[mysqldump]
quick
max_allowed_packet = 16M

MYSQL學(xué)習(xí)[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

MYSQL學(xué)習(xí)[myisamchk]
#key_buffer_size = 128M
#sort_buffer_size = 128M
#read_buffer = 2M
#write_buffer = 2M

MYSQL學(xué)習(xí)key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 32M
write_buffer = 32M

MYSQL學(xué)習(xí)[mysqlhotcopy]
interactive-timeout

MYSQL學(xué)習(xí)[mysqlhotcopy]
interactive-timeout

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/5579.html