MySQL-19.資料庫備份與恢復

C-19.資料庫備份與恢復

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

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

1.物理備份和邏輯備份

---

物理備份：備份資料檔案，轉儲資料庫物理檔案到某一目錄。物理備份恢復速度比較快，但佔用空間比較大，MySQL中可以用xtrabackup工具來進行物理備份。本質就是備份某一資料庫例項的資料目錄。

邏輯備份：對資料庫物件利用工具進行匯出工作，彙總入備份檔案。邏輯備份恢復速度慢，但佔用空間小，更靈活。MySQL中常用的邏輯備份工具為mysqldump。邏輯備份就是備份sql語句，在恢復的時候執行備份的sql語句實現資料庫資料的重現。

2.mysqldump實現邏輯備份

---

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

2.1 備份一個資料庫

mysqldump命令執行時，可以將資料庫備份成一個文字檔案，該檔案中實際上包含多個CREATE和INSERT語句，使用這些語句可以重新建立表和插入資料。

• 查出需要備份的表的結構，在文字檔案中生成一個CREATE語句
• 將表中的所有記錄轉換成一條INSERT語句。
• 注意，該語句備份資料庫不會生成建立資料庫的語句，只備份資料庫中的表和表的資料。

基本語法

mysqldump -u使用者名稱稱 -h主機名稱 -p密碼 待備份的資料庫名稱[tbname,[tbname...]]> 備份檔名稱.sql

說明：
備份的檔案並非一定要求字尾名為.sql，例如字尾名為.txt的檔案也是可以的。

舉例：使用root使用者備份atguigudb3資料庫

#自定義備份目錄 
mkdir /mysql_backup #下文中，備份檔案如果寫的是相對路徑，則都是在該目錄下執行的

mysqldump -uroot -p atguigudb3>/mysql_backup/atguigudb3.sql 

#也可以直接備份到/var/lib/mysql下，這個目錄的所有者和所在使用者組就是mysql.mysql
mysqldump -uroot -p atguigudb3>/var/lib/mysql/atguigudb3.sql

檢視邏輯備份檔案

vim /mysql_backup/atguigudb3.sql

-- MySQL dump 10.13  Distrib 8.0.25, for Linux (x86_64)
--
-- Host: localhost    Database: atguigudb3
-- ------------------------------------------------------
-- Server version       8.0.25

