實驗2 數據庫基本操作

數據庫是用來存儲數據庫對象和數據的地方，數據庫對象包括表（Table）、存儲過程（Stored?Procedure）、視圖（View）、觸發器（Trigger）等。在創建數據庫對象之前需要先創建數據庫。數據庫備份是避免突如其來的數據破壞（如：黑客攻擊、病毒襲擊、硬件故障和人為誤操作等），是提高數據安全性的重要措施之一，是恢復數據最容易和最有效的保證方法。

【實驗目的】

①理解數據庫的概念和特點。

②掌握創建數據庫的方法。

③掌握數據庫屬性的查看和修改方法。

④理解數據庫備份的重要性。

⑤掌握數據庫備份和恢復的方法。

⑥掌握數據庫刪除的方法。

【知識要點】

（1）MySQL數據目錄

數據目錄用來存儲MySQL在運行過程中所產生的數據，MySQL服務器程序在啟動時，會加載數據目錄下的一些文件。

數據目錄對應著一個系統變量datadir，用戶在使用客戶端與服務器建立連接之后，可以使用下面命令查看datadir變量的值。

SHOW?VARIABLES?LIKE?'datadir';

執行上面語句后，查詢到MySQL的數據目錄為“C：\\ProgramData\\MySQL\\MySQL?Server?8.0\\Data\\”。

數據目錄下主要有這些文件夾或文件。

1）與數據庫同名的文件夾

MySQL創建數據庫時，會在數據目錄下創建一個與數據庫同名的子文件夾。在數據庫下創建表時，會在該子文件夾下創建一個表示該獨立表空間的文件，文件名和表名相同，擴展名為.ibd。

2）auto.cnf

MySQL啟動時，會自動從該文件中獲取server-uuid值，并將這個值存儲在全局變量server_uuid中。如果該值或者該文件不存在，系統將會生成一個新的uuid值，并將該值保存在auto.cnf文件中。uuid的作用是在MySQL復制時如果發生故障，slave可以通過uuid辨識master的日志。

3）binlog.index

binlog.index用于記錄MySQL產生的binlog日志的具體位置，binlog是MySQL記錄所有操作的邏輯日志，主要用于故障恢復和主從同步。

4）*.pem

*.pem文件用于ssl認證登錄。

5）ib_buffer_pool

當MySQL數據庫InnoDB?Buffer?Pool達到一定量級后，在因為異常造成宕機需要重啟時，會面臨一個嚴峻的問題，就是如何快速預熱Buffer?Pool，光靠InnoDB是不夠的，這時就需要將InnoDB?Buffer?Pool進行存儲，在數據文件中進行備份，重啟時InnoDB直接讀取此文件。

6）#ib_16384_0.dblwr??#ib_16384_1.dblwr

Doublewrite?Buffer是一個存儲，InnoDB將頁寫入InnoDB數據文件適當位置之前，會將緩沖池中頁刷新到該存儲中。如果操作系統存儲子系統，或者mysqld進程在頁寫入中途崩潰，InnoDB可以在恢復中從Doublewrite?Buffer中找到一份好的備份。

7）.ibdata1

ibdata1文件是InnoDB默認共享表空間。

8）.ib_logfile*

InnoDB?Redo日志，區別于binlog，由于MySQL是一個支持多存儲引擎共存的數據庫，所以InnoDB?Redo只記錄InnoDB存儲引擎的重做日志，并且Redo是一個物理日志，通過xid記錄數據文件位置和binlog中的位置，而binlog記錄的是MySQL全局的變化量，是一個邏輯日志。

9）ibtmp1

ibtmp1文件是InnoDB臨時表空間。

10）undo_001

undo_001文件是InnoDB?Undo表空間。

（2）SQL語法約定

表2.1列出了?SQL?參考語法中使用的約定，并進行了說明。

（3）創建數據庫的語法格式

CREATE?{DATABASE?|?SCHEMA}?[IF?NOT?EXISTS]?db_name

CREATE?DATABASE指創建具有給定名稱的數據庫。

CREATE?SCHEMA是CREATE?DATABASE?的同義詞。如果數據庫存在而沒有指定IF?NOT?EXISTS，則會發生錯誤。

create_option指定數據庫特征。數據庫特征存儲在數據字典中。

CHARACTER?SET選項指定默認數據庫字符集。

COLLATE?選項指定默認的數據庫排序規則。字符集規定了字符在數據庫中的存儲格式，比如占多少空間、支持哪些字符等，不同的字符集有不同的排序規則。在維護和使用MySQL數據庫的過程中，選取合適的字符集非常重要，如果選擇不恰當，輕則影響數據庫性能，嚴重的甚至會導致數據存儲亂碼。數據庫排序規則規定字符之間如何進行排序和比較。COLLATE會影響到ORDER?BY語句的順序，會影響到WHERE條件中大于、小于號篩選出來的結果。排序規則通常是和字符集相關的，一般來說，每種排序規則都有多種它所支持的字符集，并且每種字符集都指定一種排序規則為默認值。例如Latin1編碼的默認COLLATE為latin1_swedish_ci，GBK編碼的默認COLLATE為gbk_chinese_ci，utf8mb4編碼的默認值為utf8mb4_general_ci。

