Confused maybe about MySQL Dump & PHP

jzacsh · May 29, 2009, 9:45am

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)

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

#!/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 · June 2, 2009, 6:17am

If he'll put it in a cronjob, you cannot rightly assume what the PWD (present working directory) will be, so that the line:

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:

mysqldump -u NAME -p PASS  DBASENAME

Finally, you should understand that the output is not automatically compressed. Do that separately, via a pipe

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.

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.

mysqldump -u NAME -p PASS --single-transaction DBASENAME | dd bs=1M | bzip2 -c >dumpfile

jzacsh · June 2, 2009, 9:29am

Thanks for the reply!!

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:
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)

otheus:

Second, the mysqldump line should be:
mysqldump -u NAME -p PASS  DBASENAME  
Finally, you should understand that the output is not automatically compressed. Do that separately, via a pipe
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:

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?

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.
mysqldump -u NAME -p PASS DBASENAME  | dd bs=1M | gzip -c > $sql_bName.gz

This is really cool, thanks!

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.
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 · June 2, 2009, 10:27am

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 · June 2, 2009, 12:51pm

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

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

#!/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:

 
# 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 · June 2, 2009, 3:40pm

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

jzacsh · June 2, 2009, 6:27pm

okay, done. didn't seem to make a difference (the file still comes out 0 B, just uncompressed)

test.php output:

 
# 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

#!/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)

otheus · June 3, 2009, 6:07am

Redirect stderr from mysqldump as well. Use &> instead of just >

jzacsh · June 3, 2009, 10:15am

Thanks! I did just that, and got the following error (which, allow me to preface, I've been having similar errors trying to make a small test site on my server of Drupal)

Enter password: mysqldump: Got error: 1045: Access denied for user 'jzacsh_jzacsh'@'localhost' (using password: NO) when trying to connect

But I don't understand - I'm using the same username/password and database as my wordpress install, I even copy/pasted the syntax of it out of the wp-config.php file, so I don't know what I'm mistyping

otheus · June 3, 2009, 12:55pm

Isn''t that curious... with mysqldump, you have to specify --password, not -p.

mysqldump -u jzacsh_jzacsh --password=wordpres --single-transaction jzacsh_jzwp

jzacsh · June 3, 2009, 2:10pm

aah! thank you. okay, this is now what I get:

mysqldump: Got error: 1044: Access denied for user 'jzacsh_jzacsh'@'localhost' to database 'jzwp' when selecting the database
-- MySQL dump 10.11
--
-- Host: localhost    Database: jzwp
-- ------------------------------------------------------
-- Server version	5.0.81-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

any suggestions? how is it possible I'm still getting an access error at the beginning of the output, if I clearly have some kind of interaction going on below??

otheus · June 3, 2009, 3:33pm

Could it be you are selecting the wrong database? Maybe internal to MySQL it is simply 'jzwp' and not 'jzacsh_jzwp' ? So the login works and you have access to your own database. But you can't switch to a non-existent database, so you get the error. But because you successfully logged in, you get the database dump as normal.

jzacsh · June 3, 2009, 11:40pm

wow that's amazing! (I had been trying that previously, when I had the --password syntax incorrect). Thank you so much for sticking around! I'll be finishing a book soon on PHP & MySQL and a 2-part unix admin course... you can be sure I'll be around to support these forums!

ps.
so that others can benefit, here's what the final proper output looks like for me:

mysqldump -u uname --password=psswd --single-transaction prefix_dbname | dd bs=1M | gzip -c > $sql_Path 2>&1