CODEWITHSUNDEEP
mysqldatabase-restoregunzip
Amit Kumar
Amit Kumar

Reputation: 53

Restore data from sql.gz file, but skip a single table

I took a backup of my database from engine yard, which is downloaded in sql.gz File. Since one of the tables of my database is too large, so I want to skip it while restoring it in my local system.

I use gunzip < file_name.sql.gz | mysql -u user_name -p password database name command to restore backup.

Upvotes: 0

Views: 3469

Answers (2)

tpol
tpol

Reputation: 301

You may have identified a solution for this by now but I figured I'd add some insight. The restore operation of MySQL does not currently offer an easy way to exclude a single table from the restore operation, but there are a few options you could consider:

  • If your local MySQL server offers the 'Blackhole' engine you could use gawk to alter the ENGINE definition for that table when it is created. This would be something like:

    gunzip < file_name.sql.gz | gawk -v RS='' '{print gensub(/(CREATE TABLE .[table_to_be_skipped].*) ENGINE=InnoDB/, "\\1 ENGINE=Blackhole", 1)}' | mysql -u user_name -p password database name.

    This instructs the database to just pass through the row inserts against this table during the reload. Once the load completes you could modify this back to the InnoDB engine with alter table [table_to_be_skipped] engine=innodb;. The drawback to this would be that you are still downloading and parsing through a larger backup.

  • Another option would be to use the Awk method described here to create two backup files, one representing the tables and data prior to the excluded table, and another representing everything that follows it. The tricky part here is that if you add tables on either side of the excluded table you would have to update this script. This also has the consequence of having to download and parse a larger backup file but tends to be a bit easier to remember from a syntax perspective.

  • By far the best option to addressing this would be to simply do a manual backup on your source database that ignores this table. Use a replica if possible, and use the --single-transaction option to mysqldump if you are using all InnoDB tables or consistency of non-InnoDB tables is of minimal importance to your local environment. The following should do the trick:

    mysqldump -u user_name -p --single-transaction --ignore-table=[table_to_be_skipped] database name | gzip > file_name.sql.gz

    This has the obvious benefit of not requiring any complex parsing or larger file downloads.

Upvotes: 1

P.J.
P.J.

Reputation: 1

Not sure if this will help, but we have documentation available for dealing with database backups - also, you may want to talk to someone from support through a ticket or in #engineyard on IRC freenode.

Upvotes: 0

Related Questions