MYSQL數(shù)據(jù)庫(kù)學(xué)習(xí)系列五

                  MYSQL數(shù)據(jù)庫(kù)學(xué)習(xí)系列五

五．MYSQL運(yùn)維實(shí)踐
5.1-MySQL日志系統(tǒng)
什么是日志
?日志(log)是一種順序記錄事件流水的文件
?記錄計(jì)算機(jī)程序運(yùn)行過(guò)程中發(fā)生了什么
?多種多樣的用途
o幫助分析程序問(wèn)題
o分析服務(wù)請(qǐng)求的特征、流量等
o判斷工作是否成功執(zhí)行
o等等……
MySQL日志的分類(lèi)
?服務(wù)器日志
o記錄進(jìn)程啟動(dòng)運(yùn)行過(guò)程中的特殊事件，幫助分析MySQL服務(wù)遇到的問(wèn)題
o根據(jù)需求抓取特定的SQL語(yǔ)句，追蹤性能可能存在的問(wèn)題的業(yè)務(wù)SQL
?事務(wù)日志
o記錄應(yīng)用程序?qū)?shù)據(jù)的所有更改
o可用于數(shù)據(jù)恢復(fù)
o可用于實(shí)例間數(shù)據(jù)同步
分類(lèi) 日志名稱
服務(wù)器日志 服務(wù)錯(cuò)誤日志
服務(wù)器日志 慢查詢?nèi)罩?br/>服務(wù)器日志 綜合查詢?nèi)罩?br/>事務(wù)日志 存儲(chǔ)引擎事務(wù)日志
事務(wù)日志 二進(jìn)制日志
服務(wù)錯(cuò)誤日志
?記錄實(shí)例啟動(dòng)運(yùn)行過(guò)程中重要消息
?配置參數(shù)
olog_error = /data/mysql_data/node-1/mysql.log
?內(nèi)容并非全是錯(cuò)誤消息
?如果mysqld進(jìn)程無(wú)法正常啟動(dòng)首先查看錯(cuò)誤日志
慢查詢?nèi)罩?br/>?記錄執(zhí)行時(shí)間超過(guò)一定閾值的SQL語(yǔ)句
?配置參數(shù)
slow_query_log = 1
slow_query_log_file = /data/mysql_data/node-1/mysql-slow.log
long_query_time = 5
?用于分析系統(tǒng)中可能存在性能問(wèn)題的SQL
綜合查詢?nèi)罩?br/>?如果開(kāi)啟將會(huì)記錄系統(tǒng)中所有SQL語(yǔ)句
?配置參數(shù)
general_log = 1
general_log_file = /data/mysql_data/node-1/mysql-slow.log
?偶爾用于幫助分析系統(tǒng)問(wèn)題，對(duì)性能有影響
查詢?nèi)罩镜妮敵雠c文件切換
?日志輸出參數(shù)
log_output={file|table|none}
?如果日志文件過(guò)大，可以定期截?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語(yǔ)句或數(shù)據(jù)邏輯變化的內(nèi)容
?MySQL服務(wù)層記錄，無(wú)關(guān)存儲(chǔ)引擎
?binlog的主要作用：
o基于備份恢復(fù)數(shù)據(jù)
o數(shù)據(jù)庫(kù)主從同步
o挖掘分析SQL語(yǔ)句
開(kāi)啟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)
o應(yīng)對(duì)硬件故障數(shù)據(jù)丟失
o應(yīng)對(duì)人為或程序bug導(dǎo)致數(shù)據(jù)刪除
?制作鏡像庫(kù)以供服務(wù)
o需要將數(shù)據(jù)遷移、統(tǒng)計(jì)分析等用處
o需要為線上數(shù)據(jù)建立一個(gè)鏡像
基本知識(shí) - 備份內(nèi)容
?數(shù)據(jù)
o數(shù)據(jù)文件或文本格式數(shù)據(jù)
?操作日志(binlog)
o數(shù)據(jù)庫(kù)變更日志
基本知識(shí) - 冷備份與熱備份
?冷備份
o關(guān)閉數(shù)據(jù)庫(kù)服務(wù)，完整拷貝數(shù)據(jù)文件
?熱備份
o在不影響數(shù)據(jù)庫(kù)讀寫(xiě)服務(wù)的情況下備份數(shù)據(jù)庫(kù)
基本知識(shí) - 物理備份與邏輯備份
?物理備份
o以數(shù)據(jù)頁(yè)的形式拷貝數(shù)據(jù)
?邏輯備份
o導(dǎo)出為裸數(shù)據(jù)或者SQL(insert)語(yǔ)句
基本知識(shí) - 本地備份與遠(yuǎn)程備份
?本地備份
o在數(shù)據(jù)庫(kù)服務(wù)器本地進(jìn)行備份
?遠(yuǎn)程備份
o遠(yuǎn)程連接數(shù)據(jù)庫(kù)進(jìn)行備份
基本知識(shí) - 全量備份與增量備份
?全量備份
o備份完整的數(shù)據(jù)庫(kù)
?增量備份
o只備份上一次備份以來(lái)發(fā)生修改的數(shù)據(jù)
基本知識(shí) - 備份周期
考慮因素：
?數(shù)據(jù)庫(kù)大小(決定備份時(shí)間)
?恢復(fù)速度要求(快速or慢速)
?備份方式(全量or增量)
常用工具及用法
?mysqldump - 邏輯備份，熱備
?xtrabackup - 物理備份， 熱備
?Lvm/zfs snapshot - 物理備份
?mydumper - 邏輯備份，熱備
?cp - 物理備份，冷備
常用工具及用法 - mysqldump
MySQL官方自帶的命令行工具
主要示例：
?演示使用mysqldump備份表、庫(kù)、實(shí)例

備份所有數(shù)據(jù)庫(kù)

mysqldump -uroot -p123456 --socket=/var/run/mysqld/mysqld.sock --all-databases > /dbbackup/all_db.sql# 備份指定的數(shù)據(jù)庫(kù)
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ù)庫(kù)
mysqldump -utest -ptest -h292.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)：
?開(kāi)源，在線備份InnoDB表
?支持限速備份，避免對(duì)業(yè)務(wù)造成影響
?支持流備
?支持增量備份
?支持備份文件壓縮與加密
?支持并行備份與恢復(fù)，速度快
xtrabackup備份原理
?基于InnoDB的crash-recovery功能
?備份期間允許用戶讀寫(xiě)，寫(xiě)請(qǐng)求產(chǎn)生redo日志
?從磁盤(pán)上拷貝數(shù)據(jù)文件
?從InnoDB redo log file實(shí)時(shí)拷貝走備份期間產(chǎn)生的所有redo日志
?恢復(fù)的時(shí)候 數(shù)據(jù)文件 + redo日志 = 一致性數(shù)據(jù)
實(shí)用腳本innobackupex
?開(kāi)源Perl腳本，封裝調(diào)用xtrabackup及一系列相關(guān)工具與OS操作，最終完成備份過(guò)程
?支持備份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ù)庫(kù)是不是都是innodb引擎表 -> 備份方式，熱備or冷備
?數(shù)據(jù)量大小 -> 邏輯備份or物理備份，全量or增量
?數(shù)據(jù)庫(kù)本地磁盤(pán)空間十分充足 -> 備份到本地or遠(yuǎn)程
?需要多塊恢復(fù) -> 備份頻率 小時(shí)or天
5.3-MySQL數(shù)據(jù)恢復(fù)
什么時(shí)候需要恢復(fù)數(shù)據(jù)
?硬件故障(如磁盤(pán)損壞)
?人為刪除(如誤刪除數(shù)據(jù)、被黑)
?業(yè)務(wù)回滾(如游戲bug需要回檔)
?正常需求(如部署鏡像庫(kù)、查看歷史某時(shí)刻數(shù)據(jù))
數(shù)據(jù)恢復(fù)的必要條件
?有效備份
?完整的數(shù)據(jù)庫(kù)操作日志(binlog)
數(shù)據(jù)恢復(fù)思路
?最新一次備份 + binlog恢復(fù)到故障時(shí)間點(diǎn)(適用于各種數(shù)據(jù)丟失場(chǎng)景)
?挖掘最后一次備份到故障點(diǎn)之間的binlog獲取相關(guān)SQL語(yǔ)句，構(gòu)造反轉(zhuǎn)SQL語(yǔ)句并應(yīng)用到數(shù)據(jù)庫(kù)(只是用于記錄丟失，且binlog必須是row格式)
反轉(zhuǎn)SQL語(yǔ)句
例：
t1(id primary key, a int)
反轉(zhuǎn)SQL語(yǔ)句：
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ù)庫(kù)恢復(fù)工具與命令
?mysqldump備份 -> source恢復(fù)
?xtrabackup備份 -> xtrabackup恢復(fù)
?binlog備份 -> mysqlbinlog恢復(fù)
詳細(xì)示例講解
?恢復(fù)某幾條誤刪數(shù)據(jù)
?恢復(fù)誤刪表、庫(kù)
?將數(shù)據(jù)庫(kù)恢復(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ù)庫(kù)操作日志(binlog)
delete from user where sex='female';

