MySQL 数据备份与恢复
本次使用的MySQL版本為8.0.20
目錄
一、數據備份
1.使用 MySQLdump 命令備份
（1）使用MySQLdump備份單個數據庫中的所有表
案例：完成數據插入后，輸入備份命令如下：
（2）使用MySQLdump備份數據庫中的某個表
案例：備份booksDB數據庫中的books表，輸入語句如下：
（3）使用MySQLdump備份多個數據庫
案例：使用MySQLdump備份booksDB和test_db數據庫，輸入語句如下：
二、數據恢復
1.使用MySQL命令恢復
案例：使用MySQL命令將C:\backup\booksdb_20190301.sql文件中的備份導入到數據庫中，輸入語句如下：
案例：使用root用戶登錄到服務器，然后使用source導入本地的備份文件bookdb_20221016.sql，輸入語句如下：
三、表的導出和導入
1.使用MySQLdump命令導出文本文件
案例：使用MySQLdump將test_db數據庫person表中的記錄導出到文本文件，執行的命令如下：?
案例：使用MySQLdump命令將test_db數據庫person表中的記錄導出到文本文件，使用FIELDS選項，要求字段之間使用逗號“,”間隔，所有字符類型字段值用雙引號括起來，定義轉義字符為問號“?”，每行記錄以回車換行符“\r\n”結尾，執行的命令如下：?
2.使用MySQLimport命令導入文本文件
案例：使用MySQLimport命令將D盤目錄下的person.txt文件內容導入到test_db數據庫中，字段之間使用逗號“,”間隔，字符類型字段值用雙引號括起來，將轉義字符定義為問號“?”，每行記錄以回車換行符“\r\n”結尾，執行的命令如下：?
一、數據備份
1.使用 MySQLdump 命令備份
????????MySQLdump 是 MySQL 提供的一個非常有用的數據庫備份工具。MySQLdump 命令執行時，可以將數據庫備份成一個文本文件，該文件中實際包含了多個 CREATE 和 INSERT 語句，使用這些語句可以重新創建表和插入數據。
????????MySQLdump 備份數據庫語句的基本語法格式如下：
mysqldump -u user -h host -ppassword dbname[tbname, [tbname...]] > filename.sql????????user表示用戶名稱；host表示登錄用戶的主機名稱；password為登錄密碼；dbname為需要備 份的數據庫名稱；tbname為dbname數據庫中需要備份的數據表，可以指定多個需要備份的表；右 箭頭符號“>”告訴MySQLdump將備份數據表的定義和數據寫入備份文件；filename.sql為備份文件的名稱。
（1）使用MySQLdump備份單個數據庫中的所有表
????????為了更好地理解MySQLdump工具是如何工作的，這里給出一個完整的數據庫例子。首先登錄MySQL，按下面數據庫結構創建booksDB數據庫和各個表，并插入數據記錄。數據庫和表定義如下：
[root@client01 home]# mysql -uroot -p123456CREATE DATABASE booksDB; use booksDB;CREATE TABLE books (bk_id INT NOT NULL PRIMARY KEY,bk_title VARCHAR(50) NOT NULL,copyright YEAR NOT NULL );INSERT INTO books VALUES (11078, 'Learning MySQL', 2010), (11033, 'Study Html', 2011), (11035, 'How to use php', 2003), (11072, 'Teach yourself javascript', 2005), (11028,'Learning C++', 2005), (11069, 'MySQL professional', 2009), (11026, 'Guide to MySQL 8.0', 2008), (11041, 'Inside VC++', 2011);CREATE TABLE authors (auth_id INT NOT NULL PRIMARY KEY,auth_name VARCHAR(20),auth_gender CHAR(1) );INSERT INTO authors VALUES (1001,'WriterX','f'), (1002,'WriterA','f'), (1003,'WriterB','m'), (1004,'WriterC','f'), (1011,'WriterD','f'), (1012,'WriterE' ,'m'), (1013,'WriterF','m'), (1014,'WriterG' ,'f'), (1015,'WriterH','f');CREATE TABLE authorbook (auth_id INT NOT NULL,bk_id INT NOT NULL,PRIMARY KEY (auth_id, bk_id),FOREIGN KEY (auth_id) REFERENCES authors (auth_id),FOREIGN KEY (bk_id) REFERENCES books (bk_id) );INSERT INTO authorbook VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028), (1011, 11078), (1012, 11026),(1012, 11041), (1014, 11069);exit-
案例：完成數據插入后，輸入備份命令如下：
????????輸入密碼之后，MySQL便對數據庫進行了備份，在/home/mysql_back文件夾下面查看剛才備份過的文件，使用VS Code 打開文件可以看到，備份文件包含了一些信息，文件開頭首先表明了備份文件使用的MySQLdump工具的版本號；然后是備份賬戶的名稱和主機信息，以及備份的數據庫的名稱，最后是MySQL服務器的版本號，在這里為8.0.20。
????????備份文件接下來的部分是一些SET語句，這些語句將一些系統變量值賦給用戶定義變量，以確保被恢復的數據庫的系統變量和原來備份時的變量相同，例如：
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;????????該SET語句將當前系統變量character_set_client的值賦給用戶定義變量@old_character_ set_client。其他變量與此類似。
????????備份文件中以“--”字符開頭的行為注釋語句；以“/*!”開頭、“*/”結尾的語句為可執行的MySQL注釋，這些語句可以被MySQL執行，但在其他數據庫管理系統中將被作為注釋忽略，以提高數據庫的可移植性。另外，備份文件開始的一些語句以數字開頭，代表的是MySQL版本號，這些語句只有在指定的MySQL版本或者比該版本高的情況下才能執行。例如，40101，表明這些語句只有在MySQL版本號為4.01.01或者更高的條件下才可以被執行。
（2）使用MySQLdump備份數據庫中的某個表
其語法格式為：
mysqldump –u user –h host –p dbname [tbname, [tbname...]] > filename.sql????????tbname表示數據庫中的表名，多個表名之間用空格隔開。備份表和備份數據庫中所有表的語句中不同的地方在于，要在數據庫名稱dbname之后指定需要備份的表名稱。
-
案例：備份booksDB數據庫中的books表，輸入語句如下：
（3）使用MySQLdump備份多個數據庫
????????如果要使用MySQLdump備份多個數據庫，就需要使用--databases參數。備份多個數據庫的語句格式如下：
mysqldump –u user –h host –p --databases [dbname, [dbname...]] > filename.sql????????使用--databases參數之后，必須指定至少一個數據庫的名稱，多個數據庫名稱之間用空格隔開。
-
案例：使用MySQLdump備份booksDB和test_db數據庫，輸入語句如下：
另外，使用--all-databases參數可以備份系統中所有的數據庫，語句如下：
mysqldump -u root -p --all-databases > /home/mysql_back/alldbinMySQL.sql?使用參數--all-databases時，不需要指定數據庫名稱。
二、數據恢復
1.使用MySQL命令恢復
????????對于已經備份的包含CREATE、INSERT語句的文本文件，可以使用MySQL命令導入到數據庫中。本此使用MySQL命令導入sql文件的方法。
????????備份的sql文件中包含CREATE、INSERT語句（有時也會有DROP語句）。MySQL命令可以直接執行文件中的這些語句。其語法如下：
mysql –u user –p [dbname] < filename.sql????????user是執行backup.sql中語句的用戶名；-p表示輸入用戶密碼；dbname是數據庫名。如果filename.sql文件為MySQLdump工具創建的包含創建數據庫語句的文件，執行的時候不需要指定數據庫名。
-
案例：使用MySQL命令將C:\backup\booksdb_20190301.sql文件中的備份導入到數據庫中，輸入語句如下：
????????執行該語句前，必須先在MySQL服務器中創建booksDB數據庫，如果不存在恢復過程將會出錯。命令執行成功之后bookdb_20221016.sql文件中的語句就會在指定的數據庫中恢復以前的表。
如果已經登錄連接MySQL服務器，還可以使用source命令導入sql文件。source語句語法如下：
source filename-
案例：使用root用戶登錄到服務器，然后使用source導入本地的備份文件bookdb_20221016.sql，輸入語句如下：
????????命令執行后，會列出備份文件bookdb_20221016.sql中每一條語句的執行結果。source命令執行成功后，bookdb_20221016.sql中的語句會全部導入到現有數據庫中。
執行source命令前，必須使用use語句選擇數據庫。不然，恢復過程中會出現“ERROR 1046 (3D000): No database selected”的錯誤。
三、表的導出和導入
1.使用MySQLdump命令導出文本文件
????????前面介紹了使用MySQLdump備份數據庫，該工具不僅可以將數據導出為包含CREATE、INSERT的sql文件，也可以導出為純文本文件。MySQLdump創建一個包含創建表的CREATE TABLE語句的tablename.sql文件和一個包含其數據的tablename.txt文件。MySQLdump導出文本文件的基本語法格式如下：
mysqldump -T path -u root -p dbname [tables] [OPTIONS]--OPTIONS選項 --fields-terminated-by=value --fields-enclosed-by=value --fields-optionally-enclosed-by=value --fields-escaped-by=value --lines-terminated-by=value????????只有指定了-T參數才可以導出純文本文件；path表示導出數據的目錄；tables為指定要導出的表名稱，如果不指定，將導出數據庫dbname中所有的表；[OPTIONS]為可選參數選項，這些選項需要結合-T選項使用。
使用OPTIONS常見的取值有：
- --fields-terminated-by=value：設置字段之間的分隔字符，可以為單個或多個字符，默認情況下為制表符“\t”。
- --fields-enclosed-by=value：設置字段的包圍字符。
- --fields-optionally-enclosed-by=value：設置字段的包圍字符，只能為單個字符，只能包括CHAR和VERCHAR等字符數據字段。
- --fields-escaped-by=value：控制如何寫入或讀取特殊字符，只能為單個字符，即設置轉義字符，默認值為反斜線“\”。
- --lines-terminated-by=value：設置每行數據結尾的字符，可以為單個或多個字符，默認值為“\n”。
-
案例：使用MySQLdump將test_db數據庫person表中的記錄導出到文本文件，執行的命令如下：?
????????語句執行成功，/home/mysql_back/目錄下面將會有兩個文件，分別為person.sql和person.txt。person.sql包含創建person表的CREATE語句；person.txt包含數據包中的數據，其內容如下：
-
案例：使用MySQLdump命令將test_db數據庫person表中的記錄導出到文本文件，使用FIELDS選項，要求字段之間使用逗號“,”間隔，所有字符類型字段值用雙引號括起來，定義轉義字符為問號“?”，每行記錄以回車換行符“\r\n”結尾，執行的命令如下：?
????????上面語句要在一行中輸入，語句執行成功，/home/backup/目錄下面將會有兩個文件，分別為person.sql和person.txt。person.sql包含創建person表的CREATE語句，其內容與前面例子中的相同，person.txt文件的內容與上一個例子不同，顯示如下：
1,"Green",21,"Lawyer"2,"Suse",22,"dancer"3,"Mary",24,"Musician"4,"Willam",20,"sports man"5,"Laura",25,?N6,"Evans",27,"secretary"7,"Dale",22,"cook"8,"Edison",28,"singer"9,"Harry",21,"magician"10,"Harriet",19,"pianist"????????可以看到，只有字符類型的值被雙引號括了起來，而數值類型的值沒有；第5行記錄中的NULL值表示為“?N”，使用問號“?”替代了系統默認的反斜線轉義字符“\”。
2.使用MySQLimport命令導入文本文件
????????使用MySQLimport可以導入文本文件，并且不需要登錄MySQL客戶端。使用MySQLimport語句需要指定所需的選項、導入的數據庫名稱以及導入的數據文件的路徑和名稱。MySQLimport命令的基本語法格式如下：
mysqlimport -u root-p dbname filename.txt [OPTIONS]--OPTIONS選項 --fields-terminated-by=value --fields-enclosed-by=value --fields-optionally-enclosed-by=value --fields-escaped-by=value --lines-terminated-by=value --ignore-lines=n????????dbname為導入的表所在的數據庫名稱。注意，MySQLimport命令不指定導入數據庫的表名稱，數據表的名稱由導入文件名稱確定，即文件名作為表名，導入數據之前該表必須存在。
[OPTIONS]為可選參數選項，其常見的取值有：
- ?--fields-terminated-by= 'value'：設置字段之間的分隔字符，可以為單個或多個字符，默認情況下為制表符“\t”。
- ?--fields-enclosed-by= 'value'：設置字段的包圍字符。
- ?--fields-optionally-enclosed-by= 'value'：設置字段的包圍字符，只能為單個字符，包括CHAR和VERCHAR等字符數據字段。
- ?--fields-escaped-by= 'value'：控制如何寫入或讀取特殊字符，只能為單個字符，即設置轉義字符，默認值為反斜線“\”。
- ?--lines-terminated-by= 'value'：設置每行數據結尾的字符，可以為單個或多個字符，默認值為“\n”。
- ?--ignore-lines=n：忽視數據文件的前n行。
-
案例：使用MySQLimport命令將D盤目錄下的person.txt文件內容導入到test_db數據庫中，字段之間使用逗號“,”間隔，字符類型字段值用雙引號括起來，將轉義字符定義為問號“?”，每行記錄以回車換行符“\r\n”結尾，執行的命令如下：?
上面的語句要在一行中輸入，語句執行成功，將把person.txt中的數據導入到數據庫。
總結
以上是生活随笔為你收集整理的MySQL 数据备份与恢复的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 新疆农业大学计算机考研科目,2021新疆
- 下一篇： 计算机文化进制的转换,计算机文化根本基础