（4）設置默認數據庫的語法格式

USE?db_name

USE語句告訴MySQL使用命名數據庫作為后續語句的默認（當前）數據庫。

（5）數據庫備份

數據庫備份是指從數據庫或其事務日志中將數據或日志記錄復制到備份設備（如磁盤），以創建數據備份或日志備份。數據庫備份應定期進行，并執行有效的數據管理。

從物理與邏輯的角度分類，數據庫備份分為物理備份和邏輯備份。從數據庫的備份策略角度分類，數據庫備份分為完整備份、差異備份和增量備份。

1）從物理與邏輯的角度分類

①物理備份：直接復制數據庫文件，適用于大型數據庫環境，不受存儲引擎的限制，但不能恢復到不同的MySQL版本。物理備份又可分為：

冷備份（脫機備份）：在數據庫關閉狀態下進行備份操作。

熱備份（聯機備份）：在數據庫處于運行狀態時進行備份操作，該備份方法依賴數據庫的日志文件。

溫備份：數據庫鎖定表格（不可寫入但可讀）的狀態下進行備份操作。

②邏輯備份：是對數據庫邏輯組件（如表等數據庫對象）的備份，這種類型的備份適用于可以編輯數據值或表結構，或者在不同的機器體系結構上重新創建數據。

2）從數據庫的備份策略角度分類

①完整備份：每次對數據進行完整的備份，即對整個數據庫的備份以及數據庫結構和文件結構的備份，保存的是備份完成時刻的數據庫，完整備份還是差異備份與增量備份的基礎。完整備份的備份與恢復操作都非常簡單方便，但數據存在大量的重復，并且會占用大量的磁盤空間，備份時間也很長。

②差異備份：備份那些從上一次完整備份之后被修改過的所有文件，備份的時間節點是從上次完整備份起，隨著時間的增長，需要備份的數據量會越來越大。恢復數據時，只需恢復上次的完整備份與最近一次的差異備份。

③增量備份：只有那些在上次完整備份或者增量備份后被修改的文件才會被備份。以上次完整備份或上次增量備份的時間為時間點，僅備份之后的數據變化，因此備份的數據量小，占用空間小，備份速度快。但恢復時，需要從上一次的完整備份開始到最近一次增量備份之間的所有增量一次恢復，如中間某次的備份數據損壞，將導致數據的丟失。

（6）備份數據庫工具mysqldump?

MySQL?GUI工具（如MySQL?Workbench等）通常為備份MySQL數據庫提供了便捷高效的功能。但是，如果數據庫很大，則備份過程可能會非常緩慢，因為備份文件需要通過網絡傳輸到客戶端，而且若備份過程進展緩慢，那么MySQL數據庫服務器的鎖定時間則會增長，可用時間則會大幅度減少。

MySQL為了簡化用戶的備份操作，使用戶能夠在服務器上進行本地備份或轉儲MySQL數據庫，提供了非常有用的工具，其中備份文件存儲在服務器中的文件系統里，這意味著只需在需要時下載即可。

備份MySQL數據庫的工具是mysqldump，位于MySQL安裝文件夾下的bin文件夾中。mysqldump是由MySQL提供的程序，可用于備份數據庫或將數據庫傳輸到另一個數據庫服務器。備份文件包含一組用于創建數據庫對象的SQL語句。此外，mysqldump也可用于生成CSV、分隔符或XML文件。

備份MySQL數據庫的命令如下：

mysqldump?[–h?localhost]?-u?[username]?–p[password]?[database_name]?>?[dump_file.sql]

參數含義如下：

localhost：主機名。

username：有效的MySQL用戶名。

password：用戶的有效密碼。?請注意，-p和密碼之間沒有空格。

database_name：?要備份的數據庫名稱。

dump_file.sql：?要生成的備份文件。

通過執行上述命令，所有數據庫結構和數據將導出到一個dump_file.sql備份文件中。

還原MySQL數據庫的命令如下：

mysql?[–h?localhost]?-u?[username]?–p[password]?[database_name]?<?[dump_file.sql]

（7）修改數據庫的語法格式

ALTER?{DATABASE?|?SCHEMA}?[db_name]

????alter_option?...

alter_option:?{

????[DEFAULT]?CHARACTER?SET?[=]?charset_name

??|?[DEFAULT]?COLLATE?[=]?collation_name

??|?[DEFAULT]?ENCRYPTION?[=]?{'Y'?|?'N'}

??|?READ?ONLY?[=]?{DEFAULT?|?0?|?1}

}

數據庫創建后，用戶可以根據需要修改數據庫的字符集、排序規則等參數。

（8）刪除數據庫的語法格式

DROP?{DATABASE?|?SCHEMA}?[IF?EXISTS]?db_name

DROP?DATABASE刪除數據庫中的所有表并刪除數據庫。

DROP?SCHEMA是DROP?DATABASE?的同義詞。