一文搞懂MySQL數據庫怎么備份與恢復

本篇文章給大家帶來了關于mysql的相關知識，其中主要介紹了關于數據庫備份與恢復的相關問題，包括了mysqldump實現邏輯備份、mysql命令恢復數據、物理備份以及物理恢復等等內容，下面一起來看一下，希望對大家有幫助。

推薦學習：mysql視頻教程

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

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

一、物理備份與邏輯備份

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

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

二、mysqldump實現邏輯備份

mysqldump是MySQL提供的一個非常有用的數據庫備份工具。

2.1 備份一個數據庫

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

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

基本語法：

mysqldump –u 用戶名稱 –h 主機名稱 –p密碼 待備份的數據庫名稱[tbname, [tbname...]]> 備份文件名稱.sql

舉例：使用root用戶備份atguigu數據庫：

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語句的注釋；
• 以/* !開頭、*/結尾的語句為可執行的MySQL注釋，這些語句可以被MySQL執行，但在其他數據庫管理系統中被作為注釋忽略，這可以提高數據庫的可移植性；
• 文件開頭指明了備份文件使用的MySQLdump工具的版本號；接下來是備份賬戶的名稱和主機信息，以及備份的數據庫的名稱；最后是MySQL服務器的版本號，在這里為8.0.26。
• 備份文件接下來的部分是一些SET語句，這些語句將一些系統變量值賦給用戶定義變量，以確保被恢復的數據庫的系統變量和原來備份時的變量相同，例如：
  
• 備份文件的最后幾行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

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

2.5 備份單表的部分數據

有些時候一張表的數據量很大，我們只需要部分數據。這時就可以使用--where選項了。where后面附帶需要滿足的條件。

舉例：備份student表中id小于10的數據：

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);

2.6 排除某些表的備份

如果我們想備份某個庫，但是某些表數據量很大或者與業務關聯不大，這個時候可以考慮排除掉這些表，同樣的，選項--ignore-table可以完成這個功能。

mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql

通過如下指定判定文件中沒有student表結構：

grep "student" no_stu_bak.sql

2.7 只備份結構或只備份數據

只備份結構的話可以使用--no-data簡寫為--d選項；只備份數據可以使用--no-create-info簡寫為--t選項。

• 只備份結構

mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql #使用grep命令，沒有找到insert相關語句,表示沒有數據備份。 [root@node1 ~]# grep "INSERT" atguigu_no_data_bak.sql [root@node1 ~]#

• 只備份數據

mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql #使用grep命令，沒有找到insert相關語句，表示沒有數據備份。 [root@node1 ~]# grep "INSERT" atguigu_no_data_bak.sql [root@node1 ~]#

• 只備份數據

mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql #使用grep命令，沒有找到create相關語句，表示沒有數據結構。 [root@node1 ~]# grep "CREATE" atguigu_no_create_info_bak.sql [root@node1 ~]#

2.8 備份中包含存儲過程、函數、事件

mysqldump備份默認是不包含存儲過程，自定義函數及事件的。可以使用--routines或-R選項來備份存儲過程及函數，使用--events或-E參數來備份事件。

舉例：備份整個atguigu庫，包含存儲過程及事件：

• 使用下面的SQL可以查看當前庫有哪些存儲過程或者函數

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)

下面備份atguigu庫的數據，函數以及存儲過程。

mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql

查詢備份文件中是否存在函數，如下所示，可以看到確實包含了函數。

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 #循環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 #循環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 ;

三、mysql命令恢復數據

使用mysqldump命令將數據庫中的數據備份成一個文本文件。需要恢復時，可以使用mysql命令來恢復備份的數據。

mysql命令可以執行備份文件中的CREATE語句和INSERT語句。通過CREATE語句來創建數據庫和表。通過INSERT語句來插入備份的數據。

基本語法：

mysql –u root –p [dbname] < backup.sql

