Dump sql file to ClearDB in Heroku

Question

I have a sql file that I want to be dumped into a MySQL database that I have in Heroku using the ClearDB addon. When dumping in local I do the following:

mysql -u my_user -p mydatabasename < my_dump_file.sql

However, I don't have any clue on how to dump it to the Heroku MySQL database. All I know is this address:

mysql://b5xxxxx7:[email protected]/heroku_xxxxxx?reconnect=true

But if I try to do:

mysql://b5xxxxx7:[email protected]/heroku_xxxxxx?reconnect=true < my_dump_file.sql

I get No such file or directory.

How am I supposed to do it?

Solution 1

You might be able to do something like this

mysql --host=us-cdbr-east.cleardb.com --user=b5xxxxx7 --password=37d8faad --reconnect heroku_xxxxxx < my_dump_file.sql

Solution 2

It doesn't work for me on the new version of mysql. So I tried following code:

mysql -h us-cdbr-east.cleardb.com -u b5xxxxx7 -p heroku_xxxxxx < my_dump_file.sql 

and then it asks your password.

Solution 3

I was getting errors when I typed it all at once so I broke it down into small steps first I entered my MySQL details like so:

mysql --host=us-cdbr-iron-east-02.cleardb.net --user=xxxxxxxxxxxxxx --password=xxxxxxxx --reconnect 

then once I got in I changed to the database like so:

mysql> use heroku_xxxxxxxxxx;

then I now imported the SQL file like so:

mysql> source path/to/file.sql;

Comments

[Magne]

When dumping in local you probably do > and not <, as the latter will actually restore and overwrite mydatabasename.

[Kyle Carlson]

Thanks for that! I didn't use the command line, but it worked perfectly when I connected remotely with Sequel Pro. You're a lifesaver.

[Magne]

use heroku config to get the CLEARDB_DATABASE_URL which contains the info you need for this command, in the format: mysql://user:password@host/heroku_db?reconnect=true. In this case: mysql://b5xxxxx7:[email protected]/heroku_xx‌​xxxx?reconnect=true

[thegreathypocrite]

I tried following your comment but it gave me this error: REFERENCES command denied to user 'baexxxxxxxxxxx'@'ec2-54-165-50-141.compute-1.amazonaws.com' for table 'ycdb.recipe' Any ideas on how to solve this?

[Yunnosch]

Is this supposed to answer the question at the top of the page? If so please rephrase to make the answering part more obvious.

[mLstudent33]

I got: ERROR 1044 (42000) at line 4: Access denied for user 'bexxxxxxxxxxxxxx@'%' to database 'xxxxxxxx'

[mLstudent33]

I get access denied although I checked my password and made sure to enter it correctly.

[mLstudent33]

ERROR 1045 (28000): Access denied for user 'bXXXXXXXXX'@'ip-10-0-6-79.ec2.internal' (using password: YES)