Replying to my own message:
- Original Message -
> We are shortly to go live with a new set of databases (InnoDB).
>
> The data in each database is identical in fields and types, the only
> difference being in the relevance of the data - they are soccer
> leagues, and each database represents information on the leagues/teams
> for a single season. There is a separate controlling database which
> directs web requests to the right year.
>
> Each league is identified within every table by a 3-4 character code.
> This all works pretty well, and was deemed a better scenario than
> having a database for each league, with a numeric field to identify the
> year. There are something like 160 leagues involved, but only 6 years
> (and rising). So we have 6 databases to take care of, not 160.
>
> Having explained the basic setup, we come to a problem: what if one of
> the administrators accidentally deletes a set of league information
> (maybe fixture information, for instance) six hours after the last
> backup?
>
> We could, of course, restore from the backup, but that would compromise
> every other league administrator's efforts since backup. Ideally, we
> would restore only the data relevant to that particular league (with
> the proper 3-4 letter code).
>
> Apart from opening up the latest mysqldump file and extracting the data
> from there (assuming the dork who deleted it knows more or less what
> has gone), and re-inserting table by table, having removed the data
> from other leagues, I can't think of another way to do it.
>
> We're currently using 4.1.3/Apache/WinNT, with hopes of upgrading to
> 4.1.7 before going live, though I doubt if the MySQL sub-version is
> relevant.
>
> I'd appreciate any help or guidance or advice on suitable tools.
>
It would seem that one way to get what I need (and I have tried this) is
to restore a 'usable' backup into a dummy database, and run through the
tables of the one which needs the partial restore (after backing it up
first, naturally) deleting records for that league (using the 3-4 letter
code), then insert ... select from the dummy. There are fifteen tables
involved, and in the trial I carried out using this method, the actual
delete/insert...select sequence took about 20 seconds on my beat-up,
memory-starved machine. Took me longer doing the backup/transfer to dummy.
So I have found *a* method for doing what I need, while quite a few of you
have been away enjoying (hopefully) your Thanksgiving holiday. It would be
useful if anyone could point me at a simpler alternative, though.
Cheers
Terry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
