How to pull mysql database from heroku to local machine

Question

Hi I have a ruby on rails app hosted on heroku and it is using mysql as database.

Now I have to take backup of the database to my local machine. But I am getting issues while taking backup.

For this I installed taps gem and I am using following commands for it

heroku pg:pull mysql2://[email protected]/heroku_database  local_database --app my_app

but it is giving error as !Your app has no databases.

Can any one guide me how to pull mysql database from heroku to local machine.

EDIT

I have used following syntax for the command

heroku pg:pull <REMOTE_SOURCE_DATABASE> <LOCAL_TARGET_DATABASE>

and for getting REMOTE_SOURCE_DATABASE I have used following command

 heroku config:get DATABASE_URL --app my_app

I refer this link1 and link2 for more detailed heroku documentation.

Answer 1

The pg:pull command only works with Postgres databases in your Heroku app. But, you are using a third-party MySQL provider. Your database is hosted on the ClearDB servers and it's available to anyone with the right credentials, including both your app server on Heroku and your dev machine.

Even though there aren't special commands to pull the database, you don't need any - plain mysqldump should do.

mysqldump -h hostname.cleardb.com -u username heroku_database | mysql local_database

Answer 2

Running $heroku config | grep ^DATABASE will give you something like this:

DATABASE_URL: mysql2://username:password@host/dbname?reconnect=true`

From there you can construct your db dump command:

mysqldump -h host -p -u username dbname | mysql local_database

This will prompt you for the password which you received from the previous command. If you wanted to create a script that would automatically include the password from the heroku command you could do something like this:

mysqldump -u username --password=`heroku config | grep ^DATABASE | sed 's/.*[a-z0-9][a-z0-9]*:\([a-z][a-z0-9]*\).*/\1/'` -h host dbname | mysql cedric

In this way you can have a script that will import the database without requiring any user input but also does not expose the password to your database.