MySQL運(yùn)維實(shí)踐

5.1-MySQL日志系統(tǒng)

什么是日志

• 日志(log)是一種順序記錄事件流水的文件
• 記錄計(jì)算機(jī)程序運(yùn)行過程中發(fā)生了什么
• 用途多樣
• 幫助分析程序問題
• 分析服務(wù)請(qǐng)求的特征、流量等
• 判斷工作是否成功執(zhí)行
• ……

MySQL日志的分類

• 服務(wù)器日志
  • 記錄進(jìn)程啟動(dòng)運(yùn)行過程中的特殊事件，幫助分析MySQL服務(wù)遇到的問題
  • 根據(jù)需求抓取特定的SQL語句，追蹤性能可能存在的問題的業(yè)務(wù)SQL
• 事務(wù)日志
  • 記錄應(yīng)用程序?qū)?shù)據(jù)的所有更改
  • 可用于數(shù)據(jù)恢復(fù)
  • 可用于實(shí)例間數(shù)據(jù)同步

服務(wù)錯(cuò)誤日志

• 記錄實(shí)例啟動(dòng)運(yùn)行過程中重要消息
• 配置參數(shù) log_error = /data/mysql_data/node-1/mysql.log
• 內(nèi)容并非全是錯(cuò)誤消息
• 如果mysqld進(jìn)程無法正常啟動(dòng)首先查看錯(cuò)誤日志

慢查詢?nèi)罩?/h3>

• 記錄執(zhí)行時(shí)間超過一定閾值的SQL語句
• 配置參數(shù)

slow_query_log = 1
slow_query_log_file = /data/mysql_data/node-1/mysql-slow.log
long_query_time = 5

• 用于分析系統(tǒng)中可能存在性能問題的SQL

綜合查詢?nèi)罩?/h3>

• 如果開啟將會(huì)記錄系統(tǒng)中所有SQL語句
• 配置參數(shù)

general_log = 1
general_log_file = /data/mysql_data/node-1/mysql-slow.log

• 偶爾用于幫助分析系統(tǒng)問題，對(duì)性能有影響

查詢?nèi)罩镜妮敵雠c文件切換

• 日志輸出參數(shù)

log_output={file|table|none}

• 如果日志文件過大，可以定期截?cái)嗖⑶袚Q新文件

flush log;

存儲(chǔ)引擎事務(wù)日志

• 部分存儲(chǔ)引擎擁有重做日志(redo log)
• 如InnoDB, TokuDB等WAL(Write Ahead Log)機(jī)制存儲(chǔ)引擎
• 日志隨著事務(wù)commit優(yōu)先持久化，確保異?；謴?fù)不丟數(shù)據(jù)
• 日志順序?qū)懶阅茌^好

InnoDB事務(wù)日志重用機(jī)制

• InnoDB事務(wù)日志采用兩組文件交替重用

二進(jìn)制日志binlog

• binlog (binary log)
• 記錄數(shù)據(jù)引起數(shù)據(jù)變化的SQL語句或數(shù)據(jù)邏輯變化的內(nèi)容
• MySQL服務(wù)層記錄，無關(guān)存儲(chǔ)引擎
• binlog的主要作用：
  • 基于備份恢復(fù)數(shù)據(jù)
  • 數(shù)據(jù)庫主從同步
  • 挖掘分析SQL語句

開啟binlog

• 主要參數(shù)

log_bin = c:/tmp/mylog/mysql-bin
sql_log_bin = 1
sync_binlog = 1

• 查看binlog

show binary logs;

binlog管理

• 主要參數(shù)

max_binlog_size = 100MB
expire_logs_days = 7

• binlog始終生成新文件，不會(huì)重用

• 手工清理binlog

purge binary logs to 'mysql-bin.000009';
purge binary logs before '2016-4-2 21:00:40'

查看binlog內(nèi)容

• 日志

show binlog events in 'mysql-bin.000011';
show binlog events in 'mysql-bin.000011' from 60 limit 3;

• mysqlbinlog工具

mysqlbinlog c:/tmp/mylog/mysql-bin.000001
--start-datetime | --stop-datetime
--start-position | --stop-position

