這篇“MySQL數(shù)據(jù)庫如何備份與恢復”文章的知識點大部分人都不太理解，所以小編給大家總結了以下內容，內容詳細，步驟清晰，具有一定的借鑒價值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“MySQL數(shù)據(jù)庫如何備份與恢復”文章吧。
員工經(jīng)過長期磨合與沉淀，具備了協(xié)作精神，得以通過團隊的力量開發(fā)出優(yōu)質的產(chǎn)品。創(chuàng)新互聯(lián)堅持“專注、創(chuàng)新、易用”的產(chǎn)品理念，因為“專注所以專業(yè)、創(chuàng)新互聯(lián)網(wǎng)站所以易用所以簡單”。公司專注于為企業(yè)提供做網(wǎng)站、成都網(wǎng)站設計、微信公眾號開發(fā)、電商網(wǎng)站開發(fā)，小程序設計，軟件按需定制等一站式互聯(lián)網(wǎng)企業(yè)服務。
在任何數(shù)據(jù)庫環(huán)境中，總會有不確定的意外情況發(fā)生，比如例外的停電、計算機系統(tǒng)中的各種軟硬件故障、人為破壞、管理員誤操作等是不可避免的，這些情況可能會導致數(shù)據(jù)的丟失、服務器癱瘓等嚴重的后果。存在多個服務器時，會出現(xiàn)主從服務器之間的數(shù)據(jù)同步問題。
為了有效防止數(shù)據(jù)丟失，并將損失降到最低，應定期對MySQL數(shù)據(jù)庫服務器做備份。如果數(shù)據(jù)庫中的數(shù)據(jù)丟失或者出現(xiàn)錯誤，可以使用備份的數(shù)據(jù)進行恢復。主從服務器之間的數(shù)據(jù)同步問題可以通過復制功能實現(xiàn)。
物理備份：備份數(shù)據(jù)文件，轉儲數(shù)據(jù)庫物理文件到某一目錄。物理備份恢復速度比較快，但占用空間比較大，MySQL中可以用xtrabackup工具來進行物理備份。
邏輯備份：對數(shù)據(jù)庫對象利用工具進行導出工作，匯總入備份文件內。邏輯備份恢復速度慢，但占用空間小，更靈活。MySQL 中常用的邏輯備份工具為mysqldump。邏輯備份就是備份sql語句，在恢復的時候執(zhí)行備份的sql語句實現(xiàn)數(shù)據(jù)庫數(shù)據(jù)的重現(xiàn)。
mysqldump是MySQL提供的一個非常有用的數(shù)據(jù)庫備份工具。
mysqldump命令執(zhí)行時，可以將數(shù)據(jù)庫備份成一個文本文件，該文件中實際上包含多個CREATE和INSERT語句，使用這些語句可以重新創(chuàng)建表和插入數(shù)據(jù)。
查出需要備份的表的結構，在文本文件中生成一個CREATE語句
將表中的所有記錄轉換成一條INSERT語句。
基本語法：
mysqldump –u 用戶名稱 –h 主機名稱 –p密碼 待備份的數(shù)據(jù)庫名稱[tbname, [tbname...]]> 備份文件名稱.sql
舉例：使用root用戶備份atguigu數(shù)據(jù)庫：
mysqldump -uroot -p atguigu>atguigu.sql #備份文件存儲在當前目錄下
mysqldump -uroot -p atguigudb1 > /var/lib/mysql/atguigu.sql
備份文件剖析：
-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64) -- -- Host: localhost Database: atguigu -- ------------------------------------------------------ -- Server version 8.0.26 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Current Database: `atguigu` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `atguigu` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */; USE `atguigu`; -- -- Table structure for table `student` -- DROP TABLE IF EXISTS `student`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `student` ( `studentno` int NOT NULL, `name` varchar(20) DEFAULT NULL, `class` varchar(20) DEFAULT NULL, PRIMARY KEY (`studentno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; /*!40101 SET character_set_client = @saved_cs_client */; INSERT INTO `student` VALUES (1,'張三_back','一班'),(3,'李四','一班'),(8,'王五','二班'), (15,'趙六','二班'),(20,'錢七','>三班'),(22,'zhang3_update','1ban'),(24,'wang5','2ban'); /*!40000 ALTER TABLE `student` ENABLE KEYS */; UNLOCK TABLES; . . . . /*!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語句的注釋；
以/* !開頭、*/結尾的語句為可執(zhí)行的MySQL注釋，這些語句可以被MySQL執(zhí)行，但在其他數(shù)據(jù)庫管理系統(tǒng)中被作為注釋忽略，這可以提高數(shù)據(jù)庫的可移植性；
文件開頭指明了備份文件使用的MySQLdump工具的版本號；接下來是備份賬戶的名稱和主機信息，以及備份的數(shù)據(jù)庫的名稱；最后是MySQL服務器的版本號，在這里為8.0.26。
備份文件接下來的部分是一些SET語句，這些語句將一些系統(tǒng)變量值賦給用戶定義變量，以確保被恢復的數(shù)據(jù)庫的系統(tǒng)變量和原來備份時的變量相同，例如：
備份文件的最后幾行MySQL使用SET語句恢復服務器系統(tǒng)變量原來的值，例如：
后面的DROP語句、CREATE語句和INSERT語句都是還原時使用的。例如，DROPTABLE IF EXISTS 'student'語句用來判斷數(shù)據(jù)庫中是否還有名為student的表，如果存在，就刪除這個表；CREATE語句用來創(chuàng)建student的表； INSERT語句用來還原數(shù)據(jù)。
備份文件開始的一些語句以數(shù)字開頭。這些數(shù)字代表了MySQL版本號，告訴我們這些語句只有在制定的MySQL版本或者比該版本高的情況下才能執(zhí)行。例如，40101表明這些語句只有在MySQL版本號為4.01.01或者更高的條件下才可以被執(zhí)行。文件的最后記錄了備份的時間。
若想用mysqldump備份整個實例，可以使用--all-databases或-A參數(shù)：
mysqldump -uroot -pxxxxxx --all-databases > all_database.sql mysqldump -uroot -pxxxxxx -A > all_database.sql
使用--databases或-B參數(shù)了，該參數(shù)后面跟數(shù)據(jù)庫名稱，多個數(shù)據(jù)庫間用空格隔開。如果指定databases參數(shù)，備份文件中會存在創(chuàng)建數(shù)據(jù)庫的語句，如果不指定參數(shù)，則不存在。語法如下:
mysqldump –u user –h host –p --databases [數(shù)據(jù)庫的名稱1 [數(shù)據(jù)庫的名稱2...]] > 備份文件名稱.sql
舉例
mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql
或
mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql
比如，在表變更前做個備份。語法如下：
mysqldump –u user –h host –p 數(shù)據(jù)庫的名稱 [表名1 [表名2...]] > 備份文件名稱.sql
舉例：備份atguigu數(shù)據(jù)庫下的book表
mysqldump -uroot -p atguigu book> book.sql#備份多張表 mysqldump -uroot -p atguigu book account > 2_tables_bak.sql
book.sql文件內容如下
mysqldump -uroot -p atguigu book> book.sql^C [root@node1 ~]# ls kk kubekey kubekey-v1.1.1-linux-amd64.tar.gz README.md test1.sql two_database.sql [root@node1 ~]# mysqldump -uroot -p atguigu book> book.sql Enter password: [root@node1 ~]# ls book.sql kk kubekey kubekey-v1.1.1-linux-amd64.tar.gz README.md test1.sql two_database.sql [root@node1 ~]# vi book.sql -- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64) -- -- Host: localhost Database: atguigu -- ------------------------------------------------------ -- Server version 8.0.26 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `book` -- DROP TABLE IF EXISTS `book`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `book` ( `bookid` int unsigned NOT NULL AUTO_INCREMENT, `card` int unsigned NOT NULL, `test` varchar(255) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`bookid`), KEY `Y` (`card`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `book` -- LOCK TABLES `book` WRITE; /*!40000 ALTER TABLE `book` DISABLE KEYS */; INSERT INTO `book` VALUES (1,9,NULL),(2,10,NULL),(3,4,NULL),(4,8,NULL),(5,7,NULL), (6,10,NULL),(7,11,NULL),(8,3,NULL),(9,1,NULL),(10,17,NULL),(11,19,NULL),(12,4,NULL), (13,1,NULL),(14,14,NULL),(15,5,NULL),(16,5,NULL),(17,8,NULL),(18,3,NULL),(19,12,NULL), (20,11,NULL),(21,9,NULL),(22,20,NULL),(23,13,NULL),(24,3,NULL),(25,18,NULL), (26,20,NULL),(27,5,NULL),(28,6,NULL),(29,15,NULL),(30,15,NULL),(31,12,NULL), (32,11,NULL),(33,20,NULL),(34,5,NULL),(35,4,NULL),(36,6,NULL),(37,17,NULL), (38,5,NULL),(39,16,NULL),(40,6,NULL),(41,18,NULL),(42,12,NULL),(43,6,NULL), (44,12,NULL),(45,2,NULL),(46,12,NULL),(47,15,NULL),(48,17,NULL),(49,2,NULL), (50,16,NULL),(51,13,NULL),(52,17,NULL),(53,7,NULL),(54,2,NULL),(55,9,NULL), (56,1,NULL),(57,14,NULL),(58,7,NULL),(59,15,NULL),(60,12,NULL),(61,13,NULL), (62,8,NULL),(63,2,NULL),(64,6,NULL),(65,2,NULL),(66,12,NULL),(67,12,NULL),(68,4,NULL), (69,5,NULL),(70,10,NULL),(71,16,NULL),(72,8,NULL),(73,14,NULL),(74,5,NULL), (75,4,NULL),(76,3,NULL),(77,2,NULL),(78,2,NULL),(79,2,NULL),(80,3,NULL),(81,8,NULL), (82,14,NULL),(83,5,NULL),(84,4,NULL),(85,2,NULL),(86,20,NULL),(87,12,NULL), (88,1,NULL),(89,8,NULL),(90,18,NULL),(91,3,NULL),(92,3,NULL),(93,6,NULL),(94,1,NULL), (95,4,NULL),(96,17,NULL),(97,15,NULL),(98,1,NULL),(99,20,NULL),(100,15,NULL); /*!40000 ALTER TABLE `book` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
可以看到， book文件和備份的庫文件類似。不同的是，book文件只包含book表的DROP、CREATE和INSERT語句。
備份多張表使用下面的命令，比如備份book和account表：
#備份多張表mysqldump -uroot -p atguigu book account > 2_tables_bak.sql
有些時候一張表的數(shù)據(jù)量很大，我們只需要部分數(shù)據(jù)。這時就可以使用--where選項了。where后面附帶需要滿足的條件。
舉例：備份student表中id小于10的數(shù)據(jù)：
mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql
內容如下所示，insert語句只有id小于10的部分
LOCK TABLES `student` WRITE; /*!40000 ALTER TABLE `student` DISABLE KEYS */; INSERT INTO `student` VALUES (1,100002,'JugxTY',157,280),(2,100003,'QyUcCJ',251,277), (3,100004,'lATUPp',80,404),(4,100005,'BmFsXI',240,171),(5,100006,'mkpSwJ',388,476), (6,100007,'ujMgwN',259,124),(7,100008,'HBJTqX',429,168),(8,100009,'dvQSQA',61,504), (9,100010,'HljpVJ',234,185);
如果我們想備份某個庫，但是某些表數(shù)據(jù)量很大或者與業(yè)務關聯(lián)不大，這個時候可以考慮排除掉這些表，同樣的，選項--ignore-table可以完成這個功能。
mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql
通過如下指定判定文件中沒有student表結構：
grep "student" no_stu_bak.sql
只備份結構的話可以使用--no-data簡寫為--d選項；只備份數(shù)據(jù)可以使用--no-create-info簡寫為--t選項。
只備份結構
mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql #使用grep命令，沒有找到insert相關語句,表示沒有數(shù)據(jù)備份。 [root@node1 ~]# grep "INSERT" atguigu_no_data_bak.sql [root@node1 ~]#
只備份數(shù)據(jù)
mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql #使用grep命令，沒有找到insert相關語句，表示沒有數(shù)據(jù)備份。 [root@node1 ~]# grep "INSERT" atguigu_no_data_bak.sql [root@node1 ~]#
只備份數(shù)據(jù)
mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql #使用grep命令，沒有找到create相關語句，表示沒有數(shù)據(jù)結構。 [root@node1 ~]# grep "CREATE" atguigu_no_create_info_bak.sql [root@node1 ~]#
mysqldump備份默認是不包含存儲過程，自定義函數(shù)及事件的?？梢允褂?code>--routines或-R選項來備份存儲過程及函數(shù)，使用--events或-E參數(shù)來備份事件。
舉例：備份整個atguigu庫，包含存儲過程及事件：
使用下面的SQL可以查看當前庫有哪些存儲過程或者函數(shù)
mysql> SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA FROM information_schema.Routines WHERE ROUTINE_SCHEMA="atguigu"; +---------------+--------------+----------------+ | SPECIFIC_NAME | ROUTINE_TYPE | ROUTINE_SCHEMA | +---------------+--------------+----------------+ | rand_num | FUNCTION | atguigu | | rand_string | FUNCTION | atguigu | | BatchInsert | PROCEDURE | atguigu | | insert_class | PROCEDURE | atguigu | | insert_order | PROCEDURE | atguigu | | insert_stu | PROCEDURE | atguigu | | insert_user | PROCEDURE | atguigu | | ts_insert | PROCEDURE | atguigu | +---------------+--------------+----------------+ 9 rows in set (0.02 sec)
下面?zhèn)浞輆tguigu庫的數(shù)據(jù)，函數(shù)以及存儲過程。
mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql
查詢備份文件中是否存在函數(shù)，如下所示，可以看到確實包含了函數(shù)。
重慶分公司
重慶分公司
grep -C 5 "rand_num" fun_atguigu_bak.sql
--
--
-- Dumping routines for database 'atguigu'--
/*!50003 DROP FUNCTION IF EXISTS `rand_num` */;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = utf8mb3 */ ;/*!50003 SET character_set_results = utf8mb3 */ ;/*!50003 SET collation_connection = utf8_general_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode ='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_pISIO
N_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;DELIMITER ;;CREATE DEFINER=`root`@`%` FUNCTION `rand_num`(from_num BIGINT ,to_num BIGINT) RETURNS
bigint
BEGIN
DECLARE i BIGINT DEFAULT 0;SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;RETURN i;END ;;--
BEGIN
DECLARE i INT DEFAULT 0;SET autocommit = 0;REPEAT
SET i = i + 1;INSERT INTO class ( classname,address,monitor ) VALUES(rand_string(8),rand_string(10),rand_num());UNTIL i = max_num
END REPEAT;COMMIT;END ;;DELIMITER ;--
BEGIN
DECLARE i INT DEFAULT 0;SET autocommit = 0; #設置手動提交事務REPEAT #循環(huán)SET i = i + 1; #賦值INSERT INTO order_test (order_id, trans_id ) VALUES(rand_num(1,7000000),rand_num(100000000000000000,700000000000000000));UNTIL i = max_num
END REPEAT;COMMIT; #提交事務END ;;DELIMITER ;--
BEGIN
DECLARE i INT DEFAULT 0;SET autocommit = 0; #設置手動提交事務REPEAT #循環(huán)SET i = i + 1; #賦值INSERT INTO student (stuno, name ,age ,classId ) VALUES((START+i),rand_string(6),rand_num(),rand_num());UNTIL i = max_num
END REPEAT;COMMIT; #提交事務END ;;DELIMITER ;--
BEGIN
DECLARE i INT DEFAULT 0;SET autocommit = 0;REPEAT
SET i = i + 1;INSERT INTO `user` ( name,age,sex ) VALUES ("atguigu",rand_num(1,20),"male");UNTIL i = max_num
END REPEAT;COMMIT;END ;;DELIMITER ;使用mysqldump命令將數(shù)據(jù)庫中的數(shù)據(jù)備份成一個文本文件。需要恢復時，可以使用mysql命令來恢復備份的數(shù)據(jù)。
mysql命令可以執(zhí)行備份文件中的CREATE語句和INSERT語句。通過CREATE語句來創(chuàng)建數(shù)據(jù)庫和表。通過INSERT語句來插入備份的數(shù)據(jù)。
基本語法：
mysql –u root –p [dbname] < backup.sql
其中，dbname參數(shù)表示數(shù)據(jù)庫名稱。該參數(shù)是可選參數(shù)，可以指定數(shù)據(jù)庫名，也可以不指定。指定數(shù)據(jù)庫名時，表示還原該數(shù)據(jù)庫下的表。此時需要確保MySQL服務器中已經(jīng)創(chuàng)建了該名的數(shù)據(jù)庫。不指定數(shù)據(jù)庫名時，表示還原文件中所有的數(shù)據(jù)庫。此時sql文件中包含有CREATE DATABASE語句，不需要MysQL服務器中已存在這些數(shù)據(jù)庫。
使用root用戶，將之前練習中備份的atguigu.sql文件中的備份導入數(shù)據(jù)庫中，命令如下：
如果備份文件中包含了創(chuàng)建數(shù)據(jù)庫的語句，則恢復的時候不需要指定數(shù)據(jù)庫名稱，如下所示
#備份文件中包含了創(chuàng)建數(shù)據(jù)庫的語句mysql -uroot -p < atguigu.sql
否則需要指定數(shù)據(jù)庫名稱，如下所示
#備份文件中不包含了創(chuàng)建數(shù)據(jù)庫的語句mysql -uroot -p atguigu4< atguigu.sql
如果我們現(xiàn)在有昨天的全量備份，現(xiàn)在想整個恢復，則可以這樣操作：
mysql –u root –p < all.sql
mysql -uroot -pxxxxxx < all.sql
執(zhí)行完后，MySQL數(shù)據(jù)庫中就已經(jīng)恢復了all.sql文件中的所有數(shù)據(jù)庫。
補充：
如果使用--all-databases參數(shù)備份了所有的數(shù)據(jù)庫，那么恢復時不需要指定數(shù)據(jù)庫。對應的sql文件包含有CREATE DATABASE語句，可通過該語句創(chuàng)建數(shù)據(jù)庫。創(chuàng)建數(shù)據(jù)庫后，可以執(zhí)行sql文件中的USE語句選擇數(shù)據(jù)庫，再創(chuàng)建表并插入記錄。
可能有這樣的需求，比如說我們只想恢復某一個庫，但是我們有的是整個實例的備份，這個時候我們可以從全量備份中分離出單個庫的備份。
舉例：
sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql #分離完成后我們再導入atguigu.sql即可恢復單個庫
這個需求還是比較常見的。比如說我們知道哪個表誤操作了，那么就可以用單表恢復的方式來恢復。
舉例：我們有atguigu整庫的備份，但是由于class表誤操作，需要單獨恢復出這張表。
cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql
cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql
#用shell語法分離出創(chuàng)建表的語句及插入數(shù)據(jù)的語句后 再依次導出即可完成恢復
use atguigu;
mysql> source class_structure.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> source class_data.sql;
Query OK, 1 row affected (0.01 sec)直接將MySQL中的數(shù)據(jù)庫文件復制出來。這種方法最簡單，速度也最快。MySQL的數(shù)據(jù)庫目錄位置不一定相同：
在Windows平臺下，MySQL 8.0存放數(shù)據(jù)庫的目錄通常默認為 C:\ProgramData\MySQL\MySQL Server 8.0\Data或者其他用戶自定義目錄；
在Linux平臺下，數(shù)據(jù)庫目錄位置通常為/var/lib/mysql/；
在MAC OSX平臺下，數(shù)據(jù)庫目錄位置通常為“/usr/local/mysql/data”
但為了保證備份的一致性。需要保證：
方式1：備份前，將服務器停止。
方式2：備份前，對相關表執(zhí)行FLUSH TABLES WITH READ LOCK操作。這樣當復制數(shù)據(jù)庫目錄中的文件時，允許其他客戶繼續(xù)查詢表。同時，F(xiàn)LUSH TABLES語句來確保開始備份前將所有激活的索引頁寫入硬盤。
這種方式方便、快速，但不是最好的備份方法，因為實際情況可能不允許停止MySQL服務器或者鎖住表，而且這種方法對InnoDB存儲引擎的表不適用。對于MyISAM存儲引擎的表，這樣備份和還原很方便，但是還原時最好是相同版本的MySQL數(shù)據(jù)庫，否則可能會存在文件類型不同的情況。
注意，物理備份完畢后，執(zhí)行UNLOCK TABLES來結算其他客戶對表的修改行為。
說明： 在MySQL版本號中，第一個數(shù)字表示主版本號，主版本號相同的MySQL數(shù)據(jù)庫文件格式相同。
此外，還可以考慮使用相關工具實現(xiàn)備份。比如，MySQLhotcopy工具。MySQLhotcopy是一個Perl腳本，它使用LOCK TABLES、FLUSH TABLES和cp或scp來快速備份數(shù)據(jù)庫。它是備份數(shù)據(jù)庫或單個表最快的途徑，但它只能運行在數(shù)據(jù)庫目錄所在的機器上，并且只能備份MyISAM類型的表。多用于mysql5.5之前。
步驟：
演示刪除備份的數(shù)據(jù)庫中指定表的數(shù)據(jù)
將備份的數(shù)據(jù)庫數(shù)據(jù)拷貝到數(shù)據(jù)目錄下，并重啟MySQL服務器
查詢相關表的數(shù)據(jù)是否恢復。需要使用下面的 chown 操作。
要求：
必須確保備份數(shù)據(jù)的數(shù)據(jù)庫和待恢復的數(shù)據(jù)庫服務器的主版本號相同。
因為只有MySQL數(shù)據(jù)庫主版本號相同時，才能保證這兩個MySQL數(shù)據(jù)庫文件類型是相同的。
這種方式對MyISAM類型的表比較有效，對于InnoDB類型的表則不可用。
因為InnoDB表的表空間不能直接復制。
在Linux操作系統(tǒng)下，復制到數(shù)據(jù)庫目錄后，一定要將數(shù)據(jù)庫的用戶和組變成mysql，命令如下：
chown -R mysql.mysql /var/lib/mysql/dbname
其中，兩個mysql分別表示組和用戶；“-R”參數(shù)可以改變文件夾下的所有子文件的用戶和組；“dbname”參數(shù)表示數(shù)據(jù)庫目錄。
提示 Linux操作系統(tǒng)下的權限設置非常嚴格。通常情況下，MySQL數(shù)據(jù)庫只有root用戶和mysql用戶組下的mysql用戶才可以訪問，因此將數(shù)據(jù)庫目錄復制到指定文件夾后，一定要使用chown命令將文件夾的用戶組變?yōu)閙ysql，將用戶變?yōu)閙ysql。
1. 使用SELECT…INTO OUTFILE導出文本文件
在MySQL中，可以使用SELECT…INTO OUTFILE語句將表的內容導出成一個文本文件。
舉例：使用SELECT…INTO OUTFILE將atguigu數(shù)據(jù)庫中account表中的記錄導出到文本文件。
（1）選擇數(shù)據(jù)庫atguigu，并查詢account表，執(zhí)行結果如下所示。
use atguigu; select * from account; mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 張三 | 90 | | 2 | 李四 | 100 | | 3 | 王五 | 0 | +----+--------+---------+ 3 rows in set (0.01 sec)
（2）mysql默認對導出的目錄有權限限制，也就是說使用命令行進行導出的時候，需要指定目錄進行操作。
查詢secure_file_priv值：
mysql> SHOW GLOBAL VARIABLES LIKE '%secure%'; +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | OFF | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ 2 rows in set (0.02 sec)
參數(shù)secure_file_priv的可選值和作用分別是：
如果設置為empty，表示不限制文件生成的位置，這是不安全的設置；
如果設置為一個表示路徑的字符串，就要求生成的文件只能放在這個指定的目錄，或者它的子目錄；
如果設置為NULL，就表示禁止在這個MySQL實例上執(zhí)行select … into outfile操作。
（3）上面結果中顯示，secure_file_priv變量的值為/var/lib/mysql-files/，導出目錄設置為該目錄，SQL語句如下。
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";
（4）查看 /var/lib/mysql-files/account.txt`文件。
1 張三 902 李四 1003 王五 0
2. 使用mysqldump命令導出文本文件
舉例1：使用mysqldump命令將將atguigu數(shù)據(jù)庫中account表中的記錄導出到文本文件：
mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account
mysqldump命令執(zhí)行完畢后，在指定的目錄/var/lib/mysql-files/下生成了account.sql和account.txt文件。
打開account.sql文件，其內容包含創(chuàng)建account表的CREATE語句。
[root@node1 mysql-files]# cat account.sql -- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64) -- -- Host: localhost Database: atguigu -- ------------------------------------------------------ -- Server version 8.0.26 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `account` -- DROP TABLE IF EXISTS `account`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `account` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `balance` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3; /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!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 23:19:27
打開account.txt文件，其內容只包含account表中的數(shù)據(jù)。
[root@node1 mysql-files]# cat account.txt1 張三 902 李四 1003 王五 0
舉例2：使用mysqldump將atguigu數(shù)據(jù)庫中的account表導出到文本文件，使用FIELDS選項，要求字段之間使用逗號“，”間隔，所有字符類型字段值用雙引號括起來：
mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminatedby=',' --fields-optionally-enclosed-by='\"'
語句mysqldump語句執(zhí)行成功之后，指定目錄下會出現(xiàn)兩個文件account.sql和account.txt。
打開account.sql文件，其內容包含創(chuàng)建account表的CREATE語句。
[root@node1 mysql-files]# cat account.sql -- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64) -- -- Host: localhost Database: atguigu -- ------------------------------------------------------ -- Server version 8.0.26 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `account` -- DROP TABLE IF EXISTS `account`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `account` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `balance` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3; /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!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 23:36:39
打開account.txt文件，其內容包含創(chuàng)建account表的數(shù)據(jù)。從文件中可以看出，字段之間用逗號隔開，字符類型的值被雙引號括起來。
[root@node1 mysql-files]# cat account.txt1,"張三",902,"李四",1003,"王五",0
3. 使用mysql命令導出文本文件
舉例1：使用mysql語句導出atguigu數(shù)據(jù)中account表中的記錄到文本文件：
mysql -uroot -p --execute="SELECT * FROM account;" atguigu> "/var/lib/mysqlfiles/account.txt"
打開account.txt文件，其內容包含創(chuàng)建account表的數(shù)據(jù)。
[root@node1 mysql-files]# cat account.txtid name balance1 張三 902 李四 1003 王五 0
舉例2：將atguigu數(shù)據(jù)庫account表中的記錄導出到文本文件，使用–veritcal參數(shù)將該條件記錄分為多行顯示：
mysql -uroot -p --vertical --execute="SELECT * FROM account;" atguigu >"/var/lib/mysql-files/account_1.txt"
打開account_1.txt文件，其內容包含創(chuàng)建account表的數(shù)據(jù)。
[root@node1 mysql-files]# cat account_1.txt*************************** 1. row *************************** id: 1name: 張三 balance: 90*************************** 2. row *************************** id: 2name: 李四 balance: 100*************************** 3. row *************************** id: 3name: 王五 balance: 0
舉例3：將atguigu數(shù)據(jù)庫account表中的記錄導出到xml文件，使用–xml參數(shù)，具體語句如下。
mysql -uroot -p --xml --execute="SELECT * FROM account;" atguigu>"/var/lib/mysqlfiles/account_3.xml"
