Last active
February 17, 2021 11:32
-
-
Save Instagit/bcbd212483404e8e2430 to your computer and use it in GitHub Desktop.
MySQL to SQLite DB converter
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| # | |
| # convert a database from MySQL to SQLite | |
| echo -ne "Database: " | |
| read db | |
| echo -ne "User: " | |
| read user | |
| echo -ne "Password: " | |
| read -s pw | |
| echo "" | |
| # remove files from previous runs | |
| rm -f $db.sql $db.sqlite $db.db $db.err | |
| echo "Dumping MySQL DB \"$db\" to file $db.sql..." | |
| mysqldump -u $user -p$pw --compatible=ansi --skip-opt --default-character-set=utf8 $db > $db.sql || exit 10 | |
| echo "Converting SQL dump ($db.sql) to SQLite dump ($db.sqlite)..." | |
| cat $db.sql | | |
| grep -v ' KEY "' | | |
| grep -v ' UNIQUE KEY "' | | |
| grep -v ' PRIMARY KEY ' | | |
| sed '/^SET/d' | | |
| sed 's/ unsigned / /gI' | | |
| sed 's/ auto_increment/ primary key autoincrement/gI' | | |
| sed 's/ smallint([0-9]*) / integer /gI' | | |
| sed 's/ tinyint([0-9]*) / integer /gI' | | |
| sed 's/ int([0-9]*) / integer /gI' | | |
| sed 's/ enum([^)]*) / varchar(255) /gI' | | |
| sed 's/ on update [^,]*//gI' | | |
| sed 's/\\r\\n/\\n/g' | | |
| sed 's/\\"/"/g' | | |
| sed -r 's/ character set [^, ]* ?/ /gI' | | |
| sed -r 's/ collate [^, ]* ?/ /gI' | | |
| perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' | | |
| perl -pe ' | |
| if (/^(INSERT.+?)\(/) { | |
| $a=$db; | |
| s/\\'\''/'\'\''/g; | |
| s/\\'\'\','/'\','/g; | |
| s/\\n/\n/g; | |
| s/\),\(/\);\n$a\(/g; | |
| } | |
| ' > $db.sqlite | |
| echo "Importing SQLite dump to $db.db..." | |
| cat $db.sqlite | sqlite3 $db.db 2> $db.err | |
| ERRORS=`cat $db.err | wc -l` | |
| if [ $ERRORS == 0 ]; then | |
| echo "Conversion completed without errors. Output file: $db.db" | |
| rm -f $db.sql $db.sqlite $db.err | |
| else | |
| echo "There were errors during conversion. Please review $db.err and $db.sqlite for details." | |
| fi |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment