MYSQL數據庫學習系列五

                  MYSQL數據庫學習系列五

五．MYSQL運維實踐
5.1-MySQL日志系統
什么是日志
?日志(log)是一種順序記錄事件流水的文件
?記錄計算機程序運行過程中發生了什么
?多種多樣的用途
o幫助分析程序問題
o分析服務請求的特征、流量等
o判斷工作是否成功執行
o等等……
MySQL日志的分類
?服務器日志
o記錄進程啟動運行過程中的特殊事件，幫助分析MySQL服務遇到的問題
o根據需求抓取特定的SQL語句，追蹤性能可能存在的問題的業務SQL
?事務日志
o記錄應用程序對數據的所有更改
o可用于數據恢復
o可用于實例間數據同步
分類 日志名稱
服務器日志 服務錯誤日志
服務器日志 慢查詢日志
服務器日志 綜合查詢日志
事務日志 存儲引擎事務日志
事務日志 二進制日志
服務錯誤日志
?記錄實例啟動運行過程中重要消息
?配置參數
olog_error = /data/mysql_data/node-1/mysql.log
?內容并非全是錯誤消息
?如果mysqld進程無法正常啟動首先查看錯誤日志
慢查詢日志
?記錄執行時間超過一定閾值的SQL語句
?配置參數
slow_query_log = 1
slow_query_log_file = /data/mysql_data/node-1/mysql-slow.log
long_query_time = 5
?用于分析系統中可能存在性能問題的SQL
綜合查詢日志
?如果開啟將會記錄系統中所有SQL語句
?配置參數
general_log = 1
general_log_file = /data/mysql_data/node-1/mysql-slow.log
?偶爾用于幫助分析系統問題，對性能有影響
查詢日志的輸出與文件切換
?日志輸出參數
log_output={file|table|none}
?如果日志文件過大，可以定期截斷并切換新文件
flush log;
存儲引擎事務日志
?部分存儲引擎擁有重做日志(redo log)
?如InnoDB, TokuDB等WAL(Write Ahead Log)機制存儲引擎
?日志隨著事務commit優先持久化，確保異常恢復不丟數據
?日志順序寫性能較好
InnoDB事務日志重用機制
?InnoDB事務日志采用兩組文件交替重用
二進制日志binlog
?binlog (binary log)
?記錄數據引起數據變化的SQL語句或數據邏輯變化的內容
?MySQL服務層記錄，無關存儲引擎
?binlog的主要作用：
o基于備份恢復數據
o數據庫主從同步
o挖掘分析SQL語句
開啟binlog
?主要參數
log_bin = c:/tmp/mylog/mysql-bin
sql_log_bin = 1
sync_binlog = 1
?查看binlog
show binary logs;
binlog管理
?主要參數
max_binlog_size = 100MB
expire_logs_days = 7
?
binlog始終生成新文件，不會重用
?
?
手工清理binlog
?
purge binary logs to 'mysql-bin.000009';
purge binary logs before '2016-4-2 21:00:40'
查看binlog內容
?日志
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格式
?主要參數
binlog_format = {ROW|STATEMENT|MIXED}
?查看row模式的binlog內容
mysqlbinlog --base64-output=decode-rows -v c:/tmp/mylpg/mysql-bin.000001
5.2-MySQL數據備份
基本指數 - 備份用途
?數據備災
o應對硬件故障數據丟失
o應對人為或程序bug導致數據刪除
?制作鏡像庫以供服務
o需要將數據遷移、統計分析等用處
o需要為線上數據建立一個鏡像
基本知識 - 備份內容
?數據
o數據文件或文本格式數據
?操作日志(binlog)
o數據庫變更日志
基本知識 - 冷備份與熱備份
?冷備份
o關閉數據庫服務，完整拷貝數據文件
?熱備份
o在不影響數據庫讀寫服務的情況下備份數據庫
基本知識 - 物理備份與邏輯備份
?物理備份
o以數據頁的形式拷貝數據
?邏輯備份
o導出為裸數據或者SQL(insert)語句
基本知識 - 本地備份與遠程備份
?本地備份
o在數據庫服務器本地進行備份
?遠程備份
o遠程連接數據庫進行備份
基本知識 - 全量備份與增量備份
?全量備份
o備份完整的數據庫
?增量備份
o只備份上一次備份以來發生修改的數據
基本知識 - 備份周期
考慮因素：
?數據庫大小(決定備份時間)
?恢復速度要求(快速or慢速)
?備份方式(全量or增量)
常用工具及用法
?mysqldump - 邏輯備份，熱備
?xtrabackup - 物理備份， 熱備
?Lvm/zfs snapshot - 物理備份
?mydumper - 邏輯備份，熱備
?cp - 物理備份，冷備
常用工具及用法 - mysqldump
MySQL官方自帶的命令行工具
主要示例：
?演示使用mysqldump備份表、庫、實例

