?
Solved

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

Posted on 2011-02-23
8
Medium Priority
?
680 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

Technology Partners: 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!

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

777 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