/*!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 `a`
--

DROP TABLE IF EXISTS `a`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `a` (
  `f1` int DEFAULT NULL,
  `f2` int DEFAULT NULL,
  KEY `f1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `a`
--

LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;
.
.
.
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!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 2024-07-11 10:59:53

• 檔案開頭指明瞭備份檔案使用的mysqldump工具的版本號;接下來是備份賬戶的名稱和主機資訊，以及備份的資料庫的名稱；最後是MySQL伺服器的版本號，在這裡為8.0.25。
• 備份檔案接下來的部分是一些SET語句，這些語句將一些系統變數值賦給使用者定義變數，以確保被恢復的資料庫的系統變數和原來備份時的變數相同，例如:

# 該SET語句將當前系統變數character_set_client的值賦給使用者定義變數@old_character_set_client、其他變數與此類似。

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

• 備份檔案的最後幾行MySQL使用SET語句恢復伺服器系統變數原來的值，例如：

#該語句將使用者定義的變數@old_character_set_client中儲存的值賦給實際的系統變數character_set_client。

/* ! 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT * / ;

• 後面的DROP語句、CREATE語句和INSERT語句都是還原時使用的。例如，“DROP TABLE IF EXISTS 'student1"語句用來判斷資料庫中是否還有名為student1的表，如果存在，就刪除這個表; CREATE語句用來建立student1的表;INSERT語句用來還原資料。
• 備份檔案開始的一些語句以數字開頭。這些數字代表了MySQL版本號，告訴我們這些語句只有在制定的MySQL版本或者比該版本高的情況下才能執行。例如，40101表明這些語句只有在MySQL版本號為4.01.01或者更高的條件下才可以被執行。檔案的最後記錄了備份的時間。

2.2 備份全部資料庫

若想用mysqldump備份整個例項(當前mysql下的所有的資料庫例項)，可以使用 --all-databases或 -A引數：

mysqldump -uroot -p --all-databases > all_databases.sql
mysqldump -uroot -p --A > all_databases.sql

2.3 備份部分資料庫

使用--databases或-B引數了，該引數後面跟資料庫名稱，多個資料庫間用空格隔開。如果指定databases引數，備份檔案中會存在建立資料庫的語句，如果不指定引數，則不存在。語法如下:

mysqldump -u user -h host -p --databases [dbname1 [dbanme2...]] > 備份檔名稱.sql

舉例:

mysqldump -uroot -p --databases atguigudb2 atguigudb3 > /mysql_backup/part_database.sql
#或
mysqldump -uroot -p -B atguigudb2 atguigudb3 > /mysql_backup/part_database.sql

2.4 備份部分表

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

mysqldump -u user -h host -p dbname [tname1 [tname2...]] > 備份檔名稱.sql

舉例：

mysqmysqldump -uroot -p atguigudb3 student1 > /mysql_backup/student1.sql

mysqmysqldump -uroot -p atguigudb3 student1 book > /mysql_backup/student1.sql #備份多張表

備份檔案中，只包含被備份表的DROP，CREATE和INSERT語句。

2.5 備份單表的部分資料

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

舉例：備份student1表中id小於10的資料

mysqldump -uroot -p atguigudb3 student1 --where="id < 10" > student1_part_id_lt_10_bak.sql

mysql> select * from student1;#student1表中的資料
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 張三1   | 一班   |
|  3 | 李四    | 一班   |
|  8 | 王五    | 二班   |
| 15 | 趙六    | 二班   |
| 20 | 錢七1   | 三班   |
+----+---------+--------+
5 rows in set (0.00 sec)

#備份的資料
--
-- Dumping data for table `student1`
--
-- WHERE:  id < 10

LOCK TABLES `student1` WRITE;
/*!40000 ALTER TABLE `student1` DISABLE KEYS */;
INSERT INTO `student1` VALUES (1,'張三1','一班'),(3,'李四','一班'),(8,'王五','二班');
/*!40000 ALTER TABLE `student1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

2.6 排除某些表的備份

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

mysqldump -uroot -p atguigudb3 --ignore-table=atguigudb3.book > no_book_bak.sql #在/mysql_backup目錄下

透過如下指定判定檔案中沒有book表結構

grep "book" ./no_book_bak.sql

2.7 只備份結構或只備份資料

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

• 只備份結構

mysqldump -uroot -p atguigudb3 --no-data > atguigudb3_no_data_bak.sql

#grep命令，找不到insert相關語句，表示沒有備份資料
[root@LinuxCentOS7-132 mysql_backup]# grep -i "INSERT" ./atguigudb3_no_data_bak.sql 
[root@LinuxCentOS7-132 mysql_backup]# 

• 只備份資料

mysqldump -uroot -p atguigudb3 --no-create-info > atguigudb3_no_create_info_bak.sql

#grep命令，找不到create相關語句，表示沒有備份表結構
[root@LinuxCentOS7-132 mysql_backup]# grep "create" ./atguigudb3_no_create_info_bak.sql 
[root@LinuxCentOS7-132 mysql_backup]# 

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

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

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

• 使用下面的sql可以檢視當前庫有那些儲存過程或者函式

mysql> SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA FROM
    -> information_schema.Routines WHERE ROUTINE_SCHEMA="atguigudb3";
+-----------------+--------------+----------------+
| SPECIFIC_NAME   | ROUTINE_TYPE | ROUTINE_SCHEMA |
+-----------------+--------------+----------------+
| rand_num        | FUNCTION     | atguigudb3     |
| rand_string     | FUNCTION     | atguigudb3     |
| insert_class    | PROCEDURE    | atguigudb3     |
| insert_stu      | PROCEDURE    | atguigudb3     |
| insert_user     | PROCEDURE    | atguigudb3     |
| proc_drop_index | PROCEDURE    | atguigudb3     |
+-----------------+--------------+----------------+
6 rows in set (0.01 sec)

mysqldump -uroot -p -R -E --databases atguigudb3 > func_proc_atguigudb3.sql

#查詢並顯示rand_num關鍵詞，上下5行的語句
[root@LinuxCentOS7-132 mysql_backup]# grep -C 5 "rand_num" ./func_proc_atguigudb3.sql 
--

--
-- Dumping routines for database 'atguigudb3'
--
/*!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  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!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_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `rand_num`(from_num INT ,to_num INT) RETURNS int
BEGIN
        DECLARE i INT DEFAULT 0;
        SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
        RETURN i;
    END ;;
--
        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(1,100000));
        UNTIL i = max_num
        END REPEAT;
        COMMIT;
    END ;;
DELIMITER ;
--
    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(1,50),rand_num(1,1000));
    UNTIL i = max_num
    END REPEAT;
    COMMIT; #提交事務
END ;;
DELIMITER ;
--
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO `user1` ( NAME,age,sex ) 
 VALUES ("atguigu",rand_num(1,20),"male");  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END ;;
DELIMITER ;

2.9 mysqldump常用選項

--add-drop-database：在每個CREATE DATABASE語句前新增DROP DATABASE語句。

--add-drop-tables：在每個CREATE TABLE語句前新增DROP TABLE語句。

--add-locking：用LOCK TABLES和UNLOCK TABLES語句引用每個錶轉儲。過載轉儲檔案時插入得更快。

--all-database, -A：轉儲所有資料庫中的所有表。與使用--database選項相同，在命令列中命名所有資料庫。

--comment[=0|1]：如果設定為0，禁止轉儲檔案中的其他資訊，例如程式版本、伺服器版本和主機。
--skip-comments與--comments=0的結果相同。預設值為1，即包括額外資訊。

--compact：產生少量輸出。該選項禁用註釋並啟用--skip-add-drop-tables、--no-set-names、--skipdisable-keys和--skip-add-locking選項。

--compatible=name：產生與其他資料庫系統或舊的MySQL伺服器更相容的輸出，值可以為ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者no_field_options。

--complete_insert, -c：使用包括列名的完整的INSERT語句。沒有該引數的執行結果，中insert語句，不包括列名

--debug[=debug_options], -#[debug_options]：寫除錯日誌。

--delete，-D：匯入文字檔案前清空表。

--default-character-set=charset：使用charsets預設字符集。如果沒有指定，就使用utf8。

--delete--master-logs：在主複製伺服器上，完成轉儲操作後刪除二進位制日誌。該選項自動啟用-master-data。

--extended-insert，-e：使用包括幾個VALUES列表的多行INSERT語法。這樣使得轉儲檔案更小，過載檔案時可以加速插入。

--flush-logs，-F：開始轉儲前重新整理MySQL伺服器日誌檔案。該選項要求RELOAD許可權。

--force，-f：在錶轉儲過程中，即使出現SQL錯誤也繼續。

--lock-all-tables，-x：對所有資料庫中的所有表加鎖。在整體轉儲過程中透過全域性鎖定來實現。該選項自動關閉--single-transaction和--lock-tables。

--lock-tables，-l：開始轉儲前鎖定所有表。用READ LOCAL鎖定表以允許並行插入MyISAM表。對於事務表（例如InnoDB和BDB），--single-transaction是一個更好的選項，因為它根本不需要鎖定表。

--no-create-db，-n：該選項禁用CREATE DATABASE /*!32312 IF NOT EXIST*/db_name語句，如果給出--database或--all-database選項，就包含到輸出中。