其中，dbname參數表示數據庫名稱。該參數是可選參數，可以指定數據庫名，也可以不指定。指定數據庫名時，表示還原該數據庫下的表。此時需要確保MySQL服務器中已經創建了該名的數據庫。不指定數據庫名時，表示還原文件中所有的數據庫。此時sql文件中包含有CREATE DATABASE語句，不需要MysQL服務器中已存在這些數據庫。

3.1 單庫備份中恢復單庫

使用root用戶，將之前練習中備份的atguigu.sql文件中的備份導入數據庫中，命令如下：

如果備份文件中包含了創建數據庫的語句，則恢復的時候不需要指定數據庫名稱，如下所示

#備份文件中包含了創建數據庫的語句mysql -uroot -p < atguigu.sql

否則需要指定數據庫名稱，如下所示

#備份文件中不包含了創建數據庫的語句mysql -uroot -p atguigu4< atguigu.sql

3.2 全量備份恢復

如果我們現在有昨天的全量備份，現在想整個恢復，則可以這樣操作：

mysql –u root –p < all.sql

mysql -uroot -pxxxxxx < all.sql

執行完后，MySQL數據庫中就已經恢復了all.sql文件中的所有數據庫。

補充：
如果使用--all-databases參數備份了所有的數據庫，那么恢復時不需要指定數據庫。對應的sql文件包含有CREATE DATABASE語句，可通過該語句創建數據庫。創建數據庫后，可以執行sql文件中的USE語句選擇數據庫，再創建表并插入記錄。

3.3 從全量備份中恢復單庫

可能有這樣的需求，比如說我們只想恢復某一個庫，但是我們有的是整個實例的備份，這個時候我們可以從全量備份中分離出單個庫的備份。

舉例：

sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql   #分離完成后我們再導入atguigu.sql即可恢復單個庫

3.4 從單庫備份中恢復單表

這個需求還是比較常見的。比如說我們知道哪個表誤操作了，那么就可以用單表恢復的方式來恢復。

舉例：我們有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語法分離出創建表的語句及插入數據的語句后 再依次導出即可完成恢復   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中的數據庫文件復制出來。這種方法最簡單，速度也最快。MySQL的數據庫目錄位置不一定相同：

• 在Windows平臺下，MySQL 8.0存放數據庫的目錄通常默認為 C:ProgramDataMySQLMySQL Server 8.0Data或者其他用戶自定義目錄；
• 在Linux平臺下，數據庫目錄位置通常為/var/lib/mysql/；
• 在MAC OSX平臺下，數據庫目錄位置通常為“/usr/local/mysql/data”

但為了保證備份的一致性。需要保證：

• 方式1：備份前，將服務器停止。
• 方式2：備份前，對相關表執行FLUSH TABLES WITH READ LOCK操作。這樣當復制數據庫目錄中的文件時，允許其他客戶繼續查詢表。同時，FLUSH TABLES語句來確保開始備份前將所有激活的索引頁寫入硬盤。

這種方式方便、快速，但不是最好的備份方法，因為實際情況可能不允許停止MySQL服務器或者鎖住表，而且這種方法對InnoDB存儲引擎的表不適用。對于MyISAM存儲引擎的表，這樣備份和還原很方便，但是還原時最好是相同版本的MySQL數據庫，否則可能會存在文件類型不同的情況。

注意，物理備份完畢后，執行UNLOCK TABLES來結算其他客戶對表的修改行為。

說明： 在MySQL版本號中，第一個數字表示主版本號，主版本號相同的MySQL數據庫文件格式相同。

此外，還可以考慮使用相關工具實現備份。比如，MySQLhotcopy工具。MySQLhotcopy是一個Perl腳本，它使用LOCK TABLES、FLUSH TABLES和cp或scp來快速備份數據庫。它是備份數據庫或單個表最快的途徑，但它只能運行在數據庫目錄所在的機器上，并且只能備份MyISAM類型的表。多用于mysql5.5之前。

五、物理恢復：直接復制到數據庫目錄

步驟：