備份所有數據庫

mysqldump -uroot -p123456 --socket=/var/run/mysqld/mysqld.sock --all-databases > /dbbackup/all_db.sql# 備份指定的數據庫
mysqldump -uroot -p123456 --socket=/var/run/mysqld/mysqld.sock --databases db2 > /dbbackup/db2.sql# 備份單個表
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遠程備份一個數據庫
mysqldump -utest -ptest -h292.168.0.68 -P3306 --all-databases > /dbbackup/remote_bakall.sql
?演示使用mysqldump導出數據為csv格式
mysqldump -uroot -p123456 --single-transaction --fields-terminated-by=, db1 -T /tmp
常用工具及用法 - xtrabackup
特點：
?開源，在線備份InnoDB表
?支持限速備份，避免對業務造成影響
?支持流備
?支持增量備份
?支持備份文件壓縮與加密
?支持并行備份與恢復，速度快
xtrabackup備份原理
?基于InnoDB的crash-recovery功能
?備份期間允許用戶讀寫，寫請求產生redo日志
?從磁盤上拷貝數據文件
?從InnoDB redo log file實時拷貝走備份期間產生的所有redo日志
?恢復的時候 數據文件 + redo日志 = 一致性數據
實用腳本innobackupex
?開源Perl腳本，封裝調用xtrabackup及一系列相關工具與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/
如何制定備份策略
需要考慮的因素
?數據庫是不是都是innodb引擎表 -> 備份方式，熱備or冷備
?數據量大小 -> 邏輯備份or物理備份，全量or增量
?數據庫本地磁盤空間十分充足 -> 備份到本地or遠程
?需要多塊恢復 -> 備份頻率 小時or天
5.3-MySQL數據恢復
什么時候需要恢復數據
?硬件故障(如磁盤損壞)
?人為刪除(如誤刪除數據、被黑)
?業務回滾(如游戲bug需要回檔)
?正常需求(如部署鏡像庫、查看歷史某時刻數據)
數據恢復的必要條件
?有效備份
?完整的數據庫操作日志(binlog)
數據恢復思路
?最新一次備份 + binlog恢復到故障時間點(適用于各種數據丟失場景)
?挖掘最后一次備份到故障點之間的binlog獲取相關SQL語句，構造反轉SQL語句并應用到數據庫(只是用于記錄丟失，且binlog必須是row格式)
反轉SQL語句
例：
t1(id primary key, a int)
反轉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)
數據庫恢復工具與命令
?mysqldump備份 -> source恢復
?xtrabackup備份 -> xtrabackup恢復
?binlog備份 -> mysqlbinlog恢復
詳細示例講解
?恢復某幾條誤刪數據
?恢復誤刪表、庫
?將數據庫恢復到指定時間點
恢復誤刪除數據
case：誤操作，刪除數據忘記帶完整條件，執行delete from user where age > 30 [and sex=male]
需求：將被刪除的數據還原
恢復前提：完整的數據庫操作日志(binlog)
delete from user where sex='female';

首先需要找到binlog里的信息

