Convert Postgres to MySQL

steps.md

Software:

• https://www.mycli.net/

Steps

• dump and gzip postgres (source)

pg_dump -Ox -T sessions -U mos mos_staging > ~/mos_staging.sql
gzip mos_staging.sql

• scp down and decompress to dev machine

scp USER@DOMAIN:mos_staging.sql.gz .

• migrate MySQL database (target) [rails only]

rake db:drop db:create db:migrate

Ensure to add a new migration first which changes the encoding, such as:

# db/migrate/20000000000000_change_encoding.rb

class ChangeEncoding < ActiveRecord::Migration
  def change
    execute("ALTER DATABASE mos_staging CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")
  end
end

Truncate the schema_migrations table.

TRUNCATE `mos_staging`.`schema_migrations`;

Now we have the source data dump and the target schema.

• Create source database and import dump

createdb -U postgres mos_staging
psql -U postgres mos_staging < mos_staging.sql

• Create target database (MySQL)

create DATABASE mos_staging

I recommend [mycli] as a client.

• Migrate the data from source (postgres) to target (mysql)

  • Open the "MySQL Workbench" application
  • Database > Migration Wizard > Start Migration
  • Enter source details and remove Driver entry, leaving it blank (important!)
  • Fill in the default databases for both source and target
  • Press next a few times
  • Tick "Create schema in target RDBMS"
  • Tick "Keep schemas if they already exist"
  • Tick "Truncate target tables before copying"

• Wait.

• dump and gzip MySQL database

• scp up and decompress dump

• Import dump into MySQL (on server)