binlog格式

• 主要參數(shù)

binlog_format = {ROW|STATEMENT|MIXED}

• 查看row模式的binlog內(nèi)容

mysqlbinlog --base64-output=decode-rows -v c:/tmp/mylpg/mysql-bin.000001

5.2-MySQL數(shù)據(jù)備份

基本指數(shù) - 備份用途

• 數(shù)據(jù)備災(zāi)
  • 應(yīng)對(duì)硬件故障數(shù)據(jù)丟失
  • 應(yīng)對(duì)人為或程序bug導(dǎo)致數(shù)據(jù)刪除
• 制作鏡像庫以供服務(wù)
  • 需要將數(shù)據(jù)遷移、統(tǒng)計(jì)分析等用處
  • 需要為線上數(shù)據(jù)建立一個(gè)鏡像

基本知識(shí) - 備份內(nèi)容

• 數(shù)據(jù)
  • 數(shù)據(jù)文件或文本格式數(shù)據(jù)
• 操作日志(binlog)
  • 數(shù)據(jù)庫變更日志

基本知識(shí) - 冷備份與熱備份

• 冷備份
  • 關(guān)閉數(shù)據(jù)庫服務(wù)，完整拷貝數(shù)據(jù)文件
• 熱備份
  • 在不影響數(shù)據(jù)庫讀寫服務(wù)的情況下備份數(shù)據(jù)庫

基本知識(shí) - 物理備份與邏輯備份

• 物理備份
  • 以數(shù)據(jù)頁的形式拷貝數(shù)據(jù)
• 邏輯備份
  • 導(dǎo)出為裸數(shù)據(jù)或者SQL(insert)語句

基本知識(shí) - 本地備份與遠(yuǎn)程備份

• 本地備份
  • 在數(shù)據(jù)庫服務(wù)器本地進(jìn)行備份
• 遠(yuǎn)程備份
  • 遠(yuǎn)程連接數(shù)據(jù)庫進(jìn)行備份

基本知識(shí) - 全量備份與增量備份

• 全量備份
  • 備份完整的數(shù)據(jù)庫
• 增量備份
  • 只備份上一次備份以來發(fā)生修改的數(shù)據(jù)

基本知識(shí) - 備份周期

考慮因素：

• 數(shù)據(jù)庫大小(決定備份時(shí)間)
• 恢復(fù)速度要求(快速or慢速)
• 備份方式(全量or增量)

常用工具及用法

• mysqldump - 邏輯備份，熱備
• xtrabackup - 物理備份， 熱備
• Lvm/zfs snapshot - 物理備份
• mydumper - 邏輯備份，熱備
• cp - 物理備份，冷備

常用工具及用法 - mysqldump

MySQL官方自帶的命令行工具

主要示例：

• 演示使用mysqldump備份表、庫、實(shí)例

# 備份所有數(shù)據(jù)庫
mysqldump -uroot -p123456 --socket=/var/run/mysqld/mysqld.sock --all-databases > /dbbackup/all_db.sql
# 備份指定的數(shù)據(jù)庫
mysqldump -uroot -p123456 --socket=/var/run/mysqld/mysqld.sock --databases db2 > /dbbackup/db2.sql
# 備份單個(gè)表
mysqldump -uroot -p123456 --socket=/var/run/mysqld/mysqld.sock db2 t1 >/dbbackup/db2_t1.sql
# 還原表
mysql > source /dbbackup/db2_t1.sql

• 演示使用mysqldump制作一致性備份

mysqldump --single-transaction -uroot -p123456 --all-databases > /dbbackup/add_db_2.sql

• 演示使用mysqldump遠(yuǎn)程備份一個(gè)數(shù)據(jù)庫

mysqldump -utest -ptest -h192.168.0.68 -P3306 --all-databases > /dbbackup/remote_bakall.sql

• 演示使用mysqldump導(dǎo)出數(shù)據(jù)為csv格式

mysqldump -uroot -p123456 --single-transaction --fields-terminated-by=, db1 -T /tmp

常用工具及用法 - xtrabackup

特點(diǎn)：

