convert a mysql database to sqlite3

convert_mysql_to_sqlite3.sh

#!/bin/bash
#
# convert a mysql database to sqlite3
#
#see https://stackoverflow.com/questions/5164033/
#    export-a-mysql-database-to-sqlite-database

mysql_host=localhost
mysql_user=george
#mysql_passwd=****************
mysql_dbname=lfsc_questions

sqlite3_dbname=lfsc_questions.sqlite3

# dump the mysql database to a txt file
#
# NOTE: including the database password inline will generate a warning
#       simply say -p to be prompted

#mysqldump --skip-create-options --compatible=ansi --skip-extended-insert --compact --single-transaction -h$mysql_host -u$mysql_user -p$mysql_passwd $mysql_dbname  > /tmp/localdb.txt
mysqldump --skip-create-options --compatible=ansi --skip-extended-insert --compact --single-transaction -h$mysql_host -u$mysql_user -p $mysql_dbname  > /tmp/localdb.txt

# remove lines mentioning "PRIMARY KEY" or "KEY"

cat /tmp/localdb.txt | grep -v "PRIMARY KEY" | grep -v KEY > /tmp/localdb.txt.1

# mysqldump leaves trailing commas before closing parentheses
#
#    CREATE ...
#      ...,
#    );
#
# my thanks to
#   https://unix.stackexchange.com/questions/1233/
#   replacing-multiple-lines-in-sed-or-awk
# for this solution
# -0 sets the record separator to null, so the whole file will be read at once instead of line-by-line
# -p makes it print the result after the substitution
# -e has it take the next argument as the expression to run
# 's/,\n\)/\)/g'
#     s(ubstitute)
#       a ',' followed by a newline '\n' followed by a close paren (escaped) '\)'
#       by simply a close paren (escaped) '\)'
#     g(lobally)

perl -0pe 's/,\n\)/\)/g' /tmp/localdb.txt.1 > /tmp/localdb.txt.2

#    Then you need to change all \' to ''
#    where \ is escaped \\
#    and ' is escaped '\''
#
# my thanks to
#   https://stackoverflow.com/questions/17357952/
#   how-do-i-replace-single-quotes-with-space-in-sed
# for the otherwise-unguessable escaping of single quotes in sed

sed -e 's/\\'\''/'\'''\''/g' /tmp/localdb.txt.2 > /tmp/localdb.txt.3

# Then save the old sqlite3 db and do the import:

if [ -e $sqlite3_dbname ]; then
    mv $sqlite3_dbname $sqlite3_dbname.bak
fi

sqlite3 $sqlite3_dbname < /tmp/localdb.txt.3

rm /tmp/localdb.txt*