1.MySQL客戶端配置

[client]
port = 5550

MySQL客戶端端口號，默認(rèn)3306

socket = /usr/local/mysql/tmp/mysql.sock

用于本地連接的Unix套接字文件存放路徑

default_character_set = utf8mb4

MySQL客戶端字符集，mysql的utf8字符集默認(rèn)為3位的，不支持emoji表情及部分不常見的漢字，故推薦使用utf8mb4

2.MySQL命令行配置

[mysql]
no_auto_rehash

不通過 TAB 鍵提示

auto-rehash

允許通過 TAB 鍵提示

default_character_set = utf8mb4

MySQL命令行字符集

socket = /usr/local/mysql/tmp/mysql.sock

用于本地連接的Unix套接字文件存放路徑

max_allowed_packet = 67108864

服務(wù)所能處理的請求包的最大大小以及服務(wù)所能處理的最大的請求大小(當(dāng)與大的BLOB 字段一起工作時(shí)相當(dāng)必要)， 每個(gè)連接獨(dú)立的大小.大小動態(tài)增加。 設(shè)置最大包,限制server接受的數(shù)據(jù)包大小，避免超長SQL的執(zhí)行有問題 默認(rèn)值為64M，當(dāng)MySQL客戶端或mysqld服務(wù)器收到大于max_allowed_packet 字節(jié)的信息包時(shí)，將發(fā)出“信息包過大”錯(cuò)誤，并關(guān)閉連接。對于某些客戶端，如果通信信息包過大，在執(zhí)行查詢期間，可能會遇到“丟失與 MySQL 服務(wù)器的連接”錯(cuò)誤。默認(rèn)值 64M(mysql8.0,mysql5.7為4MB)。

3.MySQL服務(wù)端配置

[mysqld]

1)服務(wù)端General配置

port = 5550

MySQL服務(wù)端監(jiān)聽的TCP/IP端口，默認(rèn)3306

socket = /usr/local/mysql/tmp/mysql.sock

用于本地連接的Unix套接字文件存放路徑

pid_file = /usr/local/mysql/tmp/mysql.pid

進(jìn)程ID文件存放路徑

basedir = /usr/local/mysql

MySQL軟件安裝路徑

datadir = /var/lib/mysql/mysql_data

MySQL數(shù)據(jù)文件存放路徑

tmpdir = /var/lib/mysql/mysql_tmp

MySQL臨時(shí)文件存放路徑 .例如,它被用來處理基于磁盤的大型排序,和內(nèi)部排序一樣，以及簡單的臨時(shí)表.如果不創(chuàng)建非常大的臨時(shí)文件,將其放置到 swapfs/tmpfs 文件系統(tǒng)上也許比較好。另一種選擇是也可以將其放置在獨(dú)立的磁盤上.可以使用”;”來放置多個(gè)路徑，他們會按照 roud-robin 方法被輪詢使用.

character_set_server = utf8mb4

MySQL服務(wù)端字符集

collation_server = utf8mb4_0900_ai_ci

MySQL服務(wù)端校對規(guī)則，也可以utf8_general_ci

default_storage_engine = InnoDB

默認(rèn)存儲引擎

autocommit = OFF

默認(rèn)為ON，設(shè)置為OFF，關(guān)閉事務(wù)自動提交

transaction_isolation = REPEATABLE-READ

設(shè)定默認(rèn)的事務(wù)隔離級別.可用的級別如下:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ,SERIALIZABLE
1.READ UNCOMMITTED-讀未提交
2.READ COMMITTE-讀已提交
3.REPEATABLE READ -可重復(fù)讀
4.SERIALIZABLE -串行

event_scheduler = ON

開啟事件調(diào)度器event_scheduler

explicit_defaults_for_timestamp = ON

控制TIMESTAMP數(shù)據(jù)類型的特性，默認(rèn)ON，設(shè)置為OFF，update 時(shí)timestamp列關(guān)閉自動更新。

lower_case_table_names = 1

庫名、表名是否區(qū)分大小寫。默認(rèn)為0，設(shè)置1，不區(qū)分大小寫，創(chuàng)建的表、數(shù)據(jù)庫都以小寫形式存放磁盤。

2)Network & Connection參數(shù)配置

max_connections = 1000

指定MySQL允許的最大連接進(jìn)程數(shù)。如果在訪問數(shù)據(jù)庫時(shí)經(jīng)常出現(xiàn)"Too Many Connections"的錯(cuò)誤提 示，則需要增大該參數(shù)值。

max_user_connections = 800 # custom

每個(gè)數(shù)據(jù)庫用戶的最大連接，（同一個(gè)賬號能夠同時(shí)連接到mysql服務(wù)的最大連接數(shù)），默認(rèn)為0，表示不限制。

back_log = 50

MySQL監(jiān)聽TCP端口時(shí)設(shè)置的積壓請求棧大小，默認(rèn)-1,表示自動調(diào)整大??；最大值65535

max_connect_errors = 99999999

如果某個(gè)用戶發(fā)起的連接 error 超過該數(shù)值，則該用戶的下次連接將被阻塞，直到管理員執(zhí)行 flush hosts ; 命令或者服務(wù)重啟， 防止黑客非法的密碼以及其他在鏈接時(shí)的錯(cuò)誤會增加此值

interactive_timeout = 3600

