本篇文章給大家?guī)砹岁P(guān)于mysql的相關(guān)知識，其中主要介紹了關(guān)于數(shù)據(jù)庫備份與恢復(fù)的相關(guān)問題，包括了mysqldump實現(xiàn)邏輯備份、mysql命令恢復(fù)數(shù)據(jù)、物理備份以及物理恢復(fù)等等內(nèi)容，下面一起來看一下，希望對大家有幫助。

推薦學(xué)習(xí)：mysql視頻教程

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

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

一、物理備份與邏輯備份

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

邏輯備份：對數(shù)據(jù)庫對象利用工具進行導(dǎo)出工作，匯總?cè)雮浞菸募?nèi)。邏輯備份恢復(fù)速度慢，但占用空間小，更靈活。MySQL 中常用的邏輯備份工具為mysqldump。邏輯備份就是備份sql語句，在恢復(fù)的時候執(zhí)行備份的sql語句實現(xiàn)數(shù)據(jù)庫數(shù)據(jù)的重現(xiàn)。

二、mysqldump實現(xiàn)邏輯備份

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

2.1 備份一個數(shù)據(jù)庫

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

• 查出需要備份的表的結(jié)構(gòu)，在文本文件中生成一個CREATE語句
• 將表中的所有記錄轉(zhuǎn)換成一條INSERT語句。

基本語法：

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

舉例：使用root用戶備份atguigu數(shù)據(jù)庫：

mysqldump -uroot -p atguigu>atguigu.sql #備份文件存儲在當(dāng)前目錄下

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

2.2 備份全部數(shù)據(jù)庫

若想用mysqldump備份整個實例，可以使用--all-databases或-A參數(shù)：

mysqldump -uroot -pxxxxxx --all-databases > all_database.sql  mysqldump -uroot -pxxxxxx -A > all_database.sql

2.3 備份部分?jǐn)?shù)據(jù)庫

使用--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

2.4 備份部分表

比如，在表變更前做個備份。語法如下：

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文件內(nèi)容如下

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 備份單表的部分?jǐn)?shù)據(jù)

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

舉例：備份student表中id小于10的數(shù)據(jù)：

mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql

內(nèi)容如下所示，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 排除某些表的備份

如果我們想備份某個庫，但是某些表數(shù)據(jù)量很大或者與業(yè)務(wù)關(guān)聯(lián)不大，這個時候可以考慮排除掉這些表，同樣的，選項--ignore-table可以完成這個功能。

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

通過如下指定判定文件中沒有student表結(jié)構(gòu)：

grep "student" no_stu_bak.sql

2.7 只備份結(jié)構(gòu)或只備份數(shù)據(jù)

只備份結(jié)構(gòu)的話可以使用--no-data簡寫為--d選項；只備份數(shù)據(jù)可以使用--no-create-info簡寫為--t選項。

• 只備份結(jié)構(gòu)

mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql #使用grep命令，沒有找到insert相關(guān)語句,表示沒有數(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相關(guān)語句，表示沒有數(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相關(guān)語句，表示沒有數(shù)據(jù)結(jié)構(gòu)。 [root@node1 ~]# grep "CREATE" atguigu_no_create_info_bak.sql [root@node1 ~]#

2.8 備份中包含存儲過程、函數(shù)、事件

mysqldump備份默認(rèn)是不包含存儲過程，自定義函數(shù)及事件的?？梢允褂?code>--routines或-R選項來備份存儲過程及函數(shù)，使用--events或-E參數(shù)來備份事件。

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

