Remote connect to clearDB heroku database

https://stackoverflow.com/questions/9822313/remote-connect-to-cleardb-heroku-database/18413171

Ask QuestionAsked 7 years, 10 months agoActive 6 months agoViewed 65k times9432

How can i perform a remote connect to ClearDB MySQL database on heroku using for example MySQL Query Browser. Where to get url, port, login and password?mysql database heroku database-connectionshareimprove this questionasked Mar 22 '12 at 12:29roman4,2521212 gold badges3636 silver badges7373 bronze badgesadd a comment

12 Answers

activeoldestvotes171

In heroku website, go to My Apps and select the app on which you have installed ClearDB.

On the top corner click on Addons and then select ClearDB MySQL Database. Once there, click on your database and choose the 'Endpoint Information' tab. There you see your username/password. The URL to the database can be acquired by running heroku config --app <YOUR-APP-NAME> in the command line.

In my case, it was something like: mysql://user:[e-mail]/DATABASE?reconnect=true What you need is this part: us-cdbr-east.cleardb.comshareimprove this answeredited Oct 29 '16 at 11:15Community♦111 silver badgeanswered Apr 27 '12 at 20:59Abbas2,76811 gold badge1919 silver badges2323 bronze badges

• 11You can also see this directly on the Heroku dashboard/website, just go to your app, click "Settings" and "Reveal config vars". – Eirik H Aug 24 '14 at 21:06

• 1In my case at least, the us-cdbr-east.cleardb.com equivalent part was not the name of the database but rather the name of the host. The database name was however found on the dashboard for the ClearDB addon. – Roseaboveit Jan 27 '15 at 7:23

• 1you'll need to export your local database file to the database created by Heroku. Just follow this video and you'll be good to go youtube.com/watch?v=mBCH9OTVaGw&t=6s – Kartik Chauhan May 22 '17 at 19:17

add a comment80

You run heroku config to get the CLEARDB_DATABASE_URL and it should be something of this format:

CLEARDB_DATABASE_URL => mysql://[username]:[password]@[host]/[database name]?reconnect=true

So basically you just look at your own url and get all you want from there. That's how i set up mysql workbench.shareimprove this answeredited Jul 27 '12 at 7:11Druid6,04233 gold badges3030 silver badges5050 bronze badgesanswered Jul 26 '12 at 9:51Andrei86111 gold badge77 silver badges99 bronze badges

• 3Thank you @Andrei but what about the port for the database? – BKSpurgeon Nov 16 '16 at 6:43

• 2@BKSpurgeon. I got it to work with phpMyAdmin with the default port (See stackoverflow.com/a/22092539/4900327) – Abhishek Divekar Feb 16 '17 at 16:47

• This is much simpler :) – Aman Alam Aug 16 '17 at 6:40

• Thank you so much Mr. It helps me deploy my mysql file to heroku successfully – Travis Le Mar 17 '19 at 10:33

• @AbhishekDivekar your comment helped me a lot. I just changed the username and some stuff on a congig.inc.php file in my xamp and it worked on PHPMyAdmin. – lilhamad Dec 3 '19 at 16:15

add a comment14

Paste this command in terminal

  heroku config | grep CLEARDB_DATABASE_URL

After this you will get Database URL. e.g this is your cleardb database URL.

'mysql://b0600ea495asds:9cd2b111@us-cdbr-hirone-west-
 06.cleardb.net/heroku_4a1dc3673c4114d?reconnect=true'

Than this will be your database credentials. (Extracted from Above URL)

USER NAME = b0600ea495asds

PASSWORD = 9cd2b111

HOST = us-cdbr-hirone-west- 06.cleardb.net

DATABASE NAME = heroku_4a1dc3673c4114dshareimprove this answeranswered Oct 5 '17 at 9:59Developine3,32233 gold badges2121 silver badges3030 bronze badgesadd a comment10

I did a video explaining how to connect to MySql using NodeJS on a Heroku server, take a look:

http://www.youtube.com/watch?v=2OGHdii_42s

This is the code in case you want to see:

https://github.com/mescalito/MySql-NodeJS-Heroku

Here is part of the code:

var express = require("express");
var mysql      = require('mysql');
var app = express();
app.use(express.logger());

var connection = mysql.createConnection({
  host     : 'us-cdbr-east-04.cleardb.com',
  user     : 'b6d6c6e874',
  password : 'b3f7###',
  database : 'heroku_1daa39da0'
});

connection.connect();

app.get('/', function(request, response) {
  connection.query('SELECT * from t_users', function(err, rows, fields) {
      if (err) {
        console.log('error: ', err);
        throw err;
      }
      response.send(['Hello World!!!! HOLA MUNDO!!!!', rows]);
    });
});