關(guān)閉連接之前，允許 interactive_timeout（取代了wait_timeout）秒的不活動時(shí)間?？蛻舳说臅?wait_timeout 變量被設(shè)為會話interactive_timeout 變量的值。如果前端程序采用短連接，建議縮短這2個(gè)值, 如果前端程序采用長連接，可直接注釋掉這兩個(gè)選項(xiàng)，默認(rèn)配置(8小時(shí))

open_files_limit = 65535

MySQL打開的文件描述符限制，默認(rèn)最小1024;當(dāng)open_files_limit沒有被配置的時(shí)候，比較max_connections5和ulimit-n的值，哪個(gè)大用哪個(gè)，當(dāng)open_file_limit被配置的時(shí)候，比較open_files_limit和max_connections5的值，哪個(gè)大用哪個(gè)。

connect_timeout = 30

連接超時(shí)之前的最大秒數(shù),在 Linux 平臺上，該超時(shí)也用作等待服務(wù)器首次回應(yīng)的時(shí)間。

wait_timeout = 3600

等待關(guān)閉連接的時(shí)間

net_read_timeout = 30

從服務(wù)器讀取信息的超時(shí)

net_write_timeout = 60

從服務(wù)器寫入信息的超時(shí)

net_retry_count = 10

如果某個(gè)通信端口的讀操作中斷了，在放棄前重試多次

net_buffer_length = 8192

包消息緩沖區(qū)初始化為 net_buffer_length 字節(jié)，但需要時(shí)可以增長到 max_allowed_packet 字節(jié)

3)Thread & Buffer參數(shù)配置

sort_buffer_size = 2097152

查詢排序時(shí)所能使用的緩沖區(qū)大小。排序緩沖被用來處理類似 ORDER BY 以及 GROUP BY 隊(duì)列所引起的排序.一個(gè)用來替代的基于磁盤的合并分類會被使用.查看 “Sort_merge_passes” 狀態(tài)變量. 在排序發(fā)生時(shí)由每個(gè)線程分配 注意：該參數(shù)對應(yīng)的分配內(nèi)存是每連接獨(dú)占！memeory < 16GB, suggest to set 2M

read_buffer_size = 2097152

讀查詢操作所能使用的緩沖區(qū)大小。和 sort_buffer_size 一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每連接獨(dú)享！用來做 MyISAM 表全表掃描的緩沖大小.當(dāng)全表掃描需要時(shí),在對應(yīng)線程中分配.memeory < 16GB, suggest to set 2M

read_rnd_buffer_size = 2097152 # custom: memeory < 16GB, suggest to set 2M

MyISAM 以索引掃描(Random Scan)方式掃描數(shù)據(jù)的 buffer大小 memeory < 16GB, suggest to set 2M

join_buffer_size = 2097152 # custom: memeory < 16GB, suggest to set 2M

聯(lián)合查詢操作所能使用的緩沖區(qū)大小，和 sort_buffer_size 一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每連接獨(dú)享!此緩沖被使用來優(yōu)化全聯(lián)合(full JOINs 不帶索引的聯(lián)合).類似的聯(lián)合在極大多數(shù)情況下有非常糟糕的性能表現(xiàn), 但是將此值設(shè)大能夠減輕性能影響.通過 “Select_full_join”狀態(tài)變量查看全聯(lián)合的數(shù)量， 當(dāng)全聯(lián)合發(fā)生時(shí),在每個(gè)線程中分配。memeory < 16GB, suggest to set 2M

bulk_insert_buffer_size = 64M

MyISAM 使用特殊的類似樹的 cache 來使得突發(fā)插入(這些插入是,INSERT … SELECT, INSERT … VALUES (…), (…), …, 以及 LOAD DATAINFILE) 更快. 此變量限制每個(gè)進(jìn)程中緩沖樹的字節(jié)數(shù).設(shè)置為 0 會關(guān)閉此優(yōu)化.為了最優(yōu)化不要將此值設(shè)置大于 “key_buffer_size”.當(dāng)突發(fā)插入被檢測到時(shí)此緩沖將被分配MyISAM 用在塊插入優(yōu)化中的樹緩沖區(qū)的大小。注釋：這是一個(gè) per thread 的限制 （ bulk 大量）.此緩沖當(dāng) MySQL 需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE到一個(gè)空表中引起重建索引時(shí)被分配.這在每個(gè)線程中被分配.所以在設(shè)置大值時(shí)需要小心.

thread_cache_size = 20

在 cache 中保留多少線程用于重用.當(dāng)一個(gè)客戶端斷開連接后,如果 cache 中的線程還少于 thread_cache_size,則客戶端線程被放入 cache 中.這可以在需要大量新連接的時(shí)候極大的減少線程創(chuàng)建的開銷(一般來說如果有好的線程模型的話,這不會有明顯的性能提升.)服務(wù)器線程緩存這個(gè)值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開連接時(shí)如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個(gè)線程將被重新創(chuàng)建,如果有很多新的線程，增加這個(gè)值可以改善系統(tǒng)性能.通過比Connections 和 Threads_created 狀態(tài)的變量，可以看到這個(gè)變量的作用。
根據(jù)物理內(nèi)存設(shè)置規(guī)則如下：
1G —> 8
2G —> 16
3G —> 32
大于3G —> 64

thread_stack = 192K

線程使用的堆大小. 此容量的內(nèi)存在每次連接時(shí)被預(yù)留.MySQL 本身常不會需要超過 64K 的內(nèi)存，如果使用需要大量堆的 UDF 函數(shù)或者操作系統(tǒng)對于某些操作需要更多的堆,也許需要將其設(shè)置的更高一點(diǎn).默認(rèn)設(shè)置足以滿足大多數(shù)應(yīng)用

