Repository files navigation

Converts MySQL dump to SQLite3 compatible dump (including MySQLKEY xxxxx statements from theCREATE block).

1. 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

2. Convert the dump to SQLite3 DB

   ./mysql2sqlite dump_mysql.sql | sqlite3 mysqlite3.db

(bothmysql2sqlite andsqlite3 might write something to stdout and stderr - e.g.memory coming fromPRAGMA journal_mode = MEMORY; is not harmful)

The script is written inawk (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 originalmysql2sqlite.sh (https://gist.github.com/esperlu/943776/be469f0a0ab8962350f3c5ebe8459218b915f817) with the following patches:

• fix the non-standardCOMMENT field statement removal
• ignoreCREATE DATABASE statements (USE statements were already ignored)
• add support for multiple-recordINSERT INTO VALUES
• revise support for lower-case SQL statements
• fixAUTO_INCREMENT handling
• trim hexadecimal values longer than 16 characters and issue a warning about it
• add identifier case sensitivity warning in caseIF NOT EXISTS orTEMPORARY has been detected (on unix sqlite3 treats temporary table nameFILES the same asfiles; in other words, sqlite3 doesn't issue any warning about cross-collisions betweenTABLE andTEMPORARY TABLE identifiers)
• replaceCOLLATE xxx_xxxx_xx statements withCOLLATE BINARY (https://gist.github.com/4levels/0d5da65bf9d70479fbe3/d0ac3d295dc5e2f72411ad06c07a22931368a1b7)
• handleCONSTRAINT FOREIGN KEY (https://gist.github.com/BastienDurel/7f413d13d7b858aef31c/922be110d011b9da340ae545372214b597ad7b84)

Feel free tocontribute (preferably by issuing a pull request)!

MIT

• @esperlu created initial version in 2011 as gist on GitHub
• many different contributors forked the gist and made wildly varying changes, because @esperlu stopped working on it and didn't respond
• @dumblob took over in Aug 2015 and applied the most important patches from all the forks as well as many his own patches tested on Drupal DB
• @dumblob added the MIT license under assumption, that the original gist was released into public domain, because despite significant changes, it wasn't clean room engineering.
• 2016-05-11 17:32 UTC+2@esperlu declared MIT as a fitting license (also retrospectively) and theoriginal gist as deprecated.