MySQL數(shù)據(jù)庫運維管理基礎(chǔ)知識.ppt

1、,MySQL數(shù)據(jù)庫運維管理基礎(chǔ)知識,2013 年 7 月,金官丁 136 6166 8096 ,數(shù)據(jù)庫版本選擇 數(shù)據(jù)庫監(jiān)控體系 數(shù)據(jù)庫備份恢復(fù)策略 大數(shù)據(jù)量DML操作的技巧 DDL變更注意事項和變更技巧 在線數(shù)據(jù)庫遷移的技巧,目錄,數(shù)據(jù)庫版本選擇,MySQL數(shù)據(jù)庫分支,數(shù)據(jù)庫版本選擇,MySQL數(shù)據(jù)庫版本種類,版本號小于 5.0：MySQL3.X MySQL4.X 等 MySQL5.0.X MySQL5.1.X+Build-in InnoDB存儲引擎 MySQL5.1.X+Plug-in InnoDB存儲引擎 MySQL5.5.X MySQL5.6.X MySQL Cluster 6.2.X

2、和 MySQL Cluster 6.3.X MySQL Cluster 7.0.X MySQL Cluster 7.1.X MySQL Cluster 7.2.X MySQL Cluster 7.3.X,數(shù)據(jù)庫版本選擇,挑選版本的技巧,大版本發(fā)布，至少要大于或等于18個版本號 最新版本號倒退1-2個版本號 假設(shè)選擇版本號為 MySQL5.5.31 看版本號MySQL5.5.31修復(fù)的BUG數(shù)和新增功能 看版本號MySQL5.5.32修復(fù)的BUG數(shù) 看版本號MySQL5.5.30修復(fù)的BUG數(shù),數(shù)據(jù)庫版本選擇,版本定型的過程,內(nèi)部的功能測試，以用到的MySQL功能為測試基準 針對用到的MySQL

3、功能做基準的壓力測試 內(nèi)部開發(fā)環(huán)境試用 內(nèi)部產(chǎn)品的功能和性能測試環(huán)境試用 穩(wěn)定性要求不高且壓力較小的生產(chǎn)環(huán)境試用 穩(wěn)定性要求高且壓力中等的生產(chǎn)環(huán)境試用 生產(chǎn)環(huán)境正式試用,數(shù)據(jù)庫版本選擇 數(shù)據(jù)庫監(jiān)控體系 數(shù)據(jù)庫備份恢復(fù)策略 大數(shù)據(jù)量DML操作的技巧 大表DDL變更注意事項和變更技巧 在線數(shù)據(jù)庫遷移的技巧,目錄,數(shù)據(jù)庫監(jiān)控體系,數(shù)據(jù)庫服務(wù)是否正常 數(shù)據(jù)庫的性能 數(shù)據(jù)庫的邏輯IO和物理IO 數(shù)據(jù)庫的業(yè)務(wù)量 數(shù)據(jù)庫的數(shù)據(jù)與二進制日志的增長量 主機的CPU、IO、內(nèi)存、網(wǎng)卡流量、數(shù)據(jù)增長 數(shù)據(jù)庫的日志 主機系統(tǒng)的日志,數(shù)據(jù)庫版本選擇 數(shù)據(jù)庫監(jiān)控體系 數(shù)據(jù)庫備份恢復(fù)策略 大數(shù)據(jù)量DML操作的技巧 大表DD

4、L變更注意事項和變更技巧 在線數(shù)據(jù)庫遷移的技巧,目錄,數(shù)據(jù)庫備份恢復(fù)策略,數(shù)據(jù)故障恢復(fù)指標 業(yè)務(wù)中斷少于10秒鐘，則在線熱備 業(yè)務(wù)中斷少于5分鐘，則在線延遲恢復(fù)熱備 業(yè)務(wù)中斷小于2小時，則完整備份+二進制日志備份 數(shù)據(jù)庫備份恢復(fù)規(guī)劃 雙主在線熱備 雙主在線熱備+延遲恢復(fù)備庫 每天完整備份+每小時二進制日志備份 數(shù)據(jù)庫備份恢復(fù)軟件組合 開源工具：mysqldump 開源工具：xtrabackup 或 收費工具：InnoDB hot backup,數(shù)據(jù)庫版本選擇 數(shù)據(jù)庫監(jiān)控體系 數(shù)據(jù)庫備份恢復(fù)策略 大數(shù)據(jù)量DML操作的技巧 大表DDL變更注意事項和變更技巧 在線數(shù)據(jù)庫遷移的技巧,目錄,大數(shù)據(jù)量DM