query_cache_type = 0

關(guān)閉查詢緩存

query_cache_size = 64M

指定 MySQL 查詢緩沖區(qū)的大小。在高并發(fā)，寫入量大的系統(tǒng)，建議把該功能禁掉。 可以通過在 MySQL 控制臺執(zhí)行以下命令觀察：

> SHOW VARIABLES LIKE '%query_cache%';
> SHOW STATUS LIKE 'Qcache%';

如果 Qcache_lowmem_prunes 的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況；如果 Qcache_hits 的值非常大，則表明查詢緩沖使用非常頻繁，如果該值較小反而會影響效率，那么可以考慮不用查詢緩沖； Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多。

query_cache_limit = 2M

不緩存查詢大于該值的結(jié)果.只有小于此設(shè)定值的結(jié)果才會被緩沖, 此設(shè)置用來保護(hù)查詢緩沖,防止一個(gè)極大的結(jié)果集將其他所有的查詢結(jié)果都覆蓋.

tmp_table_size = 67108864

臨時(shí)表的最大大小，如果超過該值，則結(jié)果放到磁盤中,此限制是針對單個(gè)表的,而不是總和.

max_heap_table_size = 8388608

獨(dú)立的內(nèi)存表所允許的最大容量.此選項(xiàng)為了防止意外創(chuàng)建一個(gè)超大的內(nèi)存表導(dǎo)致永盡所有的內(nèi)存資源.

table_definition_cache = 400

表定義緩存區(qū)，緩存frm文件。表定義(global)是全局的，可以被所有連接有效的共享。

table_open_cache = 512

所有SQL線程可以打開表緩存的數(shù)量，緩存ibd/MYI/MYD文件。 打開的表(session級別)是每個(gè)線程，每個(gè)表使用。

table_open_cache_instances = 4

對table cache 能拆成的分區(qū)數(shù)，用于減少鎖競爭，最大值64.

4)Safety參數(shù)配置

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

MySQL支持的SQL語法模式，與其他異構(gòu)數(shù)據(jù)庫之間進(jìn)行數(shù)據(jù)遷移時(shí)，SQL Mode組合模式會有幫助

local_infile = OFF

禁用LOAD DATA LOCAL命令

plugin-load = validate_password.so

加密認(rèn)證插件，強(qiáng)制mysql設(shè)置復(fù)雜密碼

skip-external-locking = ON

避免MySQL的外部鎖定，減少出錯(cuò)幾率，增強(qiáng)穩(wěn)定性

skip-name-resolve = OFF

禁止 MySQL 對外部連接進(jìn)行 DNS 解析，使用這一選項(xiàng)可以消除 MySQL 進(jìn)行 DNS 解析的時(shí)間。但需要注意，如果開啟該選項(xiàng)，則所有遠(yuǎn)程主機(jī)連接授權(quán)都要使用 IP 地址方式，否則 MySQL 將無法正常處理連接請求！

skip-networking

開啟該選項(xiàng)可以徹底關(guān)閉 MySQL 的 TCP/IP 連接方式，如果 WEB 服務(wù)器是以遠(yuǎn)程連接的方式訪問 MySQL 數(shù)據(jù)庫服務(wù)器則不要開啟該選項(xiàng)！否則將無法正常連接！ 如果所有的進(jìn)程都是在同一臺服務(wù)器連接到本地的 mysqld, 這樣設(shè)置將是增強(qiáng)安全的方法。

sysdate-is-now = 1

把SYSDATE 函數(shù)編程為 NOW的別名

skip-symbolic-links

不能使用連接文件，多個(gè)客戶可能會訪問同一個(gè)數(shù)據(jù)庫，因此這防止外部客戶鎖定 MySQL 服務(wù)器。 該選項(xiàng)默認(rèn)開啟

5)Log參數(shù)配置

General Log

general_log = OFF

將所有到達(dá)MySQL Server的SQL語句記錄下來,默認(rèn)關(guān)閉

general_log_file = /var/lib/mysql/mysql_log/mysql.log

general_log路徑

log_warnings = 1

將警告打印輸出到錯(cuò)誤 log 文件.如果對于MySQL有任何問題，應(yīng)該打開警告 log 并且仔細(xì)審查錯(cuò)誤日志,查出可能的原因.

Slow Log

slow_query_log = ON

指定是否開啟慢查詢?nèi)罩? 慢查詢是指消耗了比 “l(fā)ong_query_time” 定義的更多時(shí)間的查詢.如果 log_long_format 被打開,那些沒有使用索引的查詢也會被記錄.如果你經(jīng)常增加新查詢到已有的系統(tǒng)內(nèi)的話. 一般來說這是一個(gè)好主意,

slow_query_log_file = /var/lib/mysql/mysql_log/slow.log

指定慢日志文件存放位置，可以為空，系統(tǒng)會給一個(gè)缺省的文件host_name-slow.log

long_query_time = 10

設(shè)定慢查詢的閥值，超出次設(shè)定值的SQL即被記錄到慢查詢?nèi)罩荆笔≈禐?0s.所有的使用了比這個(gè)時(shí)間(以秒為單位)更多的查詢會被認(rèn)為是慢速查詢.不要在這里使用”1″, 否則會導(dǎo)致所有的查詢,甚至非?？斓牟樵冺摫挥涗浵聛?由于MySQL 目前時(shí)間的精確度只能達(dá)到秒的級別).

