At 0:03 -0400 4/16/04, Mark Susol|Ultimate Creative Media wrote:
Did I really ask that tough a question? Anyone?
I'm not sure you're asking an answerable question. Consider this requirement
that you give below:
> So what do I need to do before running any backup scripts to ensure the
> tables will not be corrupted during any kind of shell operation? Do I have
I'm not sure I know of any precaution that can prevent you from corrupting
your tables during *any kind of shell operation*. It's usually possible
to cause a violation of table integrity from the shell if you're determined
to do so.
(I know that may not be quite the question you intended to ask, but it's
what you actually *did* ask.)
The general answer to your question, if you're willing to cooperate
with 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.
On 4/15/04 7:38 PM, "Mark Susol | Ultimate Creative Media"
<[EMAIL PROTECTED]> wrote:
I've found a nice shell script to use to backup my server's MySQL databases.
> https://sourceforge.net/projects/automysqlbackup/
However, when I tried this earlier today it resulted in a corrupt table. Now
the table in question is one I've had issues with for other reasons, but it
has over 2 mil records in it when only partially built.
What I need to do is put a lock on the database/tables before the script is
run. The script allows for a pre & post shell script to run.
> So what do I need to do before running any backup scripts to ensure the
tables will not be corrupted during any kind of shell operation? Do I have
> to stop services like mysqld or httpd before hand?
> Does anyone use MySQL 4.0.18 and the hot copy instead? I'm using 4.0.17-max
--
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]