• 開源，在線備份InnoDB表
• 支持限速備份，避免對(duì)業(yè)務(wù)造成影響
• 支持流備
• 支持增量備份
• 支持備份文件壓縮與加密
• 支持并行備份與恢復(fù)，速度快

xtrabackup備份原理

• 基于InnoDB的crash-recovery功能
• 備份期間允許用戶讀寫，寫請(qǐng)求產(chǎn)生redo日志
• 從磁盤上拷貝數(shù)據(jù)文件
• 從InnoDB redo log file實(shí)時(shí)拷貝走備份期間產(chǎn)生的所有redo日志
• 恢復(fù)的時(shí)候 數(shù)據(jù)文件 + redo日志 = 一致性數(shù)據(jù)

實(shí)用腳本innobackupex

• 開源Perl腳本，封裝調(diào)用xtrabackup及一系列相關(guān)工具與OS操作，最終完成備份過程
• 支持備份InnoDB和其他引擎的表
• 備份一致性保證

innobackupex備份基本流程

start xtrabackup_log -> copy .ibd; ibdata1 -> FLUSH TABLE WITH READ LOCK -> copy .FRM; MYD; MYI; misc files -> Get binary log position -> UNLOCK TABLES -> stop and copy xtrabackup_log

innobackupex使用

主要示例：

• 全量備份

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf /dbbackup

• 增量備份

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --incremental --incremental-dir /dbbackup/2016-4-3_13:24:32 /dbbackup

• 流方式備份

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --stream=xbstream /dbbackup/ > /dbbackup/stream.bak

• 并行備份

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --parallel=4 /dbbackup/

• 限流備份

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --throttle=10 /dbbackup/

• 壓縮備份

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --compress --compress-thread 4 /dbbackup/

如何制定備份策略

需要考慮的因素

• 數(shù)據(jù)庫是不是都是innodb引擎表 -> 備份方式，熱備or冷備
• 數(shù)據(jù)量大小 -> 邏輯備份or物理備份，全量or增量
• 數(shù)據(jù)庫本地磁盤空間十分充足 -> 備份到本地or遠(yuǎn)程
• 需要多塊恢復(fù) -> 備份頻率 小時(shí)or天

5.3-MySQL數(shù)據(jù)恢復(fù)

什么時(shí)候需要恢復(fù)數(shù)據(jù)

• 硬件故障(如磁盤損壞)
• 人為刪除(如誤刪除數(shù)據(jù)、被黑)
• 業(yè)務(wù)回滾(如游戲bug需要回檔)
• 正常需求(如部署鏡像庫、查看歷史某時(shí)刻數(shù)據(jù))

數(shù)據(jù)恢復(fù)的必要條件

• 有效備份
• 完整的數(shù)據(jù)庫操作日志(binlog)

數(shù)據(jù)恢復(fù)思路

• 最新一次備份 + binlog恢復(fù)到故障時(shí)間點(diǎn)(適用于各種數(shù)據(jù)丟失場(chǎng)景)
• 挖掘最后一次備份到故障點(diǎn)之間的binlog獲取相關(guān)SQL語句，構(gòu)造反轉(zhuǎn)SQL語句并應(yīng)用到數(shù)據(jù)庫(只是用于記錄丟失，且binlog必須是row格式)

反轉(zhuǎn)SQL語句

例：

t1(id primary key, a int)

反轉(zhuǎn)SQL語句：

insert into t(id, a) values(1, 1) -> delete t1 where id=1 and a=1
update t1 set a=5 where id=1 -> update t1 set a=1 where id=1
delete from t1 where id=1 -> insert into t(id, a) values(1, 1)

數(shù)據(jù)庫恢復(fù)工具與命令

• mysqldump備份 -> source恢復(fù)
• xtrabackup備份 -> xtrabackup恢復(fù)
• binlog備份 -> mysqlbinlog恢復(fù)

詳細(xì)示例講解

• 恢復(fù)某幾條誤刪數(shù)據(jù)
• 恢復(fù)誤刪表、庫
• 將數(shù)據(jù)庫恢復(fù)到指定時(shí)間點(diǎn)

