How to Use mysqldump to export databases and how to import Databases

 

Use mysqldump to export databases.

First, I met a database named mydb,

There are two tables, mytable and mytabletwo.

Both tables have data.

 

C: \ Program Files \ mysql \ MySQL Server 5.1 \ bin> mysqldump-uroot-proot-D mydb> C:/K
Ujiegou. SQL
// Export the structure of all tables in the specified database.
(Specifically, drop before create)

 

 

C: \ Program Files \ mysql \ MySQL Server 5.1 \ bin> mysqldump-uroot-proot-D mydb myta
Ble> C:/biaojiegou. SQL
// Export the structure of the specified table in the specified database.
(Specifically, drop before create)

 

 

C: \ Program Files \ mysql \ MySQL Server 5.1 \ bin> mysqldump-uroot-proot mydb> C:/All.
SQL
// Export the structure and data of all tables in the specified database.
(Specifically, drop first, create, and then insert) This is the most common!

 

 

C: \ Program Files \ mysql \ MySQL Server 5.1 \ bin> mysqldump-uroot-proot mydb mytable
> C:/alltable. SQL
// Export the structure and data of the specified table in the specified database.
(Specifically, drop, create, and insert)

The following is how to use the command.

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

For remote export, add the IP address before the user name and password:

Mysqldump-h10.6.50.238-uroot-proot SMP> C:/smp3. SQL

The exported SQL script is as follows:

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

 

Now we have successfully exported the database,

Generally, the most common method is to export all the table structures and data of a database.

That is, all. SQL above.

Now we will practice how to import data.

Create an empty database mydbtwo first. There are no tables in it.

Go to the database and use the source File URL Command to import the data, as shown in the following code:

 

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>

 

Note source C:/all. SQL

// Supplement: if multiple tables in a database are exported, separate the names of the tables with spaces. For example, if you want to export one of the two tables in the mydb database

The SQL statement is as follows:

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