>Though your solution is workable, it can violate RI when transactions (INSERT UPDATE and DELETEs) are done during the update. This might be an academical question in your case but could be very dangerous in others.
Exactly what I was saying... is the hole wider now that two of us have put our fingers into it?
> It might be good an enhancement to FLOCK() all tables at forehand before you append them to the empty database.
>If you look at Message #
446049, this code will do exacly that, (and I don't agree this is THAT complicated). It does not require users to leave the system, but it locks all tables before copying, to overcome the same limitation your method has. It does its job quick and clean (Maybe someone else can shoot holes in my method ;).
This is bulletproof (unless there's a bug in flock() itself, or the OS has second thougts - none that I know of so far). The point was, if I remember correctly, that "nobody has to get out", which is nearly impossible. The Flock() is "nobody has to get out but you'll have to freeze if I'm going to do backup", which is probably the best halfway solution we could hope for.
So I see no holes in this - except that you may be unable to do backup until they all freeze for a while, which may be completely or nearly impossible in some situations - website, big office on several floors, low discipline... or even if you manage to get them all out-or-frozen, someone new comes in.