> The general answer to your question, if you're willing to cooperatewith the server, is to lock the tables from within the server so that no other clients can modify them, and use FLUSH TABLES to flush any changes to disk. While the lock remains in place, copy the table files. Then unlock the tables. Remember that the client that locks the table *must remain connected* while you copy the table files, because any locks are released automatically when the client connection ends.
Have a look at the mysqlhotcopy source for any idea of how it uses this approach. Essentially, what it does is open a connection to the server, tells the server to lock the tables, and then while the tables are locked, goes "behind the server's back" to directly copy table files. (This is why mysqlhotcopy must be run on the server host. It's also (I believe) why it doesn't work on WIndows: Windows file locking semantics do not allow you to copy a file while the server has it locked.)
By the way, it's difficult to see how automysqlbackup could corrupt any tables. A quick look through it seems to indicate that it only uses mysqldump to perform backups.
Yes the script is using mysqldump with --opt , so these seems like what to expect for mysqlhotcopy.
?
It's not necessary that one program must operate the same way as another.
When you read the MYSQL docs, mysqlhotcopy apears to be the tool for choice for live servers. Maybe I'm not interpeting the documentation correctly, maybe it refers to having to be on the SAME server to run.
http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html indicates that you must run mysqlhotcopy on the same machine where the database directories are located. A server only manages databases on the same machine where it runs so that means the server must also be run on that same machine.
I want the automysqlbackup script to run from cron at 4am..one site in question is very busy still during that time. What does the user experience who may in the middle of a write action to the database as the server starts the mysqldump --opt process?
A user who is in the *middle* of a write action will see no effect. Any backup program attempting to acquire a lock will not succeed in getting the lock until the write action has completed.
A user who attempts to write *after* the backup program has acquired a lock will be blocked until the backup has finished. And that is what you want. (If you're backing up a table by copying its files directly, you do *not* want people messing with that table.)
How can I do myisamchk with tables locked to make sure the tables are not corrupt before I try this shell script again? I want to make sure I start clean before I try it so that if it repeats the problem I can relate the problem to the action just performed.
I don't understand this question. I thought you wanted to make a backup.
If you want to check your tables, I suggest using CHECK TABLES, and if you want to repair them, I suggest REPAIR TABLES. These operate by having the server perform the check or repair operation, so it takes care of locking the tables so that other clients cannot change them during the operation.
-- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