恢復(fù)誤刪除數(shù)據(jù)

case：誤操作，刪除數(shù)據(jù)忘記帶完整條件，執(zhí)行delete from user where age > 30 [and sex=male]

需求：將被刪除的數(shù)據(jù)還原

恢復(fù)前提：完整的數(shù)據(jù)庫操作日志(binlog)

delete from user where sex='female';

# 首先需要找到binlog里的信息
mysqlbinlog -vv mysql-bin.000001
# 找出sql語句，然后寫出反轉(zhuǎn)sql語句

恢復(fù)誤刪表、庫

case：業(yè)務(wù)被黑，表被刪除了(drop teble user)

需求：將表恢復(fù)

前提：備份 + 備份以來完整binlog

innobackupex --apply-log /dbbackup/filename
# 查看binlog的位置點(diǎn)
cat xtrabackup_binlog_info
# 查看結(jié)束點(diǎn)
mysqlbinlog -vv filename

mysqlbinlog -vv --start-position=2556990 -- stop-position=2776338
mysqlbinlog -vv --start-position=2556990 -- stop-position=2776338 | mysql -uroot -p123456 --sock=/dbbackup/mysql_3309/mysqld.sock

課程小結(jié)

• 恢復(fù)是已經(jīng)非常苦逼的差事，盡量避免做。我們要做數(shù)據(jù)衛(wèi)士而不是救火隊(duì)員。(線上應(yīng)該嚴(yán)格把控權(quán)限，數(shù)據(jù)變更操作應(yīng)事先測(cè)試，操作時(shí)做好備份)
• 有效備份(+binlog)是重中之重，對(duì)數(shù)據(jù)庫定期備份是必須的
• 備份是一切數(shù)據(jù)恢復(fù)的基礎(chǔ)

5.4-MySQL線上部署

MySQL線上部署

考慮因素：

• 版本選擇， 5.1、5.5還是5.6？
• 分支選擇，官方社區(qū)版？ percona server？ Mariadb？
• 安裝方式，包安裝？二進(jìn)制包安裝？源碼安裝？
• 路徑配置，參數(shù)配置(盡量模板化、標(biāo)準(zhǔn)化)
• 一個(gè)實(shí)例多個(gè)庫 or 多個(gè)實(shí)例單個(gè)庫？

二進(jìn)制安裝MySQL

• 下載軟件包
• 解壓放到指定目錄(比如/usr/local)
• 將MySQL目錄放到PATH中
• 初始化實(shí)例，編輯配置文件并啟動(dòng)
• 賬戶安全設(shè)置

編譯安裝MySQL

• 下載MySQL源碼安裝包
• 安裝必要包(make cmake bison-devel ncurses-devel build-essential)
• Cmake配置MySQL編譯選項(xiàng)，可以定制需要安裝的功能
• make && make install
• 初始化實(shí)例，編輯配置文件并啟動(dòng)
• 賬戶安全設(shè)置

MySQL升級(jí)

• 下載MySQL5.6安裝包并配置MySQL5.6安裝包安裝路徑
• 關(guān)閉MySQL5.5實(shí)例，修改部分參數(shù)，使用MySQL5.6軟件啟動(dòng)
• 執(zhí)行MySQL5.6路徑下mysql_upgrade腳本
• 驗(yàn)證是否成功升級(jí)

MySQL多實(shí)例安裝

• 部署好mysql軟件
• 編輯多個(gè)配置文件，初始化多個(gè)實(shí)例
• 啟動(dòng)MySQL實(shí)例

MySQL多實(shí)例部署

為啥多實(shí)例部署？

• 充分利用系統(tǒng)資源
• 資源隔離
• 業(yè)務(wù)、模塊隔離

MySQL線上安裝小結(jié)

• 根據(jù)需求選擇合適的版本以及分支，建議使用或升級(jí)到較高版本5.5或5.6
• 如果需要定制MySQL功能的話，可以考慮編譯安裝，否則的話建議使用二進(jìn)制包安裝，比較省事
• 根據(jù)機(jī)器配置選擇部署多個(gè)MySQL實(shí)例還是單個(gè)實(shí)例，機(jī)器配置非常好的話，建議部署多實(shí)例

