Confused maybe about MySQL Dump & PHP

jzacsh · #1 (**permalink**) 05-29-2009

Hello. the purpose of my efforts right now are to get a larger script of mine (which the admin told me he'd put into cron for me) to properly back-up my MySQL database. To test out the sql back-up part (before getting the whole script into cron, and having it not work) I wanted to test it. So here's what I did:

PHP file to do my testing (on the live server)

Code:

<?php

$test = shell_exec('echo $SHELL');
echo "<pre>$test</pre>";

$output = shell_exec('sh ./sqlBack.sh');
echo "<pre>$output</pre>";
?>

Script being called by the PHP

Code:

#!/usr/local/bin/bash

# MYSQL BACKUP:
sql_bDir="./sql_backups/" #LOCATION FOR SQL BACKUPS TO LAND
sql_bName=SqlBackup_$(date "+%A")

echo "The Shopping Cart's database backup will be in $sql_bDir and will be named $sql_bName" #NOTICE OF DATABASE BACKUP
mysqldump -uNAME -pPASS --opt DBASENAME > $sql_bDir$sql_bName.tgz

just for testing, both these files were set to 777 on the server. The output from the PHP file looks just as it should, but then I find the ./sql_backups/ directory empty. I tried the username, password and database of the last line of that script in both capital letters and lower case (read somewhere that capitals works better.. anyways). I'm not experienced with php/mysql.. I found the syntax for that mysqldump line in an article.

Thanks in advance for all your help!!

otheus · #2 (**permalink**) 06-02-2009

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.

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

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

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

jzacsh · #3 (**permalink**) 06-02-2009

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!!

otheus · #4 (**permalink**) 06-02-2009

You don't need to use --opt since it's enabled by default. 8000 items is not a lot. The "quick" option will not defeat the purpose of using bs=1M.

jzacsh · #5 (**permalink**) 06-02-2009

Quote:

Originally Posted by otheus

You don't need to use --opt since it's enabled by default. 8000 items is not a lot. The "quick" option will not defeat the purpose of using bs=1M.

Thanks so much for your help. I'm trying to test this out on my personal live website's wordpress database first. so I uploaded a test.php file to run the shell script, like so:

test.php

Code:

<?php

$output=shell_exec('sh /home/jzacsh/public_html/private/scripts/dbackup.sh');

echo "<pre>$output</pre>";

?>

the shell script its set to run is:

Code:

#!/bin/sh

sql_bDir=./sql_pickup/

#START SQL BACKUP + TGZ 
echo " " #VISUAL PADDING
echo "# BEGINNING DATABASE BACKUP PORTION:" #VISUAL
    #MYSQL LANDING DIR. CHECK
    echo "Checking if sql backup landing directory exists: $sql_bDir"
    if test -d "$sql_bDir"
    then
        echo "Directory to receive database backup exists"
    else
        echo "Directory to receive database backup does not exist, creating it"
        mkdir $sql_bDir
    fi #ENDIF SQL DIR. CHECK

echo " " #VISUAL PADDING

# MYSQL BACKUP
sql_bName=SQLBackUp_$(date +%A).gz
sql_Path=$sql_bDir$sql_bName

echo "The Shopping Cart's database backup will be:" $sql_Path #NOTICE OF DATABASE BACKUP
echo "Note: If the main backup is backing up the root directory, you do not need to separately download the database backup."

mysqldump -u name -p wppass --single-transaction db123 | dd bs=1M | gzip -c > $sql_Path

then I visit the above ^ test.php via browser and get this output:

Code:

 
# BEGINNING DATABASE BACKUP PORTION:
Checking if sql backup landing directory exists: ./sql_pickup/
Directory to receive database backup exists
 
The Shopping Cart's database backup will be: ./sql_pickup/SQLBackUp_Tuesday.gz
Note: If the main backup is backing up the root directory, you do not need to separately download the database backup.

When I download the file its only 4KB, and upon unarchiving the file, I find a Zero KB file, which using "cat" via terminal shows that the file is indeed blank.

Any ideas what I could be doing wrong??

otheus · #6 (**permalink**) 06-02-2009

1. change sql_bDir to $PWD/sql_pickup/ so we can see where at actually outputs.
2. for testing, turn off compression and skip the dd command... output directly to the file.

jzacsh · #7 (**permalink**) 06-02-2009

Quote:

Originally Posted by otheus

1. change sql_bDir to $PWD/sql_pickup/ so we can see where at actually outputs.
2. for testing, turn off compression and skip the dd command... output directly to the file.

okay, done. didn't seem to make a difference

(the file still comes out 0 B, just uncompressed)

test.php output:

Code:

 
# BEGINNING DATABASE BACKUP PORTION:
Checking if sql backup landing directory exists: ./sql_pickup/
Directory to receive database backup exists
 
The Shopping Cart's database backup will be: ./sql_pickup/SQLBackUp_Tuesday
Note: If the main backup is backing up the root directory, you do not need to separately download the database backup.

source of dbackup.sh

Code:

#!/bin/sh

sql_bName=SQLBackUp_$(date +%A)
mysqldump -u jzacsh_jzacsh -p wordpres --single-transaction jzacsh_jzwp  > $PWD/sql_pickup/$sql_bName

actually... just in posting that ^ I realized I haven't checked:
echo $SHELL

...it returned
/usr/local/cpanel/bin/noshell

i'm guessing that means I can't run things properly? (I'm not sure, I obviously had a shell to output a file with and everything... I just wasn't successful in outputting a GOOD file)

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
MySQL Developer, meet “Quan” (aka the MySQL Query Analyzer)	iBot	MySQL DevZone RSS	0	05-15-2009 01:30 PM
MySQL University - Training on MySQL Development & Internals	iBot	Solaris BigAdmin RSS	0	11-12-2008 09:10 AM
how to break mysql dump sql file	learnbash	Shell Programming and Scripting	2	05-14-2008 05:39 AM
restore mysql dump file in many remote servers?	blesets	Shell Programming and Scripting	2	09-13-2006 01:17 AM
help, what is the difference between core dump and panic dump?	aileen	UNIX for Dummies Questions & Answers	1	06-11-2001 08:08 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: