By -
陳 思敬
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
Tagged : MySQL