Mysql設定檔my.cnf配置及配置參數詳解，mysqlmy.cnf

Mysql設定檔my.cnf配置及配置參數詳解，mysqlmy.cnf

Mysql設定檔my.cnf

安裝了mysql沒有my.cnf檔案的情況

1、可以把mysql的樣本設定檔，如my-medium.cnf拷貝到/etc/my.cnf，再去修改/etc/my.cnf的配置/usr/share/doc/MySQL-server-5.5.38/my-medium.cnf(Example MySQL config file for medium systems with little memory (32M - 64M) )

可以通過命令：>find / -name 你要找的檔案(my.cnf/ my-medium.cnf)  或者  >whereis my.cnf 找到.

所有mysql的設定檔例子都放在：

# ll /usr/share/doc/MySQL-server-5.5.38/

2、參考下面的文章修改my.cnf檔案（Example MySQL config file for very large systems）

修改MySQL的時區：

查看： mysql> show variables like '%time_zone%';   

1 、可以通過修改my.cnf
在 [mysqld] 之下加
default-time-zone=timezone
來修改時區。如：
default-time-zone = '+8:00'
修改完重啟msyql  ># service mysql restart
注意一定要在 [mysqld] 之下加 ，否則會出現 unknown variable 'default-time-zone=+8:00'

2、通過命令列線上修改（經測試不行）

 看到有人通過命令列修改，試過之後發現只能改掉 timezone（而且只針對當前用戶端影響）,但是查處的時間還是原來的CST時間，重啟mysql也沒有，全部恢複了。所以這種方式行不通，還是需要修改my.cnf設定檔才行.以下為測試：

1. set time_zone = timezone  
2. 比如北京時間（GMT+0800）  
3. set time_zone = '+8:00'; 如下:  
4. mysql> set time_zone='+8:00';
5.   
6. mysql> show variables like '%time_zone%';     
7. +------------------+--------+  
8. | Variable_name    | Value  |  
9. +------------------+--------+  
10. | system_time_zone | CST    |  
11. | time_zone        | +08:00 |  
12. +------------------+--------+  

mysql> flush privileges;   # 立即生效此時mysql時區已更改正確，與系統時區都使用為 北京時間。## 能改掉 timezone（而且只針對當前用戶端影響）,但是查處的時間還是原來的CST時間，重啟mysql也沒有，全部恢複了。所以這種方式行不通，還是需要修改my.cnf設定檔才行.

驗證：

mysql> show variables like '%time_zone%';

mysql > select CURTIME();或mysql > select now();

下面一篇講解my.cnf參數配置的文章供參考：

《linux下mysql的編譯安裝和my.cnf設定檔詳解》：http://blog.itpub.net/26690043/viewspace-719495/

還有一篇：《linux下mysql設定檔my.cnf詳解》http://www.cnblogs.com/captain_jack/archive/2010/10/12/1848496.html 

linux下mysql的編譯安裝和my.cnf設定檔詳解 

本文主要撰寫如何在CentOS6.0上編譯安裝Mysql資料庫，和一些收集的Mysql主配檔案my.cnf注釋詳解。雖然在系統上已經內建了Mysql安裝包，但是為了保證整個系統的統一、和一些績效參數的調優，我們需要編譯安裝Mysql才能滿足我們的需求。Mysql的官方網站提供Mysql伺服器的源碼包和已經編譯的二進位安裝包供下載。可以通過訪問官網下載所需要的Mysql版本，本文採用的是Mysql 5.1版本。

下載Mysql
# wget http://mysql.mirrors.hoobly.com/Downloads/MySQL-5.1/mysql-5.1.57.tar.gz

編譯安裝Mysql
# tar zxvf mysql-5.1.57.tar.gz
# cd mysql-5.1.57
#./configure --prefix=/usr/local/webserver/mysql/ \
--enable-assembler \
--with-extra-charsets=complex \
--enable-thread-safe-client \
--with-big-tables \
--with-readline \
--with-ssl \
--with-embedded-server \
--enable-local-infile \
# make \
# make install

接下來建立mysql使用者和相關目錄，這根據個人習慣目錄自定。

