How to pull mysql database from heroku to local machine

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

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.

(IMPORTANT DISCLAIMER: You MUST have your database.yml configured correctly in order for this to work. I am not responsible for any data you lose as a result of running the below script.)

For Ruby on Rails users ... you could consider writing a Rake task like these db:clone tasks below.

I find myself using this script constantly to clone down from production to development. It's way easier than remembering the mysqldump syntax, much less all of the usernames and passwords involved ...

To clone from production to development:

rake db:clone:production

To clone from staging to development:

rake db:clone:staging

To clone from production to staging:

rake db:clone:production_to_staging

And here's the code enjoy (and be careful in setting up your database.yml):

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  endend