MySQL mysqldump 导入导出数据库

日常我们在进行 MySQL 备份还原的时候，多用 phpMyAdmin，今天测试了命令行下操作整个流程，记录 Shell 以备后查。

~  mysqldump --help
mysqldump  Ver 10.13 Distrib 5.5.29, for osx10.8 (i386)
Copyright (c) 2000, 2012, 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]
• mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]
• mysqldump [OPTIONS] –all-databases [OPTIONS]

Step 1：登录MySQL

 ~  mysql -uuser -ppassword
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs6               |
...
| conwiki            |
| myapp_development  |
| myapp_test         |
| test               |
+--------------------+

Step 2：创建新的数据库

mysql> create database tylenol;
Query OK, 1 row affected (0.00 sec)

Step 3：导入数据

mysql> use tylenol;
Database changed
mysql> source /Users/Christen/Desktop/ty.dmp;
...
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
...
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
...
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-----------------------------+
| Tables_in_tylenol           |
+-----------------------------+
| cmn_cache                   |
| cmn_syscfg                  |
| cmn_template                |
...
| cmn_usr_userrightexd        |
| cmn_usr_users               |
| usr_userinfo                |
+-----------------------------+
23 rows in set (0.00 sec)

mysql> select * from usr_userinfo;
-- 数据正常

Step 4：测试导出

~  mysqldump -uuser -ppassword tylenol > /Users/Christen/Desktop/tyl.sql
~  mysqldump -uuser -ppassword tylenol > /Users/Christen/Desktop/tyl.dmp
~  mysqldump -uuser -ppassword tylenol > /Users/Christen/Desktop/tyl.txt

导出文件的后缀名看使用场合，客户打不开dmp文件情有可原，但是程序员也打不开，就不正常了。
按条件导出：

 ~  mysqldump -uuser -ppassword tylenol usr_userinfo --where="user_id < 4" > /Users/Christen/Desktop/tyl-top3.sql

导出结果：

-- MySQL dump 10.13  Distrib 5.5.29, for osx10.8 (i386)
--
-- Host: localhost    Database: tylenol
-- ------------------------------------------------------
-- Server version	5.5.29
/*!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 `usr_userinfo`
--
DROP TABLE IF EXISTS `usr_userinfo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `usr_userinfo` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户代码',
  `user_phone` varchar(11) DEFAULT NULL COMMENT '用户手机号码',
  `submit_time` datetime DEFAULT NULL COMMENT '提交时间',
  `ip` varchar(100) DEFAULT NULL COMMENT 'IP',
  `memo` varchar(200) DEFAULT NULL COMMENT '备注',
  `cmn_createuserid` bigint(20) DEFAULT NULL COMMENT '创建用户代码',
  `cmn_createdate` datetime DEFAULT NULL COMMENT '创建日期',
  `cmn_lastuserid` bigint(20) DEFAULT NULL COMMENT '更新用户代码',
  `cmn_modifydate` datetime DEFAULT NULL COMMENT '更新日期',
  `cmn_islock` char(1) DEFAULT '0' COMMENT '是否锁定:0:否;1:是;',
  `cmn_isvalid` char(1) DEFAULT '1' COMMENT '是否有效:0:否;1:是;',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1036 DEFAULT CHARSET=utf8 COMMENT='用户留资表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `usr_userinfo`
--
-- WHERE:  user_id < 4
LOCK TABLES `usr_userinfo` WRITE;
/*!40000 ALTER TABLE `usr_userinfo` DISABLE KEYS */;
INSERT INTO `usr_userinfo` VALUES (1,'13419646950','2015-01-22 23:11:32',NULL,NULL,NULL,NULL,NULL,NULL,'0','1'),(2,'13652142539','2015-01-22 23:21:19',NULL,NULL,NULL,NULL,NULL,NULL,'0','1'),(3,'13419646955','2015-01-23 17:12:20','58.40.45.36',NULL,NULL,NULL,NULL,NULL,'0','1');
/*!40000 ALTER TABLE `usr_userinfo` 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 2015-02-26 12:45:36

只导出表结构：

mysqldump --opt -d tylenol -uuser -ppassword > /Users/Christen/Desktop/tyl-st.sql