Re: MySQLDump and master/slave Behavior

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



1. mysqldump will only keep the connection open for as long as it needs it. Once your calling script is allowed to continue mysqldump has either exited successfully or with an error, but either way the connection should no longer be active.

2. I don't think that would be a good idea. 30 minutes should buy you enough time, but if the system is getting slammed or some other unforeseeable event happens that makes the system slow down to a crawl then you run the risk of breaking your backup process. The bulk of the execution time in your script is almost certainly with your tar command. The 'j' option is telling it to use the bzip2 algorithm for compression, which is a very high compression level, but takes a long time to obtain compared to other compression algorithms. In short, I don't think anything that you're doing is going to cause any data loss as long as mysqldump isn't locking the slave database for a huge amount of time.

As an aside, this isn't really PHP related so much as it is MySQL or even *nix shell commands related, you might try a MySQL list for verification.


On Oct 3, 2006, at 10:29 AM, David Giragosian wrote:

Howdy Guys,

I have a PHP backup script running as a cronjob that has an exec (mysqldump) line followed a bit later by an exec(tar -cjf) line. The backup script runs against a slave db in the wee hours, and the master gets a continuous stream of inputs at the rate of about 18,720 per day. Obviously as the database grows in size, the script takes longer and longer to run. Right now, after about 15 months of operation, it's taking about 40 minutes to complete. But I did a test yesterday, and the mysqldump part only takes about 2 minutes to
finish.

Even though I've never lost data, my concern is that since master- slave syncing is blocked while the script runs, I just might lose something if
there's a buffer overrun or something like that.

So, Question 1 is: does mysqldump's connection to the slave db exist for the entire script execution time, or just for the length of time of its own execution? I imagine if I used mysql_connect() in the script that it would be for the entire length of the script execution, but mysqldump makes its
own connection, so I'm just not sure about this.

Question 2: Should I, just to be on the safe side, break out the mysqldump code into its own scipt, and run it, say, 30 minutes before the tar script?

Thanks for your time,

David


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux