數據庫原理與應用教程-MySQL8.0第12章

數據庫的備份與恢復第12章

數據庫的備份與恢復避免數據丟失是數據庫管理員需要面對的最關鍵的問題之一。盡管在MySQL中采取了許多措施來保證數據庫的安全性和完整性，但故障仍不可避免，仍會影響甚至破壞數據庫，造成數據丟失。同時還存在其他一些可能造成數據丟失的因素，例如用戶的操作失誤、蓄意破壞、病毒攻擊和自然界不可抗力等。MySQL制定了一個良好的備份和恢復策略，定期將數據庫進行備份以保護數據庫，以便在事故發(fā)生后恢復數據庫。12.1數據庫備份概述對于計算機用戶來說，對一些重要文件、資料定期進行備份是一種良好的習慣。如果出現突發(fā)情況，比如系統(tǒng)崩潰、系統(tǒng)遭受病毒攻擊等，使得原先的文件遭到破壞以至于全部丟失，啟用文件備份，就可以節(jié)省大量的時間和精力。數據庫備份就是創(chuàng)建整個數據庫(或者其中一部分)的副本，其中記錄著在進行備份這一操作時數據庫中所有數據的狀態(tài)，以便在數據庫遭到破壞時能夠恢復數據庫。12.1.1數據庫備份的類型為了在數據庫遭到破壞時能及時地被恢復，對于每一個數據庫都要制定備份計劃，定期進行數據庫的備份。數據庫的備份是一個很長的過程，如何進行備份要考慮兩個方面：怎樣備份（備份策略）和什么情況下備份（備份時間）。12.1.1數據庫備份的類型1.按備份策略分類分為完全備份和增量備份。（1）完全備份完全備份將備份整個數據庫，它代表備份完成時的數據庫。完全備份易于使用，因為完全備份包含數據庫中的所有數據，所以對于可以快速備份的小數據庫而言，最佳方法就是使用完全備份。大型數據庫可以用增量備份來補充完全備份。12.1.1數據庫備份的類型（2）增量備份增量備份只備份自上一次完全備份發(fā)生改變的內容和在增量備份過程中所發(fā)生的所有活動。增量備份基于以前的完全備份，完全備份稱為“基準備份”。增量備份比完全備份更小、更快，可以簡化頻繁的備份操作，減少數據丟失的風險。每次連續(xù)的增量備份都大于前一次增量備份，這就需要更長的備份時間、還原時間和更大的空間。因此，可定期執(zhí)行新的完全備份以提供新的增量基準。12.1.1數據庫備份的類型（2）增量備份當使用增量備份時，最好遵循以下原則：在每次完全備份后，定期安排增量備份。例如，可以每五小時執(zhí)行一次增量備份，對于活動性較高的系統(tǒng)，此頻率也可以更高。在確保增量備份不會太大的情況下，定期安排新的完全備份。例如，可以每周備份一次完整數據庫。

