mysql基礎(chǔ)技能與原理――高級應(yīng)用.ppt-linuxtone.org

1、MySQL基礎(chǔ)技能與原理——高級應(yīng)用,MySQL DBA Team,彭立勛（http://www.penglixun.com）,內(nèi)容概要,6. MySQL數(shù)據(jù)庫同步方案7. MySQL與應(yīng)用程序的優(yōu)化8. MySQL管理基礎(chǔ)9. 網(wǎng)站常見錯(cuò)誤分析10. MySQL故障案例,MySQL數(shù)據(jù)庫同步方案,Trigger同步方案(Otter)Log同步方案（Erosa）自帶Replication同步方案,Trigger同步方案,O

2、tter采用Trigger方式進(jìn)行同步?；驹硎菫槊繌堃降谋砑由弦粋€(gè)觸發(fā)器，表上增刪改都會(huì)觸發(fā)Trigger把主鍵記錄到一張同步表，同步程序讀取同步表的主鍵和修改類型，到目標(biāo)表去執(zhí)行相同的操作，保證最終一致。優(yōu)點(diǎn)：安全可靠，不會(huì)因?yàn)槿罩靖袷阶兓驍?shù)據(jù)庫本身的機(jī)制變動(dòng)帶來同步程序的大改動(dòng)。缺點(diǎn)：對數(shù)據(jù)庫入侵大，需要為每張表建立Trigger，并且在數(shù)據(jù)庫建立存儲(chǔ)過程，執(zhí)行存儲(chǔ)過程和觸發(fā)器會(huì)帶來一定的數(shù)據(jù)庫壓力。,Log同步方案,

3、Erosa采用解析日志的方式進(jìn)行同步。通過解析Oracle的RedoLog或MySQL的BinLog，獲取在數(shù)據(jù)庫上執(zhí)行的SQL及其順序，分發(fā)到目標(biāo)數(shù)據(jù)庫去執(zhí)行一遍，以獲得最終一致。優(yōu)點(diǎn)：對數(shù)據(jù)庫入侵小，可以將日志拷貝到異地分析，對數(shù)據(jù)庫本身不會(huì)增加壓力。缺點(diǎn)：對日志格式依賴大，日志格式發(fā)生變動(dòng)則同步程序需要進(jìn)行較大改動(dòng)。,Replication同步方案,MySQL自帶Replication復(fù)制方案，Oracle也有基于塊復(fù)制的St

4、anf by方案。MySQL通過將執(zhí)行的增刪改SQL記錄到日志，傳輸?shù)絺錂C(jī)再執(zhí)行一遍保證最終一致性?？梢栽O(shè)置日志格式為ROW（按行記錄），Statement（按SQL記錄），MIXED（混合式，讓MySQL決定每次記錄用哪個(gè)格式），DDL全部通過Statement方式記錄。優(yōu)點(diǎn)：MySQL自帶，無需第三方工具，部署方便，MySQL自帶豐富的參數(shù)可以實(shí)現(xiàn)各種復(fù)制方案，包括雙Master，級聯(lián)Slave等。缺點(diǎn)：無法實(shí)現(xiàn)多源復(fù)制，復(fù)制位

5、置丟失很難恢復(fù)復(fù)制。,MySQL與應(yīng)用程序的優(yōu)化,預(yù)處理與綁定變量連接池設(shè)置及影響應(yīng)用程序?qū)崿F(xiàn)JOIN,預(yù)處理與綁定變量,PREPARE語句：SET@sql1=‘xxx’; PREPARE stmt1 FROM @sql1; 變量用問號(hào)代替（綁定變量）。 作用：將SQL預(yù)先解析，在一個(gè)Session內(nèi)再次使用此語句時(shí)不再重新解析。EXECUTE語句：EXECUTE stmt1 USING @val1,@val2;

6、 作用：將綁定變量賦予具體的值并且執(zhí)行。DEALLOCATE語句： DEALLOCATE PREPARE stmt1; 作用：銷毀一個(gè)預(yù)解析SQL，釋放資源。為何使用綁定綁定變量？ 因?yàn)榻壎ㄗ兞勘戎苯觽髦蹈踩?，尤其是拼接SQL非常容易被注入攻擊。同時(shí)綁定變量也可以提高一點(diǎn)效率。為何使用預(yù)處理，尤其在存儲(chǔ)過程？ 因?yàn)轭A(yù)處理可以避免SQL的反復(fù)解析，尤其是執(zhí)行速度快的小SQL，解析時(shí)間甚至可能超過執(zhí)行時(shí)