5.5-MySQL主從復(fù)制

MySQL主從復(fù)制

• 一主一從
• 主主復(fù)制
• 一主多從
• 多主一從
• 聯(lián)級(jí)復(fù)制

MySQL主從復(fù)制用途

• 實(shí)時(shí)災(zāi)備，用于故障切換
• 讀寫分離，提供查詢服務(wù)
• 備份，避免影響業(yè)務(wù)

MySQL主從復(fù)制部署

主從部署必要條件

• 主庫開啟binlog日志(設(shè)置log-bin參數(shù))
• 主從server-id不同
• 從庫服務(wù)器能連通主庫

主從部署步驟：

• 備份還原(mysqldump或xtrabackup)
• 授權(quán)(grant replication slave on .)
• 配置復(fù)制，并啟動(dòng)(change master to)
• 查看主從復(fù)制信息(show slave status\G)

MySQL復(fù)制存在的問題

存在的問題

• 主機(jī)宕機(jī)后，數(shù)據(jù)可能丟失
• 從庫只有一個(gè)sql thread，主庫寫壓力大，復(fù)制很可能延時(shí)

解決方法：

• 半同步復(fù)制
• 并行復(fù)制

MySQL semi-sync(半同步復(fù)制)

半同步復(fù)制

• 5.5集成到MySQL，以插件形式存在，需要單獨(dú)安裝
• 確保事務(wù)提交后binlog至少傳輸?shù)揭粋€(gè)從庫
• 不保證從庫應(yīng)用完這個(gè)事務(wù)的binlog
• 性能有一定的降低，響應(yīng)時(shí)間更長(zhǎng)
• 網(wǎng)絡(luò)異?；驈膸戾礄C(jī)，卡住主庫，直到超時(shí)或從庫恢復(fù)

MySQL異步復(fù)制

./sorence/images/04.png

異步復(fù)制

MySQL semi-sync(半同步復(fù)制)

./sorence/images/05.png

半同步復(fù)制

配置MySQL半同步復(fù)制

只需一次：

主庫：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

從庫：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

動(dòng)態(tài)設(shè)置：

主庫：

SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout=N; master 延遲切異步

從庫：

SET GLOBAL rpl_semi_sync_slave_enabled=1;

配置MySQL并行復(fù)制

并行復(fù)制

• 社區(qū)版5.6中新增
• 并行是指從庫多線程apply binlog
• 庫級(jí)別并行應(yīng)用binlog，同一個(gè)數(shù)據(jù)庫更改還是串行的(5.7版并行復(fù)制基于事務(wù)組)

設(shè)置

set global slave_parallel_workers=10; 設(shè)置sql線程數(shù)為10

聯(lián)級(jí)復(fù)制

A -> B -> C

B中添加參數(shù)：
log_slave_updates
B將把A的binlog記錄到自己的binlog日志中

復(fù)制監(jiān)控

查詢從庫狀態(tài)：

show slave status\G

復(fù)制出錯(cuò)處理

常見：1062(主鍵沖突) 1032(記錄不存在)
解決：手動(dòng)處理
或：
跳過復(fù)制出錯(cuò)
set global sql_slave_skip_counter=1

總結(jié)

• MySQL主從復(fù)制是MySQL高可用性、高性能(負(fù)載均衡)的基礎(chǔ)
• 簡(jiǎn)單、靈活，部署方式多樣，可以根據(jù)不同業(yè)務(wù)場(chǎng)景部署不同復(fù)制結(jié)構(gòu)
• MySQL主從復(fù)制目前也存在一些問題，可以根據(jù)需要部署復(fù)制增強(qiáng)功能來解決問題
• 復(fù)制過程中應(yīng)該時(shí)刻監(jiān)控復(fù)制狀態(tài)，復(fù)制出錯(cuò)或延時(shí)可能給系統(tǒng)造成影響
• MySQL復(fù)制是MySQL數(shù)據(jù)庫工程師必知必會(huì)的一項(xiàng)基本技能

