ä¸è¬å为windowsålinux
windowsé常æ¾å¨å®è£
ç®å½ä¸ç \MySQL\MySQL Server 5.0\my.ini
Linux é»è®¤æ¯æ¾å¨ /etc/my.cnf
以ä¸æ¯my.cnfé
ç½®æ件åæ°è§£éï¼
[client]
port = 3309
socket = /home/mysql/mysql/tmp/mysql.sock
[mysqld]
!include /home/mysql/mysql/etc/mysqld.cnf #å
å«çé
ç½®æ件 ï¼æç¨æ·åï¼å¯ç æ件åç¬åæ¾
port = 3309
socket = /home/mysql/mysql/tmp/mysql.sock
pid-file = /longxibendi/mysql/mysql/var/mysql.pid
basedir = /home/mysql/mysql/
datadir = /longxibendi/mysql/mysql/var/
# tmp dir settings
tmpdir = /home/mysql/mysql/tmp/
slave-load-tmpdir = /home/mysql/mysql/tmp/
#å½slave æ§è¡ load data infile æ¶ç¨
#language = /home/mysql/mysql/share/mysql/english/
character-sets-dir = /home/mysql/mysql/share/mysql/charsets/
# skip options
skip-name-resolve #grant æ¶ï¼å¿
须使ç¨ipä¸è½ä½¿ç¨ä¸»æºå
skip-symbolic-links #ä¸è½ä½¿ç¨è¿æ¥æ件
skip-external-locking #ä¸ä½¿ç¨ç³»ç»éå®ï¼è¦ä½¿ç¨myisamchk,å¿
é¡»å
³éæå¡å¨
skip-slave-start #å¯å¨mysql,ä¸å¯å¨å¤å¶
#sysdate-is-now
# res settings
back_log = 50 #æ¥åéåï¼å¯¹äºæ²¡å»ºç«tcpè¿æ¥ç请æ±éåæ¾å
¥ç¼åä¸ï¼éå大å°ä¸ºback_logï¼åéå¶ä¸OSåæ°
max_connections = 1000 #æ大并åè¿æ¥æ° ï¼å¢å¤§è¯¥å¼éè¦ç¸åºå¢å å
许æå¼çæ件æ述符æ°
max_connect_errors = 10000 #å¦ææ个ç¨æ·åèµ·çè¿æ¥errorè¶
è¿è¯¥æ°å¼ï¼å该ç¨æ·çä¸æ¬¡è¿æ¥å°è¢«é»å¡ï¼ç´å°ç®¡çåæ§è¡flush hosts ; å½ä»¤ï¼é²æ¢é»å®¢
#open_files_limit = 10240
connect-timeout = 10 #è¿æ¥è¶
æ¶ä¹åçæ大ç§æ°,å¨Linuxå¹³å°ä¸ï¼è¯¥è¶
æ¶ä¹ç¨ä½çå¾
æå¡å¨é¦æ¬¡ååºçæ¶é´
wait-timeout = 28800 #çå¾
å
³éè¿æ¥çæ¶é´
interactive-timeout = 28800 #å
³éè¿æ¥ä¹åï¼å
许interactive_timeoutï¼å代äºwait_timeoutï¼ç§çä¸æ´»å¨æ¶é´ã客æ·ç«¯çä¼è¯wait_timeoutåé被设为ä¼è¯interactive_timeoutåéçå¼ã
slave-net-timeout = 600 #ä»æå¡å¨ä¹è½å¤å¤çç½ç»è¿æ¥ä¸æãä½æ¯ï¼åªæä»æå¡å¨è¶
è¿slave_net_timeoutç§æ²¡æä»ä¸»æå¡å¨æ¶å°æ°æ®æéç¥ç½ç»ä¸æ
net_read_timeout = 30 #ä»æå¡å¨è¯»åä¿¡æ¯çè¶
æ¶
net_write_timeout = 60 #ä»æå¡å¨åå
¥ä¿¡æ¯çè¶
æ¶
net_retry_count = 10 #å¦ææ个é信端å£ç读æä½ä¸æäºï¼å¨æ¾å¼åéè¯å¤æ¬¡
net_buffer_length = 16384 #å
æ¶æ¯ç¼å²åºåå§å为net_buffer_lengthåèï¼ä½éè¦æ¶å¯ä»¥å¢é¿å°max_allowed_packetåè
max_allowed_packet = 64M #
#table_cache = 512 #ææ线ç¨æå¼ç表çæ°ç®ãå¢å¤§è¯¥å¼å¯ä»¥å¢å mysqldéè¦çæ件æ述符çæ°é
thread_stack = 192K #æ¯ä¸ªçº¿ç¨çå æ 大å°
thread_cache_size = 20 #线ç¨ç¼å
thread_concurrency = 8 #åæ¶è¿è¡ç线ç¨çæ°æ® æ¤å¤æ好为CPU个æ°ä¸¤åãæ¬æºé
置为CPUç个æ°
# qcache settings
query_cache_size = 256M #æ¥è¯¢ç¼å大å°
query_cache_limit = 2M #ä¸ç¼åæ¥è¯¢å¤§äºè¯¥å¼çç»æ
query_cache_min_res_unit = 2K #æ¥è¯¢ç¼ååé
çæå°å大å°
# default settings
# time zone
default-time-zone = system #æå¡å¨æ¶åº
character-set-server = utf8 #server级å«å符é
default-storage-engine = InnoDB #é»è®¤åå¨
# tmp & heap
tmp_table_size = 512M #临æ¶è¡¨å¤§å°ï¼å¦æè¶
è¿è¯¥å¼ï¼åç»ææ¾å°ç£çä¸
max_heap_table_size = 512M #该åé设置MEMORY (HEAP)表å¯ä»¥å¢é¿å°çæ大空é´å¤§å°
log-bin = mysql-bin #è¿äºè·¯å¾ç¸å¯¹äºdatadir
log-bin-index = mysql-bin.index
relayrelay-log = relay-log
relayrelay_log_index = relay-log.index
# warning & error log
log-warnings = 1
log-error = /home/mysql/mysql/log/mysql.err
log_output = FILE #åæ°log_outputæå®äºæ
¢æ¥è¯¢è¾åºçæ ¼å¼ï¼é»è®¤ä¸ºFILEï¼ä½ å¯ä»¥å°å®è®¾ä¸ºTABLEï¼ç¶åå°±å¯ä»¥æ¥è¯¢mysqlæ¶æä¸çslow_log表äº
# slow query log
slow_query_log = 1
long-query-time = 1 #æ
¢æ¥è¯¢æ¶é´ è¶
è¿1ç§å为æ
¢æ¥è¯¢
slow_query_log_file = /home/mysql/mysql/log/slow.log
#log-queries-not-using-indexes
#log-slow-slave-statements
general_log = 1
general_log_file = /home/mysql/mysql/log/mysql.log
max_binlog_size = 1G
max_relay_log_size = 1G
# if use auto-ex, set to 0
relay-log-purge = 1 #å½ä¸ç¨ä¸ç»§æ¥å¿æ¶ï¼å é¤ä»ä»¬ãè¿ä¸ªæä½æSQL线ç¨å®æ
# max binlog keeps days
expire_logs_days = 30 #è¶
è¿30天çbinlogå é¤
binlog_cache_size = 1M #session级å«
# replication
replicate-wild-ignore-table = mysql.% #å¤å¶æ¶å¿½ç¥æ°æ®åºå表
replicate-wild-ignore-table = test.% #å¤å¶æ¶å¿½ç¥æ°æ®åºå表
# slave_skip_errors=all
key_buffer_size = 256M #myisamç´¢å¼buffer,åªækey没ædata
sort_buffer_size = 2M #æåºbuffer大å°ï¼çº¿ç¨çº§å«
read_buffer_size = 2M #以å
¨è¡¨æ«æ(Sequential Scan)æ¹å¼æ«ææ°æ®çbufferå¤§å° ï¼çº¿ç¨çº§å«
join_buffer_size = 8M # join buffer 大å°;线ç¨çº§å«
read_rnd_buffer_size = 8M #MyISAM以索å¼æ«æ(Random Scan)æ¹å¼æ«ææ°æ®çbufferå¤§å° ï¼çº¿ç¨çº§å«
bulk_insert_buffer_size = 64M #MyISAM ç¨å¨åæå
¥ä¼åä¸çæ ç¼å²åºç大å°ã注éï¼è¿æ¯ä¸ä¸ªper threadçéå¶
myisam_sort_buffer_size = 64M #MyISAM 设置æ¢å¤è¡¨ä¹æ¶ä½¿ç¨çç¼å²åºç尺寸,å½å¨REPAIR TABLEæç¨CREATE INDEXå建索å¼æALTER TABLEè¿ç¨ä¸æåº MyISAMç´¢å¼åé
çç¼å²åº
myisam_max_sort_file_size = 10G #MyISAM å¦æ临æ¶æ件ä¼åå¾è¶
è¿ç´¢å¼ï¼ä¸è¦ä½¿ç¨å¿«éæåºç´¢å¼æ¹æ³æ¥å建ä¸ä¸ªç´¢å¼ã注éï¼è¿ä¸ªåæ°ä»¥åèçå½¢å¼ç»åº.é建MyISAMç´¢å¼(å¨REPAIR TABLEãALTER TABLEæLOAD DATA INFILEè¿ç¨ä¸)æ¶ï¼å
许MySQL使ç¨ç临æ¶æ件çæ大空é´å¤§å°ãå¦ææ件ç大å°è¶
è¿è¯¥å¼ï¼å使ç¨é®å¼ç¼åå建索å¼ï¼è¦æ
¢å¾å¤ã该å¼çåä½ä¸ºåè
myisam_repair_threads = 1 #å¦æ该å¼å¤§äº1ï¼å¨Repair by sortingè¿ç¨ä¸å¹¶è¡å建MyISAM表索å¼(æ¯ä¸ªç´¢å¼å¨èªå·±ç线ç¨å
)
myisam_recover = 64K#å
许çGROUP_CONCAT()å½æ°ç»æçæ大é¿åº¦
transaction_isolation = REPEATABLE-READ
innodb_file_per_table
#innodb_status_file = 1
#innodb_open_files = 2048
innodb_additional_mem_pool_size = 100M #帧ç¼åçæ§å¶å¯¹è±¡éè¦ä»æ¤å¤ç³è¯·ç¼åï¼æ以该å¼ä¸innodb_buffer_pool对åº
innodb_buffer_pool_size = 2G #å
æ¬æ°æ®é¡µãç´¢å¼é¡µãæå
¥ç¼åãéä¿¡æ¯ãèªéåºåå¸æ以ãæ°æ®åå
¸ä¿¡æ¯
innodb_data_home_dir = /longxibendi/mysql/mysql/var/
#innodb_data_file_path = ibdata1:1G:autoextend
innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend #表空é´
innodb_file_io_threads = 4 #io线ç¨æ°
innodb_thread_concurrency = 16 #InnoDBè¯çå¨InnoDBå
ä¿ææä½ç³»ç»çº¿ç¨çæ°éå°äºæçäºè¿ä¸ªåæ°ç»åºçéå¶
innodb_flush_log_at_trx_commit = 1 #æ¯æ¬¡commit æ¥å¿ç¼åä¸çæ°æ®å·å°ç£çä¸
innodb_log_buffer_size = 8M #äºç©æ¥å¿ç¼å
innodb_log_file_size = 500M #äºç©æ¥å¿å¤§å°
#innodb_log_file_size =100M
innodb_log_files_in_group = 2 #两ç»äºç©æ¥å¿
innodb_log_group_home_dir = /longxibendi/mysql/mysql/var/#æ¥å¿ç»
innodb_max_dirty_pages_pct = 90 #innodb主线ç¨å·æ°ç¼åæ± ä¸çæ°æ®ï¼ä½¿èæ°æ®æ¯ä¾å°äº90%
innodb_lock_wait_timeout = 50 #InnoDBäºå¡å¨è¢«åæ»ä¹åå¯ä»¥çå¾
ä¸ä¸ªéå®çè¶
æ¶ç§æ°ãInnoDBå¨å®èªå·±ç éå®è¡¨ä¸èªå¨æ£æµäºå¡æ»é并ä¸åæ»äºå¡ãInnoDBç¨LOCK TABLESè¯å¥æ³¨æå°éå®è®¾ç½®ãé»è®¤å¼æ¯50ç§
#innodb_flush_method = O_DSYNC
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
disable-auto-rehash #å
许éè¿TABé®æ示
default-character-set = utf8
connect-timeout = 3