--no-create-info，-t：只匯出資料，而不新增CREATE TABLE語句。

--no-data，-d：不寫表的任何行資訊，只轉儲表的結構。

--opt：該選項是速記，它可以快速進行轉儲操作併產生一個能很快裝入MySQL伺服器的轉儲檔案。該選項預設開啟，但可以用--skip-opt禁用。

--password[=password]，-p[password]：當連線伺服器時使用的密碼。

-port=port_num，-P port_num：用於連線的TCP/IP埠號。預設3306

--protocol={TCP|SOCKET|PIPE|MEMORY}：使用的連線協議。

--replace，-r –replace和--ignore：控制替換或複製唯一鍵值已有記錄的輸入記錄的處理。如果指定--replace，新行替換有相同的唯一鍵值的已有行；如果指定--ignore，複製已有的唯一鍵值的輸入行被跳過。如果不指定這兩個選項，當發現一個複製鍵值時會出現一個錯誤，並且忽視文字檔案的剩餘部分。

--silent，-s：沉默模式。只有出現錯誤時才輸出。

--socket=path，-S path：當連線localhost時使用的套接字檔案（為預設主機）。

--user=user_name，-u user_name：當連線伺服器時MySQL使用的使用者名稱。

--verbose，-v：冗長模式，列印出程式操作的詳細資訊。

