Let's say on a test database about a year's data got erased. I got the two id's for the data in one year at the earliest and the data in the other at the latest and therefore a range of what's missing here. My question; Is there any danger at all in using the following command from the full instance of the database, in order to get a working dump which can be used to repair the database with the missing chunk of information in it? The command:

mysqldump -t --insert-ignore --skip-opt --single-transaction --quick --where="id<156789339" -w"id>124054297" -u root -p database table > partial.sql

And this to import after gzipping/moving it:

zcat partial.sql.gz | mysql -u root -p database table

There may be one caveat worth mentioning - data is coming from mysql 5.5 (percona) while imported into a mysql 5.1 instance, though I think there's no compatibility issues I know of offhand which may arise from this.

I understand -t is to avoid creating CREATE TABLE statements (--no-create-info), --insert-ignore in case my range overlaps so it ignores if that id exists already, and --skip-opt for making sure it doesn't do a whole bunch of things that would mangle things upon importation (--add-drop-tab, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset according to the man page for mysqldump). Just want to know for sure that this is all I need on the exportation and if there's maybe anything I'm missing on the importation before any possible mistakes end up being made.

  • 1
    All seems ok, but to be sure (specially about mysql version), you can import data in a different table, then you can import again in ther right table.
    – lg.
    Jan 19 '17 at 8:05
  • 1
    It depends on the database design. If the data you are importing references other data in other tables then you might be messing with the referential integrity of the database. Other than that I would test it first in a non-productive environment. DO NOT do this directly on a productive server. If there are references between tables you will mess things up.
    – John K. N.
    Jan 19 '17 at 9:51

Probably that will be ok. There are some special cases when it might fail.

  • There are FOREIGN KEYs in your DB pointing to your table using ON DELETE CASCADE statement, which case you lost other data in consequence of the prior deletion and you have to find and copy that data as well. If your db is using MYISAM, you don't have foreign keys, so you are safe.

  • You are using special features not supported in the previous version eg. FULLTEXT indexes. Since you said it's a test db, I suppose the model is identical. In that case there should be no problem.

  • You are using different encoding/collation in the two DB and you have non ASCII (localised) text fields in the table. Again, if the model is the same, there should be no problem. (If your table has no explicit encoding definition and the default encoding in the mysql servers differs you might have problem, but it's not probable).

If you are using INNODB you might want to execute your entire dump in a TRANSACTION (between BEGIN; and COMMIT;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.