MySQL資料備份

brucexia發表於2024-10-12
MySql

MySQL資料備份是資料庫管理員非常重要的工作之一。系統意外崩潰或者硬體的損壞都可能導致資料的丟失，因此MySQL管理員應該定期地備份資料，使得在意外情況發生時最大限度地減少損失。本節將介紹資料備份的3種方法。

11.1.1 使用mysqldump命令備份資料

mysqldump是MySQL提供的一個非常有用的資料庫備份工具。mysqldump命令執行時，可以將資料庫備份成一個文字檔案，該檔案中實際包含了多個CREATE和INSERT語句，使用這些語句可以重新建立表和插入資料。

mysqldump備份資料庫的基本語法格式如下：

mysqldump  –u user –h host –ppassword dbname[tbname, [tbname...]]> filename.sql

user表示使用者名稱稱；host表示登入使用者的主機名稱；password為登入密碼；dbname為需要備份的資料庫名稱；tbname為dbname資料庫中需要備份的資料表，可以指定多張需要備份的表，如果不指定，則表示備份所有資料表；右箭頭符號（>）告訴mysqldump將備份資料表的定義和資料寫入備份檔案；filename.sql為備份檔案的名稱。

1. 使用mysqldump備份單個資料庫中的所有表

【例11.1】使用mysqldump命令備份單個資料庫中的所有表。

為了更好地理解mysqldump工具是如何工作的，這裡給出一個完整的資料庫例子。首先登入MySQL，按下面資料庫結構建立booksDB資料庫和各個表，並插入資料記錄。資料庫和表定義如下：

CREATE DATABASE booksDB;

use booksDB;

 

CREATE TABLE books

(

bk_id INT NOT NULL PRIMARY KEY,

bk_title VARCHAR(50) NOT NULL,

copyright YEAR NOT NULL

);

INSERT INTO books

VALUES (11078, 'Learning MySQL', 2010),

(11033, 'Study Html', 2011),

(11035, 'How to use php', 2003),

(11072, 'Teach yourself javascript', 2005),

(11028, 'Learning C++', 2005),

(11069, 'MySQL professional', 2009),

(11026, 'Guide to MySQL 9.0', 2024),

(11041, 'Inside VC++', 2011);

 

CREATE TABLE authors

(

auth_id INT NOT NULL PRIMARY KEY,

auth_name VARCHAR(20),

auth_gender CHAR(1)

);

INSERT INTO authors  

VALUES (1001, 'WriterX' ,'f'),

(1002, 'WriterA' ,'f'),

(1003, 'WriterB' ,'m'),

(1004, 'WriterC' ,'f'),

(1011, 'WriterD' ,'f'),

(1012, 'WriterE' ,'m'),

(1013, 'WriterF' ,'m'),

(1014, 'WriterG' ,'f'),

(1015, 'WriterH' ,'f');

 

CREATE TABLE authorbook

(

auth_id INT NOT NULL,

bk_id INT NOT NULL,

PRIMARY KEY (auth_id, bk_id),

FOREIGN KEY (auth_id) REFERENCES authors (auth_id),

FOREIGN KEY (bk_id) REFERENCES books (bk_id)

);

 

INSERT INTO authorbook

VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),

(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);

完成資料插入後，開啟作業系統命令列輸入視窗，輸入如下備份命令：

C:\ >mysqldump -u root -p booksdb > C:/backup/booksdb_20240301.sql

Enter password: **

這裡要保證C盤下的backup資料夾存在，否則將提示錯誤資訊：系統找不到指定的路徑。

輸入密碼之後，MySQL便對資料庫進行備份。使用文字檢視器開啟C:\backup資料夾下剛才備份過的檔案，部分檔案內容大致如下：

-- MySQL dump 10.13  Distrib 9.0.1, for Win64 (x86_64)

--

-- Host: localhost    Database: booksdb

-- ------------------------------------------------------

-- Server version    9.0.1

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

--

DROP TABLE IF EXISTS `authorbook`;

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

/*!50503 SET character_set_client = utf8mb4 */;

