|
存儲(chǔ)引擎的概念是MySQL的一個(gè)特性，可簡(jiǎn)單理解為表類型;每一個(gè)表都有一個(gè)存儲(chǔ)引擎，可在創(chuàng)建時(shí)指定，也可之后使用ALTER TABLE語(yǔ)句修改，都是通過(guò)ENGINE關(guān)鍵字設(shè)置的;若創(chuàng)建時(shí)沒(méi)有指定，則為默認(rèn)存儲(chǔ)引擎，默認(rèn)存儲(chǔ)引擎也可通過(guò)參數(shù)文件中default-table-type參數(shù)修改。 ----查看當(dāng)前默認(rèn)存儲(chǔ)引擎，默認(rèn)為MyISAM mysql> show variables like 'table_type'; ----查看當(dāng)前數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎 mysql> show engines G; 下面介紹幾個(gè)重要的存儲(chǔ)引擎如下: 從輸出結(jié)果可看到支持多個(gè)存儲(chǔ)引擎， MyISAM 默認(rèn)存儲(chǔ)引擎，不支持事務(wù)、外鍵，但訪問(wèn)速度快，對(duì)事務(wù)完整性不要求，適合于以SELECT/INSERT為主的表;每個(gè)MyISAM物理上存儲(chǔ)為3個(gè)文件，文件名與表名相同，擴(kuò)展名分別為:.frm(存儲(chǔ)表定義)、MYD(MYData存儲(chǔ)數(shù)據(jù))、MYI(MYIndex存儲(chǔ)索引)，其中數(shù)據(jù)文件和索引文件可以放置在不同目錄，平衡I/O。 數(shù)據(jù)文件和索引文件的路徑，需要在創(chuàng)建表時(shí)通過(guò)DATA DIRECTORY和INDEX DIRECTORY語(yǔ)句指定，需要絕對(duì)路徑，且具有訪問(wèn)權(quán)限。 MySQL類型的表可能因各種原因損壞，可通過(guò)CHECK TABLE語(yǔ)句檢查表的健康，使用REPAIR TABLE語(yǔ)句修改。 MySQL類型的表支持3中不同的存儲(chǔ)格式，如下: 靜態(tài)表:默認(rèn)存儲(chǔ)格式，字段長(zhǎng)度固定，存儲(chǔ)迅速，容易緩存，缺點(diǎn)是占用空間多。注意:字段存儲(chǔ)按照 寬度定義補(bǔ)足空格，應(yīng)用訪問(wèn)時(shí)去掉空格，若字段本身就帶有空格，也會(huì)去掉，這點(diǎn)特別注意。 動(dòng)態(tài)表:變長(zhǎng)字段，記錄不是固定長(zhǎng)度，優(yōu)點(diǎn)是占用空間少，但頻繁的更新刪除操作會(huì)產(chǎn)生碎片，需要定期執(zhí)行OPTIMIZE TABLE語(yǔ)句或myisamchk –r命令來(lái)改善，出現(xiàn)故障時(shí)難以恢復(fù)。 壓縮表:由myisampack工具創(chuàng)建，每個(gè)記錄單獨(dú)壓縮，訪問(wèn)開(kāi)支小，占用空間小。 InnoDB具有提交、回滾、崩潰恢復(fù)的事務(wù)安全，相對(duì)MySQL來(lái)說(shuō)，寫(xiě)處理能力差些，且會(huì)占用較多磁盤(pán)空間以保留數(shù)據(jù)和索引。具有以下不同于其他引擎的特點(diǎn): (1)自動(dòng)增長(zhǎng)列 InnoDB表可定義自動(dòng)增長(zhǎng)列，若插入的值為0或空時(shí)，則實(shí)際插入的值為自動(dòng)增長(zhǎng)后的值;可通過(guò)ALTER table語(yǔ)句強(qiáng)制設(shè)置自動(dòng)增長(zhǎng)列的初始值，默認(rèn)從1開(kāi)始，該值保存在內(nèi)存中，若數(shù)據(jù)庫(kù)重啟，則會(huì)丟失;可使用LAST_INSERT_ID()查詢當(dāng)前線程最后插入記錄使用的值，若一次插入多條記錄，則返回第一條記錄使用的自動(dòng)增長(zhǎng)值。 對(duì)于InnoDB表，自動(dòng)增長(zhǎng)列必須是索引，或者是組合索引的第一列;對(duì)于MyISAM表，可以為組合索引的其他列，插入記錄后，自動(dòng)增長(zhǎng)列是按照組合索引的前面幾列進(jìn)行排序后遞增的。 (2)外鍵約束 只有InnoDB引擎支持外鍵約束;創(chuàng)建索引時(shí)，可指定刪除、更新父表時(shí)，對(duì)子表的相應(yīng)操作;RESTRICT/NO ACTION相同，是指限制在子表有關(guān)聯(lián)記錄的情況下父表不能更新;CASCADE表示父表在更新或刪除時(shí)，更新或刪除子表對(duì)應(yīng)記錄;SET NULL表示父表在更新或刪除時(shí)，子表對(duì)應(yīng)字段被SET NULL。 (3)存儲(chǔ)格式 InnoDB存儲(chǔ)表和索引有兩種方式: 使用共享表空間存儲(chǔ)――表結(jié)構(gòu)保存在.frm文件中;數(shù)據(jù)和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以為多個(gè)文件使用多表空間存儲(chǔ)――表結(jié)構(gòu)仍然存儲(chǔ)在.frm文件中，但每個(gè)表的數(shù)據(jù)和索引單獨(dú)保存在.ibd中。若為分區(qū)表，則每個(gè)分區(qū)對(duì)應(yīng)單獨(dú)的.ibd文件，文件名為表名+分區(qū)名使用多表空間存儲(chǔ)，需設(shè)置參數(shù)innodb_file_per_table，并重啟服務(wù)才可生效，只對(duì)新建表有效 MEMORY該存儲(chǔ)引擎使用存在于內(nèi)存中的內(nèi)容來(lái)創(chuàng)建表，每個(gè)表實(shí)際對(duì)應(yīng)一個(gè)磁盤(pán)文件，格式為.frm;這類表因?yàn)閿?shù)據(jù)在內(nèi)存中，且默認(rèn)使用HASH索引，所以訪問(wèn)速度非?？?但一旦服務(wù)關(guān)閉，表中的數(shù)據(jù)會(huì)丟失。 每個(gè)MEMORY表可以放置數(shù)據(jù)量的大小受max_heap_table_size系統(tǒng)變量的約束，初始值為16MB，可按需求增大;此外，在定義MEMORY表時(shí)可通過(guò)MAX_ROWS子句定義表的最大行數(shù)。 該存儲(chǔ)引擎主要用于那些內(nèi)容穩(wěn)定的表，或者作為統(tǒng)計(jì)操作的中間表;對(duì)該類表需謹(jǐn)慎，因?yàn)閿?shù)據(jù)并沒(méi)有實(shí)際寫(xiě)入磁盤(pán)，一旦重啟，則會(huì)丟失。 MERGE 該引擎是一組MyISAM表的組合，這些表必須結(jié)構(gòu)相同，MERGE表本身沒(méi)有數(shù)據(jù);對(duì)MERGE表查詢、更新、刪除操作實(shí)際上是對(duì)內(nèi)部實(shí)際MyISAM表進(jìn)行的;對(duì)于MERGE表的插入，是通過(guò)INSERT_METHOD子句定義插入的表，可以有3個(gè)不同的值，使用FIRST/LAST值使得插入操作被相應(yīng)地作用在第一個(gè)或最后一個(gè)表上，不定義這個(gè)子句或定義為NO表示不能對(duì)這個(gè)MERGE表執(zhí)行插入操作。 DROP操作只是刪除MERGE地定義，對(duì)內(nèi)部表沒(méi)有任何影響。 MERGE表在磁盤(pán)上保存為兩個(gè)文件，文件名以表名開(kāi)始，.frm文件存儲(chǔ)表定義，.MRG文件包含組合表地信息，如:由那些表組成、插入新記錄時(shí)的依據(jù)。 MERGE表不能智能的將記錄插入對(duì)應(yīng)的表中，而分區(qū)表可以，這是兩者的區(qū)別。 MySQL: MyISAM和InnoDB的區(qū)別 InnoDB和MyISAM是在使用MySQL最常用的兩個(gè)表類型，各有優(yōu)缺點(diǎn)，視具體應(yīng)用 而定?；镜牟顒e為:MyISAM類型不支持事務(wù)處理等高級(jí)處理，而InnoDB類型支持。MyISAM類型的表強(qiáng)調(diào)的是性能，其執(zhí)行數(shù)度比InnoDB 類型更快，但是不提供事務(wù)支持，而InnoDB提供事務(wù)支持已經(jīng)外部鍵等高級(jí)數(shù)據(jù)庫(kù)功能。 移值性: MyISAM類型的二進(jìn)制數(shù)據(jù)文件可以在不同操作系統(tǒng)中遷移。也就是可以直接從Windows系統(tǒng)拷貝到linux系統(tǒng)中使用。 MyIASM是IASM表的新版本，有如下擴(kuò)展: 二進(jìn)制層次的可移植性。 NULL列索引。 對(duì)變長(zhǎng)行比ISAM表有更少的碎片。 支持大文件。 更好的索引壓縮。 更好的鍵嗎統(tǒng)計(jì)分布。 更好和更快的auto_increment處理。 支持全文搜索，不過(guò)它們是事務(wù)不安全的，而且也不支持外鍵。如果事務(wù)回滾將會(huì)造成不完全回滾，從而不具備原子性。 讀鎖和寫(xiě)鎖是互斥的，從而讀寫(xiě)操作是串行的，MyISAM表不太適合于有大量更新操作和查詢操作應(yīng)用的原因。因?yàn)?，大量的更新操作?huì)造成查詢操作很難獲得讀鎖，從而可能永遠(yuǎn)阻塞。 數(shù)據(jù)行鎖定: 不支持，只有表鎖定 以下是一些細(xì)節(jié)和具體實(shí)現(xiàn)的差別: 1.InnoDB不支持FULLTEXT類型的索引。 2.InnoDB中不保存表的 具體行數(shù)，也就是說(shuō)，執(zhí)行select count(*) from table時(shí)，InnoDB要掃描一遍整個(gè)表來(lái)計(jì)算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count(*)語(yǔ)句包含 where條件時(shí)，兩種表的操作是一樣的。 3.對(duì)于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。 4.DELETE FROM table時(shí)，InnoDB不會(huì)重新建立表，而是一行一行的刪除。 5.LOAD TABLE FROM MASTER操作對(duì)InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對(duì)于使用的額外的InnoDB特性(例如外鍵)的表不適用。 如果你的數(shù)據(jù)執(zhí)行大量的INSERT或UPDATE，出于性能方面的考慮，同樣應(yīng)該使用InnoDB表。 對(duì)于支持事務(wù)的InnoDB類型的表來(lái)說(shuō)，影響速度的主要原因是AUTOCOMMIT默認(rèn)設(shè)置是打開(kāi)的，而且程序沒(méi)有顯式調(diào)用BEGIN 開(kāi)始事務(wù)，導(dǎo)致每插入一條都自動(dòng)提交，嚴(yán)重影響了速度。可以在執(zhí)行sql前調(diào)用begin，多條sql形成一個(gè)事物(即使autocommit打開(kāi)也可以)，將大大提高性能。 查看autocommit:select @@autocommit; 設(shè)置autocommit:set autocommit=0; MyISAM和InnoDB存儲(chǔ)引擎性能差別并不是很大，針對(duì)InnoDB來(lái)說(shuō)，影響性能的主要是 innodb_flush_log_at_trx_commit 這個(gè)選項(xiàng)，如果設(shè)置為1的話，那么每次插入數(shù)據(jù)的時(shí)候都會(huì)自動(dòng)提交，導(dǎo)致性能急劇下降，應(yīng)該是跟刷新日志有關(guān)系，設(shè)置為0效率能夠看到明顯提升，當(dāng)然，同樣你可以SQL中提交“SET AUTOCOMMIT = 0”來(lái)設(shè)置達(dá)到好的性能。 另外，InnoDB表的行鎖也不是絕對(duì)的，如果在執(zhí)行一個(gè)SQL語(yǔ)句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表，例如update table set num=1 where name like “%aaa%” 要想在創(chuàng)建表時(shí)指定存儲(chǔ)引擎，可使用ENGINE參數(shù): CREATE TABLE engineTest( id INT ) ENGINE = MyISAM; 要想更改已有表的存儲(chǔ)引擎，可使用ALTER TABLE語(yǔ)句: ALTER TABLE engineTest ENGINE = ARCHIVE; (1) 存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)服務(wù)器端的一段程序. mysql的存儲(chǔ)過(guò)程,只有版本是mysql5.0或以上的才有此特性. (2)什么時(shí)候需要用存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程通常是一些經(jīng)常要執(zhí)行的任務(wù)，這些任務(wù)往往是針對(duì)大量的記錄而進(jìn)行的。在服務(wù)器上執(zhí)行存儲(chǔ)過(guò)程，可以 改善應(yīng)用程序的性能。這是因?yàn)? 2.1. 服務(wù)器往往具有強(qiáng)大的計(jì)算能力和速度。 2.2 避免把大量的數(shù)據(jù)下載到客戶端，減少網(wǎng)絡(luò)上的傳輸量。 2.3 存儲(chǔ)過(guò)程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯，而一般SQL語(yǔ)句每執(zhí)行一次就編譯一次,所以使用存儲(chǔ)過(guò)程可提高數(shù)據(jù)庫(kù)執(zhí)行速度。 2.4 存儲(chǔ)過(guò)程可以重復(fù)使用,可減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量 2.5 安全性高,可設(shè)定只有某此用戶才具有對(duì)指定存儲(chǔ)過(guò)程的使用權(quán) 我們大家都知道MySQL 存儲(chǔ)過(guò)程是從 MySQL 5.0 開(kāi)始逐漸增加新的功能。存儲(chǔ)過(guò)程在實(shí)際應(yīng)用中也是優(yōu)點(diǎn)大于缺點(diǎn)。不過(guò)最主要的還是執(zhí)行效率和SQL 代碼封裝。特別是 SQL 代碼封裝功能，如果沒(méi)有存儲(chǔ)過(guò)程,在外部程序訪問(wèn)數(shù)據(jù)庫(kù)時(shí)(例如 PHP)，要組織很多 SQL 語(yǔ)句。特別是業(yè)務(wù)邏輯復(fù)雜的時(shí)候，一大堆的 SQL 和條件夾雜在 PHP 代碼中，讓人不寒而栗。現(xiàn)在有了 MySQL 存儲(chǔ)過(guò)程，業(yè)務(wù)邏輯可以封裝存儲(chǔ)過(guò)程中，這樣不僅容易維護(hù)，而且執(zhí)行效率也高。 一、MySQL 創(chuàng)建存儲(chǔ)過(guò)程 “pr_add” 是個(gè)簡(jiǎn)單的 MySQL 存儲(chǔ)過(guò)程，這個(gè)MySQL 存儲(chǔ)過(guò)程有兩個(gè) int 類型的輸入?yún)?shù) “a”、“b”，返回這兩個(gè)參數(shù)的和。 1. drop procedure if exists pr_add; 計(jì)算兩個(gè)數(shù)之和 1. create procedure pr_add 2. ( 3. a int, 4. b int 5. ) 6. begin 7. declare c int; 8. if a is null then 9. set a = 0; 10. end if; 11. if b is null then 12. set b = 0; 13. end if; 14. set c = a + b; 15. select c as sum; 16. /* 17. return c; 不能在 MySQL 存儲(chǔ)過(guò)程中使用。return 只能出現(xiàn)在函數(shù)中。 1. */ 2. end; 二、調(diào)用 MySQL 存儲(chǔ)過(guò)程 1. call pr_add(10, 20); 執(zhí)行 MySQL 存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程參數(shù)為 MySQL 用戶變量。 1. set @a = 10; 2. set @b = 20; 3. call pr_add(@a, @b); 三、MySQL 存儲(chǔ)過(guò)程特點(diǎn) 創(chuàng)建 MySQL 存儲(chǔ)過(guò)程的簡(jiǎn)單語(yǔ)法為: 1. create procedure 存儲(chǔ)過(guò)程名字() 2. ( 3. [in|out|inout] 參數(shù) datatype 4. ) 5. begin 6. MySQL 語(yǔ)句; 7. end; MySQL 存儲(chǔ)過(guò)程參數(shù)如果不顯式指定“in”、“out”、“inout”，則默認(rèn)為“in”。 習(xí)慣上，對(duì)于是“in” 的參數(shù)，我們都不會(huì)顯式指定。 1. MySQL 存儲(chǔ)過(guò)程名字后面的“()”是必須的，即使沒(méi)有一個(gè)參數(shù)，也需要“()” 2. MySQL 存儲(chǔ)過(guò)程參數(shù)，不能在參數(shù)名稱前加“@”，如:“@a int”。下面的創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)法在 MySQL 中是錯(cuò)誤的(在 SQL Server 中是正確的)。 MySQL 存儲(chǔ)過(guò)程中的變量，不需要在變量名字前加“@”，雖然 MySQL 客戶端用戶變量要加個(gè)“@”。 1. create procedure pr_add 2. ( 3. @a int, -- 錯(cuò)誤 4. b int -- 正確 5. ) 3. MySQL 存儲(chǔ)過(guò)程的參數(shù)不能指定默認(rèn)值。 4. MySQL 存儲(chǔ)過(guò)程不需要在 procedure body 前面加 “as”。而 SQL Server 存儲(chǔ)過(guò)程必須加 “as” 關(guān)鍵字。 1. create procedure pr_add 2. ( 3. a int, 4. b int 5. ) 6. as -- 錯(cuò)誤，MySQL 不需要 “as” 7. begin 8. mysql statement ...; 9. end; 5. 如果 MySQL 存儲(chǔ)過(guò)程中包含多條 MySQL 語(yǔ)句，則需要 begin end 關(guān)鍵字。 1. create procedure pr_add 2. ( 3. a int, 4. b int 5. ) 6. begin 7. mysql statement 1 ...; 8. mysql statement 2 ...; 9. end; 6. MySQL 存儲(chǔ)過(guò)程中的每條語(yǔ)句的末尾，都要加上分號(hào) “;” 1. ... 2. declare c int; 3. if a is null then 4. set a = 0; 5. end if; 6. ... 7. end; 7. MySQL 存儲(chǔ)過(guò)程中的注釋。 1. /* 2. 這是個(gè) 3. 多行 MySQL 注釋。 4. */ 5. declare c int; -- 這是單行 MySQL 注釋 (注意 -- 后至少要有一個(gè)空格) 6. if a is null then # 這也是個(gè)單行 MySQL 注釋 7. set a = 0; 8. end if; 9. ... 10. end; 8. 不能在 MySQL 存儲(chǔ)過(guò)程中使用 “return” 關(guān)鍵字。 1. set c = a + b; 2. select c as sum; 3. /* 4. return c; -- 不能在 MySQL 存儲(chǔ)過(guò)程中使用。return 只能出現(xiàn)在函數(shù)中。 5. */ 6. end; 9. 調(diào)用 MySQL 存儲(chǔ)過(guò)程時(shí)候，需要在過(guò)程名字后面加“()”，即使沒(méi)有一個(gè)參數(shù)，也需要“()” 1. call pr_no_param(); 10. 因?yàn)?MySQL 存儲(chǔ)過(guò)程參數(shù)沒(méi)有默認(rèn)值，所以在調(diào)用 MySQL 存儲(chǔ)過(guò)程時(shí)候，不能省略參數(shù)?？梢杂?null 來(lái)替代。 備份 備份MySQL數(shù)據(jù)庫(kù)的命令 mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 備份MySQL數(shù)據(jù)庫(kù)為帶刪除表的格式 備份MySQL數(shù)據(jù)庫(kù)為帶刪除表的格式，能夠讓該備份覆蓋已有數(shù)據(jù)庫(kù)而不需要手動(dòng)刪除原有數(shù)據(jù)庫(kù)。 mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql 直接將MySQL數(shù)據(jù)庫(kù)壓縮備份 mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz 備份MySQL數(shù)據(jù)庫(kù)某個(gè)(些)表 mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql 同時(shí)備份多個(gè)MySQL數(shù)據(jù)庫(kù) mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql 僅僅備份數(shù)據(jù)庫(kù)結(jié)構(gòu) mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql 備份服務(wù)器上所有數(shù)據(jù)庫(kù) mysqldump –all-databases > allbackupfile.sql 還原MySQL數(shù)據(jù)庫(kù)的命令 mysql -hhostname -uusername -ppassword databasename < backupfile.sql 還原壓縮的MySQL數(shù)據(jù)庫(kù) gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename 將數(shù)據(jù)庫(kù)轉(zhuǎn)移到新服務(wù)器 mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename Binlog 如果MySQL服務(wù)器啟用了二進(jìn)制日志，你可以使用mysqlbinlog工具來(lái)恢復(fù)從指定的時(shí)間點(diǎn)開(kāi)始 (例如，從你最后一次備份)直到現(xiàn)在或另一個(gè)指定的時(shí)間點(diǎn)的數(shù)據(jù)。關(guān)于啟用二進(jìn)制日志的信息，參見(jiàn)5.11.3節(jié)，“二進(jìn)制日志”。對(duì)于 mysqlbinlog的詳細(xì)信息，參見(jiàn)mysql 手冊(cè)8.6節(jié)，“mysqlbinlog:用于處理二進(jìn)制日志文件的實(shí)用工具”。 要想從二進(jìn)制日志恢復(fù)數(shù)據(jù)，你需要知道當(dāng)前二進(jìn)制日志文件的路徑和文件名。一般可以從選項(xiàng)文件(即my.cnf or my.ini，取決于你的系統(tǒng))中找到路徑。如果未包含在選項(xiàng)文件中，當(dāng)服務(wù)器啟動(dòng)時(shí)，可以在命令行中以選項(xiàng)的形式給出。啟用二進(jìn)制日志的選項(xiàng)為-- log-bin。要想確定當(dāng)前的二進(jìn)制日志文件的文件名，輸入下面的MySQL語(yǔ)句: SHOW BINLOG EVENTS G 你還可以從命令行輸入下面的內(nèi)容: mysql --user=root -pmy_pwd -e 'SHOW BINLOG EVENTS G' 將密碼my_pwd替換為服務(wù)器的root密碼。 1. 指定恢復(fù)時(shí)間 對(duì)于MySQL 4.1.4，可以在mysqlbinlog語(yǔ)句中通過(guò)--start-date和--stop-date選項(xiàng)指定DATETIME格式的起止時(shí)間。舉例說(shuō) 明，假設(shè)在今天上午10:00(今天是2005年4月20 日)，執(zhí)行SQL語(yǔ)句來(lái)刪除一個(gè)大表。要想恢復(fù)表和數(shù)據(jù)，你可以恢復(fù)前晚上的備份，并輸入: mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 | mysql -u root -pmypwd 該命令將恢復(fù)截止到在--stop-date選項(xiàng)中以DATETIME格式給出的日期和時(shí)間的所有數(shù)據(jù)。如果你沒(méi)有檢測(cè)到幾個(gè)小時(shí)后輸入的錯(cuò)誤的SQL語(yǔ)句，可能你想要恢復(fù)后面發(fā)生的活動(dòng)。根據(jù)這些，你可以用起使日期和時(shí)間再次運(yùn)行mysqlbinlog: mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -u root -pmypwd 在該行中，從上午10:01登錄的SQL語(yǔ)句將運(yùn)行。組合執(zhí)行前夜的轉(zhuǎn)儲(chǔ)文件和mysqlbinlog的兩行可以將所有數(shù)據(jù)恢復(fù)到上午10:00前一秒鐘。你應(yīng)檢查日志以確保時(shí)間確切。下一節(jié)介紹如何實(shí)現(xiàn)。 2. 指定恢復(fù)位置 也可以不指定日期和時(shí)間，而使用mysqlbinlog的選項(xiàng)--start-position和--stop-position來(lái)指定 日志位置。它們的作用與起止日選項(xiàng)相同，不同的是給出了從日志起的位置號(hào)。使用日志位置是更準(zhǔn)確的恢復(fù)方法，特別是當(dāng)由于破壞性SQL語(yǔ)句同時(shí)發(fā)生許多事 務(wù)的時(shí)候。要想確定位置號(hào)，可以運(yùn)行mysqlbinlog尋找執(zhí)行了不期望的事務(wù)的時(shí)間范圍，但應(yīng)將結(jié)果重新指向文本文件以便進(jìn)行檢查。操作方法為: mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql 該命令將在/tmp目錄創(chuàng)建小的文本文件，將顯示執(zhí)行了錯(cuò)誤的SQL語(yǔ)句時(shí)的 SQL語(yǔ)句。你可以用文本編輯器打開(kāi)該文件，尋找你不要想重復(fù)的語(yǔ)句。如果二進(jìn)制日志中的位置號(hào)用于停止和繼續(xù)恢復(fù)操作，應(yīng)進(jìn)行注釋。用log_pos加 一個(gè)數(shù)字來(lái)標(biāo)記位置。使用位置號(hào)恢復(fù)了以前的備份文件后，你應(yīng)從命令行輸入下面內(nèi)容: mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 | mysql -u root -pmypwd mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 | mysql -u root -pmypwd 上面的第1行將恢復(fù)到停止位置為止的所有事務(wù)。下一行將恢復(fù)從給定的起始位置直到二進(jìn)制日志結(jié)束的所有事務(wù)。因?yàn)閙ysqlbinlog的輸出包括每個(gè)SQL語(yǔ)句記錄之前的SET TIMESTAMP語(yǔ)句，恢復(fù)的數(shù)據(jù)和相關(guān)MySQL日志將反應(yīng)事務(wù)執(zhí)行的原時(shí)間。 Binlog是mysql以二進(jìn)制形式打印的日志，它默認(rèn)不加密，不壓縮。每個(gè)正常的binlog文件頭部，有4個(gè)字節(jié)的標(biāo)記，值為0xfe 0x62 0x69 0x6e。LOG_EVENT是binlog里的單位，即正常情況下binlog按照逐LOG_EVENT的形式增長(zhǎng)。除去頭部的標(biāo)記，binlog就是一個(gè)LOG_EVENT的序列。每個(gè)LOG_EVENT都獨(dú)立單元，沒(méi)有互相引用的關(guān)系，它也有自己的二進(jìn)制頭部，主要是記錄了時(shí)間戳、類型標(biāo)記等描述信息。 Mysql把磁盤(pán)操作的實(shí)現(xiàn)封裝在IO_CACHE結(jié)構(gòu)里，這也方便了我們對(duì)binlog的研究和描述，后文如果沒(méi)有特別說(shuō)明，讀寫(xiě)binlog與讀寫(xiě)IO_CACHE的含義相同。為了解mysql寫(xiě)入binlog的過(guò)程，可以找一個(gè)sql語(yǔ)句的處理過(guò)程進(jìn)行跟蹤。以u(píng)pdate為例，在最簡(jiǎn)單的情況下，mysql會(huì)先調(diào)用為存儲(chǔ)引擎開(kāi)放的接口ha_update_row，然而執(zhí)行binlog_query對(duì)binlog進(jìn)行寫(xiě)操作。 這樣處理的原因是，在主從備份的場(chǎng)景下，如果主庫(kù)先寫(xiě)入binlog成功、在執(zhí)行update的過(guò)程中crash，從庫(kù)有可能執(zhí)行update成功，此時(shí)主庫(kù)重啟之后，與從庫(kù)的數(shù)據(jù)不一致。如果update操作發(fā)生在事務(wù)性的表上，在寫(xiě)入binlog之后會(huì)執(zhí)行開(kāi)放接口ha_autocommit_or_rollback，由存儲(chǔ)引擎判斷操作結(jié)果。 在主從備份的場(chǎng)景下，主庫(kù)相當(dāng)于server，從庫(kù)相當(dāng)于client，雙方采用tcp短連接。從庫(kù)發(fā)出讀取日志的請(qǐng)求，主庫(kù)接收請(qǐng)求、讀取本地binlog、然后發(fā)送給從庫(kù)。從庫(kù)接收日志，進(jìn)行簡(jiǎn)單校驗(yàn)后寫(xiě)本地日志，稱為relay log。此處從庫(kù)的流程專門(mén)由一個(gè)線程負(fù)責(zé)，稱為同步io線程。從庫(kù)還有一個(gè)線程，稱為同步sql線程。它的行為是，定期讀取relay log，解析并執(zhí)行同步過(guò)來(lái)的sql語(yǔ)句。 下面回答幾個(gè)問(wèn)題: 1.binlog的格式? 二進(jìn)制順序存儲(chǔ)，不加密，不壓縮。 2.binlog使用WAL嗎? No。 3.主庫(kù)發(fā)送binlog，是使用內(nèi)存里的copy嗎? 無(wú)法確定，很有可能是先從磁盤(pán)上讀一份，然后發(fā)送。 4.relaylog使用WAL嗎? Yes。從庫(kù)接收到日志后，會(huì)先寫(xiě)relay log。 5.binlog和relaylog的SQL是否一致? 在網(wǎng)絡(luò)傳輸正確性可靠的前提下，yes。 提一個(gè)問(wèn)題:既然binlog不使用WAL，那么在主從場(chǎng)景下，mysql異常之后，主庫(kù)和從庫(kù)是否會(huì)不一致呢? 之前有個(gè)問(wèn)題一直沒(méi)弄明白:既然mysql是先做數(shù)據(jù)操作、再寫(xiě)binlog，如果寫(xiě)binlog的時(shí)候失敗，mysql又crash，數(shù)據(jù)怎么辦? 答案是由存儲(chǔ)引擎決定數(shù)據(jù)?？梢园裮ysql和它的存儲(chǔ)引擎分開(kāi)看，因?yàn)閙ysql只是一個(gè)框架，而不是一個(gè)實(shí)現(xiàn)。binlog是mysql自己的日志，而事務(wù)是由存儲(chǔ)引擎本身保證的。 以u(píng)pdate為例，mysql做的事情簡(jiǎn)單分為: 1. 修改數(shù)據(jù)update。 2. 寫(xiě)binlog。 3. 如果當(dāng)前處理的表是一個(gè)事務(wù)性的表，則commit或rollback。 注意此處的update和commit/rollback都由存儲(chǔ)引擎實(shí)現(xiàn)，mysql只是站在邏輯的高度上理解這些操作。 對(duì)于事務(wù)型的引擎innodb，它本身有日志保證數(shù)據(jù)的一致性。在innodb的實(shí)現(xiàn)中，update修改數(shù)據(jù)之前，會(huì)新建一個(gè)事務(wù)，并建立一個(gè)回滾點(diǎn)。而在innodb提供的commit/rollback接口會(huì)提交/回滾事務(wù)。 因此對(duì)innodb而言，每條SQL語(yǔ)句的事務(wù)，其實(shí)包含了binlog的寫(xiě)操作。然而即使是這樣，innodb仍然無(wú)法保證binlog和數(shù)據(jù)的一致性，因?yàn)閕nnodb在寫(xiě)commit成功后crash，回滾操作不會(huì)回滾binlog。按照手冊(cè)上的說(shuō)法，把--innodb-support-xa設(shè)置為1，同時(shí)保證sync_binlog=1，才能保證innodb的binlog和數(shù)據(jù)一致。 對(duì)于非事務(wù)型的引擎myisam，沒(méi)有commit/rollback的機(jī)會(huì)，因此在異常情況下，數(shù)據(jù)會(huì)和binlog不一致。 一、簡(jiǎn)介 binlog又叫二進(jìn)制日志文件，它會(huì)將mysql中所有修改數(shù)據(jù)庫(kù)數(shù)據(jù)的Query以二進(jìn)制的形式記錄到日志文件中,如:create,insert,drop,update等;(對(duì)于select操作則不會(huì)被記錄到binlog里，因?yàn)樗](méi)有修改數(shù)據(jù)庫(kù)的數(shù)據(jù))。binlog一般存儲(chǔ)在數(shù)據(jù)目錄下，并且命名為:mysql-bin.***(這個(gè)可以在配置文件中修改my.cnf:log-bin=mysql-bin,就是文件名的前綴;mysqld在每個(gè) binlog 名后面添加一個(gè)數(shù)字?jǐn)U展名。每次啟動(dòng)服務(wù)器或刷新日志時(shí)增加文件的大小大于max_binlog_size，一個(gè)事務(wù)不會(huì)被拆分開(kāi))。 binlog主要是用于保證數(shù)據(jù)完整的，如主從備份，通過(guò)從binlog文件中讀取操作來(lái)在salve機(jī)上進(jìn)行同樣的操作，保證主從備份，當(dāng)然不可能每次都從開(kāi)始的地方redo，所以每條記錄都有一個(gè)時(shí)間截TIMESTAMP。 二、簡(jiǎn)單的使用binlog show binary logs; #顯示binlog文件 purge binary logsto 'mysql-bin.**' #刪除到**文件 bin/mysqlbinlog binlogfile #解析binlog文件 利用binlog恢復(fù)數(shù)據(jù): bin/mysqlbinlog --start-datetime='2011-7-7 18:0:0'--stop-datetime='2011-7-7 20:07:13' data/mysql-bin.000008 |mysql -u root 三、類型 binlog的格式有三種，這也反應(yīng)了mysql的復(fù)制技術(shù):基于SQL語(yǔ)句的復(fù)制(statement-based replication, SBR)，基于行的復(fù)制(row-based replication, RBR)，混合模式復(fù)制(mixed-based replication, MBR)。相應(yīng)地，binlog的格式也有三種:STATEMENT，ROW，MIXED。 mysql>showvariables like 'binlog_format' #查看binlog的格式 使用mysqlbinlog解析的binlog: MIXED(STATEMENT): # at 193(開(kāi)始位置) #110708 10:03:06(時(shí)間截) server id(產(chǎn)生該事件的服務(wù)id) 1 end_log_pos(日志的結(jié)束位置) 280 Query(事件類型) thread_id=10 exec_time=0 error_code=0 SETTIMESTAMP=1310090586/*!*/; insert into tvalues(17) /*!*/; ROW模式: BEGIN /*!*/; # at 174 # at 214 #110708 10:49:22server id 1 end_log_pos 214 Table_map: `test`.`t` mapped to number 14 #110708 10:49:22server id 1 end_log_pos 248 Write_rows: table id 14 flags: STMT_END_F BINLOG ' MnAWThMBAAAAKAAAANYAAAAAAA4AAAAAAAEABHRlc3QAAXQAAQMAAQ== MnAWThcBAAAAIgAAAPgAAAAAAA4AAAAAAAEAAf/+MgAAAA== '/*!*/; # at 248 #110708 10:49:22server id 1 end_log_pos 317 Query thread_id=1 exec_time=0 error_code=0 SETTIMESTAMP=1310093362/*!*/; COMMIT STATEMENT是基于sql語(yǔ)句級(jí)別的binlog,每一條修改數(shù)據(jù)的sql都會(huì)被保存到binlog里;ROW是基于行級(jí)別的,他會(huì)記錄每一行記錄的變化,就是將每一行的修改都記錄到binlog里面,記錄的非常詳細(xì)，但sql語(yǔ)句并沒(méi)有在binlog里,在replication里面也不會(huì)因?yàn)榇鎯?chǔ)過(guò)程觸發(fā)器等造成Master-Slave數(shù)據(jù)不一致的問(wèn)題,但是有個(gè)致命的缺點(diǎn)日志量比較大.由于要記錄每一行的數(shù)據(jù)變化,當(dāng)執(zhí)行update語(yǔ)句后面不加where條件的時(shí)候或alter table的時(shí)候,產(chǎn)生的日志量是相當(dāng)?shù)拇?。MIXED:在默認(rèn)情況下是statement,但是在某些情況下會(huì)切換到row狀態(tài)，如當(dāng)一個(gè)DML更新一個(gè)ndb引擎表，或者是與時(shí)間用戶相關(guān)的函數(shù)等。在主從的情況下，在主機(jī)上如果是STATEMENT模式，那么binlog就是直接寫(xiě)now()，然而如果這樣的話，那么從機(jī)進(jìn)行操作的時(shí)間，也執(zhí)行now()，但明顯這兩個(gè)時(shí)間不會(huì)是一樣的，所以對(duì)于這種情況就必須把STATEMENT模式更改為ROW模式，因?yàn)镽OW模式會(huì)直接寫(xiě)值而不是寫(xiě)語(yǔ)句(該案例是錯(cuò)誤的，即使是STATEMENT模式也可以使用now()函數(shù)，具體原因以后再分析)。同樣ROW模式還可以減少?gòu)臋C(jī)的相關(guān)計(jì)算，如在主機(jī)中存在統(tǒng)計(jì)寫(xiě)入等操作時(shí)，從機(jī)就可以免掉該計(jì)算把值直接寫(xiě)入從機(jī)。 四、binlog記錄 每個(gè)binlog的開(kāi)始都是由4個(gè)字節(jié):fe 62 69 6e，組成的魔數(shù)(后面三個(gè)字節(jié)就是bin)。 然后接下來(lái)的就是一條記錄的內(nèi)容它包括:Common-Header，這部分不同版本的大小不一樣，4.0以上的都是19個(gè)字節(jié)。在這個(gè)之后就是BODY。 Common-Header格式:(單位:字節(jié)) Timestamp(4) Type(1) Server_id(4) Total_size(4) End_log_pos(4) Flag(2) Timestamp:從1970開(kāi)始 Type:此log event type如FORMAT_DESCRIPTION_EVENT、QUERY、LOAD_EVENT等，其中每個(gè)binlog的第一條記錄的類型都是FORMAT_DESCRIPTION_EVENT，它記錄了該binlog的相關(guān)信息，如版本，這些信息對(duì)于后序分析binlog記錄是有用的，所以對(duì)于任務(wù)要讀取binlog的內(nèi)容的工具都必須先讀取第一條記錄。QUERY包括我們經(jīng)常操作的如:create,drop,update,insert等。 Server_id:創(chuàng)建這個(gè)事件的server id。防止循環(huán)主從導(dǎo)致的主機(jī)被從寫(xiě)。The master's server id (is preserved in therelay log; used to prevent from infinite loops in circular replication). Total_size:該記錄的大小，包括common_header及body。 End_log_pos:此下一條記錄的開(kāi)始位置。也是此條記錄結(jié)束位置的上一個(gè)字節(jié)。 Flag:標(biāo)志位。 QUERY類型的記錄: QUERY類型的記錄除了開(kāi)始的common-header之外，在body的開(kāi)頭是一個(gè)Post-header，然后之后才是真正的body內(nèi)容。 Query Post-Header:(單位字節(jié)) Thread_id(4) Exec_time(4) Db_len(1) Error_code(2) Status_var_len(2) Thread_id:is used to distinguish temporary tables that belong to differentclients. Exec_time:The time from whenthe query started to when it was logged in the binlog, in seconds.QUERY到達(dá)到這個(gè)binlog事件生成的時(shí)間間隔。 Db_len:當(dāng)前數(shù)據(jù)庫(kù)的名稱長(zhǎng)度。 Error_code:執(zhí)行出錯(cuò)的錯(cuò)誤號(hào)。 --binlog 以一種更有效的格式，并且是事務(wù)安全的方式包含更新日志中可用的所有信息。 --binlog 包含了所有更新了數(shù)據(jù)或者已經(jīng)潛在更新了數(shù)據(jù)(例如，沒(méi)有匹配任何行的一個(gè)DELETE)的所有語(yǔ)句。語(yǔ)句以“事件”的形式保存，它描述數(shù)據(jù)更改。 --binlog 還包含關(guān)于每個(gè)更新數(shù)據(jù)庫(kù)的語(yǔ)句的執(zhí)行時(shí)間信息。它不包含沒(méi)有修改任何數(shù)據(jù)的語(yǔ)句。如果你想要記錄所有語(yǔ)句(例如，為了識(shí)別有問(wèn)題的查詢)，你應(yīng)使用一般查詢?nèi)罩尽?nbsp; --binlog 的主要目的是在恢復(fù)使能夠最大可能地更新數(shù)據(jù)庫(kù)，因?yàn)?binlog 包含備份后進(jìn)行的所有更新。 --binlog 還用于在主復(fù)制服務(wù)器上記錄所有將發(fā)送給從服務(wù)器的語(yǔ)句。 --運(yùn)行服務(wù)器時(shí)若啟用 binlog 則性能大約慢1%。但是， binlog 的好處，即用于恢復(fù)并允許設(shè)置復(fù)制超過(guò)了這個(gè)小小的性能損失。 --當(dāng)--log-bin[=file_name]選項(xiàng)啟動(dòng)時(shí)，mysqld寫(xiě)入包含所有更新數(shù)據(jù)的SQL命令的日志文件。如果未給出file_name值， 默認(rèn)名為-bin后面所跟的主機(jī)名。如果給出了文件名，但沒(méi)有包含路徑，則文件被寫(xiě)入數(shù)據(jù)目錄。建議指定一個(gè)文件名。如果你在日志名中提供了擴(kuò)展名(例如，--log-bin=file_name.extension)，則擴(kuò)展名被悄悄除掉并忽略。 --mysqld在每個(gè) binlog 名后面添加一個(gè)數(shù)字?jǐn)U展名。每次你啟動(dòng)服務(wù)器或刷新日志時(shí)該數(shù)字則增加。如果當(dāng)前的日志大小達(dá)到max_binlog_size，還會(huì)自動(dòng)創(chuàng)建新的 binlog 。如果你正使用大的事務(wù)， binlog 還會(huì)超過(guò)max_binlog_size:事務(wù)全寫(xiě)入一個(gè) binlog 中，絕對(duì)不要寫(xiě)入不同的 binlog 中。 --為了能夠知道還使用了哪個(gè)不同的 binlog 文件，mysqld還創(chuàng)建一個(gè) binlog 索引文件，包含所有使用的 binlog 文件的文件名。默認(rèn)情況下與 binlog 文件的文件名相同，擴(kuò)展名為'.index'。你可以用--log-bin-index[=file_name]選項(xiàng)更改 binlog 索引文件的文件名。當(dāng)mysqld在運(yùn)行時(shí)，不應(yīng)手動(dòng)編輯該文件;如果這樣做將會(huì)使mysqld變得混亂。 --binlog 格式有一些已知限制，會(huì)影響從備份恢復(fù)。 --默認(rèn)情況下，并不是每次寫(xiě)入時(shí)都將 binlog 與硬盤(pán)同步。因此如果操作系統(tǒng)或機(jī)器(不僅僅是MySQL服務(wù)器)崩潰，有可能 binlog 中最后的語(yǔ)句丟失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使 binlog 在每N次 binlog 寫(xiě)入后與硬盤(pán)同步。 如何管理 MySQL 的 binlog 1、在 my.ini 中增加下述參數(shù)，指定保存更新到 binlog 的數(shù)據(jù)庫(kù):db_name，未在此指定的數(shù)據(jù)庫(kù)將不記錄 binlog --binlog-do-db=db_name 2、在 my.ini 中增加下述參數(shù)，指定不保存更新到 binlog 的數(shù)據(jù)庫(kù):db_name --binlog-ignore-db=db_name 3、如果 binlog 已經(jīng)產(chǎn)生，可以通過(guò) SQL 命令行清除: /* * 要清理日志，需按照以下步驟: * 1 在每個(gè)從屬服務(wù)器上，使用SHOW SLAVE STATUS來(lái)檢查它正在讀取哪個(gè)日志。 * 2 使用SHOW MASTER LOGS獲得主服務(wù)器上的一系列日志。 * 3 在所有的從屬服務(wù)器中判定最早的日志。這個(gè)是目標(biāo)日志。如果所有的從屬服務(wù)器是更新的，這是清單上的最后一個(gè)日志。 * 4 制作您將要?jiǎng)h除的所有日志的備份。(這個(gè)步驟是自選的，但是建議采用。) * 5 清理所有的日志，但是不包括目標(biāo)日志。 * */ /* * 清除 binlog * * 為了執(zhí)行RESET，您必須擁有RELOAD權(quán)限。 * 以下命令將刪除列于索引文件中的所有 binlog，把 binlog 索引文件重新設(shè)置為空，并創(chuàng)建一個(gè)新的 binlog。 * (在以前版本的MySQL中，被稱為FLUSH MASTER。) */ RESET MASTER; /* * 清除指定的 binlog * */ PURGE MASTER LOGS TO 'mysql-bin.010'; /* * 清除日期為 2006-06-06 06:06:06 以前的 binlog * * BEFORE變量的date自變量可以為'YYYY-MM-DD hh:mm:ss'格式。MASTER和BINARY是同義詞。 */ PURGE MASTER LOGS BEFORE '2006-06-06 06:06:06'; /* * 清除3天前的 binlog * */ PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
信息發(fā)布：廣州名易軟件有限公司 http://m.jetlc.com
|
名易MyOA協(xié)同綜合辦公平臺(tái)
名易MyCRM客戶關(guān)系管理平臺(tái)
名易MyHR人力資源管理平臺(tái)
名易MyIDP智能開(kāi)發(fā)平臺(tái)
名易MyIBP保險(xiǎn)業(yè)務(wù)管理平臺(tái)
名易MyHMS酒店綜合管理系統(tǒng)
名易MyPM項(xiàng)目管理平臺(tái)
名易MyTMS物流運(yùn)輸管理平臺(tái)
名易MyVMS汽車(chē)綜合管理系統(tǒng)
名易MyIMS貸款管理系統(tǒng)
名易MyEDU教育管理平臺(tái)
名易MyPCS生產(chǎn)事務(wù)協(xié)調(diào)系統(tǒng)
名易MyWIDP微信智能開(kāi)發(fā)平臺(tái)