--xml，-X：產生XML輸出。

3.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 < atguigudb3.sql

否則需要指定資料庫名稱，如下所示

mysql -uroot -p atguigudb3 < atguigudb3.sql #建議使用這種方式，去進行資料的恢復。這種方式，強制讓恢復資料的操作者，要了解資料的來源，和恢復的去向。而不是隨便執行以下恢復指令。

3.2 全量備份恢復

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

mysql -u root -p < all.sql

執行完後，MySQL資料庫中就已經恢復了all.sql檔案中的所有資料庫。

補充：

如果使用--all-databases引數備份了所有的資料庫，那麼恢復時不需要指定資料庫。對應的sql檔案包含有CREATE DATABASE語句，可透過該語句建立資料庫。建立資料庫後，可以執行sql檔案中的USE語句選擇資料庫，再建立表並插入記錄。

3.3 從全量備份中恢復單庫

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

舉例：

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

這條 sed 命令用於從一個名為 all_database.sql 的SQL備份檔案中抽取特定部分的內容，並將其寫入到一個新的檔案 atguigu.sql 中。下面是對這條命令的詳細解釋：

• sed: 是一個流編輯器，用於對輸入流進行編輯和轉換。
• -n: 這個選項告訴 sed 預設不輸出任何行，只有當使用 p 或 P 命令時才會輸出。
• /^-- Current Database: atguigu/: 這是一個正規表示式，用來匹配以 -- Current Database: 開始並以 atguigu 結束的行。這通常是在 SQL 檔案中表示資料庫名稱的註釋行。
• ,/^-- Current Database: /: 這是一個範圍定義，從上一個匹配的行開始，直到遇到下一個以 -- Current Database: 開始的行為止。這意味著它將包括兩個註釋行之間的所有內容。
• p: 這個命令告訴 sed 輸出與模式匹配的行以及它們之間的所有行。

綜合起來，這條命令的作用是從 all_database.sql 檔案中找到第一個 -- Current Database: atguigu`` 的註釋行，然後一直讀取直到遇到下一個 -- Current Database: 的註釋行（無論這個資料庫是什麼），並將這兩行之間的所有內容輸出到 atguigu.sql 檔案中。

上述內容來自，通義千問2.5。

3.4 從單庫備份中恢復單表

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

舉例:我們有atguigudb3整庫的備份，但是由於student1表誤操作，需要單獨恢復出這張表。

cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `student1`/!d;q' > student1_structure.sql
cat atguigu.sql | grep --ignore-case 'insert into `student1`' > student1_data.sql
#用shell語法分離出建立表的語句及插入資料的語句後 再依次匯出即可完成恢復

[root@LinuxCentOS7-132 mysql_backup]# cat student1_structure.sql 

DROP TABLE IF EXISTS `student1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student1` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `class` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

mysql> use atguigudb3
Database changed
#從執行結果，也看出，備份檔案中的mysql自動新增的註釋，在滿足條件的情況下，也會被執行，五個Query OK代表5條語句執行成功。
mysql> source /mysql_backup/student1_structure.sql 
Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> source /mysql_backup/student1_data.sql
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

4.物理備份：直接複製資料庫例項的資料檔案所在目錄

