How do I FREEZE/stop/lock a MYSQL database? THen how do I enable the databse back to normal?

Question

Basically, I need to stop it. (So that I can do backup without new data). Everything "frozen".

THen, how do I enable it so that people can make changes to it again?

Answer 1

To expand on 3dinfluence's answer. mysqldump will lock the tables with a read lock before dumping them (which is what you're after when you say "freeze"). Depending on your storage engine, the UPDATES (and possibly SELECTS) will be queued up until the dump is complete (at which point it'll unlock the tables).

if you want to do it manually, the command you'll want will be something like (adjust to suit if you don't want the global lock).

flush tables with read lock;

This will apply a global lock and flushes the query cache. You can then remove the lock with

unlock tables;

Answer 2

You can just use mysqldump command to get a snapshot of a running database without locking or freezing it.

Answer 3

if your database is stored on lvm - use snapshot feature. you can use own script or for instance mylvmbackup.

if you use innodb database - you can use mysqldump with single-transaction option, or you can try xtrabackup.

with all those options you will not have to take the database offline during the backup.