• 使用下面的SQL可以查看當(dāng)前庫有哪些存儲過程或者函數(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; #設(shè)置手動提交事務(wù)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; #提交事務(wù)END ;;DELIMITER ;-- BEGIN DECLARE i INT DEFAULT 0;SET autocommit = 0; #設(shè)置手動提交事務(wù)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; #提交事務(wù)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命令恢復(fù)數(shù)據(jù)

使用mysqldump命令將數(shù)據(jù)庫中的數(shù)據(jù)備份成一個文本文件。需要恢復(fù)時，可以使用mysql命令來恢復(fù)備份的數(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服務(wù)器中已經(jīng)創(chuàng)建了該名的數(shù)據(jù)庫。不指定數(shù)據(jù)庫名時，表示還原文件中所有的數(shù)據(jù)庫。此時sql文件中包含有CREATE DATABASE語句，不需要MysQL服務(wù)器中已存在這些數(shù)據(jù)庫。

3.1 單庫備份中恢復(fù)單庫

使用root用戶，將之前練習(xí)中備份的atguigu.sql文件中的備份導(dǎo)入數(shù)據(jù)庫中，命令如下：

如果備份文件中包含了創(chuàng)建數(shù)據(jù)庫的語句，則恢復(fù)的時候不需要指定數(shù)據(jù)庫名稱，如下所示

#備份文件中包含了創(chuàng)建數(shù)據(jù)庫的語句mysql -uroot -p < atguigu.sql

否則需要指定數(shù)據(jù)庫名稱，如下所示

#備份文件中不包含了創(chuàng)建數(shù)據(jù)庫的語句mysql -uroot -p atguigu4< atguigu.sql

3.2 全量備份恢復(fù)

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

mysql –u root –p < all.sql

mysql -uroot -pxxxxxx < all.sql

執(zhí)行完后，MySQL數(shù)據(jù)庫中就已經(jīng)恢復(fù)了all.sql文件中的所有數(shù)據(jù)庫。

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

3.3 從全量備份中恢復(fù)單庫

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

舉例：

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

3.4 從單庫備份中恢復(fù)單表

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

舉例：我們有atguigu整庫的備份，但是由于class表誤操作，需要單獨恢復(fù)出這張表。

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ù)的語句后 再依次導(dǎo)出即可完成恢復(fù)   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)

四、物理備份：直接復(fù)制整個數(shù)據(jù)庫

直接將MySQL中的數(shù)據(jù)庫文件復(fù)制出來。這種方法最簡單，速度也最快。MySQL的數(shù)據(jù)庫目錄位置不一定相同：

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

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

• 方式1：備份前，將服務(wù)器停止。
• 方式2：備份前，對相關(guān)表執(zhí)行FLUSH TABLES WITH READ LOCK操作。這樣當(dāng)復(fù)制數(shù)據(jù)庫目錄中的文件時，允許其他客戶繼續(xù)查詢表。同時，F(xiàn)LUSH TABLES語句來確保開始備份前將所有激活的索引頁寫入硬盤。

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

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

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

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

五、物理恢復(fù)：直接復(fù)制到數(shù)據(jù)庫目錄

步驟：

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

要求：

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

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

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

提示 Linux操作系統(tǒng)下的權(quán)限設(shè)置非常嚴(yán)格。通常情況下，MySQL數(shù)據(jù)庫只有root用戶和mysql用戶組下的mysql用戶才可以訪問，因此將數(shù)據(jù)庫目錄復(fù)制到指定文件夾后，一定要使用chown命令將文件夾的用戶組變?yōu)閙ysql，將用戶變?yōu)閙ysql。

六、表的導(dǎo)出與導(dǎo)入

6.1 表的導(dǎo)出

1. 使用SELECT…INTO OUTFILE導(dǎo)出文本文件

在MySQL中，可以使用SELECT…INTO OUTFILE語句將表的內(nèi)容導(dǎo)出成一個文本文件。

舉例：使用SELECT…INTO OUTFILE將atguigu數(shù)據(jù)庫中account表中的記錄導(dǎo)出到文本文件。

（1）選擇數(shù)據(jù)庫atguigu，并查詢account表，執(zhí)行結(jié)果如下所示。

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默認(rèn)對導(dǎo)出的目錄有權(quán)限限制，也就是說使用命令行進行導(dǎo)出的時候，需要指定目錄進行操作。

查詢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的可選值和作用分別是：

• 如果設(shè)置為empty，表示不限制文件生成的位置，這是不安全的設(shè)置；
• 如果設(shè)置為一個表示路徑的字符串，就要求生成的文件只能放在這個指定的目錄，或者它的子目錄；
• 如果設(shè)置為NULL，就表示禁止在這個MySQL實例上執(zhí)行select … into outfile操作。

（3）上面結(jié)果中顯示，secure_file_priv變量的值為/var/lib/mysql-files/，導(dǎo)出目錄設(shè)置為該目錄，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命令導(dǎo)出文本文件

舉例1：使用mysqldump命令將將atguigu數(shù)據(jù)庫中account表中的記錄導(dǎo)出到文本文件：

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

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

