第9章

數(shù)據庫備份還原和日志管理本章要點◆

數(shù)據庫備份與還原?！?/p>

數(shù)據庫遷移?！?/p>

數(shù)據導入導出。◆

數(shù)據庫日志管理。2一、數(shù)據庫備份與還原31、數(shù)據庫備份的分類1.按備份內容分類按備份內容分類可以分為物理備份和邏輯備份。（1）物理備份：對數(shù)據庫操作系統(tǒng)的物理文件的備份，直接復制數(shù)據庫的物理文件進行備份，例如數(shù)據庫文件和事務日志文件。（2）邏輯備份：使用軟件技術對數(shù)據庫邏輯件的備份，例如數(shù)據表。邏輯備份導出的文件格式一般與原數(shù)據庫的文件格式不同，只是原數(shù)據庫中數(shù)據內容的一個映像。一、數(shù)據庫備份與還原41、數(shù)據庫備份的分類2.按備份時服務器是否在線分類按備份時服務器是否在線可以分為冷備份、溫備份、熱備份。（1）冷備份：關閉數(shù)據庫進行備份，能夠較好地保證數(shù)據庫的完整性。（2）溫備份：在數(shù)據庫運行狀態(tài)中進行操作，但僅支持讀請求，不允許寫請求。（3）熱備份：在數(shù)據庫運行狀態(tài)中進行操作，依賴于數(shù)據庫的日志文件。一、數(shù)據庫備份與還原51、數(shù)據庫備份的分類3.按備份范圍分類按備份范圍可以分為完整備份、差異備份和增量備份。（1）完整備份：包含數(shù)據庫中的全部數(shù)據文件和日志文件信息，也被稱為完全備份或全庫備份。這種備份方式需要更多的存儲空間和時間。（2）差異備份：只備份那些自上次完整備份之后被修改過的文件。這種備份方式相比完整備份節(jié)約存儲空間和時間。至少要有一次完整備份才能執(zhí)行差異備份。（3）增量備份：只針對那些自上次完整備份或者增量備份后被修改過的文件。一、數(shù)據庫備份與還原62、在SQLServer中備份與還原數(shù)據庫備份數(shù)據庫還原數(shù)據庫在SQLServer中可以利用“SQLServerManagementStudio”備份數(shù)據庫和還原數(shù)據庫。一、數(shù)據庫備份與還原72、在MySQL中備份與還原數(shù)據庫在MySQLdump是MySQL提供的一個非常有用的數(shù)據庫備份工具，它可以將數(shù)據庫備份成一個文本文件，該文件內容由SQL語句組成，包含建表、插入數(shù)據指令，重新執(zhí)行這些指令，可以重新創(chuàng)建表和插入數(shù)據。MySQLdump的語法格式如下：mysqldump-uusername-hhost-ppassworddatabasename[tablename…]>filename.sql各參數(shù)含義如下：username：用戶名稱。host：登錄用戶的主機名稱。password：登錄密碼，-p選項與密碼之間不能有空格。databasename：需要備份的數(shù)據庫名稱。tablename：需要備份的數(shù)據表，若缺省該參數(shù)，表示備份整個數(shù)據庫。filename：備份文件的名稱，如果不帶絕對路徑，默認保存在bin目錄下。（1）備份數(shù)據庫一、數(shù)據庫備份與還原82、在MySQL中備份與還原數(shù)據庫【例9-1】使用mysqldump命令將數(shù)據庫toyuniverse中的所有表備份到D盤的toyuniversebak20240903.sql文件里。mysqldump-uroot-ptoyuniverse>d:\toyuniversebak20240903.sql需在操作系統(tǒng)提示符下執(zhí)行命令：（1）備份數(shù)據庫一、數(shù)據庫備份與還原92、在MySQL中備份與還原數(shù)據庫【例9-2】使用mysqldump命令將數(shù)據庫toyuniverse中的toys表備份到D盤。mysqldump-uroot-ptoyuniversetoys>d:/toyuniverse_toys_20240903.sql【例9-3】使用mysqldump命令將數(shù)據庫toyuniverse中的toys表和orders表備份到D盤。mysqldump-uroot-ptoyuniversetoysorders>d:/toyuniverse_toys_orders_20240903.sql【例9-4】使用mysqldump命令將數(shù)據庫toyuniverse中的toys的結構備份到D盤mysqldump-uroot-p--opt--no-datatoyuniversetoys>d:/toyuniverse_toys_struct_20240903.sql此命令帶有參數(shù)--no-data，備份的文件中只包含建表指令。（1）備份數(shù)據庫一、數(shù)據庫備份與還原102、在MySQL中備份與還原數(shù)據庫例9-5】使用mysqldump命令將數(shù)據庫toyuniverse備份到D盤。mysqldump-uroot-p--databasestoyuniverse>d:/toyuniverse_all_20240903.sql此命令帶有參數(shù)--databases，備份的文件中包含創(chuàng)建數(shù)據庫指令、建表指令和數(shù)據插入指令?！纠?-6】使用mysqldump命令將數(shù)據庫toyuniverse中的數(shù)據備份到D盤mysqldump-uroot-p--opt--no-create-infotoyuniverse>d:/toyuniverse_data_20240903.sql此命令帶有參數(shù)----no-create-info，備份的文件中只包含數(shù)據插入指令。（1）備份數(shù)據庫一、數(shù)據庫備份與還原112、在MySQL中備份與還原數(shù)據庫在未登錄MySQL服務器的情況下，可以使用mysql命令還原數(shù)據庫，格式如下：mysql-uusername-p[databasename]<filename.sql其中，databasename為還原的數(shù)據庫名稱，如果filename.sql是包含創(chuàng)建數(shù)據庫語句的文件，在執(zhí)行的時候不需要指定數(shù)據庫?！纠?-7】使用mysql命令和備份文件toyuniversebak20240903.sql還原toyuniverse數(shù)據庫。mysql-uroot-ptoyunivers<d:/toyuniversebak20240903.sql在已登錄MySQL服務器的情況下，可以使用source命令執(zhí)行備份文件中的SQL命令來還原數(shù)據庫，格式如下：source備份文件名【例9-8】用備份文件toyuniversebak20240903.sql還原數(shù)據庫。sourced:\toyuniversebak20240903.sql（2）還原數(shù)據庫二、數(shù)據庫遷移12數(shù)據庫遷移是指將數(shù)據庫從一個服務器移動到另一個服務器上。通常在服務器硬件升級、數(shù)據庫版本更新后需要進行數(shù)據庫遷移。在SQLServer中可以使用“分離”和“附加”的方法來遷移數(shù)據庫。分離數(shù)據庫是從服務器中移去邏輯數(shù)據庫，數(shù)據庫將不再受DBMS的管理，但不會將操作系統(tǒng)中的數(shù)據庫文件和日志文件刪除。附加數(shù)據庫可以很方便地在SQLServer服務器之間利用分離后的數(shù)據庫文件和日志文件組織成新的數(shù)據庫，并保持數(shù)據庫分離時的狀態(tài)。在MySQL中，可以使用mysqldump命令對數(shù)據庫備份，然后使用命令將其還原到目標數(shù)據庫，實現(xiàn)數(shù)據庫遷移。二、數(shù)據庫遷移13分離數(shù)據庫附加數(shù)據庫三、導入導出數(shù)據14數(shù)據庫里的數(shù)據可以導出到其它數(shù)據庫里，也可以導出到xls等文件里。反之，也可進行導入。三、導入導出數(shù)據151、SQLServer導入導出數(shù)據選擇數(shù)據源選擇目標在SQLServer中，可以使用SQLServerManagementStudio將數(shù)據表中的數(shù)據導入導出，使數(shù)據在SQLServer數(shù)據庫之間、SQLServer數(shù)據庫和其他數(shù)據庫之間、SQLServer數(shù)據庫和Excel等文件之間進行數(shù)據傳遞。三、導入導出數(shù)據16數(shù)在MySQL中，可以使用可視化工具導出數(shù)據，也可以使用“SELECT……INTOOUTFILE”命令將數(shù)據導出到本地服務器的文本文件中，其命令格式如下：SELECT……INTOOUTFILEfilename[options]其中，filename為導出的文件名稱，路徑必須是由secure_file_priv變量指定的地址，可以使用“showvariableslike'%secure%'”命令查看路徑地址。options為可選參數(shù)選項，包含F(xiàn)IELDS子句和LINES子句?！纠?-9】使用SELECT…INTOOUTFILE語句導出toyuniverse數(shù)據庫中orders表的記錄。要求字段之間用“，”隔開，字符型數(shù)據用雙引號括起來。SELECT*FROMtoyuniverse.ordersINTOOUTFILE'c:/programdata/mysql/mysqlserver8.0/uploads/orders.txt'FIELDSTERMINATEDBY'\,'OPTIONALLYENCLOSEDBY'\"'LINESTERMINATEDBY'\r\n';2、MySQL導入導出數(shù)據（1）導出數(shù)據三、導入導出數(shù)據17可以使用可視化工具導入數(shù)據，也可以使用“LOADDATA…INFILE”語句將數(shù)據導入到數(shù)據庫?！纠?-10】使用LOADDATA…INFILE語句將文件’c:/programdata/mysql/mysqlserver8.0/uploads/orders.txt’中的數(shù)據導入到tst1數(shù)據庫中的orders表。LOADDATAINFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/orders.txt'INTOTABLEtst1.ordersFIELDSTERMINATEDBY'\,'OPTIONALLYENCLOSEDBY'\"'LINESTERMINATEDBY'\r\n'2、MySQL導入導出數(shù)據（2）導入數(shù)據四、日志管理18可SQLServer和MySQL都有日志文件，日志文件中記錄著數(shù)據庫運行期間發(fā)生的變化；包括數(shù)據庫的客戶端連接狀況、SQL語句的執(zhí)行情況和錯誤信息等。對數(shù)據庫維護工作而言，這些日志文件是不可缺少的。SQLServer的日志可以使用SQLServerManagementstudio查看。具體步驟如下：（1）在“對象資源管理器”中，展開“管理”節(jié)點。（2）右鍵點擊“數(shù)據庫日志”，選擇“查看數(shù)據庫日志”1、SQLServer日志管理四、日志管理19MySQL日志根據記錄內容的不同，日志文件劃分為:錯誤日志二進制日志通用查詢日志慢查日志2、MySQL日志管理四、日志管理20My記錄MySQL服務器的啟動和停止過程中的信息、服務器在運行過程中發(fā)生的故障和異常情況的相關信息、事件調度器運行一個事件時產生的信息、啟動服務器進程時產生的信息等。錯誤日志是以文本文件的形式存儲的，可以直接使用普通文本工具打開查看。錯誤日志功能在默認情況下是開啟的，并且不能被禁止。錯誤日志默認以hostname.err為文件名，其中hostname為主機名。通過修改my.ini（Windows）或者f（Linux）文件中的log-error項可以配置錯誤日志文件路徑，修改項如下：--log-error=[path/[filename]]其中，path是日志文件目錄，filename是日志文件的文件名，配置完成后需要重啟MySQL服務使配置生效。管理員可以刪除早期的錯誤日志，這樣可以保證MySQL服務器上的硬盤空間。MySQL數(shù)據庫中，可以使用mysqladmin命令來開啟新的錯誤日志。mysqladmin命令的語法如下：mysqladmin-uroot-pflush-logs2、MySQL日志管理（1）錯誤日志四、日志管理21二進制日志是MySQL中最重要的日志，記錄了除SELECT語句外的所有更改數(shù)據的操作（INSERT、UPDATE、DELETE、CREATE等）。數(shù)據庫發(fā)生意外時，結合數(shù)據庫備份技術可實現(xiàn)數(shù)據庫還原。通過my.ini（Windows）或者f（Linux）文件中的log-bin選項可以開啟二進制日志。使用命令“showvariableslike'log_bin'”可以查看開啟狀態(tài)，產生了哪些二進制文件可以通過“showbinarylogs”命令查看。2、MySQL日志管理（2）二進制日志（log-bin）四、日志管理22二要查看日志文件內容，可以使用“mysqlbinlog”工具查看，例如查看DESKTOP-O5KMGM8-bin.000246的日志內容，可以使用如下指令將二進制日志導入一個文本文件：mysqlbinlogd:/log/DESKTOP-O5KMGM8-bin.000246>d:/log/246.txt依據二進制日志文件，使用mysqlbinlog工具可以將數(shù)據庫還原到某個時間點。命令格式如下：mysqlbinlog[option]filename|mysql-uuser-ppasswordoption為可選項，其中，--start-datetime、--stop-datetime指定數(shù)據庫恢復的起始時間點和結束時間點；--start-position、--stop-position指定恢復數(shù)據的開始位置和結束位置。filename是日志文件名。2、MySQL日志管理（2）二進制日志（log-bin）四、日志管理23【例9-11】使用mysqlbinlog恢復數(shù)據庫到2024年9月1日21:22:59時的狀態(tài)，從2024年9月1日10:23:25時開始恢復。在操作系統(tǒng)命令提示符下輸入：mysqlbinlog--start-datetime="2024-9-110:23:25"--stop-datetime="2024-9-121:22:59"d:/log/DESKTOP-O5KMGM8-bin.000246|mysql-uroot-p2、MySQL日志管理（2）二進制日志（log-bin）四、日志管理24二進制日志文件不能直接刪除，否則會出現(xiàn)系統(tǒng)錯誤，通常使用PURGE命令刪除，其格式如下：PURGE{binary|master}logs{to‘og_name’|beforedatetime_expr}其中，og_name為日志文件名，beforedatetime_expr指定某個日期之前?！纠?-12】刪除指定編號000244前的所有日志。purgebinarylogsto'DESKTOP-O5KMGM8-bin.000244';2、MySQL日志管理（2）二進制日志（log-bin）四、日志管理25通用查詢日志也稱為通用日志，以文本文件形式記錄了所有連接的起始時間和終止時間，以及連接發(fā)送給數(shù)據庫服務器的所有指令操作，包括對數(shù)據庫的增加、刪除、修改、查詢等信息。通用日志默認是關閉的，可以通過修改my.ini或者f文件的log選項來開啟，配置項如下：general-log=1general_log_file="/路徑/日志文件名.log"其中，general-log設置為“on”或“1”，開啟通用日志，general_log_fi