1. 演示刪除備份的數據庫中指定表的數據
2. 將備份的數據庫數據拷貝到數據目錄下，并重啟MySQL服務器
3. 查詢相關表的數據是否恢復。需要使用下面的 chown 操作。

要求：

• 必須確保備份數據的數據庫和待恢復的數據庫服務器的主版本號相同。
  • 因為只有MySQL數據庫主版本號相同時，才能保證這兩個MySQL數據庫文件類型是相同的。
• 這種方式對MyISAM類型的表比較有效，對于InnoDB類型的表則不可用。
  • 因為InnoDB表的表空間不能直接復制。
• 在Linux操作系統下，復制到數據庫目錄后，一定要將數據庫的用戶和組變成mysql，命令如下：

chown -R mysql.mysql /var/lib/mysql/dbname

其中，兩個mysql分別表示組和用戶；“-R”參數可以改變文件夾下的所有子文件的用戶和組；“dbname”參數表示數據庫目錄。

提示 Linux操作系統下的權限設置非常嚴格。通常情況下，MySQL數據庫只有root用戶和mysql用戶組下的mysql用戶才可以訪問，因此將數據庫目錄復制到指定文件夾后，一定要使用chown命令將文件夾的用戶組變為mysql，將用戶變為mysql。

六、表的導出與導入

6.1 表的導出

1. 使用SELECT…INTO OUTFILE導出文本文件

在MySQL中，可以使用SELECT…INTO OUTFILE語句將表的內容導出成一個文本文件。

舉例：使用SELECT…INTO OUTFILE將atguigu數據庫中account表中的記錄導出到文本文件。

（1）選擇數據庫atguigu，并查詢account表，執行結果如下所示。

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)

參數secure_file_priv的可選值和作用分別是：

• 如果設置為empty，表示不限制文件生成的位置，這是不安全的設置；
• 如果設置為一個表示路徑的字符串，就要求生成的文件只能放在這個指定的目錄，或者它的子目錄；
• 如果設置為NULL，就表示禁止在這個MySQL實例上執行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數據庫中account表中的記錄導出到文本文件：

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account

mysqldump命令執行完畢后，在指定的目錄/var/lib/mysql-files/下生成了account.sql和account.txt文件。

打開account.sql文件，其內容包含創建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表中的數據。

[root@node1 mysql-files]# cat account.txt1 張三 902 李四 1003 王五 0

舉例2：使用mysqldump將atguigu數據庫中的account表導出到文本文件，使用FIELDS選項，要求字段之間使用逗號“，”間隔，所有字符類型字段值用雙引號括起來：

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminatedby=',' --fields-optionally-enclosed-by='"'

語句mysqldump語句執行成功之后，指定目錄下會出現兩個文件account.sql和account.txt。

打開account.sql文件，其內容包含創建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文件，其內容包含創建account表的數據。從文件中可以看出，字段之間用逗號隔開，字符類型的值被雙引號括起來。

[root@node1 mysql-files]# cat account.txt1,"張三",902,"李四",1003,"王五",0

3. 使用mysql命令導出文本文件

舉例1：使用mysql語句導出atguigu數據中account表中的記錄到文本文件：

mysql -uroot -p --execute="SELECT * FROM account;" atguigu> "/var/lib/mysqlfiles/account.txt"

打開account.txt文件，其內容包含創建account表的數據。

[root@node1 mysql-files]# cat account.txtid name balance1 張三 902 李四 1003 王五 0

舉例2：將atguigu數據庫account表中的記錄導出到文本文件，使用–veritcal參數將該條件記錄分為多行顯示：

mysql -uroot -p --vertical --execute="SELECT * FROM account;" atguigu >"/var/lib/mysql-files/account_1.txt"

打開account_1.txt文件，其內容包含創建account表的數據。