直接將MySQL中的資料庫檔案複製出來。這種方法最簡單，速度也最快。MySQL的資料庫目錄位置不一定相同:

使用下面命令檢視資料庫目錄

mysql> show variables like 'datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

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

• 方式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來快速備份資料庫。它是備份資料庫或單個表最快的途徑，但它只能執行在資料庫目錄所在的機器上，並且只能備份MylSAM型別的表。多用於mysql5.5之前。

5.物理恢復：直接複製到資料庫目錄

---

步驟：

①演示刪除備份的資料庫中指定表的資料。

②將備份的資料庫資料複製到資料目錄下，並重啟MySQL伺服器。

③查詢相關表的資料是否恢復。需要使用到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.表的匯出和匯入

---

6.1 表的匯出

1.使用SELECT...INTO OUTFILE匯出文字檔案

在MySQL中，可以使用SELECT...INTO OUTFILE語句將表的內容匯出成一個文字檔案。

舉例：使用SELECT...INTO OUTFILE將atguigudb3資料庫中account表中記錄匯出到文字檔案。

①選擇資料庫atguigudb3，並查詢account表，執行結果如下所示

mysql> use atguigudb3;

mysql> select * from student1;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 張三1   | 一班   |
|  3 | 李四    | 一班   |
|  8 | 王五    | 二班   |
| 15 | 趙六    | 二班   |
| 20 | 錢七1   | 三班   |
+----+---------+--------+
5 rows in set (0.00 sec)

②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.00 sec)

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

• 如果設定為empty，表示不限制檔案生成的位置，這是不安全的設定;
• 如果設定為一個表示路徑的字串，就要求生成的檔案只能放在這個指定的目錄，或者它的子目錄;
• 如果設定為NULL，就表示禁止在這個MySQL例項上執行select ... into outfile操作。

③上面結果顯示，secure_file_priv變數的值為/var/lib/mysql-files/，匯出目錄為該目錄，sql語句如下。

SELECT * FROM student1 INTO OUTFILE "/var/lib/mysql-files/student1.txt"

④檢視student1.txt檔案

[root@LinuxCentOS7-132 mysql-files]# cat student1.txt 
1	張三1	一班
3	李四	一班
8	王五	二班
15	趙六	二班
20	錢七1	三班

2.使用mysqldump命令匯出文字檔案

舉例1：使用mysqldump命令將將atguigudb3資料庫中student1表中的記錄匯出到文字檔案：

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigudb3 student1 #生成目錄必須和secure_file_priv保持一致

命令執行完後，在指定目錄下，生成了student1.sql和student.txt檔案

開啟student1.sql檔案，其內容包含建立student1表的sql語句

-- MySQL dump 10.13  Distrib 8.0.25, for Linux (x86_64)
--
-- Host: localhost    Database: atguigudb3
-- ------------------------------------------------------
-- Server version       8.0.25

