《MySQL數(shù)據(jù)庫應(yīng)用實(shí)戰(zhàn)教程（微課版）》第11章-數(shù)據(jù)庫的日常管理

項(xiàng)目11“在線商店”項(xiàng)目

的日常管理任務(wù)1管理MySQL服務(wù)器任務(wù)1管理MySQL服務(wù)器11.1.1MySQL服務(wù)器管理11.1.2MySQL存儲(chǔ)引擎11.1.3MySQL數(shù)據(jù)庫的組成任務(wù)2備份和恢復(fù)數(shù)據(jù)任務(wù)3使用事件任務(wù)4使用日志11.1.1MySQL服務(wù)器管理1．安裝路徑Linux上的結(jié)果Windows上的結(jié)果2．?dāng)?shù)據(jù)庫文件的路徑Linux上的結(jié)果Windows上的結(jié)果showglobalvariableslike"character_sets_dir";/usr/share/mysql/C:\ProgramFiles(x86)\MySQL\MySQLServer5.5\showglobalvariableslike"datadir";/var/lib/mysql/C:\ProgramFiles(x86)\MySQL\MySQLServer5.5\Data\11.1.1MySQL服務(wù)器管理（續(xù)）3．配置文件Linux

平臺(tái)（后綴.cnf）Windows平臺(tái)（后綴.ini）4．配置文件的修改文本編輯器查看和修改它配置文件的結(jié)構(gòu)，僅需關(guān)注兩組[clent]：MySQL客戶端配置[mysqld_safe][mysqld]：服務(wù)器配置[mysqldump][mysql]修改后保存，重啟MySQL服務(wù)器才能生效/etc/mysql/fC:\ProgramFiles(x86)\MySQL\MySQLServer5.5\my.ini11.1.1MySQL服務(wù)器管理（續(xù)）5．啟動(dòng)、停止和重啟動(dòng)MySQL服務(wù)器的啟動(dòng)、停止和重啟動(dòng)采用命令行的方式是最簡便的，如表11.1所示。在Linux操作系統(tǒng)下，需要以Linux根用戶（root）的身份遠(yuǎn)程登錄到Linux上，才有權(quán)限啟動(dòng)和停止MySQL服務(wù)器。在Windows操作系統(tǒng)下，需要以管理員身份運(yùn)行“命令提示符”窗口，才有權(quán)限啟動(dòng)和停止MySQL服務(wù)器。11.1.2MySQL存儲(chǔ)引擎1．查看MySQL支持的存儲(chǔ)引擎使用下述命令查看存儲(chǔ)引擎的列表。運(yùn)行結(jié)果如圖11.12所示。Showengines;2．MySQL存儲(chǔ)引擎介紹（1）InnoDB這是最常用的一種引擎，也是MySQL的默認(rèn)引擎，本書所有的數(shù)據(jù)庫都采用了InnoDB引擎支持外鍵約束：其他的引擎都不支持外鍵約束。支持事務(wù)：只有InnoDB引擎支持事務(wù)，具有提交或回滾等事務(wù)處理能力。具有災(zāi)難恢復(fù)的能力。適用于存在大量并發(fā)的寫操作的數(shù)據(jù)庫。（2）MyISAM這是一種舊的引擎，MyISAM引擎比較簡單，不適用于存在大量寫操作的數(shù)據(jù)庫。不支持外鍵約束和事務(wù)。不具有災(zāi)難恢復(fù)的能力。適用于存在大量讀操作的數(shù)據(jù)庫，查詢性能較好。支持全文索引。（3）MEMORY這是一種基于內(nèi)存的引擎，所有數(shù)據(jù)都在內(nèi)存中，避免了對硬盤讀寫操作，因此效率特別高。效率高，查詢速度極快。受內(nèi)存容量的限制，只適用于數(shù)據(jù)較少、需要頻繁訪問的數(shù)據(jù)庫。11.1.3MySQL數(shù)據(jù)庫的組成1．?dāng)?shù)據(jù)庫目錄和表文件在MySQL服務(wù)器上，每個(gè)數(shù)據(jù)庫是一個(gè)目錄，這個(gè)目錄包含了每張表對應(yīng)的文件以及數(shù)據(jù)庫相關(guān)的文件，每張表以及每個(gè)視圖是一個(gè)文件，保存在數(shù)據(jù)庫的目錄下。在不同的操作系統(tǒng)下，這些文件是相同的，它們位于以數(shù)據(jù)庫名命名的子目錄下例如eshop數(shù)據(jù)庫在Linux操作系統(tǒng)中位于下述目錄，如圖11.13所示。在Windows10操作系統(tǒng)中位于下述目錄，如圖11.14所示。/var/lib/mysql/eshop/C:\ProgramData\MySQL\MySQLServer5.5\data\eshop\11.1.3MySQL數(shù)據(jù)庫的組成（續(xù)）2．系統(tǒng)數(shù)據(jù)庫MySQL的數(shù)據(jù)庫分為系統(tǒng)數(shù)據(jù)庫和用戶創(chuàng)建的數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫有3個(gè)mysql：MySQL的核心數(shù)據(jù)庫，保存用戶、權(quán)限設(shè)置、關(guān)鍵字等控制和管理信息information_schema：MySQL的信息數(shù)據(jù)庫，保存其他數(shù)據(jù)庫的信息，如數(shù)據(jù)庫名、表、列名及其數(shù)據(jù)類型等performance_schema：用于收集數(shù)據(jù)庫服務(wù)器性能參數(shù)它們是在安裝后，首次配置時(shí)自動(dòng)創(chuàng)建的。它們保存了MySQL服務(wù)器的關(guān)鍵數(shù)據(jù)，具有非常重要的作用，所以千萬不能刪除它們?nèi)蝿?wù)2備份和恢復(fù)數(shù)據(jù)任務(wù)1管理MySQL服務(wù)器任務(wù)2備份和恢復(fù)數(shù)據(jù)11.2.1數(shù)據(jù)庫備份概述11.2.2【實(shí)訓(xùn)11-1】數(shù)據(jù)庫備份與恢復(fù)11.2.3數(shù)據(jù)庫遷移11.2.4備份策略和恢復(fù)策略11.2.5【實(shí)訓(xùn)11-2】備份策略和恢復(fù)策略（Linux）11.2.6【實(shí)訓(xùn)11-3】備份策略和恢復(fù)策略（Windows）任務(wù)3使用事件任務(wù)4使用日志11.2.1數(shù)據(jù)庫備份概述1．為什么備份在數(shù)據(jù)庫運(yùn)行的過程中，可能會(huì)由于人為的、意外的或者不可抗的因素造成數(shù)據(jù)的損壞或丟失例如一場火災(zāi)把一家銀行的數(shù)據(jù)庫機(jī)房燒毀了，而引起數(shù)百萬人的存款和貸款數(shù)據(jù)丟失，只要平時(shí)做好備份，這種情況是完全可以避免的。造成數(shù)據(jù)損壞或丟失的可能原因有如下幾種。自然災(zāi)害：例如地震、火災(zāi)、水災(zāi)、臺(tái)風(fēng)等。系統(tǒng)硬件故障：例如磁盤損壞，或其他硬件故障導(dǎo)致的數(shù)據(jù)損壞。系統(tǒng)軟件故障：例如操作系統(tǒng)或應(yīng)用軟件的故障引起的數(shù)據(jù)損壞。計(jì)算機(jī)病毒：有些計(jì)算機(jī)病毒會(huì)故意損壞數(shù)據(jù)，包括數(shù)據(jù)庫的數(shù)據(jù)。黑客攻擊：黑客的惡意攻擊，可能造成數(shù)據(jù)丟失或被篡改。人為誤操作：人為誤操作是經(jīng)常發(fā)生的，例如誤刪除了表或修改了數(shù)據(jù)。防止數(shù)據(jù)損壞或丟失的措施有數(shù)據(jù)備份、雙機(jī)熱備、異地存儲(chǔ)、遠(yuǎn)程集群等多種方案，本書僅講解最基本的數(shù)據(jù)備份。微課：11-1數(shù)據(jù)備份與恢復(fù)11.2.1數(shù)據(jù)庫備份概述（續(xù)）2．備份的內(nèi)容通常情況下，備份的內(nèi)容是MySQL服務(wù)器上的一個(gè)數(shù)據(jù)庫或全部數(shù)據(jù)庫的數(shù)據(jù)庫對象表（Tables）：包括數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)，以及索引的定義等。視圖（Views）：視圖的定義，視圖本身并沒有數(shù)據(jù)。存儲(chǔ)過程（Procedures）：存儲(chǔ)過程的定義，即SQL編寫的代碼。存儲(chǔ)函數(shù)（Functions）：存儲(chǔ)函數(shù)的定義，即SQL編寫的代碼。觸發(fā)器（Triggers）：觸發(fā)器的定義，即SQL編寫的代碼。事件（Events）：事件的定義（事件在下一節(jié)“任務(wù)3使用事件”講解）。備份通常不包括用戶賬號(hào)、權(quán)限控制等數(shù)據(jù)，這些數(shù)據(jù)保存在mysql數(shù)據(jù)庫中，需要備份mysql數(shù)據(jù)庫才能備份用戶賬號(hào)和權(quán)限控制等數(shù)據(jù)可能還需要備份MySQL服務(wù)器的配置文件，以便重新安裝MySQL時(shí)，可以重新構(gòu)建一個(gè)完全相同的MySQL服務(wù)器11.2.1數(shù)據(jù)庫備份概述（續(xù)）3．備份類型（1）邏輯備份與物理備份邏輯備份：指以文本的方式（SQL語句），把數(shù)據(jù)庫的數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)備份為SQL腳本文件，這個(gè)文件的內(nèi)容是由Create和Insert、Update、Delete等語句組成的，用這些語句可以完整地恢復(fù)數(shù)據(jù)庫所有內(nèi)容。物理備份：指對數(shù)據(jù)庫文件進(jìn)行的備份，它僅復(fù)制文件，因此備份速度快。但是只能在MySQL服務(wù)器停止服務(wù)時(shí)才能進(jìn)行，并且只能移植到相同版本的MySQL服務(wù)器上（2）熱備份與冷備份熱備份：指在數(shù)據(jù)庫服務(wù)仍然運(yùn)行時(shí)做備份，在備份的過程中，服務(wù)器仍然能夠提供正常的讀寫操作，提供正常的服務(wù)。由于InnoDB引擎具有事務(wù)功能，因此可以支持熱備份冷備份：指數(shù)據(jù)庫服務(wù)必須暫停下來，并將所有未寫入數(shù)據(jù)庫的數(shù)據(jù)寫到數(shù)據(jù)庫文件中，然后進(jìn)行備份。由于InnoDB引擎之外的其他引擎不支持事務(wù)功能，因此對這些數(shù)據(jù)庫只能使用冷備份，才能得到一致的備份數(shù)據(jù)。11.2.2【實(shí)訓(xùn)11-1】數(shù)據(jù)庫備份與恢復(fù)本書僅講解在實(shí)際中最常使用的邏輯備份，并且是針對InnoDB引擎的熱備份。1．?dāng)?shù)據(jù)庫備份數(shù)據(jù)庫備份采用mysqldump命令進(jìn)行，其語法格式如下該命令在不同平臺(tái)上是相同的要用一個(gè)文件重定向元字符>將備份的輸出重定向到指定的文件可以同時(shí)指定多個(gè)數(shù)據(jù)庫。也可以同時(shí)備份全部數(shù)據(jù)庫。常用的參數(shù)如表11.2所示（見教材第141頁）最重要的一個(gè)參數(shù)是--routines（-R），它將會(huì)備份存儲(chǔ)例程（存儲(chǔ)過程和存儲(chǔ)函數(shù)）的定義mysqldump-u用戶名-p密碼選項(xiàng)--all-databases>腳本文件名操作系統(tǒng)提示符>mysqldump-u用戶名-p密碼選項(xiàng)數(shù)據(jù)庫名>腳本文件名mysqldump-u用戶名-p密碼選項(xiàng)--databases數(shù)據(jù)庫名1數(shù)據(jù)庫名2…>腳本文件名【實(shí)訓(xùn)11-1】11.2.2【實(shí)訓(xùn)11-1】數(shù)據(jù)庫備份與恢復(fù)（續(xù)）2．?dāng)?shù)據(jù)庫恢復(fù)數(shù)據(jù)庫恢復(fù)使用mysql命令來完成，語法格式如下其中文件重定向元字符<的含義是將“腳本文件名”的內(nèi)容作為mysql的輸入內(nèi)容。使用mysql命令進(jìn)行恢復(fù)的一個(gè)例子是項(xiàng)目9的“9.3.2【實(shí)訓(xùn)9-3】數(shù)據(jù)庫的遷移”小節(jié)使用下述命令從備份文件中恢復(fù)eshop數(shù)據(jù)庫（需要指定恢復(fù)的數(shù)據(jù)庫名）。用下述語句可以恢復(fù)eshop數(shù)據(jù)庫和mysql數(shù)據(jù)庫（無須指定恢復(fù)的數(shù)據(jù)庫名，因?yàn)閿?shù)據(jù)庫名已經(jīng)在備份文件中指定）。操作系統(tǒng)提示符>mysql-u用戶名-p密碼數(shù)據(jù)庫名<腳本文件名mysql-uroot-pJitor123eshop</root/eshop_backup.sqlmysql-uroot-psa<eshop_mysql.sql11.2.2【實(shí)訓(xùn)11-1】數(shù)據(jù)庫備份與恢復(fù)（續(xù)）2．?dāng)?shù)據(jù)庫恢復(fù)（續(xù)）如果只恢復(fù)一個(gè)數(shù)據(jù)庫，則需要指定數(shù)據(jù)庫的名字，并且該數(shù)據(jù)庫已經(jīng)存在。如果從一個(gè)備份文件中恢復(fù)多個(gè)數(shù)據(jù)庫，則無需指定數(shù)據(jù)庫名。在恢復(fù)數(shù)據(jù)庫之前，如果數(shù)據(jù)庫不存在，應(yīng)該先創(chuàng)建數(shù)據(jù)庫，如果數(shù)據(jù)庫已存在，則恢復(fù)時(shí)會(huì)通過刪除表、重新創(chuàng)建表、插入數(shù)據(jù)的方式來自動(dòng)恢復(fù)所有數(shù)據(jù)。但是如果存在備份文件中不存在的表，則這部分內(nèi)容保持不變。用備份文件完全覆蓋一個(gè)現(xiàn)有的數(shù)據(jù)庫的操作如下。丟棄現(xiàn)有數(shù)據(jù)庫：該數(shù)據(jù)庫可能有數(shù)據(jù)，但是需要確認(rèn)這些數(shù)據(jù)是可丟棄的。重新創(chuàng)建新的數(shù)據(jù)庫：這是新的空數(shù)據(jù)庫，注意指定正確的字符集。用下述命令從備份中恢復(fù)數(shù)據(jù)庫。注：不建議使用source命令恢復(fù)數(shù)據(jù)庫，因?yàn)樾阅芎艿蚼ysql-uroot-p數(shù)據(jù)庫名<數(shù)據(jù)庫備份文件；11.2.3數(shù)據(jù)庫遷移數(shù)據(jù)庫遷移是指將數(shù)據(jù)庫從一臺(tái)服務(wù)器遷移到另一臺(tái)服務(wù)器，可以通過在源服務(wù)器備份和在目標(biāo)服務(wù)器恢復(fù)數(shù)據(jù)庫的方式來實(shí)現(xiàn)。不同操作系統(tǒng)平臺(tái)對遷移的過程沒有任何影響，而不同版本或不同種類的數(shù)據(jù)庫管理系統(tǒng)則會(huì)對遷移過程造成一定的影響。1．同版本MySQL間的遷移如果源服務(wù)器和目標(biāo)服務(wù)器的MySQL是相同版本的，這時(shí)的遷移過程很方便2．不同版本MySQL間的遷移從低版本向高版本的MySQL遷移不會(huì)有什么問題，而從高版本向低版本的遷移則有可能出現(xiàn)兼容性問題，3．不同數(shù)據(jù)庫管理系統(tǒng)間的遷移這種情況是指MySQL和其他數(shù)據(jù)庫管理系統(tǒng)（例如Oracle或SQLServer等）之間的遷移，不論是從MySQL遷出，還是遷入到MySQL，都會(huì)存在比較大的問題。11.2.3數(shù)據(jù)庫遷移（續(xù)）3．不同數(shù)據(jù)庫管理系統(tǒng)間的遷移（續(xù)）存在比較大的問題，體現(xiàn)在如下幾個(gè)方面數(shù)據(jù)類型不同：例如MySQL不支持SQLServer的money數(shù)據(jù)類型，在MySQL中起類似作用的數(shù)據(jù)類型是decimal（精確浮點(diǎn)數(shù)）。SQL語句的語法不同：例如SQLServer的Select語句不支持MySQL的limit子句，而是用top子句，后者又不被MySQL支持。內(nèi)置函數(shù)不同：例如獲得當(dāng)前日期時(shí)間的函數(shù)，MySQL的是now()，而SQLServer的則是getDate()。支持的命令不同：例如MySQL不支持SQLServer的Print命令，而SQLServer不支持MySQL的Show命令。存儲(chǔ)函數(shù)、存儲(chǔ)過程寫法不同：MySQL需要用Delimeter命令定義新的分隔符，而SQLServer則沒有這個(gè)需要。觸發(fā)器的要求不同：例如在觸發(fā)器內(nèi)部訪問新舊數(shù)據(jù)，MySQL的是new和old對象，而SQLServer的則是inserted和deleted表。由于存在上述區(qū)別，特別是在函數(shù)（內(nèi)置函數(shù)和存儲(chǔ)函數(shù)）、存儲(chǔ)過程以及觸發(fā)器方面的差別，使數(shù)據(jù)庫在不同的數(shù)據(jù)庫管理系統(tǒng)之間的遷移變得比較困難。11.2.4備份策略和恢復(fù)策略靜態(tài)備份和靜態(tài)恢復(fù)存在的問題前述的數(shù)據(jù)庫備份是一種全庫備份，它是一種靜態(tài)的備份策略，是在固定的時(shí)間對整個(gè)數(shù)據(jù)庫進(jìn)行備份。數(shù)據(jù)庫的數(shù)據(jù)是動(dòng)態(tài)變化的，每時(shí)每刻都可能有數(shù)據(jù)的插入、更新或刪除。這些數(shù)據(jù)的變化是十分重要的，特別是對于實(shí)時(shí)要求較高的數(shù)據(jù)庫。定時(shí)的、對整個(gè)數(shù)據(jù)庫的備份顯然無法做到對每一個(gè)數(shù)據(jù)變化都及時(shí)備份。備份策略和恢復(fù)策略的目標(biāo)因此需要一種完善的機(jī)制，保證對動(dòng)態(tài)數(shù)據(jù)的及時(shí)備份，并在出現(xiàn)問題時(shí)，恢復(fù)所有數(shù)據(jù)11.2.4備份策略和恢復(fù)策略（續(xù)）1．備份策略概述需要建立一種完善的備份策略來實(shí)現(xiàn)對每一個(gè)數(shù)據(jù)變化都能及時(shí)進(jìn)行備份。這個(gè)策略是由兩種不同形式的備份來共同完成的，如表11.3所示2．恢復(fù)策略概述當(dāng)數(shù)據(jù)庫遭到損壞時(shí)，就需要用備份的數(shù)據(jù)恢復(fù)數(shù)據(jù)庫。先從全庫備份中恢復(fù)數(shù)據(jù)，然后再從增量備份中恢復(fù)全庫備份以后改變過的數(shù)據(jù)，如表11.4所示。11.2.5【實(shí)訓(xùn)11-2】備份策略和恢復(fù)策略（Linux）1．備份策略的實(shí)施——定時(shí)進(jìn)行全庫備份（1）創(chuàng)建備份用腳本文件首先創(chuàng)建一個(gè)備份用的腳本文件/root/backup.sh，內(nèi)容如下將這個(gè)腳本文件修改為可執(zhí)行文件，代碼如下（注意：chmod的首字母是小寫的）。測試腳本文件是否可以正常執(zhí)行，代碼如下。執(zhí)行后，將在/root目錄生成一個(gè)備份文件eshop_bakcup.sql。（2）設(shè)置定時(shí)運(yùn)行腳本文件然后設(shè)置定時(shí)備份，即定時(shí)執(zhí)行/root/backup.sh。通常定時(shí)執(zhí)行的時(shí)間都設(shè)置為凌晨，因?yàn)檫@個(gè)時(shí)候使用數(shù)據(jù)庫的用戶最少，備份對用戶的影響也最小。在遠(yuǎn)程終端上執(zhí)行命令crontab-e，在最后添加一行如下的配置。這個(gè)配置是指定每個(gè)星期日凌晨3:00執(zhí)行上述腳本文件，進(jìn)行全庫備份#!/bin/bashmysqldump-uroot-pJitor123eshop>/root/eshop_bakcup.sqlchmod777/root/backup.sh./backup.sh03**0/bin/bash-x/root/backup.sh>/dev/null2>&1【實(shí)訓(xùn)11-2】11.2.5【實(shí)訓(xùn)11-2】備份策略和恢復(fù)策略（Linux）（續(xù)）2．備份策略的實(shí)施——增量備份增量備份的實(shí)質(zhì)是記錄對數(shù)據(jù)庫的每一次數(shù)據(jù)定義（Create、Alter和Drop）操作和增、刪、改（Insert、Update和Delete）操作，MySQL提供了二進(jìn)制日志功能來記錄對數(shù)據(jù)庫的數(shù)據(jù)定義和增、刪、改操作，這種記錄就是增量備份。（1）配置二進(jìn)制日志二進(jìn)制日志功能默認(rèn)是不啟用的，需要修改MySQL的配置文件f，在[mysqld]部分修改下述配置（僅需刪除配置項(xiàng)前的注釋#號(hào)），啟用二進(jìn)制日志，如圖11.18所示。（2）修改并保存后，用下述命令重新啟動(dòng)MySQL服務(wù)器。重新啟動(dòng)后，可以看到在/var/log/mysql/目錄下有一個(gè)二進(jìn)制日志文件，它的后綴是一個(gè)6位數(shù)字，第一個(gè)文件的后綴是.000001，然后順序遞增，如圖11.19所示（教材第247頁）（3）需要時(shí)可以查看二進(jìn)制日志的內(nèi)容[mysqld]log_bin=/var/log/mysql/mysql-bin.logexpire_logs_days=10max_binlog_size=100Mservicemysqlrestart11.2.5【實(shí)訓(xùn)11-2】備份策略和恢復(fù)策略（Linux）（續(xù)）3．恢復(fù)策略的實(shí)施經(jīng)過前述兩個(gè)步驟，數(shù)據(jù)庫備份已經(jīng)完成。在PPT里，只講解整個(gè)過程，而不注重細(xì)節(jié)。為了模擬數(shù)據(jù)丟失，可以直接Drop數(shù)據(jù)庫。（1）恢復(fù)前的準(zhǔn)備當(dāng)發(fā)生了數(shù)據(jù)庫損壞或數(shù)據(jù)丟失，首先要停止MySQL服務(wù)器，然后將所有備份文件復(fù)制到一個(gè)安全的地方，以防止備份文件的損壞或丟失造成嚴(yán)重后果。特別要注意的是，在恢復(fù)過程中，數(shù)據(jù)庫不允許被其他用戶訪問，直到數(shù)據(jù)庫完全恢復(fù)。（2）從全庫備份中恢復(fù)重新啟動(dòng)MySQL服務(wù)器（不允許其他用戶使用，以免破壞數(shù)據(jù)的一致性），運(yùn)行下述SQL代碼這時(shí)查詢數(shù)據(jù)，可以發(fā)現(xiàn)全庫備份已經(jīng)恢復(fù)，但是全庫備份后的數(shù)據(jù)變化并沒有恢復(fù)。Createdatabaseeshopcharactersetutf8collateutf8;useeshop;mysql-uroot-psa<eshop_mysql.sql11.2.5【實(shí)訓(xùn)11-2】備份策略和恢復(fù)策略（Linux）（續(xù)）3．恢復(fù)策略的實(shí)施（續(xù)）（3）從二進(jìn)制日志文件中找到要恢復(fù)的數(shù)據(jù)切換到日志目錄，用命令mysqlbinlog查看二進(jìn)制日志文件。從二進(jìn)制日志文件中找到需要恢復(fù)的數(shù)據(jù)，通常是一段連續(xù)的數(shù)據(jù)（數(shù)據(jù)操縱語句）例如找到的需要恢復(fù)的日志是偏移位置從635到797的二進(jìn)制日志（4）從二進(jìn)制日志文件中恢復(fù)用以下命令恢復(fù)偏移位置從635到797的二進(jìn)制日志（注意提供正確的密碼）。其中符號(hào)“|”是管道元字符，它的前后分別是一條Linux命令，作用是把前一條命令的輸出結(jié)果作為后一條命令的輸入，就像一條管道，數(shù)據(jù)從前一條命令流到后一條命令。最后登錄到MySQL，使用查詢語句，可以驗(yàn)證全庫備份之后的數(shù)據(jù)變化（恢復(fù)的數(shù)據(jù)由偏移位置決定）已經(jīng)反映在數(shù)據(jù)庫中至此，數(shù)據(jù)恢復(fù)全部完成，可以允許其他用戶訪問cd/var/log/mysql/mysqlbinlogmysql-bin.bak.000001mysqlbinlog--start-position=635--stop-position=797mysql-bin.bak.000001|mysql-uroot-pJitor12311.2.6【實(shí)訓(xùn)11-3】備份策略和恢復(fù)策略（Windows）1．備份策略的實(shí)施——定時(shí)進(jìn)行全庫備份（1）創(chuàng)建備份用批處理文件首先創(chuàng)建一個(gè)保存?zhèn)浞菸募玫哪夸?，例如D:\eshop，然后編寫一個(gè)備份用的批處理文件（這是一個(gè)可執(zhí)行文件），例如D:\eshop\backup.bat，內(nèi)容如下（注意將代碼中的sa替換為正確的密碼）。測試腳本文件是否可以正常執(zhí)行，代碼如下。執(zhí)行后，將在D:\eshop目錄中生成一個(gè)備份文件eshop_bakcup.sql。（2）設(shè)置定時(shí)運(yùn)行批處理文件然后設(shè)置定時(shí)備份，即定時(shí)執(zhí)行D:\eshop\backup.bat。通常定時(shí)執(zhí)行的時(shí)間都設(shè)置為凌晨，因?yàn)檫@個(gè)時(shí)候使用數(shù)據(jù)庫的用戶最少，備份對用戶的影響也最小。辦法是用鼠標(biāo)右鍵單擊“我的電腦”，執(zhí)行“管理”命令，如圖11.22所示。在打開的“計(jì)算機(jī)管理”界面中選擇“任務(wù)計(jì)劃程序”，然后在右側(cè)選擇“創(chuàng)建基本任務(wù)”，如圖11.23所示，打開“創(chuàng)建基本任務(wù)向?qū)А?，根?jù)向?qū)У囊筮M(jìn)行設(shè)置（見教材第250-251頁）設(shè)置完成后，Windows操作系統(tǒng)將會(huì)在指定的時(shí)間運(yùn)行指定的程序，實(shí)現(xiàn)數(shù)據(jù)庫備份D:\eshop\backup.shmysqldump-uroot-psaeshop>D:\eshop\eshop_bakcup.sql【實(shí)訓(xùn)11-3】11.2.6【實(shí)訓(xùn)11-3】備份策略和恢復(fù)策略（Windows）（續(xù)）2．備份策略的實(shí)施——增量備份增量備份的實(shí)質(zhì)是記錄對數(shù)據(jù)庫的每一次數(shù)據(jù)定義（Create、Alter和Drop）操作和增、刪、改（Insert、Update和Delete）操作，MySQL提供了二進(jìn)制日志功能來記錄對數(shù)據(jù)庫的數(shù)據(jù)定義和增、刪、改操作，這種記錄就是增量備份。（1）配置二進(jìn)制日志默認(rèn)安裝時(shí)，二進(jìn)制日志功能是不啟用的，需要修改MySQL的配置文件my.ini，在[mysqld]部分添加下述配置，如圖11.31所示。（2）修改并保存后，用下述命令重新啟動(dòng)MySQL服務(wù)器。重新啟動(dòng)后，可以看到在C:\ProgramData\MySQL\MySQLServer5.5\Data\目錄下有一個(gè)二進(jìn)制日志文件，它的后綴是一個(gè)6位數(shù)字，第一個(gè)文件的后綴是.000001，然后依次遞增，如圖11.32所示（教材第252頁）（3）需要時(shí)可以查看二進(jìn)制日志的內(nèi)容[mysqld]log_bin=C:\ProgramData\MySQL\MySQLServer5.5\Data\mysql-bin.logexpire_logs_days=10max_binlog_size=100Mnetstopmysqlnetstartmysql11.2.6【實(shí)訓(xùn)11-3】備份策略和恢復(fù)策略（Windows）（續(xù)）3．恢復(fù)策略的實(shí)施經(jīng)過前述兩個(gè)步驟，數(shù)據(jù)庫備份已經(jīng)完成。在PPT里，只講解整個(gè)過程，而不注重細(xì)節(jié)。為了模擬數(shù)據(jù)丟失，可以直接Drop數(shù)據(jù)庫。（1）恢復(fù)前的準(zhǔn)備當(dāng)發(fā)生了數(shù)據(jù)庫損壞或數(shù)據(jù)丟失，首先要停止MySQL服務(wù)器，然后將所有備份文件復(fù)制到一個(gè)安全的地方，以防止備份文件的損壞或丟失造成嚴(yán)重后果。特別要注意的是，在恢復(fù)過程中，數(shù)據(jù)庫不允許被其他用戶訪問，直到數(shù)據(jù)庫完全恢復(fù)。（2）從全庫備份中恢復(fù)重新啟動(dòng)MySQL服務(wù)器（不允許其他用戶使用，以免破壞數(shù)據(jù)的一致性），運(yùn)行下述SQL代碼這時(shí)查詢數(shù)據(jù)，可以發(fā)現(xiàn)全庫備份已經(jīng)恢復(fù)，但是全庫備份后的數(shù)據(jù)變化并沒有恢復(fù)。Createdatabaseeshopcharactersetutf8collateutf8;useeshop;mysql-uroot-psa<eshop_mysql.sql11.2.6【實(shí)訓(xùn)11-3】備份策略和恢復(fù)策略（Windows）（續(xù)）3．恢復(fù)策略的實(shí)施（續(xù)）（3）從二進(jìn)制日志文件中找到要恢復(fù)的數(shù)據(jù)切換到日志目錄，用命令mysqlbinlog查看二進(jìn)制日志文件。從二進(jìn)制日志文件中找到需要恢復(fù)的數(shù)據(jù)，通常是一段連續(xù)的數(shù)據(jù)（數(shù)據(jù)操縱語句）例如找到的需要恢復(fù)的日志是偏移位置從817到1045的二進(jìn)制日志（4）從二進(jìn)制日志文件中恢復(fù)用以下命令恢復(fù)偏移位置從817到1045的二進(jìn)制日志（注意提供正確的密碼）。其中符號(hào)“|”是管道元字符，它的前后分別是一條Windows命令，作用是把前一條命令的輸出結(jié)果作為后一條命令的輸入，就像一條管道，數(shù)據(jù)從前一條命令流到后一條命令。最后登錄到MySQL，使用查詢語句，可以驗(yàn)證全庫備份之后的數(shù)據(jù)變化（恢復(fù)的數(shù)據(jù)由偏移位置決定）已經(jīng)反映在數(shù)據(jù)庫中至此，數(shù)據(jù)恢復(fù)全部完成，可以允許其他用戶訪問cdC:\ProgramData\MySQL\MySQLServer5.5\Data\mysqlbinlogmysql-bin.bak.000001mysqlbinlog--start-position=817--stop-position=1045mysql-bin.bak.000001|mysql-uroot-psa任務(wù)3使用事件任務(wù)1管理MySQL服務(wù)器任務(wù)2備份和恢復(fù)數(shù)據(jù)任務(wù)3使用事件11.3.1事件概述11.3.2【實(shí)訓(xùn)11-4】使用MySQL事件任務(wù)4使用日志11.3.1事件概述在“任務(wù)2備份和恢復(fù)數(shù)據(jù)”一節(jié)講解“定時(shí)進(jìn)行全庫備份”時(shí)，講解了定時(shí)任務(wù)（運(yùn)行腳本文件或批處理文件）的實(shí)施，但是它是在操作系統(tǒng)層面上進(jìn)行的，是分別在Linux和Windows操作系統(tǒng)上進(jìn)行的事件也是一種定時(shí)任務(wù)，它是在MySQL內(nèi)部執(zhí)行的，但是它不能實(shí)現(xiàn)定時(shí)備份數(shù)據(jù)庫的任務(wù)事件是一種在MySQL內(nèi)部定時(shí)執(zhí)行SQL語句的機(jī)制一個(gè)事件有下述兩方面的內(nèi)容要被執(zhí)行的SQL語句，也可以是一個(gè)存儲(chǔ)過程事件執(zhí)行的時(shí)間，可以指定在某個(gè)時(shí)間執(zhí)行一次，也可以指定循環(huán)執(zhí)行的間隔時(shí)間事件可以用于日常維護(hù)，例如，每日定時(shí)統(tǒng)計(jì)前一日的銷售金額，用于制作日報(bào)表，每月定時(shí)統(tǒng)計(jì)月報(bào)表等。11.3.2【實(shí)訓(xùn)11-4】使用MySQL事件在默認(rèn)情況下，事件調(diào)度是未啟用的，可以通過下述語句查看當(dāng)前的狀態(tài)。如果值為OFF，表示沒有啟用。1．啟用事件調(diào)度在MySQL的配置文件的[mysqld]中加入下述設(shè)置項(xiàng)。重新啟動(dòng)MySQL服務(wù)器，這時(shí)檢查event_scheduler的值，如果是ON，表示啟用。showvariableslike'event_scheduler';[mysqld]event_scheduler=1【實(shí)訓(xùn)11-4】11.3.2【實(shí)訓(xùn)11-4】使用MySQL事件（續(xù)）2．創(chuàng)建事件下面用一個(gè)例子來演示事件的創(chuàng)建，以及定時(shí)執(zhí)行的效果。先創(chuàng)建一張表，用于記錄事件執(zhí)行的效果。編寫一個(gè)事件，向event_log表插入一行，其中every10second表示每隔10秒執(zhí)行一次，連續(xù)執(zhí)行，代碼如下。Dropeventifexistse_log_event;Delimiter$$Createevente_log_eventonscheduleevery10seconddobegininsertintoevent_logvalues(null,'logtag',now());end$$Delimiter;Useeshop;Createtableevent_log(id_event_logintnotnullprimarykeyauto_increment,col_tagvarchar(20),col_timedatetime);11.3.2【實(shí)訓(xùn)11-4】使用MySQL事件（續(xù)）3．事件執(zhí)行的效果在1分多鐘后查詢event_log表的數(shù)據(jù)，可以檢查事件定時(shí)執(zhí)行的效果。圖11.36所示是在開始事件調(diào)度后1分多種的查詢結(jié)果，該事件共執(zhí)行了8次，每次時(shí)間間隔是10秒。由此證明，在規(guī)定的時(shí)間確實(shí)執(zhí)行了指定的事件Select*fromevent_log;任務(wù)4使用日志任務(wù)1管理MySQL服務(wù)器任務(wù)2備份和恢復(fù)數(shù)據(jù)任務(wù)3使用事件任務(wù)4使用日志11.4.1日志概述11.4.2【實(shí)訓(xùn)11-5】使用MySQL11.4.1日志概述運(yùn)維工作不僅是安全設(shè)置、數(shù)據(jù)備份和恢復(fù)等，還有一項(xiàng)很重要的工作是監(jiān)控系統(tǒng)的運(yùn)行狀況，通過MySQL的日志功能，系統(tǒng)管理員能夠得到一些有用的信息MySQL日志系統(tǒng)由下述4類日志組成。錯(cuò)誤日志：記錄啟動(dòng)、運(yùn)行或停止mysqld，以及運(yùn)行時(shí)出現(xiàn)的問題。通用查詢?nèi)罩荆河涗浗⒌目蛻舳诉B接和執(zhí)行的語句。二進(jìn)制日志：記錄所有DDL和DML語句。慢查詢?nèi)罩荆河涗浻绊憯?shù)據(jù)庫性能瓶頸的、占用時(shí)間過長的查詢。二進(jìn)制日志已在“任務(wù)2備份和恢復(fù)數(shù)據(jù)”一節(jié)中詳細(xì)講解過，下面將講解其余3種日志。11.4.2【實(shí)訓(xùn)11-5】使用MySQL1．錯(cuò)誤日志錯(cuò)誤日志默認(rèn)是開啟的，并且不能關(guān)閉，其他3種日志文件默認(rèn)是不開啟的。錯(cuò)誤日志文件在不同操作系統(tǒng)下位于不同的目錄中，可以用下述命令查詢所在的目錄錯(cuò)誤日志可以用任何文本編輯器打開，如圖11.40所示showvariableslike"log_error";【實(shí)訓(xùn)11-5】11.4.2【實(shí)訓(xùn)11-5】使用MySQL（續(xù)）2．通用查詢?nèi)罩就ㄓ貌樵內(nèi)罩灸J(rèn)是不開啟的，查詢是否開啟的命令如下啟用通用查詢?nèi)罩镜霓k法是在