首先需要找到binlog里的信息

mysqlbinlog -vv mysql-bin.000001# 找出sql語(yǔ)句，然后寫(xiě)出反轉(zhuǎn)sql語(yǔ)句
恢復(fù)誤刪表、庫(kù)
case：業(yè)務(wù)被黑，表被刪除了(drop teble user)
需求：將表恢復(fù)
前提：備份 + 備份以來(lái)完整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ù)庫(kù)定期備份是必須的
?備份是一切數(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è)庫(kù) or 多個(gè)實(shí)例單個(gè)庫(kù)？
二進(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)備，用于故障切換
?讀寫(xiě)分離，提供查詢服務(wù)
?備份，避免影響業(yè)務(wù)
MySQL主從復(fù)制部署
主從部署必要條件
?主庫(kù)開(kāi)啟binlog日志(設(shè)置log-bin參數(shù))
?主從server-id不同
?從庫(kù)服務(wù)器能連通主庫(kù)
主從部署步驟：
?備份還原(mysqldump或xtrabackup)
?授權(quán)(grant replication slave on?.)
?配置復(fù)制，并啟動(dòng)(change master to)
?查看主從復(fù)制信息(show slave status\G)
MySQL復(fù)制存在的問(wèn)題
存在的問(wèn)題
?主機(jī)宕機(jī)后，數(shù)據(jù)可能丟失
?從庫(kù)只有一個(gè)sql thread，主庫(kù)寫(xiě)壓力大，復(fù)制很可能延時(shí)
解決方法：
?半同步復(fù)制
?并行復(fù)制
MySQL semi-sync(半同步復(fù)制)
半同步復(fù)制
?5.5集成到MySQL，以插件形式存在，需要單獨(dú)安裝
?確保事務(wù)提交后binlog至少傳輸?shù)揭粋€(gè)從庫(kù)
?不保證從庫(kù)應(yīng)用完這個(gè)事務(wù)的binlog
?性能有一定的降低，響應(yīng)時(shí)間更長(zhǎng)
?網(wǎng)絡(luò)異?；驈膸?kù)宕機(jī)，卡住主庫(kù)，直到超時(shí)或從庫(kù)恢復(fù)
MySQL異步復(fù)制

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

