MySQL-06 資料備份和恢複

標籤：學習   資料庫系統   into   back   表名   sub   另一個   建立資料庫   .com   

學習目標

• 資料備份
• 資料恢複
• 資料庫遷移
• 匯入和匯出

 

資料備份

系統意外崩潰或者伺服器硬體損壞都有可能導致資料庫丟失，因此生產環境中資料備份非常重要。

 

MySQLdump命令備份

該命令可以將Database Backup成一個檔案檔案，該檔案包含了資料定義語句和資料操縱語句，可以執行重新建立資料庫表並插入資料。

文法格式：

mysqldump -u User -h Host -pPassword DBname[TableName,[TableName...]] > [dump file.sql]　　

樣本：備份myschool資料庫

備份文字檔：

---- Dumping data for table `subject`--LOCK TABLES `subject` WRITE;/*!40000 ALTER TABLE `subject` DISABLE KEYS */;INSERT INTO `subject` VALUES (1,‘資料庫基礎‘,1,20),(2,‘Java邏輯基礎‘,1,18),(3,‘SQL基礎‘,1,15),(4,‘HTML‘,1,20),(5,‘Control+Ajax‘,2,20),
(6,‘專案管理‘,4,40),(7,‘軟體工程‘,3,28),(8,‘演算法與資料結構‘,2,15),(9,‘C語言‘,1,25),(10,‘鄧小平理論‘,1,18),(11,‘C#物件導向‘,2,16),
(12,‘JavaWeb‘,3,40),(13,‘資料採礦‘,4,20),(14,‘英語‘,1,10),(15,‘JavaScript‘,1,15),(16,‘軟體分層架構‘,3,20);/*!40000 ALTER TABLE `subject` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET [email protected]_TIME_ZONE */;/*!40101 SET [email protected]_SQL_MODE */;/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;/*!40014 SET [email protected]_UNIQUE_CHECKS */;/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;/*!40101 SET [email protected]_COLLATION_CONNECTION */;/*!40111 SET [email protected]_SQL_NOTES */;

　　

其中：

--：表示注釋內容。

/*!......*/：表示可執行注釋，在MySQL中可執行，在其他資料庫被當成注釋。

40101：表示mysql版本，表明此語句只有在4.01.01版本或者更高版本的MySQL中才能執行。

 

樣本：備份myschool.student表

warning：在命令列中輸入密碼是不安全的。忽略。

 

樣本：備份多個資料庫

備份多個資料庫需要添加“--databases”參數，之後跟隨多個資料庫名字

如果要備份全部資料庫，使用參數：--all-databases  

 

 mysqldump的選項

  

複製資料庫目錄

備份之前需要LOCK TABLES，然後對錶執行FLUSH TABLES。也可以停止mysql服務進行備份。

缺點：1、InnoDB引擎表不支援；2、不同版本MySQL不支援。

 

MySQLhotcopy快速恢複

只能備份MyISAM引擎表。

樣本：備份mysql到d:/目錄下

mysqlhotcopy -u root -p123 myschool d:/db.sql

　　

資料恢複

將備份的資料進行恢複。

使用MySQL命令恢複

mysql命令的文法格式

mysql -u user -pPassword [DBName] < backdbfilename.sql

　　

如果backdbfilename.sql是mysqldump命令建立的包含資料庫語句的檔案，執行的時候不需要指定資料庫名。

樣本：恢複myschool資料庫

注意：需要先手動建立myschool資料庫並指定。

 

直接複製到資料庫目錄

 執行前停止mysql服務，拷貝後重啟mysql服務。

缺點：不支援InnoDB引擎表。

 

使用mysqlhotcopy快速恢複

將mysqlhotcopy匯出的資料拷貝到mysql資料庫檔案目錄下，重啟mysql服務即可。Linux中需要指定資料庫檔案的所有者，使用chown命令。

 

如果恢複的資料庫已經存在，需要使用DROP命令刪除，再進行恢複。

 

資料庫遷移

將資料從一個資料庫系統移動到另一個資料庫系統上。

相同版本MySQL資料移轉

主要版本號相同的資料庫遷移，本質是來源資料庫的備份和目標資料庫的恢複過程。

（注意：複製資料庫檔案只適合於MyISAM類型資料庫，不支援InnoDB類型資料庫）

樣本：將A主機上的MySQL資料庫全部遷移到B主機上

mysqldump -h www.a.com -uUser -pPassword DBName | mysql -h www.b.com -uUser -pPassword

"|"：管道串連符。

如果要全部遷移，使用--all-databases參數。

 

不同版本的遷移

建議匯出SQL，在目標資料庫上執行SQL。

 

表的匯出和匯入

資料表可以匯出為sql、xml或者html檔案，同時支援匯入。

使用select匯出表

文法格式

SELECT  欄位 FROM 表名 WHERE 條件 INTO OUTFILE  ‘檔案路徑‘  [選項]

　　

注意實現：需要修改my.ini的“secure-file-priv=路徑名”，MySQL只支援匯出到指定目錄。

 

使用MySQL命令匯出文字檔

文法格式

mysql -uUser -pPassword --execute="SELECT 語句" DBName >FileName.txt

　　

使用LOAD DATA INFILE匯入文字檔

文法格式

LOAD DATA INFILE ‘filename.txt‘ INTO TABLE TableName

　　

使用MySQLimport命令匯入文字檔

文法格式

mysqlimport -uUser -pPassword DBName fileName.txt

　　

 

綜合練習

需求說明

1. 使用mysqldump匯出myschool資料庫
2. 刪除myschool資料
3. 使用mysql命令恢複myschool資料庫

 

MySQL-06 資料備份和恢複