How to pull mysql database from heroku to local machine

Question

Hi, I have a ruils ruils application hosted on heroku and it uses mysql database.

Now I need to backup the database on my local machine. But I get backup problems.

To do this, I installed taps gem and I use the following commands for it

 heroku pg:pull mysql2:// [e-mail] /heroku_database local_database --app my_app 

but it gives an error like !Your app has no databases.

Can anyone advise me to pull the mysql database from heroku to the local machine.

EDIT

I used the following syntax for the command

 heroku pg:pull <REMOTE_SOURCE_DATABASE> <LOCAL_TARGET_DATABASE> 

and to get REMOTE_SOURCE_DATABASE I used the following command

  heroku config:get DATABASE_URL --app my_app 

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

Answer 1

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

Although there are no special commands to load the database, you do not need anything - plain mysqldump .

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

Answer 2

( IMPORTANT DISCLAIMER . MUST configure database.yml correctly for this to work. I am not responsible for any data that you lost as a result of running the script below.)

For Ruby on Rails users, you can consider the Rake task like these db: clone tasks below.

I use this script constantly to clone from production to development. This is easier than remembering the mysqldump syntax, and even more so all the usernames and passwords ...

Clone from production to development:

rake db:clone:production

Clone from stage to development:

rake db:clone:staging

To clone from production to production:

rake db:clone:production_to_staging

And here you will like the code (and be careful when setting up your .yml database):

 namespace :db do namespace :clone do class << self %w(development test staging production).each do |env| define_method("#{env}_db") do Rails.configuration.database_configuration[env] end end end def clone_db(from_db, to_db) start_time = Time.now puts "Cloning Remote DB...." system("mysqldump -h#{from_db['host']} -u#{from_db['username']} -p#{from_db['password']} #{from_db['database']} | mysql #{to_db['database']} -u#{to_db['username']} -p#{to_db['password']}") puts "Import Successful" end_time = Time.now puts "====================" puts "Job Completed: #{end_time - start_time} Seconds" end task :staging => :environment do clone_db(staging_db, development_db) end task :production => :environment do clone_db(production_db, development_db) end task :production_to_staging => :environment do clone_db(production_db, staging_db) if Rails.env.staging? end end end 

Answer 3

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

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

From there, you can create the db dump command:

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

This will give you the password that you received from the previous command. If you want to create a script that automatically includes 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]*:\([az][a-z0-9]*\).*/\1/'` -h host dbname | mysql cedric 

Thus, you can have a script that will import the database without requiring user input, but also does not reveal the password in your database.