配置MySQL半同步復(fù)制
只需一次：
主庫(kù)：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
從庫(kù)：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
動(dòng)態(tài)設(shè)置：
主庫(kù)：
SET GLOBAL rpl_semi_sync_master_enabled=1;?SET GLOBAL rpl_semi_sync_master_timeout=N;?master 延遲切異步
從庫(kù)：
SET GLOBAL rpl_semi_sync_slave_enabled=1;
配置MySQL并行復(fù)制
并行復(fù)制
?社區(qū)版5.6中新增
?并行是指從庫(kù)多線程apply binlog
?庫(kù)級(jí)別并行應(yīng)用binlog，同一個(gè)數(shù)據(jù)庫(kù)更改還是串行的(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)控
查詢從庫(kù)狀態(tài)：
show slave status\G
復(fù)制出錯(cuò)處理
常見(jiàn)：1062(主鍵沖突) 1032(記錄不存在) 解決：手動(dòng)處理 或： 跳過(guò)復(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ù)制目前也存在一些問(wèn)題，可以根據(jù)需要部署復(fù)制增強(qiáng)功能來(lái)解決問(wèn)題
?復(fù)制過(guò)程中應(yīng)該時(shí)刻監(jiān)控復(fù)制狀態(tài)，復(fù)制出錯(cuò)或延時(shí)可能給系統(tǒng)造成影響
?MySQL復(fù)制是MySQL數(shù)據(jù)庫(kù)工程師必知必會(huì)的一項(xiàng)基本技能
5.6-MySQL日常運(yùn)維
DBA運(yùn)維工作
日常
?導(dǎo)數(shù)據(jù)、數(shù)據(jù)修改、表結(jié)構(gòu)變更
?加權(quán)限、問(wèn)題處理 其他
?數(shù)據(jù)庫(kù)選型部署、設(shè)計(jì)、監(jiān)控、備份、優(yōu)化等
導(dǎo)數(shù)據(jù)及注意事項(xiàng)
?數(shù)據(jù)最終形式(csv、sql文本 還是直接導(dǎo)入某庫(kù)中)
?導(dǎo)數(shù)據(jù)方法(mysqldump、select into outfile)
?導(dǎo)數(shù)據(jù)注意事項(xiàng)
o導(dǎo)出為csv格式需要file權(quán)限，而且只能數(shù)據(jù)庫(kù)本地導(dǎo)
o避免鎖庫(kù)鎖表(mysqldump使用——single-transaction選項(xiàng)不鎖表)
o避免對(duì)業(yè)務(wù)造成影響，盡量在鏡像庫(kù)做
數(shù)據(jù)修改及注意事項(xiàng)
?修改前切記做好備份
?開(kāi)事務(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)變更
o可以避免主從延時(shí)
o可以避免負(fù)載過(guò)高，可以限速
加權(quán)限及注意事項(xiàng)
?只給符合需求的最低權(quán)限
?避免授權(quán)時(shí)修改密碼
?避免給應(yīng)用賬號(hào)super權(quán)限
問(wèn)題處理(數(shù)據(jù)庫(kù)慢？)
?數(shù)據(jù)庫(kù)慢在哪？
?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卡緩存
磁盤(pán)調(diào)度策略-write back
?數(shù)據(jù)寫(xiě)入cache既返回，數(shù)據(jù)異步的從cache刷入存儲(chǔ)介質(zhì)
磁盤(pán)調(diào)度策略-write through
?數(shù)據(jù)同時(shí)寫(xiě)入cache和存儲(chǔ)介質(zhì)才返回寫(xiě)入成功
Write Back VS Write Through
?write Back 性能優(yōu)于 Write Through
?Write Through 比 Write Back安全性高
RAID
?RAID Redundant Array of Independent Disks
o生產(chǎn)環(huán)境里一般不太會(huì)用裸設(shè)備，通常會(huì)使用RAID卡對(duì)一塊盤(pán)或多塊盤(pán)做RAID
oRAID卡會(huì)預(yù)留一塊內(nèi)存，來(lái)保證數(shù)據(jù)高效存儲(chǔ)與讀取
o常見(jiàn)的RAID類(lèi)型有: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.(至少三塊盤(pán)，每塊里有兩個(gè)數(shù)據(jù)塊和一個(gè)校驗(yàn)塊)
?RAID 10 - Block Mirrored.(每?jī)蓧K盤(pán)做RAID1，然后再按組做RAID0，至少四塊盤(pán))
RAID如何保證數(shù)據(jù)安全
?BBU(Backup Battery Unit)
oBBU保證在WB策略下，即使服務(wù)器發(fā)生掉電或者宕機(jī)，也能夠?qū)⒕彺鏀?shù)據(jù)寫(xiě)入到磁盤(pán)，從而保證數(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ù)庫(kù)服務(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ù)
oMySQL是通用數(shù)據(jù)庫(kù)，但業(yè)務(wù)是多變的，默認(rèn)參數(shù)無(wú)法滿足所有業(yè)務(wù)需求
oMySQL內(nèi)部一些參數(shù)是在MySQL一些很老的版本時(shí)候做的，可能之前是做限流和保護(hù)用的，但隨著機(jī)器性能的提高，這些保護(hù)類(lèi)的參數(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)來(lái)的請(qǐng)求將會(huì)排隊(duì)
寫(xiě)優(yōu)化
?表結(jié)構(gòu)設(shè)計(jì)上使用自增字段作為表的主鍵
?只對(duì)合適的字段加索引，索引太多影響寫(xiě)入性能
?監(jiān)控服務(wù)器磁盤(pán)IO情況，如果寫(xiě)延遲較大則需要擴(kuò)容
?選擇正確的MySQL版本，合理設(shè)置參數(shù)
哪些參數(shù)有助于提高寫(xiě)入性能
?innoDB_flush_log_at_trx_commit && sync_binlog
?innodb log file size
?innodb_io_capacity
?innodb insert buffer
主要影響MySQL寫(xiě)性能的兩個(gè)參數(shù)
?innoDB_flush_log_at_trx_commit
?sync_binlog
innoDB_flush_log_at_trx_commit
?控制InnoDB事務(wù)的刷新方式，一共有三個(gè)值：0,1,2
oN=0 - 每隔一秒，把事務(wù)日志緩存區(qū)的數(shù)據(jù)寫(xiě)到日志文件中，以及把日志文件的數(shù)據(jù)刷新到磁盤(pán)上(高效，但不安全)
oN=1 - 每個(gè)事務(wù)提交時(shí)候，把事務(wù)日志從緩存區(qū)寫(xiě)到日志文件中，并且刷新日志文件的數(shù)據(jù)到磁盤(pán)上，優(yōu)先使用此模式保障數(shù)據(jù)安全性(低效，非常安全)
oN=2 - 每事務(wù)提交的時(shí)候，把事務(wù)日志數(shù)據(jù)從緩存區(qū)寫(xiě)到日志文件中；每隔一秒，但不一定刷新到磁盤(pán)上，而是取決于操作系統(tǒng)的調(diào)度(高效，但不安全)
sync_binlog
?控制每次寫(xiě)入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 ->(釋放鎖)
串行有哪些問(wèn)題
?SAS盤(pán)一般每秒只能有150~200個(gè)Fsync。
?換算到數(shù)據(jù)庫(kù)每秒只能執(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ù)庫(kù)崩潰會(huì)的故障恢復(fù)
Redo log有哪些問(wèn)題
?如果寫(xiě)入頻繁導(dǎo)致Redo log里對(duì)應(yīng)的最老的數(shù)據(jù)臟頁(yè)還沒(méi)有刷新到磁盤(pán)，此時(shí)數(shù)據(jù)庫(kù)將卡住，強(qiáng)制刷新臟頁(yè)到磁盤(pán)
?MySQL默認(rèn)配置兩個(gè)文件才10M，非常容易寫(xiě)滿，生產(chǎn)環(huán)境中應(yīng)適當(dāng)調(diào)整大小。
innodb_io_capacity
?InnoDB每次刷多少個(gè)臟頁(yè)，決定InnoDB存儲(chǔ)引擎的吞吐能力。
?在SSD等高性能存儲(chǔ)介質(zhì)下，應(yīng)該提高該參數(shù)以提高數(shù)據(jù)庫(kù)的性能。
Insert Buffer
?順序讀寫(xiě) VS 隨機(jī)讀寫(xiě)
?隨機(jī)請(qǐng)求性能遠(yuǎn)小于順序請(qǐng)求
盡可能多的隨機(jī)請(qǐng)求合并為順序請(qǐng)求才是提高數(shù)據(jù)庫(kù)性能的關(guān)鍵
?MySQL從5.1版本開(kāi)始支持Insert Buffer
?MySQL5.5版本之后同時(shí)支持update和delete的merge
?Insert Buffer只對(duì)二級(jí)索引且非唯一索引有效
總結(jié)
?服務(wù)器配置要合理(內(nèi)核版本、磁盤(pán)調(diào)度策略、RAID卡緩存)
?完善的監(jiān)控系統(tǒng)，提前發(fā)現(xiàn)問(wèn)題
?數(shù)據(jù)庫(kù)版本要跟上，不要太新，也不要太老
?數(shù)據(jù)庫(kù)性能優(yōu)化：
o查詢優(yōu)化：索引優(yōu)化為主，參數(shù)優(yōu)化為輔
o寫(xiě)入優(yōu)化：業(yè)務(wù)優(yōu)化為主，參數(shù)優(yōu)化為輔