7、間，采用預(yù)處理可以顯著提高性能。,連接池設(shè)置及影響,MySQL如何處理連接？ 每次客戶端向數(shù)據(jù)庫服務(wù)器請求一個(gè)連接，服務(wù)器根據(jù)用戶名密碼在已有連接內(nèi)查找一個(gè)相同配置的連接，分配給請求。如果找不到，則數(shù)據(jù)庫分配一個(gè)新連接給當(dāng)前請求。分配新連接做哪些工作？ MySQL新開一個(gè)連接，就會(huì)創(chuàng)建一個(gè)新的mysql結(jié)構(gòu)體，其中包括Join_buffer、Sort_buffer和一個(gè)Session變量存儲(chǔ)數(shù)組。如果連接數(shù)過多會(huì)怎么

8、樣？ 如果連接MySQL的線程太多，那么每個(gè)線程分配到的join_buffer+sort_buffer總量就會(huì)很大，占用大量內(nèi)存。如何設(shè)置連接池？ 建議最小值和最大值差距不要太大，避免連接反復(fù)的創(chuàng)建和釋放，帶來CPU開銷。如果連接數(shù)一直比較平穩(wěn)的應(yīng)用，則最小值和最大值在平均線附近設(shè)置即可。如果連接數(shù)長期較高，則最小值也設(shè)置大一些。如果連接數(shù)長期較低，則最大值偏小設(shè)置。,應(yīng)用程序?qū)崿F(xiàn)JOIN,如果需要跨主機(jī)進(jìn)行JOIN，

9、跨應(yīng)用進(jìn)行JOIN，或者數(shù)據(jù)庫不能獲得較好的執(zhí)行計(jì)劃，都可以自己通過程序來實(shí)現(xiàn)JOIN。例如：SELECT a.*,b.* FROM a,b WHERE a.col1=b.col1 AND a.col2> 10 ORDER BY a.col2;可以利用程序?qū)崿F(xiàn)，先SELECT * FROM a WHERE a.col2>10 ORDER BY a.col2;--(1)利用(1)的結(jié)果集，做循環(huán)

10、，SELECT * FROM b WHERE b.col1=a.col1;這樣可以避免排序，可以在程序里控制執(zhí)行的速度，有效降低數(shù)據(jù)庫壓力，也可以實(shí)現(xiàn)跨主機(jī)的JOIN?！秲?nèi)連接實(shí)現(xiàn)例子》《外連接實(shí)現(xiàn)例子》,MySQL管理基礎(chǔ),HA與海量數(shù)據(jù)方案復(fù)制搭建方法簡單備份方案簡易故障處理運(yùn)維風(fēng)險(xiǎn)點(diǎn),HA與海量數(shù)據(jù)方案,Heartbeat方案：利用Heartbeat管理VIP，利用crm管理MySQL，MySQL進(jìn)行雙M復(fù)制。（Li

11、nux系統(tǒng)下沒有分庫的標(biāo)準(zhǔn)方案）LVS+Keepalived方案：利用Keepalived管理LVS和VIP，LVS分發(fā)請求到MySQL，MySQL進(jìn)行雙M復(fù)制。（Linux系統(tǒng)下無分庫無事務(wù)的方案）Cobar方案：利用Cobar進(jìn)行HA和分庫，應(yīng)用程序請求Cobar，Cobar轉(zhuǎn)發(fā)請求道數(shù)據(jù)庫。（有分庫的標(biāo)準(zhǔn)方案，Unix下唯一方案）,復(fù)制搭建方法,搭建復(fù)制的必備條件：復(fù)制的機(jī)器之間網(wǎng)絡(luò)通暢，Master打開了binlog。

12、搭建復(fù)制步驟： 1. 創(chuàng)建復(fù)制賬戶：GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY‘xxx'; 2. SHOW MASTER STATUS;記錄下File和Postion。 3. 在Slave上CHANGE MASTER TO master_host=‘IP‘, master_user='repl