5、L操作的技巧,大數(shù)據(jù)量INSERT 大數(shù)據(jù)量UPDATE 大數(shù)據(jù)量DELETE,大數(shù)據(jù)量DML操作的技巧,大數(shù)據(jù)量INSERT,INSERT INTO VALUES(),(),(). LOAD DATA . 寫入臨時表，再INSERT SELECT ,大數(shù)據(jù)量DML操作的技巧,大數(shù)據(jù)量UPDATE,SELECT 唯一字段，變更字段 查詢結(jié)果集寫入臨時表 給臨時表創(chuàng)建組合索引 循環(huán)臨時表的每條記錄 依據(jù)臨時表的唯一字段值，更新目標表的相關(guān)字段值,大數(shù)據(jù)量DML操作的技巧,大數(shù)據(jù)量DELETE,若是定期且特殊規(guī)則刪除大批量的數(shù)據(jù)，則建議采用分區(qū)表 若是定期且無特殊規(guī)則刪除大批量的數(shù)據(jù)，則分區(qū)表+U

6、PDATE 若是上述2種方式無法使用，則可以主備輪流刪除+數(shù)據(jù)服務(wù)切換 若是上述3種方式無法使用 調(diào)整參數(shù)，直接執(zhí)行DELETE批量語句 先查詢出要刪除數(shù)據(jù)的唯一標識寫入臨時表，然后游標循環(huán)刪除,數(shù)據(jù)庫版本選擇 數(shù)據(jù)庫監(jiān)控體系 數(shù)據(jù)庫備份恢復(fù)策略 大數(shù)據(jù)量DML操作的技巧 大表DDL變更注意事項和變更技巧 在線數(shù)據(jù)庫遷移的技巧,目錄,大表DDL變更注意事項和變更技巧,變更注意事項 表級別鎖 InnoDB存儲引擎的數(shù)據(jù)字典鎖 DDL加速技巧,大表DDL變更注意事項和變更技巧,變更注意事項,增加字段必須要有默認值 例如: ALTER TABLE tmp_test ADD COLUMN gmt_Cr

7、eateDate TIMESTAMP NOT NULL DEFAULT 0000-00-00 00:00:00; 增加的字段不能指定字段順序，必須默認尾部增加模式 例如：增加一個字段到表對象的頭部 ALTER TABLE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0 FIRST; 這是我們強烈反對的行為，可能導(dǎo)致需要更多時間用于數(shù)據(jù)重整， 以及對復(fù)制可能造成數(shù)據(jù)錯亂，應(yīng)該寫成： ALTER TABLE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0;

8、,大表DDL變更注意事項和變更技巧,變更注意事項,建議表設(shè)計都設(shè)置為NOT NULL 且默認值 非NULL且有默認值的字段屬性，有利于提高數(shù)據(jù)比對檢索的性能， 更加有利于索引結(jié)構(gòu)的優(yōu)化 同一表增加多個字段，不分多條語句，而應(yīng)只用一條變更語句 例如： ALTER TABLE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0; ALTER TABLE tmp_test ADD COLUMN gmt_CreateDate TIMESTAMP NOT NULL DEFAULT 0000-00-00; 應(yīng)該改寫成： ALTER TAB

9、LE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0, ADD COLUMN gmt_CreateDate TIMESTAMP NOT NULL DEFAULT 0000-00-00;,大表DDL變更注意事項和變更技巧,表級別鎖 InnoDB存儲引擎的數(shù)據(jù)字典鎖,DDL變更注意事項,DDL加速技巧,DDL操作的過程 申請表對象加鎖，只允許讀不允許更新 復(fù)制一份變更對象表的結(jié)構(gòu)代碼 原數(shù)據(jù)結(jié)構(gòu)基礎(chǔ)之上修改，新建一組臨時文件，MyISAM引擎和InnoDB引擎 的文件有所不同，分別為：#*.frm、#*.MYD、#*.MYI,

10、 #*.frm、#*.ibd 變更對象表中的數(shù)據(jù)寫入到臨時表中 速度讀寫完畢的情況下，完全鎖定被變更的表，并且刪除被變更表 對臨時文件執(zhí)行RENAME操作 刷新數(shù)據(jù)字典信息 釋放鎖,DDL變更注意事項,DDL加速技巧,調(diào)整的SESSION變量 建議增加SESSION級別的sort_buffer_size 值，以加速DDL過程 需要做的排序操作； 建議增加SESSION級別的read_buffer_size 值，增加順序讀的速度； 建議增加SESSION級別的tmp_table_size 和max_heap_table_size 值，增加內(nèi)存緩存數(shù)據(jù)的能力； 若是存在InnoDB引擎轉(zhuǎn)MyISA

