How to deploy local MySQL database to Heroku
MysqlHerokuMysql Problem Overview
I'm new to using Heroku and I have a Ruby/Sinatra app in which I plan on using a MySQL database as the main data store.
However, I do not want to write results directly to the database on Heroku. Instead, I want to write the results to a local database, and then be able to easily deploy/update/copy my local DB to the "production" database on Heroku.
How do I do this?
Thanks.
Mysql Solutions
Solution 1 - Mysql
Firstly Heroku natively uses postgres. Life will be easier for you if you use that locally.
You can import / export postgres dump files from heroku as described here: https://devcenter.heroku.com/articles/heroku-postgres-import-export
If you really want to use mysql, you have two paths to follow.
-
Run mysql locally, but convert to postgres when migrating to Heroku using the mysql2psql gem, as described here: https://devcenter.heroku.com/articles/heroku-mysql
-
Use a mysql addon like https://addons.heroku.com/cleardb
However my recommendation would be to use postgres end to end, as it is baked into Heroku, and you'll be working with the default ways of using Heroku, not against them.
Postgres is very good too!
Solution 2 - Mysql
I know nothing about Ruby & Sinatra, so feel free to comment and let me know how wrong I am if that's the case. However, I thought it might be pertinent to mention the JawsDB Plugin on Heroku, as the top answer here is from 2013 and may be a bit outdated.
Here is a link with info on the JawsDB plugin: https://devcenter.heroku.com/articles/jawsdb
Provisioning the plugin is as simple as running the following command in the CLI:
heroku addons:create jawsdb
Then configure the host, username, password, & database with parameters from MySQL Workbench (or whatever GUI you use).
Solution 3 - Mysql
I wrote a short article
Here is a batch script to fasten the process https://gist.github.com/MichaelTendoSsemwanga/f013963092e3abcce801834871d14b03
Save this batch script as import.bat
@echo off
heroku config | findstr CLEARDB > config.txt
set /p url=<config.txt
set "string=%url:?=" & set "x=%"
set "x=%string:/=" & set "dbname=%"
echo DB name: %dbname%
echo DB name: %dbname% >> config.txt
set "x=%string:@=" & set "substring=%"
set "host=%substring:/=" & set "x=%"
echo Host: %host%
echo Host: %host% >> config.txt
set "x=%string::=" & set "substring=%"
set "password=%substring:@=" & set "x=%"
echo Password: %password%
echo Password: %password% >> config.txt
set "x=%string:://=" & set "substring=%"
set "user=%substring::=" & set "x=%"
echo User: %user%
echo User: %user% >> config.txt
mysql -u %user% -p%password% -h %host% -D %dbname% < %1
In the heroku app directory, run
import db.sql