MySQL數據庫配置詳解

下面是我生產環境中MySQL的配置詳解：

[mysqld]

mysql服務端配置

server-id=1

服務ID，每個服務的服務ID不同即可

log-bin=mysql-bin

定義bin_log的位置和名稱，mysql操作記錄二進制日志

relay_log=mysql-realy-bin

定義relay_log的位置和名稱，從服務器解析主服務器二進制文件后記錄的文件

relay_log_index=slave-mysql-realy-bin.index

定義relay_log_index的位置和名稱

expire_logs_days=15

超過15天的binlog刪除

binlog_format=mixed

mysql復制方式為混合模式

說明：mysql復制主要有三種方式：基于SQL語句的復制(statement-based replication, SBR)，基于行的復制(row-based replication, RBR)，混合模式復制(mixed-based replication, MBR)。對應的，binlog的格式也有三種：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一條會修改數據的sql語句會記錄到binlog中。優點是并不需要記錄每一條sql語句和每一行的數據變化，減少了binlog日志量，節約IO，提高性能。缺點是在某些情況下會導致master-slave中的數據不一致(如sleep()函數， last_insert_id()，以及user-defined functions(udf)等會出現問題)

② ROW模式（RBR）

不記錄每條sql語句的上下文信息，僅需記錄哪條數據被修改了，修改成什么樣了。而且不會出現某些特定情況下的存儲過程、或function、或trigger的調用和觸發無法被正確復制的問題。缺點是會產生大量的日志，尤其是alter table的時候會讓日志暴漲。

③ MIXED模式（MBR）

以上兩種模式的混合使用，一般的復制使用STATEMENT模式保存binlog，對于STATEMENT模式無法復制的操作使用ROW模式保存binlog，MySQL會根據執行的SQL語句選擇日志保存方式。

auto-increment-increment=2

自增量每次增加的值改為2

auto-increment-offset= 1

第一次加載數值時的偏移值的個位值是1

innodb_flush_log_at_trx_commit=1

# 0：如果innodb_flush_log_at_trx_commit的值為0,log buffer每秒就會被刷寫日志文件到磁盤，提交事務的時候不做任何操作（執行是由mysql的master thread線程來執行的。

# 主線程中每秒會將重做日志緩沖寫入磁盤的重做日志文件(REDO LOG)中。不論事務是否已經提交）默認的日志文件是ib_logfile0,ib_logfile1

# 1：當設為默認值1的時候，每次提交事務的時候，都會將log buffer刷寫到日志。

# 2：如果設為2,每次提交事務都會寫日志，但并不會執行刷的操作。每秒定時會刷到日志文件。要注意的是，并不能保證100%每秒一定都會刷到磁盤，這要取決于進程的調度。

# 每次事務提交的時候將數據寫入事務日志，而這里的寫入僅是調用了文件系統的寫入操作，而文件系統是有 緩存的，所以這個寫入并不能保證數據已經寫入到物理磁盤

# 默認值1是為了保證完整的ACID。當然，你可以將這個配置項設為1以外的值來換取更高的性能，但是在系統崩潰的時候，你將會丟失1秒的數據。

# 設為0的話，mysqld進程崩潰的時候，就會丟失最后1秒的事務。設為2,只有在操作系統崩潰或者斷電的時候才會丟失最后1秒的數據。InnoDB在做恢復的時候會忽略這個值。

# 總結

# 設為1當然是最安全的，但性能頁是最差的（相對其他兩個參數而言，但不是不能接受）。如果對數據一致性和完整性要求不高，完全可以設為2，如果只最求性能，例如高并發寫的日志服務器，設為0來獲得更高性能

replicate-ignore-db=sys

復制時忽略的數據庫

datadir=/data/mysql_data

數據存儲目錄

socket=/var/lib/mysql/mysql.sock

對服務器端指定套接字文件路徑

對mysql.sock來說，其作用是程序與mysqlserver處于同一臺機器，發起本地連接時可用。

character_set_server=utf8

設置字符集

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

# mysql數據庫的中有一個環境變量sql_mode,定義了mysql應該支持的sql語法，數據校驗等

# STRICT_TRANS_TABLES：在該模式下，如果一個值不能插入到一個事務表中，則中斷當前的操作，對非事務表不做限制

# NO_ZERO_IN_DATE：這個模式影響了是否允許日期中的月份和日包含0。如果開啟此模式，2016-01-00是不允許的，但是0000-02-01是允許的。它實際的行為受到 strict mode是否開啟的影響1。

# NO_ZERO_DATE：設置該值，mysql數據庫不允許插入零日期。它實際的行為受到 strictmode是否開啟的影響2。

# ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE過程中，如果數據被零除，則產生錯誤而非警告。如果未給出該模式，那么數據被零除時MySQL返回NULL

# NO_AUTO_CREATE_USER：禁止GRANT創建密碼為空的用戶

# NO_ENGINE_SUBSTITUTION：如果需要的存儲引擎被禁用或未編譯，那么拋出錯誤。不設置此值時，用默認的存儲引擎替代，并拋出一個異常

symbolic-links=0

不支持符號鏈接

log-error=/var/log/mysqld.log

錯誤日志文件目錄

pid-file=/var/run/mysqld/mysqld.pid

進程文件目錄

innodb_buffer_pool_size=8G

InnoDB緩沖池大小（這里設置為內存大小的一半）

# InnoDB使用一個緩沖池來保存索引和原始數據, 不像MyISAM

# 這里你設置越大,你在存取表里面數據時所需要的磁盤I/O越少.

# 在一個獨立使用的數據庫服務器上,你可以設置這個變量到服務器物理內存大小的80%

# 不要設置過大,否則,由于物理內存的競爭可能導致操作系統的換頁顛簸.

# 注意在32位系統上你每個進程可能被限制在 2-3.5G 用戶層面內存限制,

# 所以不要設置的太高.

innodb_log_file_size=256M

確定數據日志文件的大小，更大的設置可以提高性能，但也會增加恢復故障數據庫所需的時間

innodb_flush_method=O_DIRECT

InnoDB 用來刷新日志的方法為O_DSYNC

# 表空間總是使用雙重寫入刷新方法

#  默認值是 “fdatasync”, 另一個是 “O_DSYNC”.

# 一般來說，如果你有硬件 RAID 控制器，并且其獨立緩存采用 write-back 機制，并有著電池斷電保護，那么應該設置配置為 O_DIRECT

# 否則，大多數情況下應將其設為 fdatasync

max_connections=500

MySQL的最大連接數

# 如果服務器的并發連接請求量比較大，建議提高此值，以增加并行連接數量，當然這建立在機器能支撐的情況下，因為如果連接數越多，介于MySQL會為每個連接提供連接緩沖區，就會開銷越多的內存，所以要適當調整該值，不能盲目提高設值。可以過'conn%'通配符查看當前狀態的連接數量，以定奪該值的大小。

innodb_autoextend_increment=128

每次自動擴展的增量大小

# 增加InnoDB系統空間最簡單的方法就是，在配置文件中配置autoextend.

# #該innodb_autoextend_increment設置不會影響每個表的單獨表空間文件或常規表空間文件。無論innodb_autoextend_increment設置如何，這些文件都是自動擴展的 。初始擴展是少量的，之后以4MB的增量擴展。 

# 默認是8M, 如果一次insert數據量比較多的話, 可以適當增加.