[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數據庫account表中的記錄導出到xml文件，使用–xml參數，具體語句如下。

mysql -uroot -p --xml --execute="SELECT * FROM account;" atguigu>"/var/lib/mysqlfiles/account_3.xml"

[root@node1 mysql-files]# cat account_3.xml <?xml version="1.0"?> <resultset statement="SELECT * FROM account" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <field name="id">1</field> <field name="name">張三</field> <field name="balance">90</field> </row> <row> <field name="id">2</field> <field name="name">李四</field> <field name="balance">100</field> </row> <row> <field name="id">3</field> <field name="name">王五</field> <field name="balance">0</field> </row> </resultset>

說明：如果要將表數據導出到html文件中，可以使用--html選項。然后可以使用瀏覽器打開。

6.2 表的導入

1. 使用LOAD DATA INFILE方式導入文本文件

舉例1：

使用SELECT…INTO OUTFILE將atguigu數據庫中account表的記錄導出到文本文件

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_0.txt';

刪除account表中的數據：

DELETE FROM atguigu.account;

從文本文件account.txt中恢復數據：

LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;

查詢account表中的數據：

mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 張三 | 90 | | 2 | 李四 | 100 | | 3 | 王五 | 0 | +----+--------+---------+ 3 rows in set (0.00 sec)

舉例2： 選擇數據庫atguigu，使用SELECT…INTO OUTFILE將atguigu數據庫account表中的記錄導出到文本文件，使用FIELDS選項和LINES選項，要求字段之間使用逗號"，"間隔，所有字段值用雙引號括起來：

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_1.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"';

刪除account表中的數據：

DELETE FROM atguigu.account;

從/var/lib/mysql-files/account.txt中導入數據到account表中：

LOAD DATA INFILE '/var/lib/mysql-files/account_1.txt' INTO TABLE atguigu.account FIELDS TERMINATED BY ',' ENCLOSED BY '"';

查詢account表中的數據，具體SQL如下：

select * from account; mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 張三 | 90 | | 2 | 李四 | 100 | | 3 | 王五 | 0 | +----+--------+---------+ 3 rows in set (0.00 sec)

2. 使用mysqlimport方式導入文本文件

舉例：

導出文件account.txt，字段之間使用逗號"，"間隔，字段值用雙引號括起來：

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"';

刪除account表中的數據：

DELETE FROM atguigu.account;

使用mysqlimport命令將account.txt文件內容導入到數據庫atguigu的account表中：

mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminatedby=',' --fields-optionally-enclosed-by='"'

查詢account表中的數據：

select * from account; mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 張三 | 90 | | 2 | 李四 | 100 | | 3 | 王五 | 0 | +----+--------+---------+ 3 rows in set (0.00 sec)

七、數據庫遷移

7.1 概述

數據遷移（data migration）是指選擇、準備、提取和轉換數據，并 將數據從一個計算機存儲系統永久地傳輸到另一個計算機存儲系統的過程 。此外，驗證遷移數據的完整性和退役原來舊的數據存儲，也被認為是整個數據遷移過程的一部分。

數據庫遷移的原因是多樣的，包括服務器或存儲設備更換、維護或升級，應用程序遷移，網站集成，災難恢復和數據中心遷移。

根據不同的需求可能要采取不同的遷移方案，但總體來講，MySQL 數據遷移方案大致可以分為物理遷移和邏輯遷移兩類。通常以盡可能自動化的方式執行，從而將人力資源從繁瑣的任務中解放出來。

7.2 遷移方案

物理遷移

物理遷移適用于大數據量下的整體遷移。使用物理遷移方案的優點是比較快速，但需要停機遷移并且要求 MySQL 版本及配置必須和原服務器相同，也可能引起未知問題。

物理遷移包括拷貝數據文件和使用 XtraBackup 備份工具兩種。

不同服務器之間可以采用物理遷移，我們可以在新的服務器上安裝好同版本的數據庫軟件，創建好相同目錄，建議配置文件也要和原數據庫相同，然后從原數據庫方拷貝來數據文件及日志文件，配置好文件組權限，之后在新服務器這邊使用 mysqld 命令啟動數據庫。

邏輯遷移

邏輯遷移適用范圍更廣，無論是 部分遷移 還是 全量遷移 ，都可以使用邏輯遷移。邏輯遷移中使用最多的就是通過 mysqldump 等備份工具。

7.3 遷移注意點

① 相同版本的數據庫之間遷移注意點

指的是在主版本號相同的MySQL數據庫之間進行數據庫移動。

方式1： 因為遷移前后MySQL數據庫的主版本號相同，所以可以通過復制數據庫目錄來實現數據庫遷移，但是物理遷移方式只適用于MyISAM引擎的表。對于InnoDB表，不能用直接復制文件的方式備份數據庫。

方式2： 最常見和最安全的方式是使用mysqldump命令導出數據，然后在目標數據庫服務器中使用MySQL命令導入。

舉例：

#host1的機器中備份所有數據庫,并將數據庫遷移到名為host2的機器上mysqldump –h host1 –uroot –p –-all-databases|mysql –h host2 –uroot –p

在上述語句中，|符號表示管道，其作用是將mysqldump備份的文件給mysql命令；--all-databases表示要遷移所有的數據庫。通過這種方式可以直接實現遷移。

② 不同版本的數據庫之間遷移注意點

例如，原來很多服務器使用5.7版本的MySQL數據庫，在8.0版本推出來以后，改進了5.7版本的很多缺陷，因此需要把數據庫升級到8.0版本

舊版本與新版本的MySQL可能使用不同的默認字符集，例如有的舊版本中使用latin1作為默認字符集，而最新版本的MySQL默認字符集為utf8mb4。如果數據庫中有中文數據，那么遷移過程中需要對默認字符集進行修改 ，不然可能無法正常顯示數據。

高版本的MySQL數據庫通常都會兼容低版本，因此可以從低版本的MySQL數據庫遷移到高版本的MySQL數據庫。

③ 不同數據庫之間遷移注意點

不同數據庫之間遷移是指從其他類型的數據庫遷移到MySQL數據庫，或者從MySQL數據庫遷移到其他類型的數據庫。這種遷移沒有普適的解決方法。

遷移之前，需要了解不同數據庫的架構，比較它們之間的差異。不同數據庫中定義相同類型的數據的關鍵字可能會不同。例如，MySQL中日期字段分為DATE和TIME兩種，而ORACLE日期字段只有DATE；SQL Server數據庫中有ntext、Image等數據類型，MySQL數據庫沒有這些數據類型；MySQL支持的ENUM和SET類型，這些SQL Server數據庫不支持。

另外，數據庫廠商并沒有完全按照SQL標準來設計數據庫系統，導致不同的數據庫系統的SQL語句有差別。例如，微軟的SQL Server軟件使用的是T-SQL語句，T-SQL中包含了非標準的SQL語句，不能和MySQL的SQL語句兼容。

不同類型數據庫之間的差異造成了互相遷移的困難，這些差異其實是商業公司故意造成的技術壁壘。但是不同類型的數據庫之間的遷移并不是完全不可能 。例如，可以使用MyODBC實現MySQL和SQL Server之間的遷移。MySQL官方提供的工具MySQL Migration Toolkit也可以在不同數據之間進行數據遷移。MySQL遷移到Oracle時，需要使用mysqldump命令導出sql文件，然后，手動更改sql文件中的CREATE語句。

7.4 遷移小結

八、刪庫了不敢跑，能干點啥？

傳統的高可用架構是不能預防誤刪數據的，因為主庫的一個drop table命令，會通過binlog傳給所有從庫和級聯從庫，進而導致整個集群的實例都會執行這個命令。

為了找到解決誤刪數據的更高效的方法，我們需要先對和MySQL相關的誤刪數據，做下分類：

1. 使用delete語句誤刪數據行；
2. 使用drop table或者truncate table語句誤刪數據表；
3. 使用drop database語句誤刪數據庫；
4. 使用rm命令誤刪整個MySQL實例。

8.1 delete：誤刪行

處理措施1：數據恢復

使用Flashback工具恢復數據。

原理：修改binlog內容，拿回原庫重放。如果誤刪數據涉及到了多個事務的話，需要將事務的順序調過來再執行。

使用前提：binlog_format=row和binlog_row_image=FULL。

處理措施2：預防

• 代碼上線前，必須SQL審查、審計。

• 建議可以打開安全模式，把sql_safe_updates參數設置為on。強制要求加where條件且where后需要是索引字段，否則必須使用limit。否則就會報錯。

8.2 truncate/drop ：誤刪庫/表

背景：
delete全表是很慢的，需要生成回滾日志、寫redo、寫binlog。所以，從性能角度考慮，優先考慮使用truncatetable或者drop table命令。

使用delete命令刪除的數據，你還可以用Flashback來恢復。而使用truncate /drop table和drop database命令刪除的數據，就沒辦法通過Flashback來恢復了。因為，即使我們配置了binlog_format=row，執行這三個命令時，記錄的binlog還是statement格式。binlog里面就只有一個truncate/drop語句，這些信息是恢復不出數據的。

方案：

這種情況下恢復數據，需要使用全量備份與增量日志結合的方式。

方案的前提：有定期的全量備份，并且實時備份binlog。

舉例：有人誤刪了一個庫，時間為下午3點。步驟如下：

1. 取最近一次全量備份。假設設置數據庫庫是一天一備，最近備份數據是當天凌晨2點；
2. 用備份恢復出一個臨時庫；（注意：這里選擇臨時庫，而不是直接操作主庫)
3. 取出凌晨2點之后的binlog日志；
4. 剔除誤刪除數據的語句外，其它語句全部應用到臨時庫。(前面講過binlog的恢復)
5. 最后恢復到主庫