11、M的操作，建議在線增加 key_buffer_size的值，增加索引數(shù)據(jù)的緩存能力；,DDL變更技巧,大表刪除,創(chuàng)建被刪除表的軟鏈接 執(zhí)行DROP TABLE 語句 業(yè)務(wù)低谷期刪除磁盤上真實的數(shù)據(jù)文件,DDL變更技巧,主備切換變更,業(yè)務(wù)低谷期，備庫上創(chuàng)建連接，設(shè)置 SESSION.sql_log_bin=0 備庫上執(zhí)行DDL變更命令 待備庫執(zhí)行完畢，切換數(shù)據(jù)服務(wù)的主備庫關(guān)系 新備庫上創(chuàng)建連接，設(shè)置 SESSION.sql_log_bin=0 新備庫上執(zhí)行DDL變更命令 變更完畢,數(shù)據(jù)庫版本選擇 數(shù)據(jù)庫監(jiān)控體系 數(shù)據(jù)庫備份恢復(fù)策略 大數(shù)據(jù)量DML操作的技巧 大表DDL變更注意事項和變更技巧 在線

12、數(shù)據(jù)庫遷移的技巧,目錄,數(shù)據(jù)遷移服務(wù),數(shù)據(jù)庫實例遷移,數(shù)據(jù)遷移服務(wù),數(shù)據(jù)庫遷移,目標數(shù)據(jù)庫服務(wù)器安裝、配置、初始化 目標數(shù)據(jù)庫服務(wù)器加載完整備份與重新初始化 搭建目標數(shù)據(jù)庫服務(wù)器與遷移數(shù)據(jù)庫服務(wù)器之間的主從復(fù)制關(guān)系 暫停遷移數(shù)據(jù)庫的業(yè)務(wù)應(yīng)用訪問 修改應(yīng)用訪問連接地址 目標數(shù)據(jù)庫服務(wù)器提供數(shù)據(jù)服務(wù) 斷開目標數(shù)據(jù)庫服務(wù)器與遷移數(shù)據(jù)庫服務(wù)器之間的主從復(fù)制關(guān)系 特殊技術(shù)刪除遷移數(shù)據(jù)庫服務(wù)器上多余的數(shù)據(jù)庫,數(shù)據(jù)遷移服務(wù),異構(gòu)數(shù)據(jù)庫遷移到MySQL數(shù)據(jù)庫,業(yè)務(wù)模型分析 數(shù)據(jù)容量預(yù)估與拆分規(guī)則 目標業(yè)務(wù)模型的POC 數(shù)據(jù)庫結(jié)構(gòu)設(shè)計與優(yōu)化 模擬生產(chǎn)環(huán)境的性能測試 全量數(shù)據(jù)導(dǎo)出與加載到MySQL數(shù)據(jù)庫 實時同步

13、原異構(gòu)數(shù)據(jù)庫的變更到MySQL數(shù)據(jù)庫 數(shù)據(jù)讀服務(wù)切換到MySQL數(shù)據(jù)庫 數(shù)據(jù)寫服務(wù)切換到MySQL數(shù)據(jù)庫 數(shù)據(jù)服務(wù)質(zhì)量檢測與優(yōu)化調(diào)整,個人介紹,2003年-2006年 主攻SQL Server 2005年-至今 主攻MySQL 2008年-2010年至少幫助過3000名不同技術(shù)人員解答疑難問題 2010年-2012年培養(yǎng) “門生”超過30名，現(xiàn)分布各大互聯(lián)網(wǎng)公司 2012年-至今，以獨立顧問身份，負責(zé)惠普-中國聯(lián)通U-Cloud 項目的數(shù)據(jù)庫平臺DaaS的架構(gòu)設(shè)計、研發(fā)和實施 曾加盟阿里巴巴，推動數(shù)據(jù)庫平臺轉(zhuǎn)型MySQL，組建MySQL 團隊、規(guī)范、標準、流程等，參與研發(fā)的項目超過50個 開心農(nóng)場早期員工，日活躍用戶數(shù)超過5000萬，負責(zé)技術(shù)研發(fā)部 現(xiàn)任上海熱璞網(wǎng)絡(luò)科