【Mysql】mysqldump 导出各种场景的应用

因为一个开发问有关mysql字符集转换的问题，一下子牵连出mysql数据库的迁移-导入，导出，更改字符集，my.cnf参数配置等等知识点，花一些时间来逐个整理一下！(作为一个oracle 的小小dba，表示要学习的知识还有灰常多。。)

介绍mysqldump 导出的各种场景应用！

只导出整个数据库的表结构

1 指定--no-data参数的方式

[root@rac3 mysqldmp]# mysqldump --default-character-set=latin1 -h127.0.0.1  -uroot --no-data  test  > la_1141.sql           

[root@rac3 mysqldmp]# cat la_1141.sql 

-- MySQL dump 10.11

-- Host: 127.0.0.1    Database: test

-- ------------------------------------------------------

-- Server version       5.0.45

-- Table structure for table `yang`

DROP TABLE IF EXISTS `yang`;

CREATE TABLE `yang` (

  `name` varchar(20) default NULL,

  `value` varchar(20) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Table structure for table `yangtab`

DROP TABLE IF EXISTS `yangtab`;

CREATE TABLE `yangtab` (

  `id` int(11) default NULL,

  `val` varchar(15) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Dump completed on 2011-10-28  3:41:21

[root@rac3 mysqldmp]# 

2 指定--tables参数

[root@rac3 mysqldmp]# mysqldump --default-character-set=latin1 -h127.0.0.1  -uroot --tables -d test > la_tabs.sql

[root@rac3 mysqldmp]# cat la_tabs.sql | more

-- Table structure for table `yang`

DROP TABLE IF EXISTS `yang`;

CREATE TABLE `yang` (

  `name` varchar(20) default NULL,

  `value` varchar(20) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Table structure for table `yangtab`

DROP TABLE IF EXISTS `yangtab`;

CREATE TABLE `yangtab` (

  `id` int(11) default NULL,

  `val` varchar(15) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Dump completed on 2011-10-28  3:36:13

[root@rac3 mysqldmp]# 

导出表结构和数据

[root@rac3 mysqldmp]# mysqldump --default-character-set=latin1 -h127.0.0.1  -uroot  test yangtab > la_ytab.sql

[root@rac3 mysqldmp]# cat la_ytab.sql 

-- Table structure for table `yangtab`

DROP TABLE IF EXISTS `yangtab`;

CREATE TABLE `yangtab` (

  `id` int(11) default NULL,

  `val` varchar(15) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Dumping data for table `yangtab`

LOCK TABLES `yangtab` WRITE;

/*!40000 ALTER TABLE `yangtab` DISABLE KEYS */;

INSERT INTO `yangtab` VALUES (1,'杨奇龙'),(2,'默默');

/*!40000 ALTER TABLE `yangtab` ENABLE KEYS */;

UNLOCK TABLES;

-- Dump completed on 2011-10-28  3:38:37

[root@rac3 mysqldmp]# 

只导出数据库所有表中的数据

[root@rac3 mysqldmp]# mysqldump --default-character-set=latin1 -h127.0.0.1  -uroot --no-create-info  test  > la_1140.sql         

[root@rac3 mysqldmp]# cat la_1140.sql 

-- Dumping data for table `yang`

LOCK TABLES `yang` WRITE;

/*!40000 ALTER TABLE `yang` DISABLE KEYS */;

INSERT INTO `yang` VALUES ('yangql','杨奇龙'),('cat','猫');

/*!40000 ALTER TABLE `yang` ENABLE KEYS */;

UNLOCK TABLES;

-- Dumping data for table `yangtab`

LOCK TABLES `yangtab` WRITE;

/*!40000 ALTER TABLE `yangtab` DISABLE KEYS */;

INSERT INTO `yangtab` VALUES (1,'杨奇龙'),(2,'默默');

/*!40000 ALTER TABLE `yangtab` ENABLE KEYS */;

UNLOCK TABLES;

-- Dump completed on 2011-10-28  3:40:13

[root@rac3 mysqldmp]# 

导出指定表的数据：

[root@rac3 mysqldmp]# mysqldump --default-character-set=latin1 -h127.0.0.1  -uroot --no-create-info  test yangtab  > la_1143.sql

[root@rac3 mysqldmp]# cat la_1143.sql 

-- Dumping data for table `yangtab`

LOCK TABLES `yangtab` WRITE;

/*!40000 ALTER TABLE `yangtab` DISABLE KEYS */;

INSERT INTO `yangtab` VALUES (1,'杨奇龙'),(2,'默默');

/*!40000 ALTER TABLE `yangtab` ENABLE KEYS */;

UNLOCK TABLES;

-- Dump completed on 2011-10-28  3:43:13

[root@rac3 mysqldmp]# 

Note：测试环境，数据量很小，没有考虑性能问题！