12.1.1數據庫備份的類型2．按備份時間分類即在什么情況下進行備份，分為靜態(tài)備份和動態(tài)備份（1）靜態(tài)備份靜態(tài)備份是指系統(tǒng)中沒有正在運行的事務時進行的備份，靜態(tài)備份期間不允許進行對數據庫的任何存取、修改活動。優(yōu)點：實現簡單，得到的備份一定能夠保證數據的一致性。缺點：降低了數據庫的可用性，因為在靜態(tài)備份開始之前所有事務都必須已經結束，而新的事務必須等到備份結束才能開始運行。12.1.1數據庫備份的類型（2）動態(tài)備份動態(tài)備份是指備份期間允許對數據庫進行存取或修改的備份。動態(tài)備份提高了數據庫的可用性，備份開始之前正在運行的事務不需要結束，備份期間任何新事務也可以隨時開始運行。缺點是，得到的備份不能保證數據的一致性。所以必須在備份期間把各事務對數據庫中數據的修改活動記錄在日志文件中，這樣，通過備份和日志文件就可以把數據庫恢復到某一時刻的一致狀態(tài)。12.1.2數據庫備份操作MySQL的數據庫備份操作可以使用可視化管理工具（比如navicat）通過菜單和界面進行，也可以在CMD命令提示符窗口使用相應的命令語句進行。兩種方式都將數據庫備份成.sql腳本文件，文件中包含創(chuàng)建表、插入數據、創(chuàng)建視圖、創(chuàng)建函數、創(chuàng)建存儲過程等信息，執(zhí)行腳本文件就可以導入備份的表結構和數據以及視圖、函數、存儲過程等。12.1.2數據庫備份操作備份數據庫的命令mysqldump：它是MySQL提供的一個非常有用的數據庫備份工具，該工具的存儲位置上是C:\ProgramFiles\MySQL\MySQLServer8.0\bin文件夾。可以在備份數據庫前將此路徑添加到系統(tǒng)環(huán)境變量PATH中，也可以在CMD窗口進入此文件夾后再進行數據庫備份。12.1.2數據庫備份操作1．利用navicat備份數據庫2.使用mysqldump命令備份數據庫（1）備份單個數據庫mysqldump–uusername–hhostname–ppassworddatabase_name[table_name[…n]]>pach\filename.sql可以指定一個或多個表進行備份，多個時用空格間隔，如果不指定表則備份整個數據庫。【例12-1】備份teaching整個數據庫，語句如下：mysqldump–uroot–hlocalhost–pteaching>e:\data\teaching.sql12.1.2數據庫備份操作（2）備份多個數據庫mysqldump–uusername–hhostname–ppassword--databasesdatabase_namedatabase_name[…n]>pach\filename.sql【例12-2】備份teaching和bankcard數據庫。mysqldump–uroot–hlocalhost–p--databasesteachingbankcard>e:\data\t_b1.sql12.1.2數據庫備份操作（3）備份所有數據庫mysqldump–uusername–hhostname–ppassword--all-databases>pach\filename.sql【例12-3】備份MySQL上的所有數據庫。mysqldump–uroot–hlocalhost–p--all-databases>e:\data\all_db.sql12.1.2數據庫備份操作3.二進制日志實現增量備份MySQL沒有提供直接的增量備份方法，但可以通過

二進制日志(binarylogs)間接實現增量備份。備份過程：（1）查看二進制日志功能是否開啟showglobalvariableslike'%log_bin%';默認是開啟狀態(tài)即為“ON”。（2）開啟二進制日志功能可以使用vim編輯數據庫配置文件“etc/f”進行開啟，然后重啟MySQL服務器即可。12.1.2數據庫備份操作（3）完全備份使用mysqldump命令備份teaching數據庫。mysqldump–uroot–hlocalhost–p--single-transaction--flush-logs--source-datateaching>e:\data\teach_backup.sql在文版編輯器中打開teach_ackup.sql文件，可以看到其中的兩行信息：第二行信息是指備份后所有的更改都會保存到“DESKTOP-LAI8BRJ-bin.000051”二進制日志文件中，其擴展名000051是按日志文件的順序編號的。

12.1.2數據庫備份操作（4）更改信息在MySQL服務器上執(zhí)行以下操作：USEteaching;CREATETABLEST(IdintPRIMARYKEY,NameNCHAR(10));

12.1.2數據庫備份操作（5）增量備份以teach_backup.sql文件的完全備份為基準，使用mysqladmin命令進行增量備份。mysqladmin–uroot–hlocalhost–pflush-logs完成增量備份后會產生一個名為“DESKTOP-LAI8BRJ-bin.000052”的二進制日志文件，而DESKTOP-LAI8BRJ-bin.000051文件中保存了從完全備份到現在的所有更改（CREATETABLE），即增量備份。

