mysqldump SQL 호환성 높이기

mysqldump를 사용해 SQL 스크립트를 만들어낼 때 여러가지 목적이 있겠지만 다른 데이터베이스와의 호환성을 염두에 두는 경우가 종종 있다. 예를 들어 MySQL을 개발용으로만 사용하고 다른 데이터베이스를 실운영용으로 사용하는 경우가 있을 수 있다.

데이터베이스간의 호환성 문제는 단순한 문제가 아니므로 이 글에서는 일단 mysqldump 명령만을 주제로 SQL 호환성을 최대한 높이는 방법을 알아보자. mysqldump로 결과물을 어느 정도 호환성 있게만 만들어도 많은 수고를 줄일 수 있다. 우선 mysqldump는 다음과 같은 형식으로 명령을 실행한다.

mysqldump [옵션] [DB명 [테이블명 ...]]

mysqldump의 실행을 제어하는 옵션 중 호환성에 관련된 건 다음과 같다. 참고로 모든 옵션은 --xxx-xxx와 같은 형태인데 기본값으로 동작하도록 켜있는 경우가 많고 --skip-xxx-xxx와 같이 하여 그 옵션을 끌 수 있다.

--compatible=<span style="text-decoration: underline;">명칭</span> - 호환성을 위해 가장 중요한 옵션이다. 명칭으로는 mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options 중 하나 이상을 콤마로 구분해주면 된다. 그러나 이 옵션이 데이터 형식을 매핑해준다거나 SQL 구문 자체를 번역하는 건 아니다. MySQL 특유의 구문을 정제해주는 것 뿐이다.
--set-charset - SQL 스크립트 결과물에 SET NAMES 문자셋 문을 넣어주는 옵션이다. 이러한 문은 다른 데이터베이스에서는 필요 없으므로 --skip-set-charset으로 꺼줘야 한다.
--quote-names - 이 옵션은 데이터베이스나 테이블, 컬럼명 등의 식별어에 작은따옴표를 붙여준다. ANSI SQL에서는 큰따옴표를 쓰도록 돼있고 또한 따옴표를 쓰면 대소문자가 구별되는 문제가 생기므로 따로 필요한 경우가 아니면 끄는 것이 좋다.
--opt - 이 옵션은 편의성을 위해 --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, --set-charset 옵션을 하나로 합쳐놓은 것이다. 이중 특히 --add-locks, --create-options, --extended-insert 등의 옵션은 MySQL만의 구문을 사용하므로 사용하지 않는 게 호환성이 높아진다. 이것들을 끄려면 --skip-opt를 붙여야 한다.

이상과 같은 옵션을 염두에 두고 실제 예시를 보자. 원래 별다른 옵션 없이 mysqldump을 실행한 경우 결과물이 다음과 같았다면,

-- MySQL dump 10.13  Distrib 5.1.12, for redhat-linux-gnu (i386)
**중략**

--
-- Table structure for table `wp_commentmeta`
--

DROP TABLE IF EXISTS `wp_commentmeta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `wp_commentmeta`
--

LOCK TABLES `wp_commentmeta` WRITE;
/*!40000 ALTER TABLE `wp_commentmeta` DISABLE KEYS */;
INSERT INTO `wp_commentmeta` VALUES (8,3,'akismet_result','false'),(9,3,'akismet_history',
**중략**,(29,7,'hc_foreign_user_id','1');
/*!40000 ALTER TABLE `wp_commentmeta` ENABLE KEYS */;
UNLOCK TABLES;

** 생략 **

이제 다음과 같이 mysqldump를 실행하면,

mysqldump -uuser -p --skip-opt --compatible="no_table_options, no_field_options, no_key_options, oracle" --skip-quote-names mydb > mydb.sql

다음과 같이 보다 호환성이 높은 SQL 스크립트가 만들어진다.

-- MySQL dump 10.13  Distrib 5.1.12, for redhat-linux-gnu (i386)
**중략**

--
-- Table structure for table "wp_commentmeta"
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE wp_commentmeta (
  meta_id bigint(20) unsigned NOT NULL,
  comment_id bigint(20) unsigned NOT NULL DEFAULT '0',
  meta_key varchar(255) DEFAULT NULL,
  meta_value longtext,
  PRIMARY KEY (meta_id),
  KEY comment_id (comment_id),
  KEY meta_key (meta_key)
);
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table "wp_commentmeta"
--

INSERT INTO wp_commentmeta VALUES (8,3,'akismet_result','false');
INSERT INTO wp_commentmeta VALUES (9,3,'akismet_history',
**중략**
INSERT INTO wp_commentmeta VALUES (29,7,'hc_foreign_user_id','1');

**생략**