13、', master_password=‘xxx’, master_log_file='mysql-bin.00000x', master_log_pos=pos; 4. Slave上執(zhí)行Slave start開始復(fù)制。,簡單備份方案,冷備份： MyISAM：拷貝數(shù)據(jù)目錄下數(shù)據(jù)庫名的目錄即可，恢復(fù)只要拷貝回來即可。 InnoDB：拷貝全部數(shù)據(jù)目錄。熱備份： MyISAM：Flush

14、 Tables With Read Lock;后可以直接拷貝。 InnoDB：xtrackaup來實(shí)現(xiàn)。邏輯備份： mysqldump –u用戶 –p密碼 數(shù)據(jù)庫 [表1 表2…] > db.sql全庫完整備份（帶復(fù)制Postion）： mysqldump -uroot -p --all-databases --opt --add-drop-database --add-drop-table --eve

15、nts --triggers --routines --default-character-set=utf8 --master-data=2 --single-transaction --complete-insert --quote-names --log-error=/tmp/mysqldump20100803.log >backup_20100803.sql,簡易故障處理,啟動(dòng)不成功怎么辦？ 查看error.log

16、，如果報(bào)出can’t create/write dictory，那就是權(quán)限設(shè)置錯(cuò)誤。MySQL的數(shù)據(jù)目錄要求MySQL可以讀它的上級目錄，可以寫數(shù)據(jù)目錄。復(fù)制主鍵沖突怎么辦？ STOP SLAVE; SET GLOBAL SQL_SLAVE_skip_counter = 1; START SLAVE; SHOW SLAVE STATUS \G，原理是跳過出錯(cuò)的行。也可以設(shè)置my.cnf中的skip_slave_errors=1

17、062來批量的跳過復(fù)制錯(cuò)誤（需要重啟）。死鎖怎么辦？ show innodb status;查看死鎖的事務(wù)，kill掉其中一個(gè)即可。大量SQL在freeing items怎么辦？ 數(shù)據(jù)庫所在磁盤已滿，請檢查磁盤空間。,運(yùn)維風(fēng)險(xiǎn)點(diǎn),主庫的任何操作都會(huì)復(fù)制到備庫。 如果需要備庫具備容災(zāi)能力，那么不要實(shí)時(shí)開著slave復(fù)制，每隔一段時(shí)間slave start一次，復(fù)制追上后slave stop停止，直到下一次復(fù)制開

18、始。同時(shí)DROP/ADD字段或索引可能導(dǎo)致數(shù)據(jù)庫Crash。 已經(jīng)在MySQL Bug庫中確認(rèn)此Bug，如果表很大，即使花再多的時(shí)間，也不要冒險(xiǎn)嘗試同時(shí)DROP/ADD字段或索引，分兩次，一次ADD一次DROP。HA切換如果主備主鍵序列一致可能導(dǎo)致沖突。 如果主鍵是自增并且主備序列一致，那么切換時(shí)可能主庫存在記錄沒有來得及復(fù)制到備機(jī)，導(dǎo)致備機(jī)的序列增長蓋過主機(jī)，導(dǎo)致復(fù)制沖突。解決方案是主備生成不一致的主鍵序列，例

19、如一個(gè)奇數(shù)，一個(gè)偶數(shù)，或者利用第三方主鍵生成器。,網(wǎng)站常見故障分析,SQL執(zhí)行計(jì)劃改變導(dǎo)致SQL占用大量CPU。統(tǒng)計(jì)SQL在數(shù)據(jù)量增大后占用大量磁盤IO和CPU。SQL中存在NULL條件，導(dǎo)致執(zhí)行計(jì)劃走錯(cuò)。存儲(chǔ)過程沒有使用預(yù)處理和綁定變量，導(dǎo)致解析SQL占用大量SQL。大批量修改操作沒有SET AUTOCOMMIT=0，導(dǎo)致每條SQL均提交，效率低。SQL未經(jīng)審核上線，導(dǎo)致沒有建立索引，執(zhí)行計(jì)劃為全表掃描。,MySQL故障案例