因為一個開發問有關mysql字元集轉換的問題,一下子牽連出mysql資料庫的<b>遷移-導入,導出,更改字元集,my.cnf參數</b>配置等等知識點,花一些時間來逐個整理一下!(<b>作為一個oracle 的小小dba,表示要學習的知識還有灰常多。。</b>)
介紹mysqldump 導出的各種場景應用!
<b>隻導出整個資料庫的表結構</b>
<b>1 指定--no-data參數的方式</b>
[root@rac3 mysqldmp]#<b> mysqldump --default-character-set=latin1 -h127.0.0.1 -uroot --no-data test > la_1141.sql </b>
[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
-- Dump completed on 2011-10-28 3:41:21
[root@rac3 mysqldmp]#
<b>2 指定--tables參數</b>
[root@rac3 mysqldmp]# <b>mysqldump --default-character-set=latin1 -h127.0.0.1 -uroot --tables -d test > la_tabs.sql</b>
[root@rac3 mysqldmp]# cat la_tabs.sql | more
-- Dump completed on 2011-10-28 3:36:13
<b>導出表結構和資料</b>
[root@rac3 mysqldmp]# <b>mysqldump --default-character-set=latin1 -h127.0.0.1 -uroot test yangtab > la_ytab.sql</b>
[root@rac3 mysqldmp]# cat la_ytab.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:38:37
<b>隻導出資料庫所有表中的資料</b>
[root@rac3 mysqldmp]#<b> mysqldump --default-character-set=latin1 -h127.0.0.1 -uroot --no-create-info test > la_1140.sql </b>
[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 */;
-- Dump completed on 2011-10-28 3:40:13
<b>導出指定表的資料:</b>
[root@rac3 mysqldmp]#<b> mysqldump --default-character-set=latin1 -h127.0.0.1 -uroot --no-create-info test yangtab > la_1143.sql</b>
[root@rac3 mysqldmp]# cat la_1143.sql
-- Dump completed on 2011-10-28 3:43:13
Note:測試環境,資料量很小,沒有考慮性能問題!