78

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.

Ken
  • 1,498
  • 2
  • 12
  • 19

3 Answers3

83

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.

1) 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

2) 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!

DanSingerman
  • 36,066
  • 13
  • 81
  • 92
  • Hi Dan, yes I was looking into using the cleardb addon. If I use postgres, will import/export just write over my database? I'm also guessing there is some postgres ruby gem that will allow me to connect directly to the DB and write to it. – Ken Mar 03 '13 at 22:10
  • Import / export will overwrite. To be honest I am not sure what you mean by " 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". I took that to mean you create data locally, then move it to Heroku, but perhaps you mean something different. – DanSingerman Mar 03 '13 at 22:12
  • Okay that's fine. If I want to read/write to the DB instead of using import/export, I can just use the 'pg' gem and create a connection, correct? I can't find any good examples of using the 'pg' gem to read/write. Any suggestions would be greatly appreciated! – Ken Mar 03 '13 at 22:19
  • Yes, you should be able to read/write from anywhere with the right config for your production database in your database.yml. The heroku databases page https://postgres.heroku.com/databases/YOUR_DB_NAME tells you the exact settings you need. Then you can just treat it like another ActiveRecord (or ORM of your choice) connection. – DanSingerman Mar 03 '13 at 22:25
  • roytuts.com/how-to-deploy-python-flask-mysql-based-application-in-heroku-cloud/ – user3470953 Dec 11 '19 at 01:49
4

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).

Billeh
  • 1,257
  • 7
  • 6
  • thank you for your suggestion. I have always used ClearDB on Heroku due to lack of other plugin options, but their service is not good. I'll be giving a try with jaws – Rafael Santos May 18 '22 at 09:55
0

I wrote a short article

https://medium.com/@michaeltendossemwanga/import-mysql-database-to-heroku-with-one-command-import-db-sql-a932d720c82b

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

Command line output

Michael Tendo Ssemwanga
  • 1
  • 1
  • 1
  • When I run 'import' it says command not found. The db.sql file is my mysql file right? The one I exported from phpmyadmin. – Hello World Aug 14 '20 at 20:37