mysqlbinlog -vv mysql-bin.000001# 找出sql語句，然后寫出反轉sql語句
恢復誤刪表、庫
case：業務被黑，表被刪除了(drop teble user)
需求：將表恢復
前提：備份 + 備份以來完整binlog
innobackupex --apply-log /dbbackup/filename# 查看binlog的位置點
cat xtrabackup_binlog_info# 查看結束點
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
課程小結
?恢復是已經非常苦逼的差事，盡量避免做。我們要做數據衛士而不是救火隊員。(線上應該嚴格把控權限，數據變更操作應事先測試，操作時做好備份)
?有效備份(+binlog)是重中之重，對數據庫定期備份是必須的
?備份是一切數據恢復的基礎
5.4-MySQL線上部署
MySQL線上部署
考慮因素：
?版本選擇， 5.1、5.5還是5.6？
?分支選擇，官方社區版？ percona server？ Mariadb？
?安裝方式，包安裝？二進制包安裝？源碼安裝？
?路徑配置，參數配置(盡量模板化、標準化)
?一個實例多個庫 or 多個實例單個庫？
二進制安裝MySQL
?下載軟件包
?解壓放到指定目錄(比如/usr/local)
?將MySQL目錄放到PATH中
?初始化實例，編輯配置文件并啟動
?賬戶安全設置
編譯安裝MySQL
?下載MySQL源碼安裝包
?安裝必要包(make cmake bison-devel ncurses-devel build-essential)
?Cmake配置MySQL編譯選項，可以定制需要安裝的功能
?make && make install
?初始化實例，編輯配置文件并啟動
?賬戶安全設置
MySQL升級
?下載MySQL5.6安裝包并配置MySQL5.6安裝包安裝路徑
?關閉MySQL5.5實例，修改部分參數，使用MySQL5.6軟件啟動
?執行MySQL5.6路徑下mysql_upgrade腳本
?驗證是否成功升級
MySQL多實例安裝
?部署好mysql軟件
?編輯多個配置文件，初始化多個實例
?啟動MySQL實例
MySQL多實例部署
為啥多實例部署？
?充分利用系統資源
?資源隔離
?業務、模塊隔離
MySQL線上安裝小結
?根據需求選擇合適的版本以及分支，建議使用或升級到較高版本5.5或5.6
?如果需要定制MySQL功能的話，可以考慮編譯安裝，否則的話建議使用二進制包安裝，比較省事
?根據機器配置選擇部署多個MySQL實例還是單個實例，機器配置非常好的話，建議部署多實例
5.5-MySQL主從復制
MySQL主從復制
?一主一從
?主主復制
?一主多從
?多主一從
?聯級復制
MySQL主從復制用途
?實時災備，用于故障切換
?讀寫分離，提供查詢服務
?備份，避免影響業務
MySQL主從復制部署
主從部署必要條件
?主庫開啟binlog日志(設置log-bin參數)
?主從server-id不同
?從庫服務器能連通主庫
主從部署步驟：
?備份還原(mysqldump或xtrabackup)
?授權(grant replication slave on?.)
?配置復制，并啟動(change master to)
?查看主從復制信息(show slave status\G)
MySQL復制存在的問題
存在的問題
?主機宕機后，數據可能丟失
?從庫只有一個sql thread，主庫寫壓力大，復制很可能延時
解決方法：
?半同步復制
?并行復制
MySQL semi-sync(半同步復制)
半同步復制
?5.5集成到MySQL，以插件形式存在，需要單獨安裝
?確保事務提交后binlog至少傳輸到一個從庫
?不保證從庫應用完這個事務的binlog
?性能有一定的降低，響應時間更長
?網絡異常或從庫宕機，卡住主庫，直到超時或從庫恢復
MySQL異步復制

MySQL semi-sync(半同步復制)

