Confused maybe about MySQL Dump & PHP

05-29-2009

Registered User

31, 0

Join Date: Apr 2009

Last Activity: 22 June 2010, 8:23 PM EDT

Posts: 31

Thanks Given: 0

Thanked 0 Times in 0 Posts

Confused maybe about MySQL Dump & PHP

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

jzacsh

View Public Profile for jzacsh

Find all posts by jzacsh

06-02-2009

Registered User

2,157, 51

Join Date: Feb 2007

Last Activity: 6 September 2017, 5:43 AM EDT

Location: Innsbruck, Austria

Posts: 2,157

Thanks Given: 12

Thanked 51 Times in 48 Posts

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

otheus

View Public Profile for otheus

Find all posts by otheus

06-02-2009

Registered User

31, 0

Join Date: Apr 2009

Last Activity: 22 June 2010, 8:23 PM EDT

Posts: 31

Thanks Given: 0

Thanked 0 Times in 0 Posts

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

jzacsh

View Public Profile for jzacsh

Find all posts by jzacsh

06-02-2009

Registered User

2,157, 51

Join Date: Feb 2007

Last Activity: 6 September 2017, 5:43 AM EDT

Location: Innsbruck, Austria

Posts: 2,157

Thanks Given: 12

Thanked 51 Times in 48 Posts

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.

otheus

View Public Profile for otheus

Find all posts by otheus

06-02-2009

Registered User

31, 0

Join Date: Apr 2009

Last Activity: 22 June 2010, 8:23 PM EDT

Posts: 31

Thanks Given: 0

Thanked 0 Times in 0 Posts

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

jzacsh

View Public Profile for jzacsh

Find all posts by jzacsh

06-02-2009

Registered User

2,157, 51

Join Date: Feb 2007

Last Activity: 6 September 2017, 5:43 AM EDT

Location: Innsbruck, Austria

Posts: 2,157

Thanks Given: 12

Thanked 51 Times in 48 Posts

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.

otheus

View Public Profile for otheus

Find all posts by otheus

06-02-2009

Registered User

31, 0

Join Date: Apr 2009

Last Activity: 22 June 2010, 8:23 PM EDT

Posts: 31

Thanks Given: 0

Thanked 0 Times in 0 Posts

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)

jzacsh

View Public Profile for jzacsh

Find all posts by jzacsh

Web Development

Confused maybe about MySQL Dump & PHP

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Create mysql database with bash script - confused

Discussion started by: ktm

2. Shell Programming and Scripting

Can't add variable to file name in mySQL dump script

Discussion started by: ojobson

3. Shell Programming and Scripting

PHP read large string & split in multidimensional arrays & assign fieldnames & write into MYSQL

Discussion started by: lowmaster

4. Web Development

[php] webpage with login & mysql-db

Discussion started by: thibautp

5. Shell Programming and Scripting

how to break mysql dump sql file

Discussion started by: learnbash

6. Shell Programming and Scripting

restore mysql dump file in many remote servers?

Discussion started by: blesets

7. Shell Programming and Scripting

PHP & MySQL: sort desending and print

Discussion started by: perleo

8. UNIX for Dummies Questions & Answers

PHP & Apache & MySQL install how-to ?

Discussion started by: perleo

9. UNIX for Dummies Questions & Answers

Linux/Unix/PHP/MySQL & servers?

Discussion started by: kolton