MySQL備份與恢復
mysqldump工具用法
1.檢視mysqldump幫助mysqldump --help
root@chenwx-VirtualBox:/backup# mysqldump --help
mysqldump Ver 10.13 Distrib 5.6.28, for debian-linux-gnu (x86_64)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
2. mysqldump 備份全庫
備份全庫至/backup/fullbackup.sql
root@chenwx-VirtualBox:/backup# mysqldump --all-databases > /backup/fullbackup.sql
說明：
--all-databases：轉儲所有資料庫
>：指定輸出
/backup/：路徑
fullbackup.sql：備份檔案
3. mysqldump 備份test 、test2資料庫
root@chenwx-VirtualBox:/backup# mysqldump --databases test test2>/backup/backuptest.sql
--database：轉儲指定資料庫多個資料庫用空格分隔
檢視備份資訊
root@chenwx-VirtualBox:/backup# cat backuptest.sql
-- MySQL dump 10.13 Distrib 5.6.28, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.6.28-0ubuntu0.15.10.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 */;
/*!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 */;
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
--
-- 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) DEFAULT NULL,
`value` varchar(30) DEFAULT NULL
) 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,NULL),(2,''),(3,'100.000.000'),(4,'1000'),(5,'1000萬元'),(6,'一百萬元');
/*!40000 ALTER TABLE `t` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Current Database: `test2`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test2` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test2`;
--
-- Table structure for table `t2`
--
DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t2`
--
LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
/*!40000 ALTER TABLE `t2` 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 2016-05-05 10:48:22
4.mysql備份指定資料表
轉儲test資料庫表t
root@chenwx-VirtualBox:/backup# mysqldump test t>/backup/testt.sql
root@chenwx-VirtualBox:/backup# ll
總用量 652
drwxr-xr-x 2 root root 4096 5月 5 11:01 ./
drwxr-xr-x 24 root root 4096 5月 5 09:28 ../
-rw-r--r-- 1 root root 2652 5月 5 10:48 backuptest.sql
-rw-r--r-- 1 root root 647363 5月 5 10:41 fullbackup.sql
-rw-r--r-- 1 root root 1890 5月 5 11:01 testt.sql
root@chenwx-VirtualBox:/backup# cat testt.sql
-- MySQL dump 10.13 Distrib 5.6.28, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.6.28-0ubuntu0.15.10.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 */;
/*!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 */;
--
-- 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) DEFAULT NULL,
`value` varchar(30) DEFAULT NULL
) 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,NULL),(2,''),(3,'100.000.000'),(4,'1000'),(5,'1000萬元'),(6,'一百萬元');
/*!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 2016-05-05 11:01:01
5. 轉儲資料庫test2表t1、t2及資料
root@chenwx-VirtualBox:/backup# mysqldump test2 t1 t2>/backup/test2t1t2.sql
root@chenwx-VirtualBox:/backup# cat test2t1t2.sql
-- MySQL dump 10.13 Distrib 5.6.28, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: test2
-- ------------------------------------------------------
-- Server version 5.6.28-0ubuntu0.15.10.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 */;
/*!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 */;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`t1name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'t1'),(1,'t11');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `t2`
--
DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t2`
--
LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
INSERT INTO `t2` VALUES (1),(2);
/*!40000 ALTER TABLE `t2` 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 2016-05-05 11:04:21
6.匯出整個資料庫結構
root@chenwx-VirtualBox:/backup# mysqldump --all-databases -d > alldatabase.sql
7.匯出指定資料庫結構
root@chenwx-VirtualBox:/backup# mysqldump -d test2 > str_test2.sql
root@chenwx-VirtualBox:/backup# cat str_test2.sql
-- MySQL dump 10.13 Distrib 5.6.28, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: test2
-- ------------------------------------------------------
-- Server version 5.6.28-0ubuntu0.15.10.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 */;
/*!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 */;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`t1name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `t2`
--
DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 2016-05-05 11:21:39
8.匯出單個表結構
root@chenwx-VirtualBox:/backup# mysqldump -d test2 t2> str_test2t2.sql
root@chenwx-VirtualBox:/backup# cat str_test2t2.sql
-- MySQL dump 10.13 Distrib 5.6.28, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: test2
-- ------------------------------------------------------
-- Server version 5.6.28-0ubuntu0.15.10.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 */;
/*!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 */;
--
-- Table structure for table `t2`
--
DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 2016-05-05 11:23:48
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28282660/viewspace-2112816/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 備份與恢復MySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- Mysql備份與恢復(1)---物理備份MySql
- MySQL 非常規恢復與物理備份恢復MySql
- Mysql備份與恢復(2)---邏輯備份MySql
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- 《入門MySQL—備份與恢復》MySql
- MySQL備份與恢復——實操MySql
- 入門MySQL——備份與恢復MySql
- MySQL備份與恢復操作解析MySql
- Mysql資料備份與恢復MySql
- Mysql備份恢復MySql
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- Mysql的幾種備份與恢復MySql
- MySQL入門--備份與恢復（三）MySql
- MySQL入門--備份與恢復（一）MySql
- MySQL入門--備份與恢復（二）MySql
- MySQL 日誌管理、備份與恢復MySql
- RAC備份恢復之Voting備份與恢復
- 【MySQL】MySQL備份和恢復MySql
- 備份與恢復：polardb資料庫備份與恢復資料庫
- Jenkins備份與恢復Jenkins
- Postgresql 備份與恢復SQL
- mysql學習筆記之備份與恢復MySql筆記
- MySQL-19.資料庫備份與恢復MySql資料庫
- docker 中 MySQL 備份及恢復DockerMySql
- Oracle 備份 與 恢復 概述Oracle
- DB的備份與恢復
- GitLab的備份與恢復Gitlab
- 《MySQL 入門教程》第 06 篇 備份與恢復MySql
- Linux下MySQL資料庫的備份與恢復LinuxMySql資料庫
- 【MySQL】Xtrabackup備份及恢復指令碼MySql指令碼
- Mysql資料庫備份及恢復MySql資料庫
- MySQL8.4備份恢復快速命令MySql
- MySQL日誌管理，備份和恢復MySql
- MySQL備份和恢復方法彙總MySql
- GitLab的自動備份、清理備份與恢復Gitlab
- MySQL不同儲存引擎的資料備份與恢復MySql儲存引擎
- 備份與恢復oracle_homeOracle