使用mysqldump匯出資料庫的幾種方式以及如何匯入資料庫

 

使用mysqldump匯出資料庫的幾種方式。

首先我見了一個叫mydb的資料庫，

裡面有兩張表mytable和mytabletwo。

這兩張表都有資料。

 

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump -uroot -proot -d mydb>c:/k
ujiegou.sql
//把指定的資料庫裡所有表的結構匯出。
(具體就是先drop再create)

 

 

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump -uroot -proot -d mydb myta
ble>c:/biaojiegou.sql
//把指定的資料庫裡指定的表的結構匯出。
(具體就是先drop再create)

 

 

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump -uroot -proot mydb>c:/all.
sql
//把指定的資料庫裡所有表的結構和資料匯出。
(具體就是先drop再create，再insert)這個最常用！

 

 

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump -uroot -proot mydb mytable
>c:/alltable.sql
//把指定的資料庫裡指定的表的結構和資料匯出。
(具體就是先drop再create，再insert)

以下是命令的使用。

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump -uroot -proot -d mydb>c:/kujiegou.sqlC:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump -uroot -proot -d mydb mytable>c:/biaojiegou.sqlC:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump -uroot -proot mydb>c:/all.sqlC:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump -uroot -proot mydb mytable>c:/alltable.sql

 關於遠程匯出，在使用者名稱，密碼前加上地址ip即可：

mysqldump -h10.6.50.238 -uroot -proot smp>c:/smp3.sql  

具體匯出的sql指令碼如下：

biaojiegou.sql

-- MySQL dump 10.13  Distrib 5.1.57, for Win32 (ia32)---- Host: localhost    Database: mydb-- -------------------------------------------------------- Server version5.1.57-community/*!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 `mytable`--DROP TABLE IF EXISTS `mytable`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `mytable` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) DEFAULT NULL,  `sex` char(2) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 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 2012-04-18 17:08:14

 

kujiegou.sql

-- MySQL dump 10.13  Distrib 5.1.57, for Win32 (ia32)---- Host: localhost    Database: mydb-- -------------------------------------------------------- Server version5.1.57-community/*!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 `mytable`--DROP TABLE IF EXISTS `mytable`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `mytable` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) DEFAULT NULL,  `sex` char(2) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Table structure for table `mytabletwo`--DROP TABLE IF EXISTS `mytabletwo`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `mytabletwo` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `hobby` char(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 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 2012-04-18 17:07:07

 

all.sql

-- MySQL dump 10.13  Distrib 5.1.57, for Win32 (ia32)---- Host: localhost    Database: mydb-- -------------------------------------------------------- Server version5.1.57-community/*!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 `mytable`--DROP TABLE IF EXISTS `mytable`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `mytable` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) DEFAULT NULL,  `sex` char(2) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `mytable`--LOCK TABLES `mytable` WRITE;/*!40000 ALTER TABLE `mytable` DISABLE KEYS */;INSERT INTO `mytable` VALUES (1,'zhangsan','1'),(2,'lisi','1');/*!40000 ALTER TABLE `mytable` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `mytabletwo`--DROP TABLE IF EXISTS `mytabletwo`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `mytabletwo` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `hobby` char(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `mytabletwo`--LOCK TABLES `mytabletwo` WRITE;/*!40000 ALTER TABLE `mytabletwo` DISABLE KEYS */;INSERT INTO `mytabletwo` VALUES (1,'football'),(2,'busketball');/*!40000 ALTER TABLE `mytabletwo` 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 2012-04-18 17:09:45

 

alltable.sql

-- MySQL dump 10.13  Distrib 5.1.57, for Win32 (ia32)---- Host: localhost    Database: mydb-- -------------------------------------------------------- Server version5.1.57-community/*!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 `mytable`--DROP TABLE IF EXISTS `mytable`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `mytable` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) DEFAULT NULL,  `sex` char(2) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `mytable`--LOCK TABLES `mytable` WRITE;/*!40000 ALTER TABLE `mytable` DISABLE KEYS */;INSERT INTO `mytable` VALUES (1,'zhangsan','1'),(2,'lisi','1');/*!40000 ALTER TABLE `mytable` 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 2012-04-18 17:10:58

 

 現在我們已經成功匯出資料庫了，

一般最常用的還是把一個資料庫所有表結構和資料全匯出。

也就是上面的all.sql。

現在我們練習如何匯入。

先建立一個空的資料庫mydbtwo.裡面什麼表也沒有。

然後進入這個資料庫，使用source 檔案url這個命令匯入，具體如下：

 

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -prootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 21Server version: 5.1.57-community MySQL Community Server (GPL)Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL v2 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mydb               || mydbtwo            || mysql              || test               |+--------------------+5 rows in set (0.00 sec)mysql> use mydbtwo;Database changedmysql> show tables;Empty set (0.00 sec)mysql> source c:/all.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.06 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 2 rows affected (0.02 sec)Records: 2  Duplicates: 0  Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.07 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 2 rows affected (0.02 sec)Records: 2  Duplicates: 0  Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> show tables;+-------------------+| Tables_in_mydbtwo |+-------------------+| mytable           || mytabletwo        |+-------------------+2 rows in set (0.00 sec)mysql> select * from mytable;+----+----------+------+| id | name     | sex  |+----+----------+------+|  1 | zhangsan | 1    ||  2 | lisi     | 1    |+----+----------+------+2 rows in set (0.00 sec)mysql>

 

 注意source c:/all.sql

//補充，如果匯出某個庫的多張表，後面的表名依次用空格隔開就行，例如我要匯出mydb庫中某兩張表

的表結構，sql如下：

mysqldump -h127.0.0.1 -uroot -proot -d mydb lsy_department lsy_user_develop>d:/mydbtablestruct.sql