log_queries_not_using_indexes

如果運(yùn)行的SQL語句沒有使用索引，則mysql數(shù)據(jù)庫同樣會將這條SQL語句記錄到慢查詢?nèi)罩疚募小?/p>

long_slow_admin_statements

記錄那些慢的optimize table，analyze table和alter table語句

min_examined_row_limit = 1000

記錄那些由于查找了多余1000次而引發(fā)的慢查詢

log_slow_slave_statements

記錄由Slave所產(chǎn)生的慢查詢

log_output = FILE

參數(shù) log_output 指定了慢查詢輸出的格式，默認(rèn)為 FILE，你可以將它設(shè)為 TABLE，然后就可以查詢 mysql 架構(gòu)下的 slow_log 表了

log_long_format

在慢速日志中記錄更多的信息.一般此項(xiàng)最好打開，打開此項(xiàng)會記錄使得那些沒有使用索引的查詢也被作為到慢速查詢附加到慢速日志里.

Error Log

log_error = /var/lib/mysql/mysql_log/error.log

錯(cuò)誤日志路徑

log_error_verbosity = 2

全局動態(tài)變量，默認(rèn)3，范圍：1～3；表示錯(cuò)誤日志記錄的信息。1：只記錄error信息；2：記錄error和warnings信息；3：記錄error、warnings和普通的notes信息

6)Replication參數(shù)配置

Bin Log

server_id = 12

表示是本機(jī)的序號為12,唯一

log_bin = /var/lib/mysql/mysql_log/binlog/binlog

打開二進(jìn)制日志功能.在復(fù)制(replication)配置中,作為 MASTER 主服務(wù)器必須打開此項(xiàng).如果需要從最后的備份中做基于時(shí)間點(diǎn)的恢復(fù),也同樣需要二進(jìn)制日志.

log_bin_index = /var/lib/mysql/mysql_log/binlog/binlog.index

二進(jìn)制的索引文件名

binlog_format = ROW

binlog格式，復(fù)制有3種模式STATEMENT，ROW，MIXED

expire_logs_days = 30

超過 30 天的 binlog 刪除

binlog_cache_size = 4194304

在一個(gè)事務(wù)中 binlog 為了記錄 SQL 狀態(tài)所持有的 cache 大小,如果經(jīng)常使用大的,多聲明的事務(wù),可以增加此值來獲取更大的性能.所有從事務(wù)來的狀態(tài)都將被緩沖在 binlog 緩沖中然后在提交后一次性寫入到 binlog 中,如果事務(wù)比此值大, 會使用磁盤上的臨時(shí)文件來替代.此緩沖在每個(gè)連接的事務(wù)第一次更新狀態(tài)時(shí)被創(chuàng)建.session 級別

max_binlog_size = 134217728

如果二進(jìn)制日志寫入的內(nèi)容超出給定值，日志就會發(fā)生滾動。不能將該變量設(shè)置為大于1GB或小于4096字節(jié)。 默認(rèn)值是1GB。如果正使用大的事務(wù)，二進(jìn)制日志還會超過max_binlog_size

binlog_cache_size = 4194304

每個(gè)session分配的binlog緩存大小，事務(wù)提交前產(chǎn)生的日志，記錄到Cache中；事務(wù)提交后，則把日志持久化到磁盤

log_slave_updates = 1

表示slave將復(fù)制事件寫進(jìn)自己的二進(jìn)制日志

sync_binlog = 1

sync_binlog=0（默認(rèn)），事務(wù)提交后MySQL不刷新binlog_cache到磁盤，而讓Filesystem自行決定，或者cache滿了才同步。sync_binlog=n，每進(jìn)行n次事務(wù)提交之后，MySQL將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤。

binlog_rows_query_log_events = ON

將row模式下的sql語句，記錄到binlog日志，默認(rèn)是0(off)

binlog_expire_logs_seconds = 604800 # custom: default(7days) /s
binlog_row_image = minimal

Relay Log

relay_log = /var/lib/mysql/mysql_log/relaylog

定義relay_log的位置和名稱，如果值為空，則默認(rèn)位置在數(shù)據(jù)文件的目錄，文件名為host_name-relay-bin.nnnnnn（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）；

relay_log_index = /var/lib/mysql/mysql_log/relaylog.index

relay_log的索引文件名

relay_log_purge = ON

是否自動清空不再需要中繼日志時(shí)。默認(rèn)值為ON(啟用)

max_relay_log_size = 536870912

標(biāo)記relaylog允許的最大值，如果該值為0，則默認(rèn)值為max_binlog_size(1G)；如果不為0，則max_relay_log_size則為最大的relay_log文件大??；

-- relay_log_recovery = ON # only for slave

啟用relaylog的自動修復(fù)功能，避免由于網(wǎng)絡(luò)之類的外因造成日志損壞，主從停止。

skip-slave-start

啟動 mysql,不啟動復(fù)制

slave_net_timeout = 600

從服務(wù)器也能夠處理網(wǎng)絡(luò)連接中斷。但是，只有從服務(wù)器超過slave_net_timeout 秒沒有從主服務(wù)器收到數(shù)據(jù)才通知網(wǎng)絡(luò)中斷

sync_master_info = 10000

slave更新mysql.slave_master_info表的時(shí)間間隔

sync_relay_log = 10000
sync_relay_log_info = 10000

slave更新mysql.slave_relay_log_info表的時(shí)間間隔

gtid_mode = ON
enforce_gtid_consistency = ON

