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.

score 55 · Accepted Answer · answered Dec 13 '11 at 14:03

55

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.

answered Dec 13 '11 at 14:03

Vince

919
8
16

9

It seems `DATABASE_URL` is enough, it works without specifying `SHARED_DATABASE_URL`. Also, I'd like to notice that `mysql2` adapter is now more common than `mysql`. So it will be looks like `mysql2://...`. Of course you have to add `gem 'mysql2'` in your `Gemfile`. – Pavel Strakhov May 23 '12 at 21:59
Can X.509 be used this way? – Benjamin Oakes Apr 05 '13 at 12:12
2

"In case `anyone` drops around looking for an answer". You've just saved my day. Thank you! This is much simpler than I could have thought. – RileyE Jul 04 '13 at 16:35
2

"as long as your server accepts connections from your heroku host.", I'm deploying a PHP application and heroku's host is constantly changing. I keep getting `Host 'ec2-xxxxxxxxxxx.compute-1.amazonaws.com' is not allowed to connect to this MySQL server`, where xxxx is some arbitrary IP. I can't just allow one specific IP because it is changing on the fly. Do you have any advice for this? – Augie Gardner Jan 16 '14 at 22:25
1

@augie-gardner unfortunately not - i guess your best shot (or the narrowest filter) would be to add a wildcard account name in your mysql server to match all ec2-%.compute-1.amazonaws.com (and work up from there). Personally I'd just go for %.amazonaws.com and set a lengthy secure password. If you're attacked from aws servers, at least you know who to email. – Vince Feb 17 '14 at 12:49

score 8 · Answer 2 · answered Mar 30 '11 at 15:35

8

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

http://github.com/nbudin/heroku_external_db

answered Mar 30 '11 at 15:35

Nat Budin

291
1
7

Thanks Nat. I've been used using your gem for one month and it works just fine! – Dorian Aug 26 '11 at 09:19
1

We also have been using this for about a year. I added some documentation to that project so it's easier to get started. Here's a link to the writeup on my blog: http://www.benjaminoakes.com/2011/11/21/using-heroku-with-an-external-mysql-database/ – Benjamin Oakes Dec 10 '12 at 20:19

score 5 · Answer 3 · answered Jan 01 '13 at 10:52

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

score 1 · Answer 4 · answered Apr 08 '18 at 01:01

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

score 1 · Answer 5 · edited Jan 27 '12 at 15:14

1

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.

edited Jan 27 '12 at 15:14

Peter DeWeese

18,141
8
79
101

answered Mar 23 '11 at 12:34

John Beynon

37,398
8
88
97

score 1 · Answer 6 · answered Mar 23 '11 at 14:42

1

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.

answered Mar 23 '11 at 14:42

mikewilliamson

24,303
17
59
90

I figured out that ignore my database.yml and I tried to configure DATABASE_URL with this: config:add DATABASE_URL=mysql2://user:password@host/dbname but don't work, app is looking for db on Amazon server. This is from log: /app/.bundle/gems/ruby/1.8/gems/mysql2-0.2.6/lib/mysql2/client.rb:37:in `connect': Access denied for user 'etok'@'ec2-174-129-89-188.compute-1.amazonaws.com' (using password: YES) (Mysql2::Error) – dormitkon Mar 24 '11 at 20:00

Remote mysql database on Heroku app

6 Answers6

Linked