# mkdir -p /data/mysqldata
# mkdir -p /data/mysqldata/database
# mkdir -p /data/mysqldata/log
# mkdir -p /data/mysqldata/pid
# groupadd mysql
# useradd -g mysql -d /data/mysqldata/database -s /sbin/nologin mysql
# chgrp -R mysql /usr/local/webserver/mysql/.
# chown -R root:mysql /usr/local/webserver/mysql/*
# chown -R mysql:mysql /data/mysqldata/*

以下是Mysql主配檔案my.cnf參數和詳解：

# Example MySQL config file for very large systems.##下面的配置參數適用於512M記憶體的伺服器。## You can copy this file to# /etc/my.cnf to set global options,# mysql-data-dir/my.cnf to set server-specific options (in this# installation this directory is /data/mysql/data) or# ~/.my.cnf to set user-specific options.## In this file, you can use all long options that a program supports.# If you want to know which options a program supports, run the program# with the "--help" option.# The following options will be passed to all MySQL clients[client]#password = your_passwordport  = 3306socket  = /tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server[mysqld]user            = mysqlport            = 3306socket          = /tmp/mysql.sockbasedir         = /usr/local/webserver/mysqldatadir         = /data/mysqldata/databaselog-error       = /data/mysqldata/log/mysql_error.logpid-file        = /data/mysqldata/pid/mysql.pid#BDB 相關選項# 如果你啟動並執行MySQL服務有BDB支援但是你不準備使用的時候使用此選項. 這會節省記憶體並且可能加速一些事.skip-bdb#避免MySQL的外部鎖定，減少出錯幾率增強穩定性。skip-locking#禁止MySQL對外部串連進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機串連授權都要使用IP地址方式，否則MySQL將無法正常處理串連請求!skip-name-resolve#當建立新表時作為預設使用的表類型, 如果在建立表示沒有特別執行表類型,將會使用此值#mysql是預設支援4種儲存引擎：CSV,MRG_MYISAM,MEMORY,MyISAM,預設不支援InnoDB儲存引擎（消耗記憶體比較大）.由於記憶體很小,推薦使用MyISAM儲存引擎.default_table_type = MyISAM#設定預設的交易隔離等級,可用的層級:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLEtransaction_isolation = READ-UNCOMMITTED#限制mysqld能開啟檔案的最大個數,取值：max_connections*5 或者 max_connections + table_cache*2open_files_limit = 600#back_log 是作業系統在監聽隊列中所能持續連線數,隊列儲存了在MySQL連線管理員線程處理之前的串連.如果你有非常高的串連率並且出現”connection refused” 報錯,你就應該增加此處的值.檢查你的作業系統文檔來擷取這個變數的最大值.如果將back_log設定到比你作業系統限制更高的值,將會沒有效果。back_log = 40# 關鍵詞緩衝的大小, 一般用來緩衝MyISAM表的索引塊.不要將其設定大於你可用記憶體的30%,因為一部分記憶體同樣被OS用來緩衝行資料，甚至在你並不使用MyISAM 表的情況下, 你也需要仍舊設定起 8-64M 記憶體由於它同樣會被內部臨時磁碟表使用.key_buffer_size = 32M#服務所能處理的請求包的最大大小以及服務所能處理的最大的請求大小(當與大的BLOB欄位一起工作時相當必要)，每個串連獨立的大小.大小動態增加max_allowed_packet = 16M#線程使用的堆大小。此容量的記憶體在每次串連時被預留.MySQL 本身常不會需要超過64K的記憶體，如果你使用你自己的需要大量堆的UDF函數，或者你的作業系統對於某些操作需要更多的堆，你也許需要將其設定的更高一點。thread_stack = 192K#所有線程所開啟表的數量。增加此值就增加了mysqld所需要的檔案描述符的數量。這樣你需要確認在[mysqld_safe]中 “open-files-limit” 變數設定開啟檔案數量允許至少4096。table_cache = 60#允許外部檔案層級的鎖。開啟檔案鎖會對效能造成負面影響。所以只有在你在同樣的檔案上運行多個資料庫執行個體時才使用此選項(注意仍會有其他約束!)。或者你在檔案層面上使用了其他一些軟體依賴來鎖定MyISAM表external-locking = FALSE#排序緩衝被用來處理類似ORDER BY以及GROUP BY隊列所引起的排序，如果排序後的資料無法放入排序緩衝， 一個用來替代的基於磁碟的合并分類會被使用，查看 “Sort_merge_passes” 狀態變數。在排序發生時由每個線程分配。sort_buffer_size = 256K#用來做MyISAM表全表掃描的緩衝大小。當全表掃描需要時,在對應線程中分配。read_buffer_size = 1M#此緩衝被使用來最佳化全聯合(full JOINs 不帶索引的聯合)，類似的聯合在極大多數情況下有非常糟糕的效能表現。但是將此值設大能夠減輕效能影響。通過 “Select_full_join” 狀態變數查看全聯合的數量。當全聯合發生時,在每個線程中分配join_buffer_size = 256K#當在排序之後,從一個已經排序好的序列中讀取行時,行資料將從這個緩衝中讀取來防止磁碟尋道。如果你增高此值,可以提高很多ORDER BY的效能。當需要時由每個線程分配。read_rnd_buffer_size = 4M#MyISAM 使用特殊的類似樹的cache來使得突發插入 (這些插入是,INSERT … SELECT, INSERT … VALUES (…), (…), …, 以及 LOAD DATA INFILE) 更快。此變數限制每個進程中緩衝樹的位元組數。設定為 0 會關閉此最佳化。為了最佳化不要將此值設定大於 “key_buffer_size”。當突發插入被檢測到時此緩衝將被分配。bulk_insert_buffer_size = 2M#此緩衝當MySQL需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一個空表中引起重建索引時被分配。這在每個線程中被分配.所以在設定大值時需要小心。myisam_sort_buffer_size = 4M#MySQL重建索引時所允許的最大臨時檔案的大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE)。如果檔案大小比此值更大,索引會通過鍵值緩衝建立(更慢)myisam_max_sort_file_size = 10G#如果被用來更快的索引建立索引所使用臨時檔案大於制定的值,那就使用鍵值緩衝方法。這主要用來強制在大表中長字串鍵去使用慢速的鍵值緩衝方法來建立索引。myisam_max_extra_sort_file_size = 10G#如果一個表擁有超過一個索引, MyISAM 可以通過並行排序使用超過一個線程去修複他們。這對於擁有多個CPU以及大量記憶體情況的使用者,是一個很好的選擇。myisam_repair_threads = 1#自動檢查和修複沒有適當關閉的 MyISAM 表.myisam_recoverthread_cache = 128#我們在cache中保留多少線程用於重用，當一個用戶端中斷連線後,如果cache中的線程還少於thread_cache_size。則用戶端線程被放入cache中。這可以在你需要大量新串連的時候極大的減少線程建立的開銷。(一般來說如果你有好的執行緒模式的話,這不會有明顯的效能提升.)thread_cache_size = 10#查詢緩衝常被用來緩衝 SELECT 的結果並且在下一次同樣查詢的時候不再執行直接返回結果。開啟查詢緩衝可以極大的提高伺服器速度, 如果你有大量的相同的查詢並且很少修改表。查看 “Qcache_lowmem_prunes” 狀態變數來檢查是否當前值對於你的負載來說是否足夠高。注意: 在你表經常變化的情況下或者如果你的查詢原文每次都不同。查詢緩衝也許引起效能下降而不是效能提升。query_cache_size = 0M#只有小於此設定值的結果才會被緩衝，此設定用來保護查詢緩衝,防止一個極大的結果集將其他所有的查詢結果都覆蓋.query_cache_limit = 2Mquery_cache_min_res_unit = 4K#內部(記憶體中)暫存資料表的最大大小，如果一個表增長到比此值更大,將會自動轉換為基於磁碟的表。此限制是針對單個表的,而不是總和.tmp_table_size = 512K#獨立的記憶體表所允許的最大容量。此選項為了防止意外建立一個超大的記憶體表導致永盡所有的記憶體資源.max_heap_table_size = 32M#所有的使用了比這個時間(以秒為單位)更多的查詢會被認為是慢速查詢。不要在這裡使用”1″, 否則會導致所有的查詢,甚至非常快的查詢頁被記錄下來(由於MySQL 目前時間的精確度只能達到秒的層級)。ong_query_time = 1#在慢速日誌中記錄更多的資訊。一般此項最好開啟。開啟此項會記錄使得那些沒有使用索引的查詢也被作為到慢速查詢附加到慢速日誌裡log_long_format#MySQL 服務所允許的同時會話數的上限，其中一個串連將被SUPER許可權保留作為管理員登入。 即便已經達到了串連數的上限.max_connections = 200#指定一個請求的最大連線時間。wait_timeout = 30#每個用戶端串連最大的錯誤允許數量,如果達到了此限制。這個用戶端將會被MySQL服務阻止直到執行了”FLUSH HOSTS” 或者服務重啟，非法的密碼以及其他在連結時的錯誤會增加此值。查看 “Aborted_connects” 狀態來擷取全域計數器.max_connect_errors = 200expire_logs_days = 7#Try number of CPU's*2 for thread_concurrency#此允許應用程式給予線程系統一個提示在同一時間給予渴望被啟動並執行線程的數量。此值只對於支援 thread_concurrency() 函數的系統有意義( 例如Sun Solaris)。你可可以嘗試使用 [CPU數量]*(2..4) 來作為thread_concurrency的值thread_concurrency = 8# Don't listen on a TCP/IP port at all. This can be a security enhancement,# if all processes that need to connect to mysqld run on the same host.# All interaction with mysqld must be made via Unix sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (via the "enable-named-pipe" option) will render mysqld useless!##skip-networking#預設關閉 Federatedskip-federated# Replication Master Server (default)# binary logging is required for replication#log-bin=mysql-bin#在一個事務中binlog為了記錄SQL狀態所持有的cache大小，如果你經常使用大的,多聲明的事務,你可以增加此值來擷取更大的效能。所有從事務來的狀態都將被緩衝在binlog緩衝中然後在提交後一次性寫入到binlog中。如果事務比此值大, 會使用磁碟上的臨時檔案來替代。此緩衝在每個串連的事務第一次更新狀態時被建立binlog_cache_size = 2Mmax_binlog_cache_size = 4Mmax_binlog_size = 512M#唯一的服務辨識號,數值位於 1 到 2^32-1之間。此值在master和slave上都需要設定。如果 “master-host” 沒有被設定,則預設為1, 但是如果忽略此選項,MySQL不會作為master生效.server-id = 1# Replication Slave (comment out master section to use this)## To configure this host as a replication slave, you can choose between# two methods :## 1) Use the CHANGE MASTER TO command (fully described in our manual) -#    the syntax is:##    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;##    where you replace <host>, <user>, <password> by quoted strings and#    <port> by the master's port number (3306 by default).##    Example:##    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,#    MASTER_USER='joe', MASTER_PASSWORD='secret';## OR## 2) Set the variables below. However, in case you choose this method, then#    start replication for the first time (even unsuccessfully, for example#    if you mistyped the password in master-password and the slave fails to#    connect), the slave will create a master.info file, and any later#    change in this file to the variables' values below will be ignored and#    overridden by the content of the master.info file, unless you shutdown#    the slave server, delete master.info and restart the slaver server.#    For that reason, you may want to leave the lines below untouched#    (commented) and instead use CHANGE MASTER TO (see above)## required unique id between 2 and 2^32 - 1# (and different from the master)# defaults to 2 if master-host is set# but will not function as a slave if omitted#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# Point the following paths to different dedicated disks#tmpdir  = /tmp/#log-update  = /path-to-dedicated-directory/hostname# Uncomment the following if you are using InnoDB tables#innodb_data_home_dir = /data/mysql/data/#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend#innodb_log_group_home_dir = /data/mysql/data/#innodb_log_arch_dir = /data/mysql/data/# You can set .._buffer_pool_size up to 50 - 80 %# of RAM but beware of setting memory usage too high#innodb_buffer_pool_size = 384M#innodb_additional_mem_pool_size = 20M# Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 100M#innodb_log_buffer_size = 8M#innodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50[mysqldump]#不要在將記憶體中的整個結果寫入磁碟之前緩衝. 在匯出非常巨大的表時需要此項quickmax_allowed_packet = 16M[mysql]no-auto-rehash

根據需求修改參數值，然後儲存為/etc/my.cnf檔案。

初始化mysql資料庫
# /usr/local/webserver/mysql/bin/mysql_install_db \
--basedir=/usr/local/webserver/mysql \
--datadir=/data/mysqldata/database \
--user=mysql

最後在說下啟動和關閉mysql伺服器
# /usr/local/webserver/mysql/bin/mysqld_safe --user=mysql & //啟動服務
# /usr/local/webserver/mysql/bin/mysqladmin -u root -p shutdown //停止服務