GTID即全局事務(wù)ID（global transaction identifier），GTID由UUID+TID組成的。
UUID是一個(gè)MySQL實(shí)例的唯一標(biāo)識，TID代表了該實(shí)例上已經(jīng)提交的事務(wù)數(shù)量，并且隨著事務(wù)提交單調(diào)遞增。GTID能夠保證每個(gè)MySQL實(shí)例事務(wù)的執(zhí)行（不會重復(fù)執(zhí)行同一個(gè)事務(wù)，并且會補(bǔ)全沒有執(zhí)行的事務(wù)）。下面是一個(gè)GTID的具體形式：4e659069-3cd8-11e5-9a49-001c4270714e:1-77

auto_increment_offset = 1

雙主復(fù)制中，2臺服務(wù)器的自增長字段初值分別配置為1和2，取值范圍是1 .. 65535

auto_increment_increment = 2

雙主復(fù)制中，2臺服務(wù)器的自增長字段的每次遞增值都配置為2，其默認(rèn)值是1，取值范圍是1 .. 65535

log_replica_updates = 1
skip_replica_start = ON
sync_source_info = 10000

replicate_wild_ignore_table = configdb.% # custom: only for slave

replicate_wild_ignore_table = mysql.% # custom: only for slave

read_only = ON # custom: only for slave

replica_net_timeout = 900

7)InnoDB參數(shù)配置

innodb_data_home_dir = /var/lib/mysql/var/

設(shè)置此選項(xiàng)是如果希望 InnoDB 表空間文件被保存在其他分區(qū).默認(rèn)保存在 MySQL 的 datadir 中.

innodb_file_per_table = 1

InnoDB為獨(dú)立表空間模式，每個(gè)數(shù)據(jù)庫的每個(gè)表都會生成一個(gè)數(shù)據(jù)空間
獨(dú)立表空間優(yōu)點(diǎn)：
1.每個(gè)表都有自已獨(dú)立的表空間。
2.每個(gè)表的數(shù)據(jù)和索引都會存在自已的表空間中。
3.可以實(shí)現(xiàn)單表在不同的數(shù)據(jù)庫中移動。
4.空間可以回收（除drop table操作處，表空不能自已回收）
缺點(diǎn)：
1.單表增加過大，如超過100G
結(jié)論：
共享表空間在Insert操作上少有優(yōu)勢。其它都沒獨(dú)立表空間表現(xiàn)好。當(dāng)啟用獨(dú)立表空間時(shí)，請合理調(diào)整：innodb_open_files

skip-innodb

如果 MySQL 服務(wù)包含 InnoDB 支持但是并不打算使用的話,使用此選項(xiàng)會節(jié)省內(nèi)存以及磁盤空間,并且加速某些部分

innodb_status_file = 1

啟用InnoDB的status file，便于管理員查看以及監(jiān)控等

innodb_open_files = 60000

限制Innodb能打開的表的數(shù)據(jù)，如果庫里的表特別多的情況，請?jiān)黾舆@個(gè)。這個(gè)值默認(rèn)是300

innodb_additional_mem_pool_size = 100M

設(shè)置InnoDB存儲引擎用來存放數(shù)據(jù)字典信息以及一些內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存空間大小，所以當(dāng)我們一個(gè)MySQL Instance中的數(shù)據(jù)庫對象非常多的時(shí)候，是需要適當(dāng)調(diào)整該參數(shù)的大小以確保所有數(shù)據(jù)都能存放在內(nèi)存中提高訪問效率的。

innodb_buffer_pool_size = 2012954214

包括數(shù)據(jù)頁、索引頁、插入緩存、鎖信息、自適應(yīng)哈希索引、數(shù)據(jù)字典信息.InnoDB 使用一個(gè)緩沖池來保存索引和原始數(shù)據(jù), 不像 MyISAM.這里設(shè)置越大,在存取表里面數(shù)據(jù)時(shí)所需要的磁盤 I/O 越少.在一個(gè)獨(dú)立使用的數(shù)據(jù)庫服務(wù)器上,可以設(shè)置這個(gè)變量到服務(wù)器物理內(nèi)存大小的 80%,不要設(shè)置過大,否則,由于物理內(nèi)存的競爭可能導(dǎo)致操作系統(tǒng)的換頁顛簸.注意在 32 位系統(tǒng)上你每個(gè)進(jìn)程可能被限制在 2-3.5G 用戶層面內(nèi)存限制,所以不要設(shè)置的太高.

innodb_buffer_pool_instances = 8

開啟8個(gè)內(nèi)存緩沖池，把需要緩沖的數(shù)據(jù)hash到不同的緩沖池中，這樣可以并行的內(nèi)存讀寫，降低并發(fā)導(dǎo)致的內(nèi)部緩存訪問沖突。InnoDB緩存系統(tǒng)會把參數(shù)innodb_buffer_pool_size指定大小的緩存，平分為innodb_buffer_pool_instances個(gè)buffer_poo

innodb_write_io_threads = 4
innodb_read_io_threads = 4

innodb使用后臺線程處理數(shù)據(jù)頁上的讀寫 I/O(輸入輸出)請求,根據(jù)你的 CPU 核數(shù)來更改,默認(rèn)是4
注:這兩個(gè)參數(shù)不支持動態(tài)改變,需要把該參數(shù)加入到my.cnf里，修改完后重啟MySQL服務(wù),允許值的范圍從 1-64

innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend

