Table of Contents
MySQL is a popular relational database maintained by Oracle. Though Heroku Postgres is the recommended database on Heroku because of its tight-integration with the platform and excellent management services and tools, there are many options for applications currently running on MySQL.
Migrate to Heroku Postgres
Most applications that utilize an ORM library to access the database will find little trouble in switching to a PostgreSQL database. Follow the following steps to switch from MySQL to PostgreSQL.
Run PostgreSQL locally
Install mysql2psql import utility
The mysql2psql ruby gem is the most mature utility for converting databases from MySQL to Postgres. Although using this tool requires that you have RubyGems installed, it is entirely independent from your application language.
mysql2psql gem in your development environment and execute the
mysql2psql command to generate the configuration file template:
$ gem install mysql2psql Fetching: mysql2psql-0.1.0.gem (100%) Successfully installed mysql2psql-0.1.0 1 gem installed $ mysql2psql No configuration file found. A new file has been initialized at: /Users/you/dev/app/mysql2psql.yml Please review the configuration and retry.. ...
This will generate a
mysql2psql.yml file which you will need to edit to specify both your existing MySQL database and new PostgreSQL db.
Specify db locations
destination sections of the generated
mysql2psql.yml file to reference the appropriate local databases and resemble the following format:
mysql: hostname: localhost port: 3306 socket: /tmp/mysql.sock username: user password: password database: mysql-db destination: postgres: hostname: localhost port: 5432 username: you password: password database: pg-db
This configuration file uses the same format as ActiveRecord, so you can copy and paste the connection settings from your application’s
config/database.yml if you’re using Rails.
mysql2psql command again to initiate the data transfer from MySQL to PostgreSQL.
mysql2psql tool is capable of migrating approximately 100,000 rows per minute. Migration time will depend on the size and nature of your database.
Once you’ve migrated the data to PostgreSQL locally you can use PG Backups to import this data to your Heroku Postgres database.
Run your application against the new PostgreSQL database to identify and resolve any common errors.
Provision a MySQL Add-on
If your application is using MySQL-specific features, there are a number of MySQL providers in the Heroku Add-ons marketplace you can use instead of Heroku Postgres. Please see the add-on documentation for provisioning instructions as they vary by provider.