設定 MariaDB 10.6
設定 MariaDB 10.6
前言
本文是關於 MariaDB 的一些常用設定，
是以 Ubuntu 環境下為基準，
如果還不會安裝的可以先去看這篇
在 Ubuntu 安裝 MariaDB 資料庫
在 Windows 安裝 MariaDB 資料庫
路徑
Ubuntu 安裝 MariaDB 預設的設定檔案路徑為 /etc/mysql/my.cnf
常用設定參考
以下是參考官方文件同時記錄一些自己有使用過的設定，
實際設定還是要根據使用者的需求調整。
設定區塊 [mysqld]
設定 mysql 資料庫的資料的存放目錄，必須是 data
預設值 /var/lib/mysql
datadir = /var/lib/mysql
mysql 服務端預設監聽(listen on)的 TCP/IP 埠
port = 3306
伺服器使用的預設編碼
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
指定每次連線時執行的 SQL
包含一個或多個 SQL 語句的字符串，用分號分隔，伺服器將為每個連接的客戶端執行這些語句。 如果其中一個語句存在語法錯誤，則客戶端將無法連接。
表示初始化連線都設定為 utf8mb4 編碼
init_connect = 'SET NAMES utf8mb4'
指定索引緩衝區的大小
決定了索引處理的速度，尤其是索引讀的速度，建議設定成實體記憶體的 1/4，
甚至實體記憶體的 30%-40%，如果設定太大，系統就會頻繁的換頁，降低系統效能
key_buffer_size = 16M
允許最大傳輸大小
可以增大此值以便於 server 端接收更大的 SQL
max_allowed_packet = 4M
分配設定的記憶體
在每個 connection 第一次需要使用這個 buffer 的時候，一次性分配設定的記憶體
sort_buffer_size = 512K
網路緩衝區大小
不可超過 1M，且必須小於等於 max_allowed_packet
net_buffer_length = 8K
讀取緩衝區大小
對資料表進行順序讀取的請求將分配一個讀取緩衝區，
如果對資料表的順序讀取請求非常頻繁，
可以通過增加該變數值以及記憶體緩衝區大小提高其效能。
read_buffer_size = 256K
讀取隨機緩衝區大小
引數用在 sort 查詢之後 ，以保證獲取以順序的方式獲取到查詢的資料。
如果你有很多 order by 查詢語句，增長這值能夠提升效能
read_rnd_buffer_size = 512K
REPAIR TABLE
設定在 REPAIR TABLE，或者用 CREATE INDEX 建立索引或 ALTER TABLE 的過程中排序索引所分配的緩衝區大小。可設定範圍 4Bytes 至 4GB，預設為 8MB。
myisam_max_sort_file_size = 2G
myisam_sort_buffer_size = 8M
錯誤日誌輸出目的地
log_error = "mysql_error.log"
設定 server-id
如果不設定則 server-id 是根據伺服器 ip 地址後 2 位生成的，預設 0 或 1
server-id = 1
sql_mode
設定 SQL 語句檢查
- NO_ZERO_IN_DATE > 在嚴格模式下，不允許日期和月份為零
- NO_ZERO_DATE > mysql 資料庫不允許插入零日期，插入零日期會丟擲錯誤而不是警告。
- NO_ENGINE_SUBSTITUTION > 如果需要的儲存引擎被禁用或未編譯，那麼丟擲錯誤。
sql_mode = NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
跳過 InnoDB 模式
如果使用的是 InnoDB 資料庫引擎，請註釋以下內容
skip-innodb
放置表空間資料的目錄，
預設在 mysql 的資料目錄，設定到和 MySQL 安裝檔案不同的分割槽可以提高效能。
innodb_data_home_dir = D:/WNMP/mariadb-10.6.7-winx64/data
指定表資料和索引儲存的空間，可以是一個或者多個檔案。
最後一個資料檔案必須是自動擴充的，也只有最後一個檔案允許自動擴充。
當空間用完後，自動擴充資料檔案就會自動增長（以 8MB 為單位）以容納額外的資料。
例如：
innodb_data_file_path = /disk1/ibdata1:900M; /disk2/ibdata2:50M:autoextend
兩個資料檔案放在不同的磁碟上。
資料首先放在 ibdata1 中，當達到 900M 以後，資料就放在 ibdata2 中。
一旦達到 50MB，ibdata2 將以 8MB 為單位自動增長。
如果磁碟滿了，需要在另外的磁碟上面增加一個資料檔案。
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = D:/WNMP/mariadb-10.6.7-winx64/data
innodb_log_arch_dir = D:/xampp/mysql/data
緩衝區記憶體大小
這是 InnoDB 最重要的設定，對 InnoDB 效能有決定性的影響。
預設的設定只有 8M，所以預設的資料庫設定下面 InnoDB 效能很差。
在只有 InnoDB 儲存引擎的資料庫伺服器上面，可以設定 60-80%的記憶體。
更精確一點，在記憶體容量允許的情況下面設定比 InnoDB tablespaces 大 10%的記憶體大小。
innodb_buffer_pool_size = 8165M
事務日誌檔案大小
該引數決定了 recovery speed。太大的話 recovery 就會比較慢，太小了影響查詢效能，一般取 256M 可以兼顧效能和 recovery 的速度
innodb_log_file_size = 256M
事務日誌緩衝大小
確保有足夠大的日誌緩衝區來儲存髒資料在被寫入到日誌檔案之前
innodb_log_buffer_size = 8M
事務日誌寫入策略
innodb_flush_log_at_trx_commit = 0
- 0：log buffer 將每秒一次地寫入 log file 中，並且 log file 的 flush(刷到磁碟)操作同時進行。該模式下在事務提交的時候，不會主動觸發寫入磁碟的操作。
innodb_flush_log_at_trx_commit = 1
- 1：每次事務提交時 MySQL 都會把 log buffer 的資料寫入 log file，並且 flush(刷到磁碟)中去，該模式為系統預設。
innodb_flush_log_at_trx_commit = 2
- 2：每次事務提交時 MySQL 都會把 log buffer 的資料寫入 log file，但是 flush(刷到磁碟)操作並不會同時進行。該模式下，MySQL 會每秒執行一次 flush(刷到磁碟)操作。
InnoDB 等待最長時間
參數單位為秒
innodb_lock_wait_timeout = 50
指定所有 InnoDB 資料表空間的頁面大小
innodb_page_size = 32K
大小寫敏感
表名存儲為給定的大小和比較是區分大小寫的
lower_case_table_names = 0
表名存儲在磁盤是小寫的，但是比較的時候是不區分大小寫
lower_case_table_names = 1
表名存儲為給定的大小寫但是比較的時候是小寫的
lower_case_table_names = 2
設定區塊 [client]
連接到資料庫時使用的密碼。
password = your_password
用於連接的 TCP/IP 端口號。
port = port_num
資料庫的外掛目錄。
plugin-dir = /path/to/plugin/directory
設定區塊 [mysqldump]
修改資料輸入上限
max_allowed_packet = 16M
設定區塊 [isamchk]
指定索引緩衝區的大小
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
設定區塊 [myisamchk]
指定索引緩衝區的大小
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M