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
