打造企業高承載需求的 Nginx（MySQL篇）

延續LNMP架構的nginx與php，動態程式網頁最終還是需要一個資料庫來儲存相關資料，雖然MySQL在SUN被Oracle收購、並移除GPL授權後，多數社群玩家已經從MySQL轉移至MariaDB，但由於企業多數的平台架構皆基於MySQL來建置，因此本文仍以MySQL的安裝與參數設定來說明。

MySQL的安裝非常容易，設定檔也只有一個「my.cnf」放置於/etc 目錄下，裡面幾乎是空白的，當mysqld啟用之後，皆以default值來載入MySQL相關環境參數，這對於測試站台的運作或許是沒有問題的，但若是面對生產環境的大量查詢需求卻是遠遠不足，也因此理解參數原理與適當的配置，才能確實地應付高承載需求。

Step 1 安裝mysql套件庫

[root@centos7 ~]# rpm -ivh https://rpms.remirepo.net/enterprise/remi-release-7.rpm

Step 2 透過mysql套件庫安裝mysql 5.6、啟動服務並設定開機自動啟用

[root@centos7 ~]# yum install mysql-community-server
[root@centos7 ~]# systemctl  enable mysqld
[root@centos7 ~]# systemctl start mysqld

Step 3 初始化MySQL

[root@centos7 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 直接按Enter跳過
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] 按Y設定MySQL的root密碼
New password: 輸入新密碼
Re-enter new password: 再次輸入確認新密碼
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] 按Y移除anonymous users
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] 按Y禁止root從遠端登入MySQL
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] 按Y移除預設建立好的test database
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] 按Y重新載入資料表權限
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

至此，MySQL服務已經安裝完畢並運作中了，早期MySQL在安裝完畢後會在/usr/share/mysql 目錄下提供五個設定檔的範例檔案，其中適用於最高負載的設定檔「my-innodb-heavy-4G.cnf」建議給有4G記憶體以上伺服器使用，這幾乎是現今伺服器或個人電腦來說的最低標，因此以下將透過「my-innodb-heavy-4G.cnf」的修改來實現更適用於線上提供生產力的資料庫服務。

自訂my.cnf 詳解

# MySQL客戶端使用參數
[client]
#password       = [your_password]
port            = 3306
socket          = /tmp/mysql.sock
default-character-set = utf8


# MySQL伺服端使用參數
[mysqld]
# MySQL伺服器啟用的tcp端口
port = 3306
# MySQL服務的pid儲存位置
socket = /tmp/mysql.sock
# 伺服器預設字元為utf8、並為應用程式連接客戶端自動執行SET NAMES
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect = 'SET NAMES utf8'

# 監聽對列長度（還未進入查詢的連線），受限內核參數tcp_max_syn_backlog
back_log = 2048
# 最大連線數（已進入查詢的連線）
max_connections = 10240
# 容忍數次查詢失敗的客戶端，以防止暴力破解
max_connect_errors = 64
# 對齊max_connections！
# 緩存已被開啟的table，其他查詢可以透過該緩存table避免重複IO
table_open_cache = 10240
# 跳過外部鎖定
# 一個MySQL服務對應一個實體數據庫目錄的環境下不需要外部鎖定以免性能下降
skip-external-locking
# 修改資料的插入上限，通常將檔案或圖片儲存於MySQL時才需要提升
max_allowed_packet = 32M
# binlog所使用的cache大小
binlog_cache_size = 1M
# 記憶體table的最大值限制
max_heap_table_size = 128M
# 記憶體暫存table的最大值限制
tmp_table_size = 128M
# 第二次排序緩存
read_rnd_buffer_size = 16M
# 各種操作緩衝區的大小
read_buffer_size = 2M
sort_buffer_size = 8M
join_buffer_size = 8M
# 線程緩存，可以快速響應下一個需求
thread_cache_size = 64
# 決定線程分配的記憶體大小，用來存放客戶端的請求、Query及各種狀態訊息
thread_stack = 256K
# 對齊主機CPU核心數
thread_concurrency = 24
# 查詢快取大小
query_cache_size = 512M
# 超過此大小的查詢結果將不會緩存
query_cache_limit = 256M
# 全文索引最小長度
ft_min_word_len = 4
# 新建DB的默認儲存引擎
default-storage-engine = INNODB
# 交易隔離等級，鎖定資料的方式，REPEATABLE-READ是默認值
transaction_isolation = REPEATABLE-READ

# 資料庫所有新增、刪除、修改等的SQL語法紀錄
log-bin=mysql-bin
# binlog檔案的紀錄格式，此為默認值
binlog_format=mixed
# log-bin檔案多久會過期
# 預設值不限制，可能會造成logbin檔案過大過多而讓磁碟空間爆滿
expire_logs_days = 5
# 執行較慢的SQL語句是否儲存（布林值），可用檢查或優化不適用的SQL查詢語法
# MySQL5.6以前是使用log-slow-queries參數舊名
slow_query_log = 1
# 定義較慢的SQL查詢查詢時間，單位是秒
long_query_time = 3
# 較慢的SQL查詢紀錄檔案存放路徑
slow_query_log_file = /var/log/mysql-slow.log

# server id用在DB replication架構用以識別
# 沒做replication也可以設置
server-id = 1

#*** MyISAM Specific options
# MyISAM表索引緩衝大小
key_buffer_size = 512M
# MyISAM使用一種類樹狀態快取的方式、
# 使得大批如insert、select等的插入可以更快些
bulk_insert_buffer_size = 256M
# MyISAM分配緩衝來排序索引的buffer大小
myisam_sort_buffer_size = 128M
# 重建索引時允許使用臨時文件的大小限制
myisam_max_sort_file_size = 10G
# 系統默認值
myisam_repair_threads = 1
# 自動檢查和修復不正確關閉的MyISAM表
myisam_recover

# *** INNODB Specific options ***
# MySQL 5.6.3版本以後innodb_additional_mem_pool_size將被棄用
# innodb用來儲存data dirctionary訊息與內部資料結構的記憶體儲存池大小
# innodb_additional_mem_pool_size = 32M
# 這個參數定義了InnoDB存儲引擎的表資料和索引資料的最大記憶體緩衝區大小
innodb_buffer_pool_size = 8G
# innodb儲存數據文件的路徑，此為系統默認值
innodb_data_file_path = ibdata1:10M:autoextend
# InnoDB 處理數據頁上寫 I/O（輸入）請求的數量
innodb_write_io_threads = 8
# InnoDB 處理數據頁上讀 I/O（输出）請求的數量
innodb_read_io_threads = 8
# 限制innodb可同時處理的併發線程數量，低於或等於cpu核心數
innodb_thread_concurrency = 24
# 如果設置為1 ,InnoDB會在每次提交後刷新事務日誌到磁碟上，較耗效能
innodb_flush_log_at_trx_commit = 0
# 預設值是1M，對於transactions較大的應用可以提升該值
innodb_log_buffer_size = 8M
# innodb日誌檔案大小
innodb_log_file_size = 512M
# 有幾個innodb日誌檔輪替
innodb_log_files_in_group = 3
# 控制innodb dirty page的百分佔比限制，默認值為75
innodb_max_dirty_pages_pct = 75
# innodb在事務進行等待獲取資源的最長等待時間
innodb_lock_wait_timeout = 60


[mysqldump]
quick
max_allowed_packet = 32M


[mysql]
no-auto-rehash
default-character-set=utf8


[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 8M
read_buffer = 8M
write_buffer = 8M


[mysqlhotcopy]
interactive-timeout


[mysqld_safe]
open-files-limit = 8192