12.2數據庫恢復概述恢復數據庫是一個裝載數據庫的備份，然后應用事務日志重建的過程。在數據庫備份之前，應該檢查數據庫中數據的一致性，這樣才能保證順利地恢復數據庫備份。在恢復數據庫的過程中，用戶不能進入數據庫，當數據庫被恢復后，數據庫中的所有數據都被替換掉。數據庫備份是在正常情況下進行的，而數據庫恢復是在諸如硬件故障、軟件故障或誤操作等非正常的狀態(tài)下進行的，因而其工作更加重要和復雜。

12.2.1數據庫故障分類常見數據庫故障：事務故障、系統(tǒng)故障和介質故障。1.事務故障事務故障又叫做事務內部故障，是指事務內部的程序代碼不能正常運行，發(fā)生了事務執(zhí)行時的故障。（1）可預期的事務故障事務內部可預期的故障是指程序中能夠預先估計到的錯誤，比如轉賬事務導致存款賬戶余額透支、商品出庫導致庫存小于0等等。這種錯誤可以通過程序代碼加以解決，比如余額透支問題，可以在事務代碼中加入一個if判斷，一旦透支則執(zhí)行ROLLBACK語句，回滾轉賬事務。12.2.1數據庫故障分類（2）非預期的事務故障事務內部的更多的故障是非預期的，是不能由應用程序處理的。如運算溢出、并發(fā)事務發(fā)生死鎖而被選中撤銷該事務、違反了某些完整性規(guī)則等等。這類非預期的故障才是真正的事務故障，由系統(tǒng)對該事務自動回滾。事務故障是所有故障中最輕的、也是最常見的故障，不會破壞數據庫，只會導致一個事務的執(zhí)行失敗。12.2.1數據庫故障分類2.系統(tǒng)故障系統(tǒng)故障是指由于某種原因，造成系統(tǒng)停止運轉，致使所有正在運行的事務都以非正常方式終止，要求系統(tǒng)重新啟動。比如操作系統(tǒng)、DBMS或者應用程序等軟件故障，再比如內存、突然停電等外存儲器以外的硬件故障。系統(tǒng)故障不會破壞數據庫，只會影響正在運行的事務，這時，內存中數據庫緩沖區(qū)的內容全部丟失，雖然存儲在外部存儲設備上的數據庫并未破壞，但其內容不可靠了。12.2.1數據庫故障分類系統(tǒng)故障發(fā)生后，可能會導致事務的兩種情況：（1）發(fā)生系統(tǒng)故障時，一些尚未完成的事務的結果可能已送入物理數據庫，從而造成數據庫可能處于不正確的狀態(tài)。為保證數據一致性，需要清除這些事務對數據庫的所有修改。（2）有些已完成的事務可能有一部分甚至全部留在緩存區(qū)中，尚未寫回到磁盤上的物理數據庫中，系統(tǒng)故障使得這些事務對數據庫的修改部分或全部丟失，這也會使數據庫處于不一致的狀態(tài)，因此應將這些事務已經提交的結果重新寫入數據庫。在系統(tǒng)重新啟動后，恢復子系統(tǒng)需要撤銷所有未完成的事務外，重做（REDO）所有已提交的事務。12.2.1數據庫故障分類3.介質故障介質故障指外存儲器故障，如磁盤損壞、磁頭碰撞、瞬間強磁場干擾等。這類故障會對數據庫造成破壞，使存儲在外存中的數據部分或全部丟失，并影響正在存取這部分數據的所有事務。介質故障比前兩類故障發(fā)生的可能性小，但破壞性最大，只能通過數據庫備份來解決。12.2.2數據庫恢復策略數據庫恢復策略認為所有的數據庫一定會在它們的生命周期的某一時刻需要恢復。數據庫管理員職責中很重要的部分就是將數據恢復的頻率降到最低，并在數據庫遭到破壞之前進行監(jiān)視，預計各種形式的潛在風險所能造成的破壞，并針對具體情況制定恢復計劃，在破壞發(fā)生時及時地恢復數據庫。在恢復一個失效的數據庫之前，調查失效背后的原因是很重要的，原因不同恢復策略也不一樣。12.2.2數據庫恢復策略1.事務故障的恢復事務故障的恢復稱為事務撤銷，由系統(tǒng)自動完成。（1）由后向前掃描日志文件，查找該事務的更新操作。（2）對該事務的更新操作執(zhí)行反操作，即對已經插入的新記錄進行刪除操作，對己刪除的記錄進行插入操作，對修改的數據恢復舊值，用舊值代替新值。（3）繼續(xù)向前逐個掃描該事務已做的所有更新操作，并做同樣處理，直到掃描到此事務的開始標記，事務故障恢復完畢為止。一個事務是一個工作單位，也是一個恢復單位，所以，一個事務越短，越便于對它進行撤銷操作。12.2.2數據庫恢復策略2．系統(tǒng)故障的恢復系統(tǒng)故障的恢復要完成兩方面的工作，既要撤銷所有未完成的事務，還要重做所有已提交的事務，這樣才能將數據庫真正恢復到一致的狀態(tài)。這些操作由系統(tǒng)在重新啟動時自動完成，不需要用戶干預。（1）正向掃描日志文件，查找未提交的事務，將其事務標識記入撤銷隊列；同時查找已提交的事務，將其事務標識記入重做隊列。（2）對撤銷隊列中的事務進行撤銷處理：反向掃描日志文件，對每個事務的操作執(zhí)行逆操作。（3）對重做隊列中的事務進行重做處理：正向掃描日志文件，按照日志文件中所登記的內容，重新執(zhí)行操作。12.2.2數據庫恢復策略系統(tǒng)發(fā)生故障后，由于無法確定哪些未完成的事務已更新過數據庫，哪些事務的提交結果尚未寫入數據庫，因此系統(tǒng)重啟后，就要撤銷所有未完成的事務，重做所有已經提交的事務。但搜索整個日志文件需耗費太多的時間，而且在故障發(fā)生前很多提交的事務已經將更新結果寫入了數據庫，恢復子系統(tǒng)對它們重做處理，會耗費大量時間。可設立檢查點解決這個問題。每隔一段時間（比如10分鐘）系統(tǒng)就產生一個檢查點，在每個檢查點時刻，系統(tǒng)將上一個檢查點以來所有已提交事務留在數據庫緩沖區(qū)中的內容寫到物理數據庫中，并對正在運行的事務進行記錄。12.2.2數據庫恢復策略12.2.2數據庫恢復策略5類事務T1~T5分別進行以下幾種不同的處理：（1）對于T1這類事務不需要重做，因為它們的更新已在檢查點time1時刻寫入了物理數據庫。（2）T2和T4這類事務發(fā)生故障前已經成功提交，但因為還沒到下一個檢查點，所以它們對數據庫的更新仍在內存緩沖區(qū)中，未寫入物理數據庫，需要重做。（3）T3和T5這類事務發(fā)生故障前還未結束，所以它們對數據庫的更新需要撤銷。12.2.2數據庫恢復策略3.介質故障的恢復介質故障是最嚴重的一種故障，破壞性很大，磁盤上的物理數據和日志文件可能被破壞，這需要恢復發(fā)生介質故障前最新的數據庫備份，然后利用日志文件重做該備份后所有已完成的事務。（1）首先裝入最新的數據庫備份，使數據庫恢復到最近一次備份時的可用狀態(tài)。（2）然后根據最新的數據庫備份后日志文件中的內容重做已完成的事務。首先反向掃描日志文件，找出故障發(fā)生時己提交的事務，將其記入重做隊列，然后正向掃描日志文件，對重做隊列中的各個事務進行重做處理。12.2.2數據庫恢復策略注意：靜態(tài)備份的數據庫，恢復后數據庫即處于一致性狀態(tài)。動態(tài)備份的數據庫，還須同時裝入備份時刻的日志文件，利用與恢復系統(tǒng)故障相同的方法（即相應事務的重做或撤銷），才能將數據庫恢復到一致性狀態(tài)。12.2.3數據庫恢復操作1．自動恢復自動恢復是指數據庫發(fā)生事務故障或系統(tǒng)故障時MySQL服務器都自動運行的帶有容錯功能的特性。對于事務故障，自動撤銷該事務完成的所有操作；對于系統(tǒng)故障，檢查所有自最新的檢查點以來發(fā)生的事務，標識所有已經提交的事務，把它們重新應用于數據庫，標識所有未提交的事務將它們回滾。這個過程保證了每個數據庫邏輯上的一致性。12.2.3數據庫恢復操作2．手動恢復如果數據庫發(fā)生介質故障或者病毒入侵、自然災害、人為破壞、用戶誤操作等造成數據庫破壞時，則恢復最新的數據庫備份，并根據日志文件重做備份后提交的事務。如果僅使用完全備份來恢復數據庫，則恢復最新的備份即可。12.2.3數據庫恢復操作2．手動恢復MySQL的數據庫恢復操作可以使用可視化管理工具（比如navicat）通過菜單和界面進行，也可以在CMD命令提示符窗口使用相應的命令語句進行。兩種方式都是執(zhí)行數據庫備份文件（.sql腳本文件），使數據庫恢復到數據庫備份時的狀態(tài)。如果要恢復的是單個數據庫，則此數據庫必須存在，只是已被破壞，如果數據庫已經丟失，需要重新創(chuàng)建此數據庫，否則恢復過程中會出錯，重建的數據庫可以與備份時的數據庫不同名；如果恢復的是多個數據庫，則數據庫存在與否均可以。12.2.3數據庫恢復操作（1）利用navicat恢復據庫（2）使用mysql命令恢復數據庫mysql–uusername–ppassword[database_name]<path\filename.sql【例12-4】通過多數據庫備份t_b.sql文件恢復teaching和bankcard數據庫。mysql–uroot–p<e:\data\t_b.sql12.2.3數據庫恢復操作（3）使用增量備份恢復數據庫一般是恢復最新的完全備份后再恢復最新增量備份。因為MySQL是通過二進制日志間接實現的，所以要依次恢復完全備份后的每一個代表增量備份的二進制日志文件。首先恢復完全備份teach_backup.sql，然后恢復增量備份DESKTOP-LAI8BRJ-bin.000051。mysql–uroot–pteaching<e:\data\teach_backup.sqlmysqlbinlog“c:\programdata\mysql\mysqlserver8.0\data\DESKTOP-LAI8BRJ-bin.000051”|mysql–uroot-p12.3數據的導入/導出通過導入和導出數據的操作，可以將MySQL數據庫中的表導出成sql腳本文件、txt文本文件、excel文件、html文件、xml文件等等，也可以將這些類型的文件導入到MySQL數據庫中。在數據庫的日常維護中，經常需要進行數據庫表的導入導出。可以采用可視化管理工具的導出向導、也可以采用SQL語句完成表的導入導出。12.3.1導出數據數據的導出是將MySQL數據庫中的表導出成文本文件、電子表格或其他格式的數據文件中。1.使用導出向導導出數據【例12-5】將teaching中student表導出至G:\data文件夾下形成st.xls文件。12.3.1導出數據2.使用SQL語句導出數據SELECTcolumn_listFROMtable_name[WHEREconditions]INTOOUTFILE‘filename’[<options>]其中，<options>包含的內容如下：FIELDSTERMINATEDBY‘value’FIELDS[OPTIONALLY]ENCLOSEDBY‘value’FIELDSESCAPEDBY‘value’LINESSTARTINGBY‘value’LINESTERMINATEDBY‘value’12.3.1導出數據【例12-6】將teaching數據庫中student表男生數據導出成st.txt文本文件，其中各字段之間的間隔符為“；”，包裹字段值的字符為“%”，轉義字符默認，每行開頭字符為@，每行結束字符也為@。SELECT*FROMstudentWHEREssex='男'INTOOUTFILE'C:\\ProgramData\\MySQL\\MySQLServer8.0\\