MySQL, Nodejs, Heroku: Connect Remote Database to Heroku

In this tutorial, I will be teaching you how to successfully connect a remote database to heroku. This tutorial will assume you already have your heroku application deployed. I will try to keep this short, sweet and to the point. It isn’t super complicated — the process is very straight forward. If you follow along you should be able to connect your remote MySQL database to Heroku quickly.

1. Download ClearDB in Heroku
2. Download Sequel Pro
3. Go to your application in Heroku and click settings
4. Choose the option to reveal Config Vars
5. You should see a CLEARDB_DATABASE_URL variable — copy the information that starts with “mysql://”
6. Paste copied information into your server and comment it out

Now, this part is important to remember:

• The characters directly after the forward slash up until the colon is your username.
• The characters right after the colon up until the @ sign is your password.
• The characters after the @ sign up until the forward slash is your host name
• The characters after the third forward slash up until the question mark is your database name.

You’ll need this information to plug into Sequel Pro.

Next, you need to open Sequel Pro. With the information you just copied, plug in your host, username, password and database name along with the port number. MySQL default port number is 3306, so plug in that in the port section. Click test, and after it succeeds, click connect.

At this point, you should see, well, nothing. We haven’t imported our data yet. To import our data, we will need to have MySQL installed. Once MySQL is installed, you’ll have access to use keywords like ‘mysql’ in the terminal.

Open your terminal and navigate somewhere you’d like to save your dump. For me, I chose my desktop. Once you’re at the place you’d like to save, run the following command:

mysqldump -h host_name -u user_name -p database_name > file_name.sql

In the above command, you want to fill in host_name, user_name, database_name and file_name with their respective values. You’ll be prompted to enter your password — you should use the password for the database that corresponds to those values you replaced. After, If you look wherever you chose to save your file, you should see it saved.

Once you’ve successfully made a dump file, it’s time to run another command which will essentially “dump” your data into the ClearDB database — in other words, importing. The command is:

mysql -u user_name -h host_name -p database_name < file_name.sql

In the above command, you want to fill in the user_name, host_name and database_name with the address values you copied earlier. Please note that in this command, the operator is reversed. Once you’ve hit enter, it should prompt you for a password. You want to use the password in the address you copied earlier. Once you click enter, you’re pretty much done. If you navigate to your Sequel Pro and close and reopen it, you should see your data.

• If you run into the issue where you have a syntax error in your dump file, just use the flag -f in your import command. This flag ignores errors and continues to execute the file.

If you have any questions please feel free to leave a comment.