Converts MySQL dump to SQLite3 compatible dump
Converts MySQL dump to SQLite3 compatible dump (including MySQL KEY xxxxx
statements from the CREATE
block).
Dump MySQL DB
mysqldump --skip-extended-insert --compact [options]... DB_name > dump_mysql.sql
# or
#mysqldump --no-data -u root -pmyPassword [options]... DB_name > dump_mysql.sql
Convert the dump to SQLite3 DB
./mysql2sqlite dump_mysql.sql | sqlite3 mysqlite3.db
(both mysql2sqlite
and sqlite3
might write something to stdout and stderr - e.g. memory
coming from PRAGMA journal_mode = MEMORY;
is not harmful)
The script is written in awk (tested with gawk, but should work with original awk, and the lightning fast mawk) and shall be fully POSIX compliant.
It's originally based on the newest fork (https://gist.github.com/bign8/9055981/05e65fd90c469c5eaa730823910c0c5f9de40ab4) of the original mysql2sqlite.sh
(https://gist.github.com/esperlu/943776/be469f0a0ab8962350f3c5ebe8459218b915f817) with the following patches:
COMMENT
field statement removalCREATE DATABASE
statements (USE
statements were already ignored)INSERT INTO VALUES
AUTO_INCREMENT
handlingIF NOT EXISTS
or TEMPORARY
has been detected (on unix sqlite3 treats temporary table name FILES
the same as files
; in other words, sqlite3 doesn't issue any warning about cross-collisions between TABLE
and TEMPORARY TABLE
identifiers)COLLATE xxx_xxxx_xx
statements with COLLATE BINARY
(https://gist.github.com/4levels/0d5da65bf9d70479fbe3/d0ac3d295dc5e2f72411ad06c07a22931368a1b7)CONSTRAINT FOREIGN KEY
(https://gist.github.com/BastienDurel/7f413d13d7b858aef31c/922be110d011b9da340ae545372214b597ad7b84)Feel free to contribute (preferably by issuing a pull request)!
MIT