InnoDB將數(shù)據(jù)保存在一個(gè)或者多個(gè)數(shù)據(jù)文件中成為表空間.如果你只有單個(gè)邏輯驅(qū)動保存你的數(shù)據(jù),一個(gè)單個(gè)的自增文件就足夠好了.其他情況下.每個(gè)設(shè)備一個(gè)文件一般都是個(gè)好的選擇.也可以配置 InnoDB 來使用裸盤分區(qū)

innodb_file_io_threads = 4

用來同步 IO 操作的 IO 線程的數(shù)量. 此值在 Unix 下被硬編碼為 4,但是在 Windows 磁盤 I/O 可能在一個(gè)大數(shù)值下表現(xiàn)的更好.

innodb_thread_concurrency = 0

在 InnoDB核心內(nèi)的允許線程數(shù)量,InnoDB 試著在 InnoDB 內(nèi)保持操作系統(tǒng)線程的數(shù)量少于或等于這個(gè)參數(shù)給出的限制,最優(yōu)值依賴于應(yīng)用程序,硬件以及操作系統(tǒng)的調(diào)度方式.過高的值可能導(dǎo)致線程的互斥顛簸.默認(rèn)設(shè)置為 0,表示不限制并發(fā)數(shù)，這里推薦設(shè)置為0，更好去發(fā)揮CPU多核處理能力，提高并發(fā)量

innodb_flush_log_at_trx_commit = 1

如果設(shè)置為 1 ,InnoDB 會在每次提交后刷新(fsync)事務(wù)日志到磁盤上,這提供了完整的 ACID 行為.如果愿意對事務(wù)安全折衷, 并且正在運(yùn)行一個(gè)小的事物, 可以設(shè)置此值到 0 或者 2 來減少由事務(wù)日志引起的磁盤 I/O
1.日志寫入日志文件并且日志文件刷新到磁盤.
0.日志寫入日志文件在每次提交后,但是日志文件只有大約每秒才會刷新到磁盤上.
2.日志寫入日志文件并且調(diào)用文件系統(tǒng)的文件寫入到磁盤（存在緩存機(jī)制）

innodb_log_buffer_size = 8388608

用來緩沖日志數(shù)據(jù)的緩沖區(qū)的大小.當(dāng)此值快滿時(shí), InnoDB 將必須刷新數(shù)據(jù)到磁盤上.由于基本上每秒都會刷新一次,所以沒有必要將此值設(shè)置的太大(甚至對于長事務(wù)而言)

innodb_log_file_size = 536870912 # custom(innodb_buffer_pool_size * 25%)

事物日志大小.在日志組中每個(gè)日志文件的大小，應(yīng)該設(shè)置日志文件總合大小到緩沖池大小的5%~100%，來避免在日志文件覆寫上不必要的緩沖池刷新行為.不論如何, 請注意一個(gè)大的日志文件大小會增加恢復(fù)進(jìn)程所需要的時(shí)間.

innodb_log_files_in_group = 2

在日志組中的文件總數(shù).通常來說 2~3 是比較好的.

innodb_log_buffer_size = 8388608

用來緩沖日志數(shù)據(jù)的緩沖區(qū)的大小.當(dāng)此值快滿時(shí), InnoDB 將必須刷新數(shù)據(jù)到磁盤上.由于基本上每秒都會刷新一次,所以沒有必要將此值設(shè)置的太大(甚至對于長事務(wù)而言)

innodb_log_group_home_dir = /var/lib/mysql/var/

InnoDB 的日志文件所在位置. 默認(rèn)是 MySQL 的 datadir.你可以將其指定到一個(gè)獨(dú)立的硬盤上或者一個(gè) RAID1 卷上來提高其性能

innodb_max_dirty_pages_pct = 60

InnoDB主線程刷新緩存池中的數(shù)據(jù)，使臟數(shù)據(jù)比例小于 90%,這是一個(gè)軟限制,不被保證絕對執(zhí)行.

innodb_lock_wait_timeout = 100

InnoDB 事務(wù)在被回滾之前可以等待一個(gè)鎖定的超時(shí)秒數(shù)。InnoDB 在它自己的 鎖定表中自動檢測事務(wù)死鎖并且回滾事務(wù)。 InnoDB 用 LOCK TABLES 語句注意到鎖定設(shè)置。默認(rèn)值是 50 秒

innodb_flush_method = O_DIRECT

InnoDB 用來刷新日志的方法.表空間總是使用雙重寫入刷新方法.有三個(gè)值fdatasync(默認(rèn))，O_DSYNC，O_DIRECT
默認(rèn)是fdatasync，調(diào)用fsync()去刷數(shù)據(jù)文件與redo log的buffer
為O_DSYNC時(shí)，innodb會使用O_SYNC方式打開和刷寫redo log,使用fsync()刷寫數(shù)據(jù)文件
為O_DIRECT時(shí)，innodb使用O_DIRECT打開數(shù)據(jù)文件，使用fsync()刷寫數(shù)據(jù)文件跟redo log

innodb_force_recovery=1

如果發(fā)現(xiàn) InnoDB 表空間損壞, 設(shè)置此值為一個(gè)非零值可能幫助導(dǎo)出表.從1 開始并且增加此值直到能夠成功導(dǎo)出表。

innodb_fast_shutdown

加速 InnoDB 的關(guān)閉. 這會阻止 InnoDB 在關(guān)閉時(shí)做全清除以及插入緩沖合并.這可能極大增加關(guān)機(jī)時(shí)間, 但是取而代之的是 InnoDB 可能在下次啟動時(shí)做這些操作.