/*!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 `student1`
--

DROP TABLE IF EXISTS `student1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student1` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `class` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 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 2024-07-11 17:46:10

[root@LinuxCentOS7-132 mysql-files]# cat student1.txt 
1	張三1	一班
3	李四	一班
8	王五	二班
15	趙六	二班
20	錢七1	三班

舉例2：使用mysqldump將atguigudb3資料庫中的teacher表匯出到文字檔案，使用FIELDS選項，要求欄位之間使用逗號“，”間隔，所有字元型別欄位值用雙引號括起來：

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigudb3 teacher --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

sql檔案就是teacher表的建立語句

[root@LinuxCentOS7-132 mysql-files]# vim teacher.txt 
1,"zhangsan",\N #代表null
2,"lisi",\N

3.使用mysql命令匯出檔案

舉例1：使用mysql語句匯出atguigudb3資料中student1表中的記錄到文字檔案：

mysql -uroot -p --execute="SELECT * FROM student1;" atguigudb3> "/var/lib/mysql-files/student3.txt"

[root@LinuxCentOS7-132 mysql-files]# cat student3.txt 
id	name	class
1	張三1	一班
3	李四	一班
8	王五	二班
15	趙六	二班
20	錢七1	三班

舉例2：將atguigudb3資料庫student1表中的記錄匯出到文字檔案，使用--vertical引數將該條件記錄分為多行顯示：

mysql -uroot -p --vertical --execute="SELECT * FROM student1;" atguigudb3> "/var/lib/mysql-files/student4.txt"

[root@LinuxCentOS7-132 mysql-files]# cat student4.txt 
*************************** 1. row ***************************
   id: 1
 name: 張三1
class: 一班
*************************** 2. row ***************************
   id: 3
 name: 李四
class: 一班
*************************** 3. row ***************************
   id: 8
 name: 王五
class: 二班
*************************** 4. row ***************************
   id: 15
 name: 趙六
class: 二班
*************************** 5. row ***************************
   id: 20
 name: 錢七1
class: 三班

舉例3：將atguigudb3資料庫student1表中的記錄匯出到xml檔案，使用--xml引數，具體語句如下。

mysql -uroot -p --xml --execute="SELECT * FROM student1;" atguigudb3> "/var/lib/mysql-files/student5.txt"

[root@LinuxCentOS7-132 mysql-files]# cat student5.xml 
<?xml version="1.0"?>

<resultset statement="SELECT * FROM student1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
	<field name="id">1</field>
	<field name="name">張三1</field>
	<field name="class">一班</field>
  </row>

  <row>
	<field name="id">3</field>
	<field name="name">李四</field>
	<field name="class">一班</field>
  </row>

  <row>
	<field name="id">8</field>
	<field name="name">王五</field>
	<field name="class">二班</field>
  </row>

  <row>
	<field name="id">15</field>
	<field name="name">趙六</field>
	<field name="class">二班</field>
  </row>

  <row>
	<field name="id">20</field>
	<field name="name">錢七1</field>
	<field name="class">三班</field>
  </row>
</resultset>

說明：如果要將表資料匯出到html檔案中，可以使用 --html 選項。然後可以使用瀏覽器開啟。

6.2 表的匯出

1.使用LOAD DATA INFILE方式匯入文字檔案

舉例1：

匯出檔案

SELECT * FROM student1 INTO OUTFILE "/var/lib/mysql-files/student1.txt"

刪除表中資料

delete from student1;

從文字檔案student1.txt中恢復資料：

LOAD DATA INFILE '/var/lib/mysql-files/student1.txt' INTO TABLE atguigudb3.student1;

檢視資料

mysql> select * from student1;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 張三1   | 一班   |
|  3 | 李四    | 一班   |
|  8 | 王五    | 二班   |
| 15 | 趙六    | 二班   |
| 20 | 錢七1   | 三班   |
+----+---------+--------+
5 rows in set (0.00 sec)

舉例2： 選擇資料庫atguigu，使用SELECT…INTO OUTFILE將atguigudb3資料庫student1表中的記錄匯出到文字檔案，使用FIELDS選項和LINES選項，要求欄位之間使用逗號"，"間隔，所有欄位值用雙引號括起來：

不建議使用這種方式，在匯入匯出是需要多加引數，容易出錯。

SELECT * FROM atguigudb3.student1 INTO OUTFILE '/var/lib/mysql-files/student_1.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

刪除表中資料

delete from student1;

從文字檔案student1.txt中恢復資料：

LOAD DATA INFILE '/var/lib/mysql-files/student_1.txt' INTO TABLE atguigudb3.student1 FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

檢視資料

mysql> select * from student1;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 張三1   | 一班   |
|  3 | 李四    | 一班   |
|  8 | 王五    | 二班   |
| 15 | 趙六    | 二班   |
| 20 | 錢七1   | 三班   |
+----+---------+--------+
5 rows in set (0.00 sec)

2.使用mysqlimport方式匯入文字檔案

匯出檔案

SELECT * FROM student1 INTO OUTFILE "/var/lib/mysql-files/student1.txt"

刪除表中資料

delete from student1;

從文字檔案student1.txt中恢復資料：

mysqlimport -uroot -p atguigudb3 '/var/lib/mysql-files/student1.txt'

檢視資料

mysql> select * from student1;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 張三1   | 一班   |
|  3 | 李四    | 一班   |
|  8 | 王五    | 二班   |
| 15 | 趙六    | 二班   |
| 20 | 錢七1   | 三班   |
+----+---------+--------+
5 rows in set (0.00 sec)

7.資料庫遷移

---

7.1 概述

資料遷移（data migration）是指選擇、準備、提取和轉換資料，並將資料從一個計算機儲存系統永久地傳輸到另一個計算機儲存系統的過程。此外，驗證遷移資料的完整性和退役原來舊的資料儲存，也被認為是整個資料遷移過程的一部分。資料庫遷移的原因是多樣的，包括伺服器或儲存裝置更換、維護或升級，應用程式遷移，網站整合，災難恢復和資料中心遷移。根據不同的需求可能要採取不同的遷移方案，但總體來講，MySQL 資料遷移方案大致可以分為物理遷移和邏輯遷移兩類。通常以儘可能自動化的方式執行，從而將人力資源從繁瑣的任務中解放出來。

7.2 遷移方案

• 物理遷移

物理遷移適用於大資料量下的整體遷移。使用物理遷移方案的優點是比較快速，但需要停機遷移並且要求 MySQL 版本及配置必須和原伺服器相同，也可能引起未知問題。物理遷移包括複製資料檔案和使用 XtraBackup 備份工具兩種。不同伺服器之間可以採用物理遷移，我們可以在新的伺服器上安裝好同版本的資料庫軟體，建立好相同目錄，建議配置檔案也要和原資料庫相同，然後從原資料庫方複製來資料檔案及日誌檔案，配置好檔案組許可權，之後在新伺服器這邊使用 mysqld 命令啟動資料庫。

• 邏輯遷移

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

7.3 遷移注意點

1. 相同版本的資料庫之間遷移注意點

指的是在主版本號相同的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”表示要遷移所有的資料庫。透過這種方式可以直接實現遷移。

2. 不同版本的資料庫之間遷移注意點

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

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

高版本的MySQL資料庫通常都會相容低版本，因此可以從低版本的MySQL資料庫遷移到高版本的MySQL資料庫。

3. 不同資料庫之間遷移注意點

不同資料庫之間遷移是指從其他型別的資料庫遷移到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 遷移小結

8.刪庫了不敢跑，能幹點啥？

---

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

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

1.使用delete語句誤刪資料行;

2.使用drop table或者truncate table語句誤刪資料表;

3.使用drop database語句誤刪資料庫;

4.使用rm命令誤刪整個MySQL例項。

8.1delete：誤刪行

處理措施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。

在這兩個條件都具備的情況下，假如有人中午12點誤刪了一個庫，恢復資料的流程如下：

1.取最近一次全量備份，假設這個庫是一天一備，上次備份是當天凌晨2點；

2.用備份恢復出一個臨時庫；(注意，這裡選擇臨時庫，而不是直接操作主庫)

3.從日誌備份裡面，取出凌晨2點之後的日誌；

4.把這些日誌，剔除誤刪除資料的語句外，全部應用到臨時庫。

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系統就會開始工作，選出一個新的主庫，從而保證整個叢集的正常工作。我們要做的就是在這個節點上把資料恢復回來，再接入整個叢集。

9.附錄

mysqlshow 客戶端物件查詢工具，用來很快地查詢存在哪些資料庫、資料庫中的表、表中的列或者索引。

mysqlshow [options] [db_name [table_name [col_name]]]

--count #顯示資料庫及表的統計資訊（資料庫，表 均可以不指定）

-i #顯示指定資料庫或者指定表的狀態資訊

#查詢每個資料庫的表的數量及表中記錄的數量
mysqlshow -uroot -p --count
#查詢某個資料庫表的欄位數，及行數
mysqlshow -uroot -p atguigu --count

關於宋紅康老師的百科全書級的MySQL課程的筆記，這裡就是最後一章了，很開心，能夠學完。這裡給出影片地址，筆記不明白，或不好理解的強烈推薦看原影片

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。