MySQL備份與恢復(二)
- 2019 年 11 月 6 日
- 筆記
MySQL備份與恢復(二)
備份與恢復方法示例
前天的文章中簡單寫了備份與恢復的方法，今天我們主要來看看不同的備份方法備份出來的文件結果，以及它們的恢復方法。
首先我們來看mysqldump備份出來的文件的內容，現將表t進行備份：
root@localhost:3306 [test]>select * from t; +----+-----+ | id | age | +----+-----+ | 1 | 5 | | 2 | 4 | | 3 | 3 | | 4 | 2 | | 5 | 1 | +----+-----+ 5 rows in set (0.08 sec) root@localhost:3306 [test]>exit Bye C:Usersyeyz>mysqldump --single-transaction -uroot -p test t > dump.sql Enter password: ********
然後打開備份文件，我們可以看到備份文件中的內容如下：
-- MySQL dump 10.13 Distrib 5.7.24, for Win32 (AMD64) -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.7.24-log /*!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 */; /*!40101 SET NAMES utf8 */; /*!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 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=154; -- -- Table structure for table `t` -- DROP TABLE IF EXISTS `t`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t` ( `id` int(11) NOT NULL, `age` int(11) NOT NULL, KEY `idx_t` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `t` -- LOCK TABLES `t` WRITE; /*!40000 ALTER TABLE `t` DISABLE KEYS */; INSERT INTO `t` VALUES (1,5),(2,4),(3,3),(4,2),(5,1); /*!40000 ALTER TABLE `t` 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 2019-01-20 19:24:07
可以看到第22行是當前數據庫的二進制日誌位置，這通常用於在從庫同步主庫的數據的時候，第31-35行是表的定義，38行是表的數據，而且只備份了數據庫test中的t這一個表。
當我們想恢復這個備份文件的時候，我們可以通過mysql -u<user> -p dbname < dump.sql的命令來進行恢復，示例如下：
root@localhost:3306 [test]>show tables; +----------------+ | Tables_in_test | +----------------+ | t | | v_t | | z | | zz | +----------------+ 4 rows in set (0.00 sec) root@localhost:3306 [test]>drop table t; Query OK, 0 rows affected (0.77 sec) root@localhost:3306 [test]>exit Bye C:Usersyeyz>mysql -uroot -p test < dump.sql Enter password: ******** C:Usersyeyz>mysql -uroot -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 6 Server version: 5.7.24-log MySQL Community Server (GPL) Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. root@localhost:3306 [test]>use test Database changed root@localhost:3306 [test]>show tables; +----------------+ | Tables_in_test | +----------------+ | t | | v_t | | z | | zz | +----------------+ 4 rows in set (0.00 sec) root@localhost:3306 [test]>select * from t; +----+-----+ | id | age | +----+-----+ | 1 | 5 | | 2 | 4 | | 3 | 3 | | 4 | 2 | | 5 | 1 | +----+-----+ 5 rows in set (0.00 sec)
再來看看select into outfile導出文件的試驗過程，話不多說，備份的代碼如下：
root@localhost:3306 [test]>select * from t; +----+-----+ | id | age | +----+-----+ | 1 | 5 | | 2 | 4 | | 3 | 3 | | 4 | 2 | | 5 | 1 | +----+-----+ 5 rows in set (0.00 sec) root@localhost:3306 [(none)]>use test Database changed root@localhost:3306 [test]>select * from t into outfile 'back.sql' ; Query OK, 5 rows affected (0.01 sec) #查看文件內容 1 5 2 4 3 3 4 2 5 1
我們可以看到，這種備份方法相當於只備份了表裏面的數據，而沒有備份表的結構。如果我們覺得這個備份結果的格式不是我們想要的，還可以改成其他的風格，如下：
root@localhost:3306 [test]>select * from t into outfile 'back3.sql' fields termi nated by ',' lines starting by '本行記錄內容:' terminated by 'rn' ; 本行記錄內容:1,5 本行記錄內容:2,4 本行記錄內容:3,3 本行記錄內容:4,2 本行記錄內容:5,1
代碼中的fields terminated表示列之間的分隔符，lines starting表示每一行開始的符號，lines terminated表示每一行結束的符號，windows中'rn'表示換行。
這種備份方式恢復的時候需要採用load data infile的方式，下面給出例子：
root@localhost:3306 [test]>show tables; +----------------+ | Tables_in_test | +----------------+ | t | | v_t | | z | | zz | +----------------+ 4 rows in set (0.00 sec) root@localhost:3306 [test]>drop table t; Query OK, 0 rows affected (0.38 sec) root@localhost:3306 [test]>load data into table t ignore 1 lines infile 'data.sql'; root@localhost:3306 [test]>load data into table t ignore 1 lines infile 'e:/mysq l-5.7.24-win32/data/test/back.sql'; root@localhost:3306 [test]>load data infile 'e:/mysql-5.7.24-win32/data/test/ba ck.sql' into table t; ERROR 1146 (42S02): Table 'test.t' doesn't exist root@localhost:3306 [test]>create table t (id int,age int); Query OK, 0 rows affected (0.63 sec) root@localhost:3306 [test]>load data infile 'e:/mysql-5.7.24-win32/data/test/back.sql' into table t; Query OK, 5 rows affected (0.21 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 root@localhost:3306 [test]>select * from t; +------+------+ | id | age | +------+------+ | 1 | 5 | | 2 | 4 | | 3 | 3 | | 4 | 2 | | 5 | 1 | +------+------+ 5 rows in set (0.00 sec) root@localhost:3306 [test]>
上面的例子有個小問題需要注意一下，就是我們在導入一個表數據的時候，這個表不能刪除，表結構必須存在，不然是沒辦法導入的，會提示table doesn't exist的錯誤。
再來看看mysqlimport方法，從本質上來說，它是load data infile的命令接口，而且大多數的選項都和load data infile語法相同，它的語法格式如下：
mysqlimport dbname text1 text2
與load data infile不同的是，mysqlimport可以用來導入多張表，並且通過–user-thread參數並發地導入不同的文件，這裡的並發值得是並發導入多個文件，而不是指mysqlimport可以並發的導入一個文件，這是有明顯區別的。如果有興趣，可以試一下在load data的同時，連接mysql然後在命令行中show full processlist;這個時候，就可以看到mysqlimport實際上是同時執行多句load data infile的命令。
