Solved

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

Posted on 2011-02-23
8
657 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 50

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 108

Accepted Solution

by:
Ray Paseur earned 250 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 108

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 50

Assisted Solution

by:Steve Bink
Steve Bink earned 250 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now