CREATE TABLE `authorbook` (

  `auth_id` int NOT NULL,

  `bk_id` int NOT NULL,

  PRIMARY KEY (`auth_id`,`bk_id`),

  KEY `bk_id` (`bk_id`),

  CONSTRAINT `authorbook_ibfk_1` FOREIGN KEY (`auth_id`) REFERENCES `authors` (`auth_id`),

  CONSTRAINT `authorbook_ibfk_2` FOREIGN KEY (`bk_id`) REFERENCES `books` (`bk_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--

-- Dumping data for table `authorbook`

--

LOCK TABLES `authorbook` WRITE;

/*!40000 ALTER TABLE `authorbook` DISABLE KEYS */;

INSERT INTO `authorbook` VALUES (1012,11026),(1004,11028),(1001,11033),(1002,11035),(1012,11041),(1014,11069),(1003,11072),(1011,11078);

/*!40000 ALTER TABLE `authorbook` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `authors`

--

DROP TABLE IF EXISTS `authors`;

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

/*!50503 SET character_set_client = utf8mb4 */;

CREATE TABLE `authors` (

  `auth_id` int NOT NULL,

  `auth_name` varchar(20) DEFAULT NULL,

  `auth_gender` char(1) DEFAULT NULL,

  PRIMARY KEY (`auth_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

 

--

-- Dumping data for table `authors`

--

LOCK TABLES `authors` WRITE;

/*!40000 ALTER TABLE `authors` DISABLE KEYS */;

INSERT INTO `authors` VALUES (1001,'WriterX','f'),(1002,'WriterA','f'),(1003,'WriterB','m'),(1004,'WriterC','f'),(1011,'WriterD','f'),(1012,'WriterE','m'),(1013,'WriterF','m'),(1014,'WriterG','f'),(1015,'WriterH','f');

/*!40000 ALTER TABLE `authors` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `books`

--

DROP TABLE IF EXISTS `books`;

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

/*!50503 SET character_set_client = utf8mb4 */;

CREATE TABLE `books` (

  `bk_id` int NOT NULL,

  `bk_title` varchar(50) NOT NULL,

  `copyright` year NOT NULL,

  PRIMARY KEY (`bk_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--

-- Dumping data for table `books`

--

LOCK TABLES `books` WRITE;

/*!40000 ALTER TABLE `books` DISABLE KEYS */;

INSERT INTO `books` VALUES (11026,'Guide to MySQL 9.0',2024),(11028,'Learning C++',2005),(11033,'Study Html',2011),(11035,'How to use php',2003),(11041,'Inside VC++',2011),(11069,'MySQL professional',2009),(11072,'Teach yourself javascript',2005),(11078,'Learning MySQL',2010);

/*!40000 ALTER TABLE `books` 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-25 18:39:42

可以看到，備份檔案中包含了一些資訊，檔案開頭首先表明了備份檔案使用的mysqldump工具的版本號；然後是備份賬戶的名稱和主機資訊，以及備份的資料庫的名稱；最後是MySQL伺服器的版本號，在這裡為9.0.1。

接下來是一些SET語句，這些語句將一些系統變數值賦給使用者自定義變數，以確保被恢復的資料庫的系統變數和原來備份時的變數相同，例如：

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

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

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

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

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

備份檔案中以“--”字元開頭的行為註釋語句；以“/*!”開頭、“*/”結尾的語句為可執行的MySQL註釋，這些語句可以被MySQL執行，但在其他資料庫管理系統中將被作為註釋忽略，以提高資料庫的可移植性。

另外，備份檔案的一些可執行註釋語句以數字開頭，該數字代表的是MySQL版本號，表示這些語句只有在指定的MySQL版本或者比該版本高的情況下才能執行。例如“40101”，表明這些語句只有在MySQL版本號為4.01.01或者更高的條件下才可以被執行。

2. 使用mysqldump備份單個資料庫中的某張表

mysqldump還可以備份資料庫中的某張表。

備份某張表和備份資料庫中所有表的語句的不同之處在於，要在資料庫名稱dbname之後指定需要備份的表名稱。

【例11.2】備份booksDB資料庫中的表books，SQL語句如下：

mysqldump -u root -p booksDB books > C:/backup/books_20240301.sql

該語句建立名稱為“books_20240301.sql”的備份檔案，檔案中包含了前面介紹的SET語句等內容；不同的是，該檔案只包含表books的CREATE和INSERT語句。

3. 使用mysqldump備份多個資料庫

如果要使用mysqldump備份多個資料庫，就需要使用--databases引數。備份多個資料庫的語法格式如下：

mysqldump  –u user –h host –p --databases  [dbname, [dbname...]] > filename.sql

使用--databases引數之後，必須指定至少一個資料庫的名稱，多個資料庫名稱之間用空格隔開。

【例11.3】使用mysqldump備份資料庫booksDB和test_db，SQL語句如下：

mysqldump -u root -p --databases  booksDB test_db>C:\backup\books_testDB_20240301.sql

該語句建立名稱為“books_testDB_20240301.sql”的備份檔案，該檔案中包含了建立兩個資料庫booksDB和test_db所必需的所有語句。

另外，使用--all-databases引數可以備份系統中所有的資料庫，SQL語句如下：

mysqldump  –u user –h host –p --all-databases > filename.sql

使用引數--all-databases時，不需要指定資料庫名稱。

【例11.4】使用mysqldump備份伺服器中的所有資料庫，輸入語句如下：

mysqldump  -u root -p --all-databases > C:/backup/alldbinMySQL.sql

該語句建立名稱為“alldbinMySQL.sql”的備份檔案，檔案中包含了系統中所有資料庫的備份資訊。

如果在伺服器上進行備份，並且表均為MyISAM表，就應該考慮使用MySQLhotcopy，因為可以更快地進行備份和恢復。

11.1.2 直接複製整個資料庫目錄

因為MySQL表儲存為檔案方式，所以可以直接複製MySQL資料庫的儲存目錄和檔案進行備份。MySQL的資料庫目錄位置不一定相同，在Windows平臺下，MySQL 9.0存放資料庫的目錄通常為“C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 9.0\data”或者其他使用者自定義目錄；在Linux平臺下，資料庫目錄位置通常為“/var/lib/MySQL/”，不同Linux版本下目錄會有所不同，讀者應在自己使用的平臺下查詢該目錄。

這是一種簡單、快速、有效的備份方式。要想保持備份的一致性，備份前需要對相關表執行LOCK TABLES操作，然後對錶執行FLUSH TABLES語句。FLUSH TABLES語句可以確保開始備份前將所有啟用的索引頁寫入硬碟，這樣當複製資料庫目錄中的檔案時，將允許其他客戶繼續查詢表。當然，也可以停止MySQL服務再進行備份操作。

這種方法雖然簡單，但並不是最好的，因為這種方法對InnoDB儲存引擎的表不適用。使用這種方法備份的資料最好恢復到相同版本的伺服器中，不同的版本可能不相容。

在MySQL版本號中，第一個數字表示主版本號，主版本號相同的MySQL資料庫檔案格式相同。

11.1.3 使用MySQLhotcopy工具快速備份

MySQLhotcopy是一個Perl指令碼，最初由Tim Bunce編寫並提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp來快速備份資料庫。它是備份資料庫或單表最快的途徑，但它只能執行在資料庫目錄所在的機器上，並且只能備份MyISAM型別的表。MySQLhotcopy在UNIX系統中執行。

MySQLhotcopy命令的語法格式如下：

mysqlhotcopy db_name_1, ... db_name_n  /path/to/new_directory

db_name_1,…,db_name_n分別為需要備份的資料庫的名稱；“/path/to/new_directory”指定備份檔案目錄。

【例11.5】使用MySQLhotcopy備份資料庫test_db到“/usr/backup”目錄下，SQL語句如下：

mysqlhotcopy  -u root –p test_db /usr/backup

要想執行MySQLhotcopy，必須可以訪問備份的表檔案，具有那些表的SELECT許可權、RELOAD許可權（以便能夠執行FLUSH TABLES）和LOCK TABLES許可權。

MySQLhotcopy只是將表所在的目錄複製到另一個位置，只能用於備份MyISAM表和ARCHIVE表，備份InnoDB型別的資料表時會出現錯誤資訊。由於它複製本地格式的檔案，因此也不能移植到其他硬體或作業系統下。

相關文章