5.6-MySQL日常運(yùn)維

DBA運(yùn)維工作

日常

• 導(dǎo)數(shù)據(jù)、數(shù)據(jù)修改、表結(jié)構(gòu)變更
• 加權(quán)限、問題處理
  其他
• 數(shù)據(jù)庫選型部署、設(shè)計(jì)、監(jiān)控、備份、優(yōu)化等

導(dǎo)數(shù)據(jù)及注意事項(xiàng)

• 數(shù)據(jù)最終形式(csv、sql文本 還是直接導(dǎo)入某庫中)
• 導(dǎo)數(shù)據(jù)方法(mysqldump、select into outfile)
• 導(dǎo)數(shù)據(jù)注意事項(xiàng)
  • 導(dǎo)出為csv格式需要file權(quán)限，而且只能數(shù)據(jù)庫本地導(dǎo)
  • 避免鎖庫鎖表(mysqldump使用——single-transaction選項(xiàng)不鎖表)
  • 避免對(duì)業(yè)務(wù)造成影響，盡量在鏡像庫做

數(shù)據(jù)修改及注意事項(xiàng)

• 修改前切記做好備份
• 開事務(wù)做，修改完檢查好了再提交
• 避免一次 修改大量數(shù)據(jù)，可以分批修改
• 避免業(yè)務(wù)高峰期做

表結(jié)構(gòu)變更注意事項(xiàng)

• 在低峰期做
• 表結(jié)構(gòu)變更是否會(huì)有鎖？(5.6包含online ddl功能)
• 使用pt-online-schema-change完成表結(jié)構(gòu)變更
  • 可以避免主從延時(shí)
  • 可以避免負(fù)載過高，可以限速

加權(quán)限及注意事項(xiàng)

• 只給符合需求的最低權(quán)限
• 避免授權(quán)時(shí)修改密碼
• 避免給應(yīng)用賬號(hào)super權(quán)限

問題處理(數(shù)據(jù)庫慢？)

• 數(shù)據(jù)庫慢在哪？
• show processlist查看mysql連接信息
• 查看系統(tǒng)狀態(tài)(iostat, top, vmstat)

小結(jié)

• 日常工作比較簡(jiǎn)單，但是任何一個(gè)操作都可能影響線上服務(wù)
• 結(jié)合不同環(huán)境，不同要求選擇最合適的方法處理
• 日常工作應(yīng)該求穩(wěn)不求快，保障線上穩(wěn)定是DBA的最大責(zé)任

5.7-MySQL參數(shù)調(diào)優(yōu)

為什么要調(diào)整參數(shù)

• 不同服務(wù)器之間的配置、性能不一樣
• 不同業(yè)務(wù)場(chǎng)景對(duì)數(shù)據(jù)的需求不一樣
• MySQL的默認(rèn)參數(shù)只是個(gè)參考值，并不適合所有的應(yīng)用場(chǎng)合

優(yōu)化之前我們需要知道什么

• 服務(wù)器相關(guān)的配置
• 業(yè)務(wù)相關(guān)的情況
• MySQL相關(guān)的配置

服務(wù)器上需要關(guān)注哪些

• 硬件情況
• 操作系統(tǒng)版本
• CPU、網(wǎng)卡節(jié)電模式
• 服務(wù)器numa設(shè)置
• RAID卡緩存

磁盤調(diào)度策略-write back

• 數(shù)據(jù)寫入cache既返回，數(shù)據(jù)異步的從cache刷入存儲(chǔ)介質(zhì)

磁盤調(diào)度策略-write through

• 數(shù)據(jù)同時(shí)寫入cache和存儲(chǔ)介質(zhì)才返回寫入成功

Write Back VS Write Through

• write Back 性能優(yōu)于 Write Through
• Write Through 比 Write Back安全性高

RAID

• RAID Redundant Array of Independent Disks
  • 生產(chǎn)環(huán)境里一般不太會(huì)用裸設(shè)備，通常會(huì)使用RAID卡對(duì)一塊盤或多塊盤做RAID
  • RAID卡會(huì)預(yù)留一塊內(nèi)存，來保證數(shù)據(jù)高效存儲(chǔ)與讀取
  • 常見的RAID類型有:RAID1、RAID0、RAID10和RAID5

