|
|
05-08-2008
|
||||
|
||||
Cách lock tables trong mySQL để phục vụ backup
In order to make a backup of a database, you have to make sure it’s not modified while it is in the process of backup.
Let’s see here how to lock a single table or a full database.. Locking a table only : Code:
[root@host](1034)# mysql -p mysql> CONNECT database; # Locking.. mysql> LOCK TABLE table READ ; Query OK, 0 rows affected (0.00 sec) # This is the only way I have found to make sure the lock was active mysql> INSERT INTO table VALUES (’1′); ERROR 1099: Table ‘table’ was locked with a READ lock and can’t be updated # Unlocking.. mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) # We make sure the lock is gone now.. mysql> INSERT INTO new VALUES (’1′); Query OK, 1 row affected (0.00 sec) Locking a whole database : [root@host](1034)# mysql -p mysql> CONNECT database; # Locking.. I term “flush” is inappropriate to my taste mysql> FLUSH TABLES WITH READ LOCK ; Query OK, 0 rows affected (0.00 sec) # Checking the lock mysql> INSERT INTO table VALUES (’1′); ERROR 1223 (HY000): Can’t execute the query because you have a conflicting read lock # Unlocking.. mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) # Lock is gone.. mysql> INSERT INTO table VALUES (’1′); Query OK, 1 row affected (0.00 sec) I personnaly use this in a cron because I need to make a monthly backup of a huge database (around 16 Gb) that doesn’t get a lot of changes. I use rsync to do that kind of backup (so making a backup of the .frm, .myi and .myd) and thus need to lock from the CLI or a script. Code:
$ mysql -u login -p password -e “CONNECT database; LOCK TABLE table READ;” ~/.my.cnf : Code:
[client] user=user pass=password Code:
mysqldump –add-drop-table –lock-all-tables –default-character-set=latin1 -uuser -ppassword DATABASE > dump.sql (Sébastien Wains)
__________________
http://hackingart.com/tinh_yeu_va_gi...c_girl_9x.html 
|
 |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
