Solved

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

Posted on 2011-02-23
8
664 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 109

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 109

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
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…
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 count occurrences of each item in an array.

776 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