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

1、MySQL基礎(chǔ)技能與原理——高級(jí)應(yīng)用,MySQL DBA Team,彭立勛（http://www.penglixun.com）,內(nèi)容概要,6. MySQL數(shù)據(jù)庫(kù)同步方案7. MySQL與應(yīng)用程序的優(yōu)化8. MySQL管理基礎(chǔ)9. 網(wǎng)站常見(jiàn)錯(cuò)誤分析10. MySQL故障案例,MySQL數(shù)據(jù)庫(kù)同步方案,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ù)庫(kù)本身的機(jī)制變動(dòng)帶來(lái)同步程序的大改動(dòng)。缺點(diǎn)：對(duì)數(shù)據(jù)庫(kù)入侵大，需要為每張表建立Trigger，并且在數(shù)據(jù)庫(kù)建立存儲(chǔ)過(guò)程，執(zhí)行存儲(chǔ)過(guò)程和觸發(fā)器會(huì)帶來(lái)一定的數(shù)據(jù)庫(kù)壓力。,Log同步方案,

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

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

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

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

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

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

9、跨應(yīng)用進(jìn)行JOIN，或者數(shù)據(jù)庫(kù)不能獲得較好的執(zhí)行計(jì)劃，都可以自己通過(guò)程序來(lái)實(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ù)庫(kù)壓力，也可以實(shí)現(xiàn)跨主機(jī)的JOIN。《內(nèi)連接實(shí)現(xiàn)例子》《外連接實(shí)現(xiàn)例子》,MySQL管理基礎(chǔ),HA與海量數(shù)據(jù)方案復(fù)制搭建方法簡(jiǎn)單備份方案簡(jiǎn)易故障處理運(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)下沒(méi)有分庫(kù)的標(biāo)準(zhǔn)方案）LVS+Keepalived方案：利用Keepalived管理LVS和VIP，LVS分發(fā)請(qǐng)求到MySQL，MySQL進(jìn)行雙M復(fù)制。（Linux系統(tǒng)下無(wú)分庫(kù)無(wú)事務(wù)的方案）Cobar方案：利用Cobar進(jìn)行HA和分庫(kù)，應(yīng)用程序請(qǐng)求Cobar，Cobar轉(zhuǎn)發(fā)請(qǐng)求道數(shù)據(jù)庫(kù)。（有分庫(kù)的標(biāo)準(zhǔn)方案，Unix下唯一方案）,復(fù)制搭建方法,搭建復(fù)制的必備條件：復(fù)制的機(jī)器之間網(wǎng)絡(luò)通暢，Master打開(kāi)了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開(kāi)始復(fù)制。,簡(jiǎn)單備份方案,冷備份： MyISAM：拷貝數(shù)據(jù)目錄下數(shù)據(jù)庫(kù)名的目錄即可，恢復(fù)只要拷貝回來(lái)即可。 InnoDB：拷貝全部數(shù)據(jù)目錄。熱備份： MyISAM：Flush

14、 Tables With Read Lock;后可以直接拷貝。 InnoDB：xtrackaup來(lái)實(shí)現(xiàn)。邏輯備份： mysqldump –u用戶 –p密碼 數(shù)據(jù)庫(kù) [表1 表2…] > db.sql全庫(kù)完整備份（帶復(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,簡(jiǎn)易故障處理,啟動(dòng)不成功怎么辦？ 查看error.log

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

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

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

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