innodb_sync_spin_loops = 40

自旋鎖的輪轉(zhuǎn)數(shù)，可以通過show engine innodb status來查看。#如果看到大量的自旋等待和自旋輪轉(zhuǎn)，則它浪費(fèi)了很多cpu資源。浪費(fèi)cpu時(shí)間和無謂的上下文切換之間可以通過該值來平衡。

innodb_support_xa = ON

第一，支持多實(shí)例分布式事務(wù)（外部xa事務(wù)），這個(gè)一般在分布式數(shù)據(jù)庫環(huán)境中用得較多。第二，支持內(nèi)部xa事務(wù)，即支持binlog與innodb redo log之間數(shù)據(jù)一致性。

--innodb_file_format = barracuda

InnoDB文件格式，Antelope是innodb-base的文件格式，Barracude是innodb-plugin后引入的文件格式，同時(shí)Barracude也支持Antelope文件格式。

innodb_strict_mode = ON

開啟InnoDB嚴(yán)格檢查模式，在某些情況下返回errors而不是warnings，默認(rèn)值是OFF

innodb_checksum_algorithm = strict_crc32

checksum函數(shù)的算法，默認(rèn)為crc32。可以設(shè)置的值有:innodb、crc32、none、strict_innodb、strict_crc32、strict_none

innodb_io_capacity = 2000

磁盤io的吞吐量，默認(rèn)值是200.對于刷新到磁盤頁的數(shù)量，會按照inodb_io_capacity的百分比來進(jìn)行控制。

innodb_purge_threads = 1

使用獨(dú)立線程進(jìn)行purge操作。 每次DML操作都會生成Undo頁，系統(tǒng)需要定期對這些undo頁進(jìn)行清理，這稱為purge操作。

innodb_purge_batch_size = 32

在進(jìn)行full purge時(shí)，回收Undo頁的個(gè)數(shù)，默認(rèn)是20，可以適當(dāng)加大。

innodb_old_blocks_pct = 75

LRU算法，默認(rèn)值是37，插入到LRU列表端的37%，差不多3/8的位置。 innodb把midpoint之后的列表稱為old列表，之前的列表稱為new列表，可以理解為new列表中的頁都是最為活躍的熱點(diǎn)數(shù)據(jù)。

innodb_change_buffering = all

用來開啟各種Buffer的選項(xiàng)。該參數(shù)可選的值為：inserts、deletes、purges、changes、all、none。changes表示啟用inserts和deletes，all表示啟用所有，none表示都不啟用。該參數(shù)默認(rèn)值為all。

innodb_thread_sleep_delay = 500
innodb_undo_directory = /var/lib/mysql/mysql_log/undolog

innodb_undo_tablespaces_total = 4

innodb_undo_tablespaces_implicit = 2 # innodb-created

innodb_undo_tablespaces_explicit = 2 # user-created

innodb_undo_tablespaces_active = 4

innodb_purge_rseg_truncate_frequency = 128
innodb_page_size = 16384
innodb_autoinc_lock_mode = 2 # only for [binlog_format]=row

其他

[mysqldump]
quick

支持較大數(shù)據(jù)庫的轉(zhuǎn)儲，在導(dǎo)出非常巨大的表時(shí)需要此項(xiàng)。增加該變量的值十分安全，這是因?yàn)閮H當(dāng)需要時(shí)才會分配額外內(nèi)存。例如，僅當(dāng)發(fā)出長查詢或mysqld必須返回大的結(jié)果行時(shí)mysqld才會分配更多內(nèi)存。該變量之所以取較小默認(rèn)值是一種預(yù)防措施，以捕獲客戶端和服務(wù)器之間的錯(cuò)誤信息包，并確保不會因偶然使用大的信息包而導(dǎo)致內(nèi)存溢出。 如果正是用大的BLOB值，而且未為mysqld授予為處理查詢而訪問足夠內(nèi)存的權(quán)限，也會遇到與大信息包有關(guān)的奇怪問題。如果懷疑出現(xiàn)了該情況，請嘗試在mysqld_safe腳本開始增加ulimit -d 256000，并重啟mysqld。

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

增加每個(gè)進(jìn)程的可打開文件數(shù)量.確認(rèn)已經(jīng)將全系統(tǒng)限制設(shè)定的足夠高!打開大量表需要將此值設(shè)大

.................................................................................
table_cache = 512

所有線程所打開表的數(shù)量. 增加此值就增加了mysqld所需要的文件描述符的數(shù)量這樣需要確認(rèn)在[mysqld_safe]中 “open-files-limit” 變量設(shè)置打開文件數(shù)量允許至少4096

thread_concurrency = 8

此允許應(yīng)用程序給予線程系統(tǒng)一個(gè)提示在同一時(shí)間給予渴望被運(yùn)行的線程的數(shù)量.該參數(shù)取值為服務(wù)器邏輯CPU數(shù)量×2，在本例中，服務(wù)器有 2 顆物理CPU，而每顆物理CPU又支持H.T超線程，所以實(shí)際取值為 4 × 2 ＝ 8.設(shè)置 thread_concurrency的值的正確與否, 對 mysql 的性能影響很大, 在多個(gè) cpu(或多核)的情況下，錯(cuò)誤設(shè)置了 thread_concurrency 的值, 會導(dǎo)致 mysql 不能充分利用多 cpu(或多核),出現(xiàn)同一時(shí)刻只能一個(gè) cpu(或核)在工作的情況。 thread_concurrency 應(yīng)設(shè)為 CPU 核數(shù)的 2 倍.比如有一個(gè)雙核的 CPU, 那么 thread_concurrency 的應(yīng)該為 4; 2個(gè)雙核的cpu,thread_concurrency 的值應(yīng)為 8,屬重點(diǎn)優(yōu)化參數(shù)

