mysql-to-postgres-converter.sh

#!/usr/bin/env bash
set -e

# You'll need to set these variables here or with an environment variable
# mysqlPassword
# mysqlHostname
# mysqlUsername
# mysqlDatabase

# List out the tables you want to copy down,
# you can probably use MySQL's `show tables` to help with this
tableList=(
plantae
fungi
animalia
)

mysql -p sql/

for table in ${tableList[@]}; do
    echo " > exporting table $table"

    echo "DROP TABLE IF EXISTS $table;" > sql/$table.sql
    MYSQL_PWD=$mysqlPassword mysqldump \
        --column-statistics=0 \
        --host=$mysqlHostname \
        --port=3307 \
        --skip-add-drop-table \
        --skip-add-locks \
        --skip-disable-keys \
        --skip-comments \
        --skip-set-charset \
        --user=$mysqlUsername \
        $mysqlDatabase $table >> sql/$table.sql

    # Removes any trailing slashes, because that throws off a later replacement
    # this could be considered this data corruption or data hygiene, you choose to comment it out or not
    convertedSQL=`cat sql/$table.sql | perl -pe "s/\\\\\\\\\\\\\',/',/g"`
    echo "$convertedSQL" > sql/$table.sql
    
    # Converts MySQL syntax to Postgres
    #                                | Drops Engine/Charset settings                      | datetime => timestamp                 | tinyint => smallint                      | int() => int (no params)          | Removes backticks    | Removes KEY statements   | Removes trailing comma from Primary Key line          | Removes trailing comma from Primary Key line (id_c, not a dupe) | Replaces escape char \' for single quotes ''  | Fixes empty timedate values                 | Fixes empty date values            | Escapes \" to just "
    convertedSQL=`cat sql/$table.sql | perl -pe "s/ENGINE=InnoDB DEFAULT CHARSET=utf8//g" | perl -pe "s/ datetime / timestamp /g" | perl -pe "s/ tinyint\(1\) / smallint /g" | perl -pe "s/ int\(\d+\) / int /g" | perl -pe "s/\\\`//g" | perl -pe "s/  KEY .+//g" | perl -pe "s/PRIMARY KEY \(id\),/PRIMARY KEY \(id\)/g" | perl -pe "s/PRIMARY KEY \(id_c\),/PRIMARY KEY \(id_c\)/g"       | perl -pe "s/\\\\\'/''/g"                      | perl -pe "s/\'0000-00-00 00:00:00\'/NULL/g" | perl -pe "s/\'0000-00-00\'/NULL/g" | perl -pe "s/\\\\\\\\\"/\"/g"`
    echo "$convertedSQL" > sql/$table.sql

done