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.
-t is to avoid creating
CREATE TABLE statements (
--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.