MySQL 데이터를 SQLite로 옮기는 방법

해당 소스를 실행하면 됩니다.

#!/bin/sh

# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the

# CREATE block and create them in separate commands _after_ all the INSERTs.

# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.

# The mysqldump file is traversed only once.

# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite

# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite

# Thanks to and @artemyk and @gkuenning for their nice tweaks.

mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \

awk '

BEGIN {

	FS=",$"

	print "PRAGMA synchronous = OFF;"

	print "PRAGMA journal_mode = MEMORY;"

	print "BEGIN TRANSACTION;"

}

# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.

/^\/\*.*CREATE.*TRIGGER/ {

	gsub( /^.*TRIGGER/, "CREATE TRIGGER" )

	print

	inTrigger = 1

	next

}

# The end of CREATE TRIGGER has a stray comment terminator

/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }

# The rest of triggers just get passed through

inTrigger != 0 { print; next }

# Skip other comments

/^\/\*/ { next }

# Print all `INSERT` lines. The single quotes are protected by another single quote.

/INSERT/ {

	gsub( /\\\047/, "\047\047" )

	gsub(/\\n/, "\n")

	gsub(/\\r/, "\r")

	gsub(/\\"/, "\"")

	gsub(/\\\\/, "\\")

	gsub(/\\\032/, "\032")

	print

	next

}

# Print the `CREATE` line as is and capture the table name.

/^CREATE/ {

	print

	if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 ) 

}

# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`

/^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }

# Get rid of field lengths in KEY lines

/ KEY/ { gsub(/\([0-9]+\)/, "") }

# Print all fields definition lines except the `KEY` lines.

/^  / && !/^(  KEY|\);)/ {

	gsub( /AUTO_INCREMENT|auto_increment/, "" )

	gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )

	gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )

	gsub( /(COLLATE|collate) [^ ]+ /, "" )

	gsub(/(ENUM|enum)[^)]+\)/, "text ")

	gsub(/(SET|set)\([^)]+\)/, "text ")

	gsub(/UNSIGNED|unsigned/, "")

	if (prev) print prev ","

	prev = $1

}

# `KEY` lines are extracted from the `CREATE` block and stored in array for later print 

# in a separate `CREATE KEY` command. The index name is prefixed by the table name to 

# avoid a sqlite error for duplicate index name.

/^(  KEY|\);)/ {

	if (prev) print prev

	prev=""

	if ($0 == ");"){

		print

	} else {

		if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 

		if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 

		key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"

	}

}

# Print all `KEY` creation lines.

END {

	for (table in key) printf key[table]

	print "END TRANSACTION;"

}

'

exit 0


해당 쉘스크립트를 실행하기 위해
MySQL 실행방법에서 옵션 부분을 확인해 보면 mysql 아이디 패스워드 DB명 이런 식으로 실행한다고 가정하자.
(여기서는 시스템 마다 옵션 부분의 내용이 다르므로 각자의 환경에 맞게 하자!)

ex) mysql -u userid -ppassword database
위의 예제에서 mysql의 옵션은 -u userid -ppassword database 이다.
-u와 아이디 값은 붙여도 띄어써도 상관 없으나 -p 뒤의 password는 반드시 붙여 써야 한다.

MySQL의 옵션 값이 확인하였다면 방금전에 만든 쉘스크립트에 실행권한을 주고(chmod 755) 아래와 같이 실행한다.

./쉘스크립트명 mysql옵션 | sqlite3 sqlite파일명
ex) ./mysql2sqlite.sh -u userid -ppassword database | sqlite3 newDB.sqlite

출처 : https://gist.github.com/943776
참고 : http://www.sqlite.org/cvstrac/wiki?p=ConverterTools