wiki:Selective

Selective Restore to a Database

This is a guide to restoring selective records from a single table to a database.

The specific example given here derives from a case where "noise" table records from a database (uslims3_CCH) were accidentally deleted. Only noise records where the description matched a certain pattern (e.g., began with "Jay") were deleted. This left some models unusable and some types of analysis impossible, since linked noise records are essential. Simply restoring the database from the previous night's dump was not feasible, since this would cause any records added since the dump to be lost. The challenge, then, was to restore the lost noise records without corrupting existing noise records or any other tables from the database.

Steps explained and sample commands given are specific to the case above. The principles, however, apply to the general case of restoring specific records (identified in various ways) from any table to any standard LIMS database. Comments on generalizing are given later in this guide.

Selective Restore Steps

Here are the steps to a selective restore in the sample case:

  • Create a temporary database (e.g., "restore").
  • Restore the last full dump with the missing records to the temporary database.
  • Delete all temporary database records from the table of interest except the ones you wish to restore.
  • Create a dump of the edited table.
  • Edit the dump text so as not to recreate the table and only insert new records.
  • Restore the edited records to the permanently used database.

Sample Commands

The commands to accomplish these tasks are as follows:

mysqladmin -u root -p create restore
zcat uslims3_CCH-dump-190410.sql.gz | mysql -u root -p restore
mysql -u root -p restore
	select count(*) from noise;						[ output: "7868" ]
	select count(*) from noise where description like "Jay%";		[ output: "267" ]
	select count(*) from noise where description not like "Jay%"; 		[ output: "7601" ]
	delete from noise where description not like "Jay%";
	select count(*) from noise:						[ output: "267" ]
	quit;
mysqldump -u root -p restore noise  >cch-noise-select.sql
vi cch-noise-select.sql
	[ remove from "DROP TABLE..." line
		thru ") ENGINE=..." line ]
mysql -u root -p uslims3_CCH  <cch-noise-select.sql

Notes on Generalizing

Some notes on generalizing this procedure:

  • Any database (other than "uslims3_CCH") can, of course, be used; and, thus, the appropriate dump file identified.
  • Any table (other than "noise") could be referenced.
  • Any identifying criteria for selecting records (other than 'where description like "Jay%") could be used.
  • If all records from a table are to be restored, no identifying criterion need be given; and step (3) of deleting non-matching records can be skipped.
Last modified 5 months ago Last modified on Apr 11, 2019 1:10:23 PM