打開account.sql文件，其內(nèi)容包含創(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文件，其內(nèi)容只包含account表中的數(shù)據(jù)。

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

舉例2：使用mysqldump將atguigu數(shù)據(jù)庫中的account表導(dǎo)出到文本文件，使用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文件，其內(nèi)容包含創(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文件，其內(nèi)容包含創(chuàng)建account表的數(shù)據(jù)。從文件中可以看出，字段之間用逗號隔開，字符類型的值被雙引號括起來。

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

3. 使用mysql命令導(dǎo)出文本文件

舉例1：使用mysql語句導(dǎo)出atguigu數(shù)據(jù)中account表中的記錄到文本文件：

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

打開account.txt文件，其內(nèi)容包含創(chuàng)建account表的數(shù)據(jù)。

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

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

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

打開account_1.txt文件，其內(nèi)容包含創(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表中的記錄導(dǎo)出到xml文件，使用–xml參數(shù)，具體語句如下。

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>

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

6.2 表的導(dǎo)入

1. 使用LOAD DATA INFILE方式導(dǎo)入文本文件

舉例1：

使用SELECT…INTO OUTFILE將atguigu數(shù)據(jù)庫中account表的記錄導(dǎo)出到文本文件

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

刪除account表中的數(shù)據(jù)：

DELETE FROM atguigu.account;

從文本文件account.txt中恢復(fù)數(shù)據(jù)：

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

查詢account表中的數(shù)據(jù)：

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

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

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

刪除account表中的數(shù)據(jù)：

DELETE FROM atguigu.account;

從/var/lib/mysql-files/account.txt中導(dǎo)入數(shù)據(jù)到account表中：

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

查詢account表中的數(shù)據(jù)，具體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方式導(dǎo)入文本文件

舉例：

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

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

刪除account表中的數(shù)據(jù)：

DELETE FROM atguigu.account;

使用mysqlimport命令將account.txt文件內(nèi)容導(dǎo)入到數(shù)據(jù)庫atguigu的account表中：

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

查詢account表中的數(shù)據(jù)：

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

七、數(shù)據(jù)庫遷移

7.1 概述

數(shù)據(jù)遷移（data migration）是指選擇、準(zhǔn)備、提取和轉(zhuǎn)換數(shù)據(jù)，并 將數(shù)據(jù)從一個計算機存儲系統(tǒng)永久地傳輸?shù)搅硪粋€計算機存儲系統(tǒng)的過程 。此外，驗證遷移數(shù)據(jù)的完整性和退役原來舊的數(shù)據(jù)存儲，也被認(rèn)為是整個數(shù)據(jù)遷移過程的一部分。

數(shù)據(jù)庫遷移的原因是多樣的，包括服務(wù)器或存儲設(shè)備更換、維護或升級，應(yīng)用程序遷移，網(wǎng)站集成，災(zāi)難恢復(fù)和數(shù)據(jù)中心遷移。

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

7.2 遷移方案

物理遷移

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

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

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

邏輯遷移

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

7.3 遷移注意點

① 相同版本的數(shù)據(jù)庫之間遷移注意點

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

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

方式2： 最常見和最安全的方式是使用mysqldump命令導(dǎo)出數(shù)據(jù)，然后在目標(biāo)數(shù)據(jù)庫服務(wù)器中使用MySQL命令導(dǎo)入。

舉例：

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

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

② 不同版本的數(shù)據(jù)庫之間遷移注意點

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

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

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

③ 不同數(shù)據(jù)庫之間遷移注意點

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

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

另外，數(shù)據(jù)庫廠商并沒有完全按照SQL標(biāo)準(zhǔn)來設(shè)計數(shù)據(jù)庫系統(tǒng)，導(dǎo)致不同的數(shù)據(jù)庫系統(tǒng)的SQL語句有差別。例如，微軟的SQL Server軟件使用的是T-SQL語句，T-SQL中包含了非標(biāo)準(zhǔn)的SQL語句，不能和MySQL的SQL語句兼容。

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

7.4 遷移小結(jié)

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

傳統(tǒng)的高可用架構(gòu)是不能預(yù)防誤刪數(shù)據(jù)的，因為主庫的一個drop table命令，會通過binlog傳給所有從庫和級聯(lián)從庫，進而導(dǎo)致整個集群的實例都會執(zhí)行這個命令。

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

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

8.1 delete：誤刪行

處理措施1：數(shù)據(jù)恢復(fù)

使用Flashback工具恢復(fù)數(shù)據(jù)。

原理：修改binlog內(nèi)容，拿回原庫重放。如果誤刪數(shù)據(jù)涉及到了多個事務(wù)的話，需要將事務(wù)的順序調(diào)過來再執(zhí)行。

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

處理措施2：預(yù)防

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

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

8.2 truncate/drop ：誤刪庫/表

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

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

方案：

這種情況下恢復(fù)數(shù)據(jù)，需要使用全量備份與增量日志結(jié)合的方式。

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

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

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

8.3 預(yù)防使用truncate /drop誤刪庫/表

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

① 權(quán)限分離

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

② 制定操作規(guī)范

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

③ 設(shè)置延遲復(fù)制備庫

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

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

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

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

8.4 rm：誤刪MySQL實例

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

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

推薦學(xué)習(xí)：mysql視頻教程