MySql邏輯備份恢復

一、Mysql邏輯備份

 

1. 使用mysqldump命令生成INSERT語句備份

此方法類似於oracle的expdp\exp工具

語法如下：

mysqldump [arguments] > file_name.sql

 

使用幫助：

[root@gc ~]# mysqldump

Usage: mysqldump [OPTIONS] database [tables]

OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

For more options, use mysqldump --help

 

備份例項：

備份所有資料庫

# mysqldump -uroot -proot --all-database > /tmp/dumpback/alldb.sql

 

備份某些資料庫

# mysqldump -uroot -proot --database sqoop hive > /tmp/dumpback/sqoop_hive.sql

 

備份某資料庫中的表

# mysqldump -uroot -proot sqoop tb1 > /tmp/dumpback/sqoop_tb1.sql

 

檢視備份內容：

[root@gc dumpback]# more sqoop_tb1.sql

-- MySQL dump 10.13  Distrib 5.5.24, for Linux (x86_64)

--

-- Host: localhost    Database: sqoop

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

-- Server version       5.5.24

 

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

......

--

-- Table structure for table `tb1`

--

DROP TABLE IF EXISTS `tb1`;

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

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb1` (

  `table_schema` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

  `table_name` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

  `table_type` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT ''

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

 

--

-- Dumping data for table `tb1`

--

 

LOCK TABLES `tb1` WRITE;

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

INSERT INTO `tb1` VALUES ('information_schema','CHARACTER_SETS','SYSTEM VIEW')

......

 

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

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

.....

 

-- Dump completed on 2013-03-25 18:26:53

 

注意事項：

如何保證資料備份的一致性？

要想保證資料的一致性可以透過以下兩種方法做到：

第一、同一時刻取出所有資料

對於事務支援的儲存引擎，如Innodb 或者BDB 等，可以透過控制將整個備份過程在同一個事務中，使用“--single-transaction”選項。

例如：

# mysqldump --single-transaction test > test_backup.sql

 

第二、資料庫中的資料處於靜止狀態

透過鎖表引數

--lock-tables 每次鎖定一個資料庫的表，此引數是預設為true(見上面備份內容例項)；

--lock-all-tables 一次鎖定所有的表，適用於dump的表分別處於各個不同的資料庫中的情況

 

 

2. 生成特定格式的純文字檔案備份

1) 透過SELECT ... TO OUTFILE FROM ...命令

透過Query將特定資料以指定方式輸出到文字檔案中，類似於oracle中的spool功能

引數說明：

FIELDS ESCAPED BY ['name'] 將SQL 語句中需要轉義的字元；

FIELDS TERMINATED BY 設定每兩個欄位之間的分隔符；

FIELDS [OPTIONALLY] ENCLOSED BY 'name' 包裝，有optionally數字型別不被包裝，否則全包裝；

LINES TERMINATED BY 'name' 行分隔符，即每記錄結束時新增的字元；

 

備份例項：

mysql> select * into outfile '/tmp/tb1.txt'

    -> fields terminated by ','

    -> optionally enclosed by '"'

    -> lines terminated by '\n' --預設

    -> from tb1 limit 50;

Query OK, 50 rows affected (0.00 sec)

 

[root@gc tmp]# more tb1.txt

"information_schema","CHARACTER_SETS","SYSTEM VIEW"

"information_schema","COLLATIONS","SYSTEM VIEW"

......

 

2) 透過mysqldump工具命令匯出文字

用此方法可以生成一個文字資料和一個對應的資料庫結構建立指令碼

主要重要引數：

-T, --tab=name      Create tab-separated textfile for each table to given

                      path. (Create .sql and .txt files.) NOTE: This only works

                      if mysqldump is run on the same machine as the mysqld

                      server.

 

備份例項：

匯出sqoop庫的tb1表

# mysqldump -uroot -proot -T /tmp sqoop tb1 --fields-enclosed-by=\" --fields-terminated-by=,

[root@gc tmp]# ls

tb1.sql  tb1.txt

 

 

二、邏輯備份恢復

1. INSERT 語句檔案的恢復

1) 使用mysql命令直接恢復

把sqoop庫的tb1表恢復到test庫

# mysql -uroot -proot -D test < /tmp/dumpback/sqoop_tb1.sql

 

2) 連線上MySql在命令列中執行恢復

上面的例子同樣可以使用下面的方法

[root@gc ~]# mysql -uroot -proot -D test

mysql> select database();

+------------+

| database() |

+------------+

| test       |

+------------+

1 row in set (0.00 sec)

 

mysql> source /tmp/dumpback/sqoop_tb1.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

......

 

或是

 

mysql> \. /tmp/dumpback/sqoop_tb1.sql

 

2. 純文字檔案的恢復

1) 使用LOAD DATA INFILE命令

此命令是SELECT ... TO OUTFILE FROM反操作，類似於oracle的sqlldr工具

語法：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'

   [REPLACE | IGNORE]

  INTO TABLE tbl_name

  [FIELDS

   [TERMINATED BY 'string']

   [[OPTIONALLY] ENCLOSED BY 'char']

   [ESCAPED BY 'char' ]

  ]

 

  [LINES

   [STARTING BY 'string']

   [TERMINATED BY 'string']

  ]

 

  [IGNORE number LINES]

  [(col_name_or_user_var,...)]

  [SET col_name = expr,...]]

 

恢復例項：

mysql> use sqoop;

Database changed

 

mysql> load data infile '/tmp/tb1.txt' into table tb1

    -> fields terminated by ','

    -> optionally enclosed by '"'

    -> lines terminated by '\n';

Query OK, 50 rows affected (0.01 sec)

Records: 50  Deleted: 0  Skipped: 0  Warnings: 0

 

2) 使用mysqlimport工具恢復

用此工具用於可用於恢復上面mysqldump生成txt和sql兩檔案，所以要保證txt檔案對應的資料庫中的表存在。

 

恢復例項：

--首先恢復表結構

[root@gc ~]# mysql -uroot -proot -D test < /tmp/tb1.sql

--恢復資料

[root@gc ~]# mysqlimport -uroot -proot test --fields-enclosed-by=\" --fields-terminated-by=, /tmp/tb1.txt

test.tb1: Records: 93  Deleted: 0  Skipped: 0  Warnings: 0