RAID0 VS RAID1

• RAID 0 - Block Striped. No Mirror. No Parity.
• RAID 1 - Block Mirrored. No Stripe. No Parity.

RAID5 VS RAID10

• RAID 5 - Block Striped. Distributed Parity.(至少三塊盤，每塊里有兩個(gè)數(shù)據(jù)塊和一個(gè)校驗(yàn)塊)
• RAID 10 - Block Mirrored.(每?jī)蓧K盤做RAID1，然后再按組做RAID0，至少四塊盤)

RAID如何保證數(shù)據(jù)安全

• BBU(Backup Battery Unit)
  • BBU保證在WB策略下，即使服務(wù)器發(fā)生掉電或者宕機(jī)，也能夠?qū)⒕彺鏀?shù)據(jù)寫入到磁盤，從而保證數(shù)據(jù)的安全

MySQL有哪些注意事項(xiàng)

• MySQL的部署安裝
• MySQL的監(jiān)控
• MySQL參數(shù)調(diào)優(yōu)

部署MySQL的要求

• 推薦的MySQL版本: >= MySQL5.5
• 推薦的MySQL存儲(chǔ)引擎: InnoDB

系統(tǒng)調(diào)優(yōu)的依據(jù)：監(jiān)控

• 實(shí)時(shí)監(jiān)控MySQL的slow log
• 實(shí)時(shí)監(jiān)控?cái)?shù)據(jù)庫服務(wù)器的負(fù)載情況
• 實(shí)時(shí)監(jiān)控MySQL內(nèi)部狀態(tài)值

通常關(guān)注哪些MySQL Status

• Com_Select/Update/Delete/Insert
• Bytes_received/Bytes_sent
• Buffer Pool Hit Rate
• Threads_connected/Threads_created/Threads_running

MySQL參數(shù)調(diào)優(yōu)

• 為什么要調(diào)整MySQL的參數(shù)
  • MySQL是通用數(shù)據(jù)庫，但業(yè)務(wù)是多變的，默認(rèn)參數(shù)無法滿足所有業(yè)務(wù)需求
  • MySQL內(nèi)部一些參數(shù)是在MySQL一些很老的版本時(shí)候做的，可能之前是做限流和保護(hù)用的，但隨著機(jī)器性能的提高，這些保護(hù)類的參數(shù)可能會(huì)成為性能瓶頸

讀優(yōu)化

