MySQL設定檔參數的一些說明

MySQL設定檔參數的一些說明

 根據mysql[d] --help --verbose的輸出可知 mysql用戶端和服務端程式載入設定檔順序和路徑如下

/etc/mysql/my.cnf --> /etc/my.cnf --> --default-extra-file 選項指定的檔案 --> ~/.my.cnf

 mysql採用集中式的設定檔，設定檔是分塊的，以 [NAME] 獨立行開始，到下一個[NAME] 獨立行結束屬於作用於NAME指定的程式的配置。

 比如[client] 是所有mysql用戶端程式讀取的配置塊。[server]是所有服務端如mysqld會讀取的配置塊。[mysqldump]則是只有 mysqldump 才會讀取的設定檔，[mysql] 是mysql這

個用戶端程式的配置塊，而[mysqld]是mysql服務端程式mysqld 和 mysqld_safe ,mysqld_multi 的設定檔。
 對初始設定檔的參數及其它參數的解釋：

[client]port            = 3306socket          = /tmp/mysql.sock#當mysql用戶端和伺服器在同一主機上時，使用 socket 通訊較TCP/IP效率更高，這裡指定的soket檔案路徑需與[mysqld]中的設定保持一致[mysqld]port            = 3306socket          = /tmp/mysql.sockskip-external-locking#禁用external-locking。參見external-locking的解釋：Use system (external) locking (disabled by default). With this option enabled you can run myisamchk to test not repair) tableswhile the MySQL server is running.  Disable with --skip-external-locking.key_buffer_size = 256M# The size of the buffer used for index blocks for MyISAM tables. Increase this to get better index handling (for all reads and multiple writes) to as much asyou can affordmax_allowed_packet = 1M# Max packet length to send to or receive from the servertable_open_cache = 256#The number of cached open tablessort_buffer_size = 1M# Each thread that needs to do a sort allocates a buffer of this size每個需要排序的線程將會被分配這樣大小的一個bufferread_buffer_size = 1M# Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increasethis valuethread_cache_size = 8# How many threads we should keep in a cache for reusequery-cache-type=name# OFF = Don't cache or retrieve results. ON = Cache all results except SELECT SQL_NO_CACHE ... queries. DEMAND = Cache only SELECT SQL_CACHE ... queriesquery_cache_size= 16M#The memory allocated to store results from old queries #查詢快取的記憶體總大小，其必須是1024的整數倍，單位為位元組。MySQL啟動時，一次性分配並且初始化這裡指定大小的記憶體空間。改變其值，MySQL會立刻刪除所有的緩衝對象並重新設定其大小及初始化。在效能較強的泛型服務器上，查詢快取可能會成影響伺服器擴充的因素，因為它存在成為伺服器資源競爭單點的可能性，在多核心的伺服器上甚至還有可能導致服務進程宕掉。不會緩衝的內容：使用者自定函數、使用者自訂的變數、暫存資料表、mysql庫的系統資料表、列層級的許可權、儲存函數、不確定資料query_cache_min_res_unit = ## The minimum size for blocks allocated by the query cache 儲存緩衝的最小記憶體塊；這個值過小，會減少空間浪費，但會導致更頻繁的記憶體塊申請操作；設定的過大，會有著更高的片段產生率。可以通過(query_cache_size-Qcache_free_memory)/Qcache_queryes_in_cache來獲得一個接近理想的值。同時，如果Qcache_free_blocks存在空閑塊，但Qcache_lowmem_prunes的值仍然在增長，則表明片段過多導致了緩衝結果會過早刪除。query_cache_limit = ## Don't cache results that are bigger than this #MySQL允許緩衝的單個緩衝對象的最大值。不過，MySQL只有在查詢的所有結果都返回後才知道其是否超出此大小，但其在查詢一開始便會嘗試使用緩衝儲存查詢結果，一旦發現逾時可緩衝最大值則會從緩衝中將其刪除，並增大Qcache_not_cached的值。因此，如果知道某查詢的結果會超出可快取的最大對象，則應該在查詢語句中使用SQL_NO_CACHE。query_cache_wlock_invalidate =# Invalidate queries in query cache on LOCK for write如果某個資料表被其它的thread鎖住，是否仍然從查詢快取中返回結果。OFF表示返回。thread_concurrency = 4# Permits the application to give the threads system a hint for the desired number of threads that should be run at the same time. Try number of CPU's*2 forthread_concurrencydatadir=/mydata/datainnodb_file_per_table = 1 #啟用InnoDB表每表一檔案，預設所有庫使用一個資料表空間，這個在單表備份和恢複中是必須的，而且也便於管理，建議開啟log-bin=/mybinlog/mysql-bin#二進位日誌目錄及檔案名稱首碼log_slow_queries={YES|NO}#是否記錄慢查詢日誌。慢查詢是指查詢的執行時間超出long_query_time參數所設定時間長度的事件。MySQL 5.6將此參數修改為了slow_query_log。作用範圍為全域層級，可用於設定檔，屬動態變數。long_query_time=##設定區別慢查詢與一般查詢的語句執行時間長度。這裡的語句執行時間長度為實際的執行時間，而非在CPU上的執行時間長度，因此，負載較重的伺服器上更容易產生慢查詢。其最小值為0，預設值為10，單位是秒鐘。它也支援毫秒級的解析度。作用範圍為全域或會話層級，可用於設定檔，屬動態變數。--slow-query-log#Log slow queries to a table or log file. Defaults logging to a file hostname-slow.log or a table mysql.slow_log if --log-output=TABLE is used. Must be enabledto activate other slow log options--slow-query-log-file=name#Log slow queries to given log file. Defaults logging to hostname-slow.log. Must be enabled to activate other slow log options--long-query-time=##Log all queries that have taken more than long_query_time seconds to execute to file. The argument will be treated as a decimal value with microsecondprecisioninnodb_flush_log_at_trx_commit:0:the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at atransaction commit；1：(the default) the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file；2: the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it.；For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit=1 andsync_binlog=1 in your master server my.cnf file.CautionMany operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not.Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using abattery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unixcommand hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.innodb_additional_mem_pool_sizeThe size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures。The more tables you have in yourapplication, the more memory you need to allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and writeswarning messages to the MySQL error log. The default value is 8MB.innodb_buffer_pool_sizeThe size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. mysql5.5上預設值128M.如果是專用的DB伺服器，且以InnoDB引擎為主的情境，通常可設定實體記憶體的50%如果是非專用DB伺服器，可以先嘗試設定成記憶體的1/4，如果有問題再調整重要配置參數和變數：sql_log_bin在恢複備份的資料邏輯備份）時，需要關閉二進位日誌記錄，以免將恢複過程也記錄進日誌。innodb_file_per_table = 1啟用InnoDB表每表一檔案，預設所有庫使用一個資料表空間，這個在單表備份和恢複中是必須的，而且也便於管理，建議開啟

 大部分設定檔的參數可在伺服器啟動時作為選項提供。因此可以使用mysqld --help --verbose查看配置參數的解。並且伺服器啟動後可以通過該命令輸出的末尾查看當前伺服器使

用的變數參數值。

 關於設定檔中各參數，更全面、詳細的請參考
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
 請將版本號碼5.5改成你所使用的版本。