Restore a single table from a large MySQL backup

jnewland (Jesse Newland) — Wed, 28 Nov 2007 14:10:03 GMT

Say, for some reason, you need to restore the entire contents of a single table from a HUGE mysqldump generated backup containing several tables. For example:



create table `baz`;



GIGS OF SQL YOU DON'T WANT;



create table `foo`;



A COUPLE THOUSAND LINES YOU DO WANT;



create table `bar`;



MORE SQL YOU DON'T WANT;

With a little dash 'o ruby, you can extract just the part you want:



$ ruby -ne '@found=true if $_ =~ /^CREATE TABLE `foo`/i; next unless @found; exit if $_ =~ /^CREATE TABLE (?!`foo`)/i; puts $_;' giant_sql_dump.sql > foo.sql

$ cat foo.sql

create table `foo`;



A COUPLE THOUSAND LINES YOU DO WANT;

You can then easily restore that entire table:



$ mysql mydatabase -e 'drop table foo'

$ mysql mydatabase < foo.sql

Synchronizing Rails DB Contents via Fixtures

jnewland (Jesse Newland) — Wed, 31 Jan 2007 01:04:37 GMT

The following rake task will dump the contents of the current environment's database to YAML fixtures. Stick the following in lib/tasks/fixtures.rake:



namespace :db do

  namespace :fixtures do

    

    desc 'Create YAML test fixtures from data in an existing database.  

    Defaults to development database.  Set RAILS_ENV to override.'

    task :dump => :environment do

      sql  = "SELECT * FROM %s"

      skip_tables = ["schema_info"]

      ActiveRecord::Base.establish_connection(:development)

      (ActiveRecord::Base.connection.tables - skip_tables).each do |table_name|

        i = "000"

        File.open("#{RAILS_ROOT}/test/fixtures/#{table_name}.yml", 'w') do |file|

          data = ActiveRecord::Base.connection.select_all(sql % table_name)

          file.write data.inject({}) { |hash, record|

            hash["#{table_name}_#{i.succ!}"] = record

            hash

          }.to_yaml

        end

      end

    end

  end

end

After making changes to the database that you'd like to dump to fixtures:



rake db:fixtures:dump

After checking out updated fixtures from SVN:



rake db:migrate

rake db:fixtures:load

DZone Snippets: mysql code

Restore a single table from a large MySQL backup

Synchronizing Rails DB Contents via Fixtures