洞悉本質在不確定的世界預見未來

MariaDB / MySQL配置文件my.cnf文件詳解

本文的MariaDB / MySQL參數配置僅供參考,不要盲目的接受這些建議。每個 MySQL 設置都是不同的,在進行任何更改之前需要慎重考慮。每次更改此文件後你需要重啟 MySQL 服務,以使更改生效。

對於存儲引擎選擇有2個原則:

第一個原則,大量讀少量寫 選用MyISAM,大量寫少量讀選用InnoDB。針對不同的需求使用不同的存儲引擎。
第二個原則,能不用InnoDB盡量不用InnoDB。

總之,如果你想追求99.9%的穩定性,方便的擴展性和高可用性還是盡量用MyISAM吧。

# 設置mysql-server相關信息
[mysqld]

# 基礎設置
# 
# 指定運行用戶為mysql(默認是以mysql用戶運行的)
user		= mysql
# 服務端pid進程文件,若丟失則重啟Mysql重新生成,若重啟失敗,
則可能由於mysqld進程未殺死,用pkill mysql後則能重啟成功Mysql
pid-file	= /var/run/mysqld/mysqld.pid
# 指定MySQL客戶端程序和服務端之間一個套接字通信文件
socket		= /var/run/mysqld/mysqld.sock
# MySQL/ MariaDB服務端監聽端口
port		= 3306
# MySQL/ MariaDB安裝目錄
basedir		= /usr
# 數據庫數據文件存放目錄
datadir		= /var/lib/mysql
# MySQL/ MariaDB保存臨時文件,如基於磁盤的大型排序,簡單的臨時表
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
# 防止外部鎖定表,允許外部文件級別的鎖。打開文件鎖會對性能造成負面影響
skip-external-locking

# 監聽服務器多個IP時,請保持0.0.0.0,這樣被綁定的IP地址才能接收客戶端的連接請求
bind-address		= 127.0.0.1
# 監聽IPV6的連接請求
# bind-address = ::

# 開啟mysql的dns反查功能。關閉反查,則不帶參數,
skip-name-resolve=1

# 微調
# 設置高速緩存大小,MyISAM存儲引擎的索引參數。
# 如果數據庫以MyISAM為主,可以適當增加此值,如:32M、64M。
key_buffer_size	= 16M
# 允許接收最大數據包的大小,防止服務器發送過大的數據包。
# 可以設置為16MB或者更大,但設置的太大也可能有危險。
max_allowed_packet	= 16M
thread_stack		= 192K
# Join操作使用內存,用於表間關聯緩存的大小,和sort_buffer_size一樣,
# 該參數對應的分配內存也是每個連接獨享。
join_buffer_size = 2M
# 表示緩存的最大線程數,這個值默認8
# 表示可以重新利用保存,在緩存中線程的數量,當斷開連接時如果緩存中還有空間,
# 那麼客戶端的線程將被放到緩存中,如果線程重新被請求,那麼請求將從緩存中讀取
# 如果緩存中是空的或者是新的請求,那麼這個線程將被重新創建,
thread_cache_size       = 8
# 啟動MyISAM自動恢復功能,有4個值,可結合使用。
# default:不用備份,強制或快速檢查恢復。
# backup:如果數據文件在恢復時被更改,會將MYD文件的備份保存為tbl_name‐datetime.BAK。
# force:即使MYD文件丟失多於一行,任進行恢復。
# quick:如果沒有任何的delete,就不檢查行。
myisam_recover_options  = BACKUP
# 表示允許同時訪問 MySQL 服務器的最大連接數。當超過該次數,MYSQL服務器將禁止
# host的連接請求,直到mysql服務器重啟或通過flush hosts命令清空此host的相關信息。
# max_connections        = 100
# 指示表調整緩衝區大小。設置表高速緩存的數目。
# table_cache            = 64
# 該值一般建議設定cpu的個數*2
# 屬重點優化參數,該參數在MariaDB默認配置文件裡未出現
# thread_concurrency     = 10

# 指定單個查詢能夠使用的緩衝區大小,默認1M。
query_cache_limit	= 1M
# 緩存select語句和結果集大小的參數。查詢緩存會存儲一個select查詢的文本
# 與被傳送到客戶端的相應結果。如果之後接收到一個相同的查詢,服務器會從查詢
# 緩存中檢索結果,而不是再次分析和執行這個同樣的查詢。如果你的環境中寫操作很少,
# 讀操作頻繁,那麼打開query_cache_type=1,會對性能有明顯提升。
# 如果寫操作頻繁,則應該關閉它(query_cache_type=0)。
query_cache_size        = 16M

# 記錄錯誤日誌文件
log_error = /var/log/mysql/error.log

# 啟用慢速查詢日誌記錄文件以查看持續時間特別長的查詢
# slow_query_log_file	= /var/log/mysql/mariadb-slow.log
# 慢日誌超時設置,默認是10s,這裡的1 是1s,超出時長為1秒,1秒後開始記錄
# long_query_time = 1
# log_slow_rate_limit	= 1000
# log_slow_verbosity	= query_plan
# log-queries-not-using-indexes