8.3 預防使用truncate /drop誤刪庫/表

上面我們說了使用truncate /drop語句誤刪庫/表的恢復方案，在生產環境中可以通過下面建議的方案來盡量的避免類似的誤操作。

① 權限分離

• 限制帳戶權限，核心的數據庫，一般都不能隨便分配寫權限，想要獲取寫權限需要審批。比如只給業務開發人員DML權限，不給truncate/drop權限。即使是DBA團隊成員，日常也都規定只使用只讀賬號，必要的時候才使用有更新權限的賬號。
• 不同的賬號，不同的數據之間要進行權限分離，避免一個賬號可以刪除所有庫。

② 制定操作規范

比如在刪除數據表之前，必須先對表做改名操作（比如加_to_be_deleted)。然后，觀察一段時間，確保對業務無影響以后再刪除這張表。

③ 設置延遲復制備庫

簡單的說延遲復制就是設置一個固定的延遲時間，比如1個小時，讓從庫落后主庫一個小時。出現誤刪除操作1小時內，到這個備庫上執行stop slave，再通過之前介紹的方法，跳過誤操作命令，就可以恢復出需要的數據。這里通過CHANGE MASTER TO MASTER_DELAY = N命令，可以指定這個備庫持續保持跟主庫有N秒的延遲。比如把N設置為3600，即代表1個小時。

此外，延遲復制還可以用來解決以下問題：

1. 用來做延遲測試，比如做好的數據庫讀寫分離，把從庫作為讀庫，那么想知道當數據產生延遲的時候到底會發生什么，就可以使用這個特性模擬延遲。

2. 用于老數據的查詢等需求，比如你經常需要查看某天前一個表或者字段的數值，你可能需要把備份恢復后進行查看，如果有延遲從庫，比如延遲一周，那么就可以解決這樣類似的需求。

8.4 rm：誤刪MySQL實例

對于一個有高可用機制的MySQL集群來說，不用擔心rm刪除數據。因為只刪掉其中某一個節點數據的話，HA系統就會選出一個新的主庫，從而保證整個集群的正常工作。我們把這個節點上的數據恢復回來后，再接入整個集群就好了。

但如果是惡意地把整個集群刪除，那就需要考慮跨機房備份，跨城市備份。

推薦學習：mysql視頻教程