Solved

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

Posted on 2011-02-23
8
660 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to dynamically set the form action using jQuery.
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 …

863 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

20 Experts available now in Live!

Get 1:1 Help Now