Re: What will happened if I haven't lock the table while restore and backup

[Beat Vontobel]

Hi Jimmy

I can't tell you about MySQL Administrator specifically as I don't use it. But the general answer (I'll keep it as simple as possible) will be the same for most of the tools:

You can of course take a database backup without any special means (no locking for MyISAM tables). But the problem is that your backup will not be "consistent": As the backup takes some time, not all tables will be backed up at the same moment.

So imagine (just an example) you have one table for users and one table for their blog entries or articles or whatever. Now it could happen that the blog table gets backed up first and during this backup a user and all its blog entries are deleted. After that the users table is backed up. Now, in your backup, you still have the blog entries for this user in the blog table - but you don't have the corresponding user anymore in your users table (as the backup for that table only started after both the user and the blog entries had been deleted).

That's what the locks are for: They block all write access to your tables, so your backup will be consistent. You should use it, if you have MyISAM tables.

InnoDB supports transactions and a consistent backup can still be taken if it happens inside a single transaction.

But if you want to have a consistent backup of all your tables (including MyISAM, InnoDB and possibly other storage engines), you still need the global lock of all tables.

The lock is certainly the better solution than to block all your users completely from your database (shutting down), as you suggest. Reads are still possible while the tables are locked. Writes will just wait ("hang") until the backup is finished. But you should still take care here: As soon as a write to a MyISAM table hangs (is locked), further reads will also lock (because the table is not up to date anymore, as a write operation is pending).

So, if your database is not too big, this should be no problem, as the backup finishes quickly. If it's really big and the backup takes long (definition of "long" depending your application - in a web interface a few seconds may be too long already), you should make sure in your application that no writes occur during the backup time. (Perhaps by telling your users, they have to try again in a few minutes.) If this is a problem: Switch everything where consistency is needed to InnoDB or take a different approach altogether (e.g. with replication servers from where you could take your backup without affecting the master server).

That's the general answer. Maybe somebody can give you some further tips for MySQL Administrator specifically.

Good luck,
Beat Vontobel