?
Solved

mysqldump --cat using php on shared hosting does not produce a csv

Posted on 2011-02-23
8
Medium Priority
?
693 Views
Last Modified: 2012-05-11
I'm trying to run mysqldump from PHP on a shared hosting server to create a csv file.  I am able to get a .sql file, but I can't get the csv output.  Please see the snippet below.

Am I dong something wrong, or is this just impossible given the way permissions are set up on shared hosting?
CODE:

//Verify that the DB and user info is good, and that the table exists
    $dbLink = mysql_connect($custDBHost, $custDBUser, $custDBPswd);
    print "dbLink = $dbLink<br><br>";
    $ok = mysql_select_db($custDBName, $dbLink);
    print "ok = $ok<br><br>";
    $query = "SELECT * FROM  $backupTable";
    $esult = mysql_query($query) or die ("no such table");
    print "result = $esult<br><br>";
    
    //Run the dump WITHOUT --cat
    $backupFile = "$dir/$custDBName" . date("Y-m-d-H-i-s") . '.sql';
    $command = $command = "mysqldump -h $custDBHost -u $custDBUser --password=$custDBPswd $custDBName  > $backupFile";
    system($command, $retVar); // or die('could not write file');
    print "retVar without --cat=$retVar   This validates that the directory is OK<br><br>";
	
    //Run the dump with --cat
    $command = "mysqldump --tab=$dir  -h $custDBHost -u $custDBUser --password=$custDBPswd $custDBName $backupTable > $backupFile";
    system($command, $retVar); // or die('could not write file');
    print "retVar without --cat=$retVar<br><br>";


OUTPUT:

dbLink = Resource id #4

ok = 1

result = Resource id #5

retVar without --cat=0 This validates that the directory is OK

retVar without --cat=2

Open in new window

0
Comment
Question by:anAppBuilder
  • 4
  • 2
  • 2
8 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 34967880
There are a couple of things I'd like you to look into:

1) According to mysqldump, the --tab option only works if it is run on the same computer currently running the MySQL daemon (mysqld).  Does your variables $custDBHost reflect "localhost"?

2) While the syntax looks correct, there is a difference between  your first command and second command - the inclusion of $backupTable.  Try eliminating it from the second command to see if that has an impact.

3) echo the $command variable in both instances, and post their contents here.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 total points
ID: 34969603
I use a slightly different approach for my simple tables.
<?php // RAY_mysql_backup_dump.php
error_reporting(E_ALL);
date_default_timezone_set("America/Chicago");


// PATTERN OF THE NAME OF THE BACKUP FILES
// 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
    die();
}


