因为一个开发问有关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：测试环境，数据量很小，没有考虑性能问题！