配置MySQL半同步復制
只需一次：
主庫：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
從庫：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
動態設置：
主庫：
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并行復制
并行復制
?社區版5.6中新增
?并行是指從庫多線程apply binlog
?庫級別并行應用binlog，同一個數據庫更改還是串行的(5.7版并行復制基于事務組)
設置
set global slave_parallel_workers=10;?設置sql線程數為10
聯級復制
A -> B -> C
B中添加參數： log_slave_updates B將把A的binlog記錄到自己的binlog日志中
復制監控
查詢從庫狀態：
show slave status\G
復制出錯處理
常見：1062(主鍵沖突) 1032(記錄不存在) 解決：手動處理 或： 跳過復制出錯?set global sql_slave_skip_counter=1
總結
?MySQL主從復制是MySQL高可用性、高性能(負載均衡)的基礎
?簡單、靈活，部署方式多樣，可以根據不同業務場景部署不同復制結構
?MySQL主從復制目前也存在一些問題，可以根據需要部署復制增強功能來解決問題
?復制過程中應該時刻監控復制狀態，復制出錯或延時可能給系統造成影響
?MySQL復制是MySQL數據庫工程師必知必會的一項基本技能
5.6-MySQL日常運維
DBA運維工作
日常
?導數據、數據修改、表結構變更
?加權限、問題處理 其他
?數據庫選型部署、設計、監控、備份、優化等
導數據及注意事項
?數據最終形式(csv、sql文本 還是直接導入某庫中)
?導數據方法(mysqldump、select into outfile)
?導數據注意事項
o導出為csv格式需要file權限，而且只能數據庫本地導
o避免鎖庫鎖表(mysqldump使用——single-transaction選項不鎖表)
o避免對業務造成影響，盡量在鏡像庫做
數據修改及注意事項
?修改前切記做好備份
?開事務做，修改完檢查好了再提交
?避免一次 修改大量數據，可以分批修改
?避免業務高峰期做
表結構變更注意事項
?在低峰期做
?表結構變更是否會有鎖？(5.6包含online ddl功能)
?使用pt-online-schema-change完成表結構變更
o可以避免主從延時
o可以避免負載過高，可以限速
加權限及注意事項
?只給符合需求的最低權限
?避免授權時修改密碼
?避免給應用賬號super權限
問題處理(數據庫慢？)
?數據庫慢在哪？
?show processlist查看mysql連接信息
?查看系統狀態(iostat, top, vmstat)
小結
?日常工作比較簡單，但是任何一個操作都可能影響線上服務
?結合不同環境，不同要求選擇最合適的方法處理
?日常工作應該求穩不求快，保障線上穩定是DBA的最大責任
5.7-MySQL參數調優
為什么要調整參數
?不同服務器之間的配置、性能不一樣
?不同業務場景對數據的需求不一樣
?MySQL的默認參數只是個參考值，并不適合所有的應用場合
優化之前我們需要知道什么
?服務器相關的配置
?業務相關的情況
?MySQL相關的配置
服務器上需要關注哪些
?硬件情況
?操作系統版本
?CPU、網卡節電模式
?服務器numa設置
?RAID卡緩存
磁盤調度策略-write back
?數據寫入cache既返回，數據異步的從cache刷入存儲介質
磁盤調度策略-write through
?數據同時寫入cache和存儲介質才返回寫入成功
Write Back VS Write Through
?write Back 性能優于 Write Through
?Write Through 比 Write Back安全性高
RAID
?RAID Redundant Array of Independent Disks
o生產環境里一般不太會用裸設備，通常會使用RAID卡對一塊盤或多塊盤做RAID
oRAID卡會預留一塊內存，來保證數據高效存儲與讀取
o常見的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.(至少三塊盤，每塊里有兩個數據塊和一個校驗塊)
?RAID 10 - Block Mirrored.(每兩塊盤做RAID1，然后再按組做RAID0，至少四塊盤)
RAID如何保證數據安全
?BBU(Backup Battery Unit)
oBBU保證在WB策略下，即使服務器發生掉電或者宕機，也能夠將緩存數據寫入到磁盤，從而保證數據的安全
MySQL有哪些注意事項
?MySQL的部署安裝
?MySQL的監控
?MySQL參數調優
部署MySQL的要求
?推薦的MySQL版本: >= MySQL5.5
?推薦的MySQL存儲引擎: InnoDB
系統調優的依據：監控
?實時監控MySQL的slow log
?實時監控數據庫服務器的負載情況
?實時監控MySQL內部狀態值
通常關注哪些MySQL Status
?Com_Select/Update/Delete/Insert
?Bytes_received/Bytes_sent
?Buffer Pool Hit Rate
?Threads_connected/Threads_created/Threads_running
MySQL參數調優
?為什么要調整MySQL的參數
oMySQL是通用數據庫，但業務是多變的，默認參數無法滿足所有業務需求
oMySQL內部一些參數是在MySQL一些很老的版本時候做的，可能之前是做限流和保護用的，但隨著機器性能的提高，這些保護類的參數可能會成為性能瓶頸
讀優化
?合理利用索引對MySQL查詢性能至關重要
?適當的調整參數也能提升查詢性能
innodb_buffer_pool_size
?InnoDB存儲引擎自己維護一塊內存區域完成新老數據的替換
?內存越大越能緩存更多的數據
innodb_thread_concurrency
?innoDB內部并發控制參數，設置為0代表不做控制
?如果并發請求較多，參數設置較小，后進來的請求將會排隊
寫優化
?表結構設計上使用自增字段作為表的主鍵
?只對合適的字段加索引，索引太多影響寫入性能
?監控服務器磁盤IO情況，如果寫延遲較大則需要擴容
?選擇正確的MySQL版本，合理設置參數
哪些參數有助于提高寫入性能
?innoDB_flush_log_at_trx_commit && sync_binlog
?innodb log file size
?innodb_io_capacity
?innodb insert buffer
主要影響MySQL寫性能的兩個參數
?innoDB_flush_log_at_trx_commit
?sync_binlog
innoDB_flush_log_at_trx_commit
?控制InnoDB事務的刷新方式，一共有三個值：0,1,2
oN=0 - 每隔一秒，把事務日志緩存區的數據寫到日志文件中，以及把日志文件的數據刷新到磁盤上(高效，但不安全)
oN=1 - 每個事務提交時候，把事務日志從緩存區寫到日志文件中，并且刷新日志文件的數據到磁盤上，優先使用此模式保障數據安全性(低效，非常安全)
oN=2 - 每事務提交的時候，把事務日志數據從緩存區寫到日志文件中；每隔一秒，但不一定刷新到磁盤上，而是取決于操作系統的調度(高效，但不安全)
sync_binlog
?控制每次寫入Binlog，是否都需要進行一次持久化
如何保證事務的安全
?innoDB_flush_log_at_trx_commit 和 sync_binlog都設為1
?事務要和Binlog保證一致性
(加鎖)-> xa_prepare, Fsync -> Write And Fsync Binlog -> InnoDB Commit, Fsync ->(釋放鎖)
串行有哪些問題
?SAS盤一般每秒只能有150~200個Fsync。
?換算到數據庫每秒只能執行50~60個事務
社區和官方的改進
?MariaDB提出改進，即使這兩個參數都是1也能做到合并效果，性能得到了大幅提高。
?官方吸收了MariaDB的思想，并在此基礎上進行了改進，性能再次得到了提高
Tips:
?官方在MySQL5.6版本之后才做了這個優化
?Percona和MariaDB版本在MySQL5.5已經包含了這個優化
InnoDB Redo log
?Write ahead Log
Redo log的作用
?Redo log用在數據庫崩潰會的故障恢復
Redo log有哪些問題
?如果寫入頻繁導致Redo log里對應的最老的數據臟頁還沒有刷新到磁盤，此時數據庫將卡住，強制刷新臟頁到磁盤
?MySQL默認配置兩個文件才10M，非常容易寫滿，生產環境中應適當調整大小。
innodb_io_capacity
?InnoDB每次刷多少個臟頁，決定InnoDB存儲引擎的吞吐能力。
?在SSD等高性能存儲介質下，應該提高該參數以提高數據庫的性能。
Insert Buffer
?順序讀寫 VS 隨機讀寫
?隨機請求性能遠小于順序請求
盡可能多的隨機請求合并為順序請求才是提高數據庫性能的關鍵
?MySQL從5.1版本開始支持Insert Buffer
?MySQL5.5版本之后同時支持update和delete的merge
?Insert Buffer只對二級索引且非唯一索引有效
總結
?服務器配置要合理(內核版本、磁盤調度策略、RAID卡緩存)
?完善的監控系統，提前發現問題
?數據庫版本要跟上，不要太新，也不要太老
?數據庫性能優化：
o查詢優化：索引優化為主，參數優化為輔
o寫入優化：業務優化為主，參數優化為輔