// GET A LIST OF THE DATA BASES ON THIS CONNECTION
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-list-dbs.php
if (!$db_list = mysql_list_dbs($db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB LIST: ";
    echo "<br/> $errmsg <br/>";
    die();
}


// ITERATE OVER THE LIST OF NAMES TO MAKE AN ARRAY
while ($row = mysql_fetch_object($db_list))
{
    $db_names[] = $row->Database ;
}


// ANYTHING IN POST DATA YET?
if (empty($_POST["d"]))
{
    // NOTHING POSTED - ASK CLIENT TO CHOOSE THE DATA BASE
    echo "<form method=\"post\">\n";
    echo "BACK UP A DATA BASE:<br/>";
    foreach ($db_names as $db_name)
    {
        echo "<input type=\"radio\" name=\"d\" value=\"$db_name\">$db_name <br/>\n";
    }
    echo "<input type=\"submit\" />\n";
    echo "</form>\n";
    die();
}


// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $db_names)) die("ERROR: DATABASE {$_POST["d"]} NOT FOUND");
$db_name = $_POST["d"];


// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}


// SET THE NAME OF THE BACKUP WITH A TIMESTAMP
$bkup = 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';
$fp   = fopen($bkup, "w");


// GET THE LIST OF TABLES
$sql = "SHOW TABLES";
$res = mysql_query($sql);
if (!$res) die( mysql_error() );
if (mysql_num_rows($res) == 0) die( "NO TABLES IN $db_name" );
while ($s = mysql_fetch_array($res))
{
    $tables[] = $s[0];
}


// ITERATE OVER THE LIST OF TABLES
foreach ($tables as $table)
{

    // WRITE THE DROP TABLE STATEMENT
    fwrite($fp,"DROP TABLE `$table`;\n");

    // GET THE CREATE TABLE STATEMENT
    $res = mysql_query("SHOW CREATE TABLE `$table`");
    if (!$res) die( mysql_error() );
    $cre = mysql_fetch_array($res);
    $cre[1] .= ";";
    $txt = str_replace("\n", "", $cre[1]); // FIT EACH QUERY ON ONE LINE
    fwrite($fp, $txt . "\n");

    // GET THE TABLE DATA
    $data = mysql_query("SELECT * FROM `$table`");
    $num  = mysql_num_fields($data);
    while ($row = mysql_fetch_array($data))
    {
        // MAKE INSERT STATEMENTS FOR ALL THE VALUES
        $txt = "INSERT INTO `$table` VALUES(";
        for ($i=0; $i < $num; $i++)
        {
            $txt .= "'".mysql_real_escape_string($row[$i])."', ";
        }
        $txt = substr($txt, 0, -2);
        fwrite($fp, $txt . ");\n");
    }
}

// ALL DONE
fclose($fp);

// SHOW THE LINK TO THE BACKUP FILE
echo "<br/>BACKUP OF $db_name CREATED HERE:\n";
echo "<br/><a href=\"$bkup\">$bkup</a>\n";

Open in new window

0
 

Author Comment

by:anAppBuilder
ID: 34972289
Thank you, routinet.

1 Attempt to use localhost:  Since I am running on shared hosting, I cannot use localhost.  When I try to connect to the DB after setting

$custDBHost = "localhost";

The connect fails and I get NO files.  Error is "Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server...

The command that prints out (edited to remove data I can't post publically):  
mysqldump --tab=/hermes/bosweb/.../myDir -h localhost -u myName --password=myPwd myDBName myTableName > /hermes/bosweb/.../myDir/myDBName_2011-02-24-12-05-31.sql

Using localhost and removing backupfile,
The command that prints out (edited to remove data I can't post publically):  
mysqldump --tab=/hermes/bosweb/.../myDir -h localhost -u myName --password=myPwd myDBName myTableName

2 Removing backupfile and using the correct host.This gives me only the the .sql file
The command that prints out (edited to remove data I can't post publically):
mysqldump --tab=/hermes/bosweb/.../myDir -h mySiteAbbr.ipowermysql.com -u myName --password=myPwd myDBName myTableName
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:anAppBuilder
ID: 34972343
Thank you, Ray_Paseur.

Yes, of course I can write a PHP program to read the tables and create CSV files.  I'm trying to avoid that if I can and use a standard utility.
0
 

Author Comment

by:anAppBuilder
ID: 34972456
One more comment.  If I can't use mysqldump because of the constraints of shared hosting, please tell me.  That would be valuable information.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 34973356
Well, my dump code works.  And I have a complementary restore script.  It works, too, I think.  Have not tested them on PHP 5.3.5 yet.
<?php // RAY_mysql_backup_restore.php
error_reporting(E_ALL);


// PATTERN OF THE NAME OF THE BACKUP FILES
// 'RAY_backups/mysql' . date('Ymd\THis') . $db_name . '.txt';


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";


// FIND THE BACKUP FILES
if (!$my_dir = scandir('backups',1)) die('NO BACKUP DIRECTORY FOUND');

// IF NOTHING POSTED YET
if (empty($_POST))
{

// ASK CLIENT TO CHOOSE ONE FOR RESTORE
    $x = FALSE;
    echo "<form method=\"post\">\n";
    echo "RESTORE A DATA BASE:<br/>";
    foreach ($my_dir as $my_file)
    {
        if (!ereg('^mysql', $my_file)) continue;
        echo "<input type=\"radio\" name=\"d\" value=\"$my_file\">$my_file <br/>\n";
        $x = TRUE;
    }
    if (!$x) die('NO BACKUP FILES FOUND');
    echo "<input type=\"submit\" />\n";
    echo "</form>\n";
    die();
}


// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $my_dir)) die("ERROR: FILE {$_POST["d"]} NOT FOUND");

// THE NAME OF THE BACKUP FILE
$fname = $_POST["d"];

// GET THE NAME OF THE DB FROM THE BACKUP FILE NAME
$db_name = $fname;
$db_name = ereg_replace("^mysql", '', $db_name);
$db_name = ereg_replace("\.txt$",         '', $db_name);
$db_name = substr($db_name, 15); // LENGTH 15 = strlen("date('Ymd\THis')")


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}

// LOAD THE BACKUP DATA QUERIES
$sqls  = file('RAY_backups/' . $fname);

// ITERATE OVER THE QUERIES TO RELOAD THE DATA
foreach ($sqls as $sql)
{
    if (!$res = mysql_query($sql))
    {
        $errmsg = mysql_errno() . ' ' . mysql_error();
        echo "<br/>QUERY FAIL: ";
        echo "<br/>$sql <br/>";
        die($errmsg);
    }
}

Open in new window

0
 
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 1000 total points
ID: 34976881
>>> Since I am running on shared hosting, I cannot use localhost.

It is not the shared hosting presenting the difficulty, but the fact that your database is hosted on a different server.  In this case, your best option is a custom export/import routine, just like the one Ray_Paseur has presented to you.  

You can still use the standard mysqldump utility - you just can't use the CSV export functionality it offers.
0
 

Author Closing Comment

by:anAppBuilder
ID: 34992536
Thank you both.  Knowing that I can't do something is as valuable as a "how to".  

Thank you for your script, Ray. It should help many people, although I need to take a somewhat different approach.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question