Remote mysql database on Heroku app

Question

Can I use mysql database from my personal web server instead of heroku's database?

I configured my production database like this:

production:
    adapter: mysql2
    database: somedatabase
    username: someusername
    password: somepassword
    host: 1.1.1.1:1234

But, this doesn't work, my app still uses heroku's shared database.

Answer 1

Yeah this is very straight forward and simple:

1 - create mysql db 2 - create mysql db user (set defaults) 3.1 - Go to your Heroku panel/Config Vars

3.2 - Click on "Reveal Vars" and edit (clicking on pencil icon) on the one you want to change in this case DATABASE_URL (if not present just a new one with DATABASE_URL as the name)

3 (#2) - Using command line

heroku config:add DATABASE_URL=mysql://dbusername:dbpassword@databasehostIP:databaseserverport/databasename

then just

heroku restart

And remember the syntax:

DATABASE_URL

mysql://user:password@hostnameOrIPAddress:PortNumber/databasename

MySQL DBMS's default port number is : 3306

That's why you see examples mentioned previously using DATABASE_URL=mysql://dbusername:dbpassword@databasehostIP:3306/databasename

Hope this helps!!!

Answer 2

heroku config:add DATABASE_URL=mysql://dbusername:dbpassword@databasehostIP:3306/databasename
heroku config:add SHARED_DATABASE_URL=mysql://dbusername:dbpassword@databasehostIP:3306/databasename

Then, do a

Heroku restart 

that should do.

Important Note: I suggest you to use database host IP address than using giving the hostname directly, coz, with some shared hosting services like godaddy, the db hostname looks like user.345432.abcd.godaddy.com and it seems like heroku is unable to resolve it properly (personal experience), I resolved the hostname to IP address and using the IP directly worked like a charm ! Also, If your database password has special characters, make sure you escape them correctly (like '\!' for '!' and so on..)

Answer 3

I've written a gem that may help with this. You can find it at:

http://github.com/nbudin/heroku_external_db

Answer 4

This is old but in case anyone drops around looking for an answer, it's much easier than using the gem. Just provide a DATABASE_URL and SHARED_DATABASE_URL (not sure if the second is needed). The database url format is adapter://username:password@hostname:port/database, so for example, you would do:

heroku config:add DATABASE_URL=mysql://etok:somepassword@<your-server>:3306/etok
heroku config:add SHARED_DATABASE_URL=mysql://etok:somepassword@79.101.41.213:3306/etok

Then re-deploy your app. It will read your DATABASE_URL and generate the database.yml from that. The default port is already 3306 so it's not needed in the url in your case. When you deploy, you may notice that it generates your database.yml:

-----> Writing config/database.yml to read from DATABASE_URL

Then you're set (as long as your server accepts connections from your heroku host.

Answer 5

have a look at Heroku Amazon RDS addon. I'm not saying use it, but it gives you an insight into what you need to do and how Heroku manages dataabases - basically you need to set a config variable to your mysql instance.

Answer 6

Heroku ignores your database.yml. You will need to explore the Amazon RDS solution John Beynon suggested or some other similar addon (if there is one). IMO, you will either have to re-evaluate your need to use your MySQL db or find some other hosting. Just in case you didn't already know it, the command:

heroku db:push

will duplicate both the schema AND data of your MySQL development database in heroku's Postgres database. So sticking with MySQL for dev is no problem.

I hope that helps.