MySQL數據庫怎樣備份與恢復

2024-07-24 12:33:06

字體：大中小

來源：轉載

供稿：網友

　　在任何數據庫環境中，總會有不確定的意外情況發生，比如例外的停電、計算機系統中的各種軟硬件故障、人為破壞、管理員誤操作等是不可避免的，這些情況可能會導致數據的丟失、服務器癱瘓等嚴重的后果。存在多個服務器時，會出現主從服務器之間的數據同步問題。

　　為了有效防止數據丟失，并將損失降到最低，應定期對MySQL數據庫服務器做備份。如果數據庫中的數據丟失或者出現錯誤，可以使用備份的數據進行恢復。主從服務器之間的數據同步問題可以通過復制功能實現。

　　一、物理備份與邏輯備份
　　物理備份：備份數據文件，轉儲數據庫物理文件到某一目錄。物理備份恢復速度比較快，但占用空間比較大，MySQL中可以用xtrabackup工具來進行物理備份。

　　邏輯備份：對數據庫對象利用工具進行導出工作，匯總入備份文件內。邏輯備份恢復速度慢，但占用空間小，更靈活。MySQL 中常用的邏輯備份工具為mysqldump。邏輯備份就是備份sql語句，在恢復的時候執行備份的sql語句實現數據庫數據的重現。

　　二、mysqldump實現邏輯備份
　　mysqldump是MySQL提供的一個非常有用的數據庫備份工具。

　　2.1 備份一個數據庫
　　mysqldump命令執行時，可以將數據庫備份成一個文本文件，該文件中實際上包含多個CREATE和INSERT語句，使用這些語句可以重新創建表和插入數據。

　　查出需要備份的表的結構，在文本文件中生成一個CREATE語句
　　將表中的所有記錄轉換成一條INSERT語句。

　　/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
　　/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
　　/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
　　/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
　　/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
　　/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
　　/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
　　-- Dump completed on 2022-01-07 9:58:23
　　--開頭的都是sQL語句的注釋；
　　以/* !開頭、*/結尾的語句為可執行的MySQL注釋，這些語句可以被MySQL執行，但在其他數據庫管理系統中被作為注釋忽略，這可以提高數據庫的可移植性；
　　文件開頭指明了備份文件使用的MySQLdump工具的版本號；接下來是備份賬戶的名稱和主機信息，以及備份的數據庫的名稱；最后是MySQL服務器的版本號，在這里為8.0.26。
　　備份文件接下來的部分是一些SET語句，這些語句將一些系統變量值賦給用戶定義變量，以確保被恢復的數據庫的系統變量和原來備份時的變量相同，例如：
　　MySQL數據庫如何備份與恢復
　　備份文件的最后幾行MySQL使用SET語句恢復服務器系統變量原來的值，例如：
　　后面的DROP語句、CREATE語句和INSERT語句都是還原時使用的。例如，DROPTABLE IF EXISTS 'student'語句用來判斷數據庫中是否還有名為student的表，如果存在，就刪除這個表；CREATE語句用來創建student的表； INSERT語句用來還原數據。
　　備份文件開始的一些語句以數字開頭。這些數字代表了MySQL版本號，告訴我們這些語句只有在制定的MySQL版本或者比該版本高的情況下才能執行。例如，40101表明這些語句只有在MySQL版本號為4.01.01或者更高的條件下才可以被執行。文件的最后記錄了備份的時間。
　　2.2 備份全部數據庫
　　若想用mysqldump備份整個實例，可以使用--all-databases或-A參數：

　　mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
　　mysqldump -uroot -pxxxxxx -A > all_database.sql
　　2.3 備份部分數據庫
　　使用--databases或-B參數了，該參數后面跟數據庫名稱，多個數據庫間用空格隔開。如果指定databases參數，備份文件中會存在創建數據庫的語句，如果不指定參數，則不存在。語法如下:

　　mysqldump –u user –h host –p --databases [數據庫的名稱1 [數據庫的名稱2...]] > 備份文件名稱.sql
　　舉例

　　mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql
　　或

　　mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql
　　2.4 備份部分表
　　比如，在表變更前做個備份。語法如下：

　　mysqldump –u user –h host –p 數據庫的名稱 [表名1 [表名2...]] > 備份文件名稱.sql
　　舉例：備份atguigu數據庫下的book表

　　mysqldump -uroot -p atguigu book> book.sql#備份多張表 mysqldump -uroot -p atguigu book account > 2_tables_bak.sql

（編輯：武林網）

上一篇：union all在mysql中怎么運用

下一篇：MySQL查詢語句的執行過程實例解析