var port = process.env.PORT || 5000;
app.listen(port, function() {
  console.log("Listening on " + port);
});

CHeers! MAGIC: http://makegif.com/g9yv.gifshareimprove this answeranswered Aug 23 '13 at 23:09lito2,44288 gold badges3737 silver badges6060 bronze badges

• 4Don't know why this is so heavily downvoted.. your createConnection cleared things up for me. Thanks :) – Nico Mar 30 '16 at 21:41

• 3Reason for down-votes also should be provided. This is the answer that helped me. Thanks, Bro! – Anoop.P.A Apr 10 '16 at 16:43

add a comment2

Paste this inside terminal:

heroku config | grep CLEARDB_DATABASE_URL

shareimprove this answeredited Sep 30 '16 at 14:35Paul Roub33.7k88 gold badges6464 silver badges7878 bronze badgesanswered Sep 30 '16 at 14:29user38054742111 bronze badgeadd a comment1

You can use this one-liner to connect to your MySQL database in your terminal.

$(ruby -e 'require "uri"; uri = URI.parse(ARGV[0]); puts "mysql -u#{uri.user} -p#{uri.password} -h#{uri.host} -D#{uri.path.gsub("/", "")}"' `heroku config:get CLEARDB_DATABASE_URL`)

shareimprove this answeranswered Feb 3 '17 at 12:12Sébastien Saunier1,4431414 silver badges2525 bronze badgesadd a comment1

If you are using mySQL workbench, follow this schema. Go to Heroku > Your Applications Settings > Config Vars, and show the long URL. That url includes your username, password, the URL of the database and the default schema. Paste all of the information as follows below, and you will be able to successfully connect to the database. There was no real explaination on how to connect to ClearDB using mySQL workbench on this thread, so hopefully this helps someone who was struggling.

shareimprove this answeranswered Apr 4 '19 at 16:46Jordan Schuetz77811 gold badge77 silver badges1818 bronze badges

• I tried this solution but this is not working. But this solution is working fine in Sequal Pro. – iDev750 Sep 4 '19 at 19:42

add a comment0

All the details will be in the database URL which can be found in heroku config. Assuming you can connect to ClearDB directly (I've never tried), these should be all you need...shareimprove this answeranswered Mar 22 '12 at 14:10Neil Middleton21.1k1717 gold badges7373 silver badges125125 bronze badgesadd a comment0

Go to your app on heroku and click to the 'settings' tab. Then click the button on the second option that says 'reveal config vars'.

You should find, listed under the CLEARDB_DATABASE_URL variable, something like this...

mysql://[username]:[password]@[host]/[database name]?reconnect=true

So the [host portion] is your host. The [database name] portion is your db name, of course.

You still need your username and password. Go back to the 'overview' tab in heroku. Go to the ClearDB add-on in your installed add-ons section. Click the database you want to access (probably only 1 option there). Click the 'system information' tab. You should see your username and password.

that should be all you need to access your database. I use sequel pro. I just plugged that info (name, host, into the 'standard' tab and I was good to go.shareimprove this answeranswered May 24 '18 at 14:22user2364424111 bronze badgeadd a comment0

All of this worked perfectly for me. Using heroku config | grep, as described above and then simply adding another entry into my config.inc.php for use by phpMyAdmin and I can access my cleardb database remotely. It saves me having to have SQL locally and using postgres with Heroku.shareimprove this answeranswered Sep 13 '18 at 18:18GiantCoder1add a comment0

should consider getting the credentials of vars in heroku configurations (Config Vars):

CLEARDB_DATABASE_URLshareimprove this answeranswered Jul 15 '19 at 19:39Diego Santa Cruz Mendezú1,7001515 silver badges1515 bronze badgesadd a comment-1

Yes, you can connect to ClearDB directly, actually I use Workbench to connect. Then you can use the same DB for your localhost and for heroku.shareimprove this answeranswered Feb 19 '13 at 1:45Alisson Reinaldo Silva3911 bronze badge

• 4That's good to know, but how does that help the OP find their connection information? – Brad Koch Mar 29 '13 at 13:46

• 1You are right, but I just added an info, because Neil Middleton said "Assuming you can connect to ClearDB directly", then I just said Yes, he can. I apologize for answering something that haven't helped the main question. – Alisson Reinaldo Silva May 15 '13 at 2:58

• No worries. Things like this are really good comment material, but it looks like you need a few more rep still for that. – Brad Koch May 15 '13 at 3:02

• i have 0 problems gettig to the heroku service. but from the service to db is where the issue lies. boooo. gettig "dial tcp 127.0.0.1:3306: getsockopt: connection refused" – filthy_wizard Sep 17 '16 at 14:34