# 唯一的服務辨識號,數值位於 1 到 2^32-1之間,在master和slave
# 上都需要設置,如果 “master-host” 沒有被設置,則默認為1, 
# 但是如果忽略此選項,MySQL不會作為master生效
# server-id		= 1
# 打開二進制日誌功能
# log_bin			= /var/log/mysql/mysql-bin.log
# 啟用二進制日誌後,保留日誌的天數。服務器會自動清理指定天數前的日誌文件,
# 如果不設置則會導致服務器空間耗盡。一般設置為7~14天。
expire_logs_days	= 10
# 每個binlog日誌大小
max_binlog_size   = 100M
# binlog_do_db		= include_database_name
# binlog_ignore_db	= exclude_database_name

# * InnoDB
# 如果只使用 InnoDB 表, 請取消下列選項的注釋
# default_storage_engine = InnoDB
# 設置獨立表空間,innodb 默認是1個表空間
# innodb_file_per_table = 1
# 默認情況下,在 /var/lib/mysql/ 中使用10MB數據文件.
# innodb_data_home_dir = /var/lib/mysql
# 指定數據文件,初始大小,指定擴展大小 注意與數據庫中初始文件大小保持一致
# innodb_data_file_path = ibdata1:2G;ibdata2:10M:autoextend
# 日誌記錄文件目錄
# innodb_log_group_home_dir = /var/lib/mysql

# 這對Innodb表來說非常重要。謹防內存使用設置得太高。
# Innodb相比MyISAM表對緩衝更為敏感。MyISAM可以在默認的 key_buffer_size 
# 設置下運行的可以,然而Innodb在默認的 innodb_buffer_pool_size 
# 設置下卻跟蝸牛似的。由於Innodb把數據和索引都緩存起來,無需留給操作系統
# 太多的內存,因此如果只需要用Innodb的話則可以設置它高達 50-80% 的可用內存。
# 一些應用於 key_buffer 的規則有 — 如果你的數據量不大,並且不會暴增,
# 那麼無需把 innodb_buffer_pool_size 設置的太大了。
# 不要設置過大,否則,由於物理內存的競爭可能導致操作系統的換頁顛簸。
# innodb_buffer_pool_size = 256M

# 附加緩存池大小
# innodb_additional_mem_pool_size = 20M
# 設置 .._log_file_size 為  buffer pool size 的 25 % 左右
# innodb_log_file_size = 64M

# 此參數確定寫日誌文件所用的內存大小,以M為單位。緩衝區更大能提高性能,
# 但意外的故障將會丟失數據,由於基本上每秒都會刷新一次,所以沒有必要將此值
# 設置的太大(甚至對於長事務而言),MySQL開發人員建議設置為1-8M之間
# innodb_log_buffer_size = 8M

# 默認1,事務提交就刷新日誌到硬盤;
# 設為2,刷新到操作系統緩存,但性能提高很多,設為 0 則可能丟事務.
# innodb_flush_log_at_trx_commit = 1
# 表死鎖的超時釋放時間,單位秒
# innodb_lock_wait_timeout = 50

# 安全功能
# chroot = /var/lib/mysql/
#
# 為了生成SSL證書,您可以使用例如GUI工具“ tinyca”。
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#僅接受使用最新和最安全的TLS協議版本的連接。
# ..當使用OpenSSL編譯MariaDB時:
# ssl-cipher=TLSv1.2
# ..when MariaDB使用YaSSL編譯時(Debian中的默認設置):
# ssl=on

# 服務器和數據庫默認字符集 MySQL / MariaDB默認為Latin1,
# 但在Debian中,默認為完整utf8 4字節字符集。 
character-set-server  = utf8mb4
# 客戶端校正字符集
collation-server      = utf8mb4_general_ci

# 以safe方式啟動數據庫,相比於mysqld,會在服務啟動後繼續監控服務狀態,死機時重啟
[mysqld_safe]
open-files-limit = 8192

[mysqldump]
# 快速導出到輸出流/硬盤,不在內存中緩存。在導出非常巨大的表時需要此項
quick
# 設定在網絡傳輸中一次可以傳輸的最大數據包,系統默認為1M,最大可以是1G。
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
# 如果對 SQL不熟悉,可以將下面的註釋符去掉,拒絕無where的不安全操作.
# safe-updates

# 是個命令,用於mysqld在不使用的情況修復表或者崩潰的情況下回復表
[myisamchk]  
key_buffer_size   = 64M
# Sort_Buffer_Size 是一個connection級參數,在每個
# connection(session)第一次需要使用這個buffer的時候,一次性分配設置的內存。
# Sort_Buffer_Size 並不是越大越好,由於是connection級的參數,
# 過大的設置+高併發可能會耗盡系統內存資源。例如:500個連接將會消耗
# 500*sort_buffer_size(8M)=4G內存。Sort_Buffer_Size 超過2KB的時候,
# 就會使用mmap() 而不是 malloc() 來進行內存分配,導致效率降低。		
sort_buffer_size  = 1M					
read_buffer = 2M
write_buffer = 2M

# mysqlhotcopy使用lock tables、flush tables和cp或scp來
# 快速備份數據庫.它是備份數據庫或單個表最快的途徑,完全屬於物理備份,
# 但只能用於備份MyISAM存儲引擎和運行在數據庫目錄所在的機器上
# 與mysqldump備份不同,mysqldump屬於邏輯備份,備份時是執行的sql語句。
# 使用mysqlhotcopy命令前需要要安裝相應的軟件依賴包..
[mysqlhotcopy]
interactive-timeout	
expire_logs_days  = 10
back to top