MYSQL备份与恢复

于 2016-06-02 14:01:08 发布
阅读量112 收藏
点赞数
文章标签： 数据库

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28282660/viewspace-2112816/

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值