• 合理利用索引對(duì)MySQL查詢性能至關(guān)重要
• 適當(dāng)?shù)恼{(diào)整參數(shù)也能提升查詢性能

innodb_buffer_pool_size

• InnoDB存儲(chǔ)引擎自己維護(hù)一塊內(nèi)存區(qū)域完成新老數(shù)據(jù)的替換
• 內(nèi)存越大越能緩存更多的數(shù)據(jù)

innodb_thread_concurrency

• innoDB內(nèi)部并發(fā)控制參數(shù)，設(shè)置為0代表不做控制
• 如果并發(fā)請(qǐng)求較多，參數(shù)設(shè)置較小，后進(jìn)來的請(qǐng)求將會(huì)排隊(duì)

寫優(yōu)化

• 表結(jié)構(gòu)設(shè)計(jì)上使用自增字段作為表的主鍵
• 只對(duì)合適的字段加索引，索引太多影響寫入性能
• 監(jiān)控服務(wù)器磁盤IO情況，如果寫延遲較大則需要擴(kuò)容
• 選擇正確的MySQL版本，合理設(shè)置參數(shù)

哪些參數(shù)有助于提高寫入性能

• innoDB_flush_log_at_trx_commit && sync_binlog
• innodb log file size
• innodb_io_capacity
• innodb insert buffer

主要影響MySQL寫性能的兩個(gè)參數(shù)

• innoDB_flush_log_at_trx_commit
• sync_binlog

innoDB_flush_log_at_trx_commit

• 控制InnoDB事務(wù)的刷新方式，一共有三個(gè)值：0,1,2
  • N=0 - 每隔一秒，把事務(wù)日志緩存區(qū)的數(shù)據(jù)寫到日志文件中，以及把日志文件的數(shù)據(jù)刷新到磁盤上(高效，但不安全)
  • N=1 - 每個(gè)事務(wù)提交時(shí)候，把事務(wù)日志從緩存區(qū)寫到日志文件中，并且刷新日志文件的數(shù)據(jù)到磁盤上，優(yōu)先使用此模式保障數(shù)據(jù)安全性(低效，非常安全)
  • N=2 - 每事務(wù)提交的時(shí)候，把事務(wù)日志數(shù)據(jù)從緩存區(qū)寫到日志文件中；每隔一秒，但不一定刷新到磁盤上，而是取決于操作系統(tǒng)的調(diào)度(高效，但不安全)

sync_binlog

• 控制每次寫入Binlog，是否都需要進(jìn)行一次持久化

如何保證事務(wù)的安全

• innoDB_flush_log_at_trx_commit 和 sync_binlog都設(shè)為1
• 事務(wù)要和Binlog保證一致性

(加鎖)-> xa_prepare, Fsync -> Write And Fsync Binlog -> InnoDB Commit, Fsync ->(釋放鎖)

串行有哪些問題

• SAS盤一般每秒只能有150~200個(gè)Fsync。
• 換算到數(shù)據(jù)庫每秒只能執(zhí)行50~60個(gè)事務(wù)

社區(qū)和官方的改進(jìn)

• MariaDB提出改進(jìn)，即使這兩個(gè)參數(shù)都是1也能做到合并效果，性能得到了大幅提高。
• 官方吸收了MariaDB的思想，并在此基礎(chǔ)上進(jìn)行了改進(jìn)，性能再次得到了提高

Tips:

• 官方在MySQL5.6版本之后才做了這個(gè)優(yōu)化
• Percona和MariaDB版本在MySQL5.5已經(jīng)包含了這個(gè)優(yōu)化

InnoDB Redo log

• Write ahead Log

Redo log的作用

• Redo log用在數(shù)據(jù)庫崩潰會(huì)的故障恢復(fù)

Redo log有哪些問題

• 如果寫入頻繁導(dǎo)致Redo log里對(duì)應(yīng)的最老的數(shù)據(jù)臟頁還沒有刷新到磁盤，此時(shí)數(shù)據(jù)庫將卡住，強(qiáng)制刷新臟頁到磁盤
• MySQL默認(rèn)配置兩個(gè)文件才10M，非常容易寫滿，生產(chǎn)環(huán)境中應(yīng)適當(dāng)調(diào)整大小。

innodb_io_capacity

• InnoDB每次刷多少個(gè)臟頁，決定InnoDB存儲(chǔ)引擎的吞吐能力。
• 在SSD等高性能存儲(chǔ)介質(zhì)下，應(yīng)該提高該參數(shù)以提高數(shù)據(jù)庫的性能。

Insert Buffer

• 順序讀寫 VS 隨機(jī)讀寫
• 隨機(jī)請(qǐng)求性能遠(yuǎn)小于順序請(qǐng)求

盡可能多的隨機(jī)請(qǐng)求合并為順序請(qǐng)求才是提高數(shù)據(jù)庫性能的關(guān)鍵

• MySQL從5.1版本開始支持Insert Buffer
• MySQL5.5版本之后同時(shí)支持update和delete的merge
• Insert Buffer只對(duì)二級(jí)索引且非唯一索引有效

總結(jié)

• 服務(wù)器配置要合理(內(nèi)核版本、磁盤調(diào)度策略、RAID卡緩存)
• 完善的監(jiān)控系統(tǒng)，提前發(fā)現(xiàn)問題
• 數(shù)據(jù)庫版本要跟上，不要太新，也不要太老
• 數(shù)據(jù)庫性能優(yōu)化：
  • 查詢優(yōu)化：索引優(yōu)化為主，參數(shù)優(yōu)化為輔
  • 寫入優(yōu)化：業(yè)務(wù)優(yōu)化為主，參數(shù)優(yōu)化為輔