slave-load-tmpdir = /var/lib/mysql/mysql_tmp

當(dāng) slave 執(zhí)行 load data infile 時(shí)用

replica_load_tmpdir = /var/lib/mysql/mysql_tmp
skip_name_resolve = ON # custom
skip_external_locking = ON
event_scheduler = ON
secure_file_priv = /var/lib/mysql/backup # custom
mysqlx_port = 55550
mysqlx_socket = /usr/local/mysql/tmp/mysqlx.sock

query_cache_min_res_unit = 2K

查詢緩存分配的最小塊大小.默認(rèn)是 4KB，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)。
查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查詢緩存碎片率超過 20%，可以用 FLUSH QUERY CACHE 整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話。
查詢緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size *100%
查詢緩存利用率在 25%以下的話說明 query_cache_size 設(shè)置的過大，可適當(dāng)減小;查詢緩存利用率在 80%以上而且 Qcache_lowmem_prunes > 50 的話說明 query_cache_size 可能有點(diǎn)小，要不就是碎片太多。
查詢緩存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

memlock

如果系統(tǒng)支持 memlock() 函數(shù),打開此選項(xiàng)用以讓運(yùn)行中的 mysql 在在內(nèi)存高度緊張的時(shí)候,數(shù)據(jù)在內(nèi)存中保持鎖定并且防止可能被 swapping out,此選項(xiàng)對于性能有益

key_buffer_size = 67108864

指定用于索引的緩沖區(qū)大小，增加它可得到更好的索引處理性能。如果是以InnoDB引擎為主的DB，專用于MyISAM引擎的 key_buffer_size 可以設(shè)置較小，8MB 已足夠 如果是以MyISAM引擎為主，可設(shè)置較大，但不能超過4G. 在這里，強(qiáng)烈建議不使用MyISAM引擎，默認(rèn)都是用InnoDB引擎.注意：該參數(shù)值設(shè)置的過大反而會是服務(wù)器整體效率降低！

myisam_sort_buffer_size = 64M

MyISAM 設(shè)置恢復(fù)表之時(shí)使用的緩沖區(qū)的尺寸,當(dāng)在REPAIR TABLE 或用 CREATE INDEX 創(chuàng)建索引或 ALTER TABLE 過程中排序 MyISAM 索引分配的緩沖區(qū)

myisam_max_sort_file_size = 10G

mysql重建索引時(shí)允許使用的臨時(shí)文件最大大小

myisam_repair_threads = 1

如果該值大于 1，在 Repair by sorting 過程中并行創(chuàng)建MyISAM 表索引(每個(gè)索引在自己的線程內(nèi)).如果一個(gè)表擁有超過一個(gè)索引, MyISAM 可以通過并行排序使用超過一個(gè)線程去修復(fù)他們.這對于擁有多個(gè) CPU 以及大量內(nèi)存情況的用戶,是一個(gè)很好的選擇.

myisam_recover = 64K

允許的 GROUP_CONCAT()函數(shù)結(jié)果的最大長度

replicate_wild_ignore_table = mysql.%

復(fù)制時(shí)忽略表，僅用于slave

slave_skip_errors=all

定義復(fù)制過程中從服務(wù)器可以自動跳過的錯(cuò)誤號，當(dāng)復(fù)制過程中遇到定義的錯(cuò)誤號，就可以自動跳過，直接執(zhí)行后面的SQL語句。slave_skip_errors選項(xiàng)有四個(gè)可用值，分別為：off，all，ErorCode，ddl_exist_errors。默認(rèn)情況下該參數(shù)值是off，我們可以列出具體的error code，也可以選擇all，mysql5.6及MySQL Cluster NDB 7.3以及后續(xù)版本增加了參數(shù)ddl_exist_errors，該參數(shù)包含一系列error code（1007,1008,1050,1051,1054,1060,1061,1068,1094,1146）
一些error code代表的錯(cuò)誤如下：
1007：數(shù)據(jù)庫已存在，創(chuàng)建數(shù)據(jù)庫失敗
1008：數(shù)據(jù)庫不存在，刪除數(shù)據(jù)庫失敗
1050：數(shù)據(jù)表已存在，創(chuàng)建數(shù)據(jù)表失敗
1051：數(shù)據(jù)表不存在，刪除數(shù)據(jù)表失敗
1054：字段不存在，或程序文件跟數(shù)據(jù)庫有沖突
1060：字段重復(fù)，導(dǎo)致無法插入
1061：重復(fù)鍵名
1068：定義了多個(gè)主鍵
1094：位置線程ID
1146：數(shù)據(jù)表缺失，請恢復(fù)數(shù)據(jù)庫
1053：復(fù)制過程中主服務(wù)器宕機(jī)
1062：主鍵沖突 Duplicate entry '%s' for key %d

max_execution_time = 3600000 # custom: (ms)
group_concat_max_len = 1048576

參考URL:
https://blog.csdn.net/juan083/article/details/49489655
https://blog.csdn.net/smooth00/article/details/72725941
https://blog.csdn.net/jerryDzan/article/details/88942411
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
http://blog.itpub.net/31397003/viewspace-2647250/