Thanks for the reply!!
Quote:
Originally Posted by otheus
If he'll put it in a cronjob, you cannot rightly assume what the PWD (present working directory) will be, so that the line:
Code:
sql_bDir="./sql_backups/" #LOCATION FOR SQL BACKUPS TO LAND
Doesn't make sense. You need a full path, or maybe something prefixed with $HOME.
|
good point, I realize that though and I only had ./ in place as something temporary as I do manual tests (easier to type out for testing)
Quote:
Originally Posted by otheus
Second, the mysqldump line should be:
Code:
mysqldump -u NAME -p PASS DBASENAME
Finally, you should understand that the output is not automatically compressed. Do that separately, via a pipe
Code:
mysqldump -u NAME -p PASS | gzip -c > $sql_bName.gz
|
Thank you, I had failed to read what the "opt" option does (was going on an online article's suggestion to use it that said opt does compression)
This is what i found:
Quote:
o --opt
This option is shorthand; it is the same as specifying
--add-drop-table --add-locks --create-options --disable-keys
--extended-insert --lock-tables --quick --set-charset. It should
give you a fast dump operation and produce a dump file that can be
reloaded into a MySQL server quickly.
The --opt option is enabled by default. Use --skip-opt to disable
it. See the discussion at the beginning of this section for
information about selectively enabling or disabling certain of the
options affected by --opt.
|
If I don't know what most of those ^ do (as I'm not familiar with MYSQL) would you suggest I just stick with the simpler code you laid out? or will the above options of "opt" not hurt?
Quote:
Originally Posted by otheus
Finally, you might gain some performance by buffering the output through a program called "dd". This allows mysqldump to do its work for a longer time before switching over to do the compression.
Code:
mysqldump -u NAME -p PASS DBASENAME | dd bs=1M | gzip -c > $sql_bName.gz
|
This is really cool, thanks!
Quote:
Originally Posted by otheus
For my dump, I make sure the dump is done in a "single-transaction" to ensure that the dump represents a recoverable state. (ie, transactions don't take place during the dump). I also send the output to bzip2 for higher compression.
Code:
mysqldump -u NAME -p PASS --single-transaction DBASENAME | dd bs=1M | bzip2 -c >dumpfile
|
There are more than 8,100 items in our shopping cart. after reading the man I see it is suggested to use "quick" in conjunction with "single-transaction" for large tables. What is a "large" table? Is 8,000+ items considered a large table? If I use "quick" will it defeat the purpose of running the bs=1M option with dd?
Thanks for all your help!!
