mysql backup script problem. :(

[calvin_leonhart]

I have a script that does a mySQL backup.

Here is the code:

                is_admin();

            require '../lib/config.php';

            connectcp();

            if ($method == "download") {

		// GET CURRENT DATE

                    $date = date("m-d-y_H-i");

                    // SEND TEMP FILE TO BROWSER

            	header("Content-disposition: attachment; filename=backup-$date.sql");
		header("Content-type: unknown/unknown");

            	// WRITE TEMP FILE

            	$filehandle = fopen("../lib/backup/backup-$date.sql", 'w');
		$result = mysql_query('SHOW tables');
			while ($currow = mysql_fetch_array($result, MYSQL_NUM)) {
				sql_info($currow[0], $filehandle, $method);
				fwrite($filehandle, "\n\n\n");
			}
		fwrite($filehandle, "### DATABASE BACKUP COMPLETED ###");
		fclose($filehandle);

                    // DELETE TEMP FILE

                    unset($filehandle);
                    unlink("../lib/backup/backup-$date.sql") or die("FAILURE TO DELETE TEMP FILE...");
            }

            if ($method == "save") {
                    $date = date("m-d-y_H-i");
            	$filehandle = fopen("../lib/backup/backup-$date.sql", 'w');
		$result = mysql_query('SHOW tables');
			while ($currow = mysql_fetch_array($result, MYSQL_NUM)) {
				sql_info($currow[0], $filehandle, $method);
				fwrite($filehandle, "\n\n\n");
			}
		fwrite($filehandle, "### DATABASE BACKUP COMPLETED ###");
		fclose($filehandle);
                    msg("Database Backup Saved", "The database backup has sucessfully been saved to /lib/backup/.", "backup.php?action=backup");
            }

            if ($method == "display") {

            	// WRITE TEMP FILE

                    $date = date("m-d-y_H-i");
            	$filehandle = fopen("../lib/backup/backup-$date.sql", 'w');
		$result = mysql_query('SHOW tables');
			while ($currow = mysql_fetch_array($result, MYSQL_NUM)) {
				sql_info($currow[0], $filehandle, $method);
				fwrite($filehandle, "\n\n\n");
			}
		fwrite($filehandle, "### DATABASE BACKUP COMPLETED ###");
		fclose($filehandle);

                    // READ TEMP FILE

                    $backup = file_get_contents("../lib/backup/backup-$date.sql");

                    $backup = nl2br($backup);

                    msg("Database Backup", "$backup", "");

                    // DELETE TEMP FILE

                    unset($backup);
                    unset($filehandle);
                    unlink("../lib/backup/backup-$date.sql") or die("FAILURE TO DELETE TEMP FILE...");

            }

And...

function sql_info($table, $fp = 0, $method)
{

$tabledump = mysql_query("SHOW CREATE TABLE $table");
$tabledump = "DROP TABLE IF EXISTS $table;\n" . $tabledump['Create Table'] . ";\n\n";

    if ($fp)
{
	fwrite($fp, $tabledump);
}
else
{
	echo $tabledump;
}

// get data
$rows = mysql_query("SELECT * FROM $table");
$numfields=mysql_num_fields($rows);
while ($row = mysql_fetch_array($rows))
{
	$tabledump = "INSERT INTO $table VALUES(";

	$fieldcounter = -1;
	$firstfield = 1;
	// get each field's data
	while (++$fieldcounter < $numfields)
	{
		if (!$firstfield)
		{
			$tabledump .= ', ';
		}
		else
		{
			$firstfield = 0;
		}

		if (!isset($row["$fieldcounter"]))
		{
			$tabledump .= 'NULL';
		}
		else
		{
			$tabledump .= "'" . mysql_escape_string($row["$fieldcounter"]) . "'";
		}
	}

	$tabledump .= ");";

	if ($fp)
	{
		fwrite($fp, $tabledump);
	}
	else
	{
	echo $tabledump;
	}
}

mysql_free_result($rows);

}

It attempts to backup all tables on the database, but it's missing the 'CREATE TABLE IF EXISTS, etc..

Okay so here is what is outputted on the sql backup file.

DROP TABLE IF EXISTS test1;
;

INSERT INTO test1 VALUES('1', 'Calvin', 'ffgf', 'dssdsd');

DROP TABLE IF EXISTS test2;
;

INSERT INTO test2 VALUES('2', 'Calvin2', 'ffssddsgf', 'dsssdsdddsdsd');

DROP TABLE IF EXISTS test3;
;

INSERT INTO test3 VALUES('3', 'Calvin3', 'test', 'asdf');

Any help would be greatly appreciated. I've been trying to get this to work for a while now.

[NogDog]

function sql_info($table, $fp = 0, $method)
{
	$tabledump = "DROP TABLE IF EXISTS $table;\n" . 
	$createResult = mysql_query("SHOW CREATE TABLE $table");
	$tabledump .= mysql_result($createResult, 0);
// rest of function . . .
}

[calvin_leonhart]

Nope.

Here is the SQL dump now:

DROP TABLE IF EXISTS test;
Resource id #10testINSERT INTO test VALUES('5', 'DF', 'h', 'DF');

[troybtj]

If your database is large, or will be large, I'd recommend using mysqldump instead. Much less resource intensive, and faster.

[calvin_leonhart]

I cannot use mysqldump. 🙁

[NogDog]

Do you do a mysql_select_db() anywhere before you try to do the SHOW CREATE TABLE query?

[calvin_leonhart]

Yes, here are the contents of connectcp();:

        function connectcp() {

            require 'config.php';

            mysql_connect("$mysqlserver", "$mysqluser", "$mysqlpass");
            mysql_select_db("$dbname") or die (dberrorhtml());

    }

[calvin_leonhart]

Okay, I fixed it. 🙂

function sql_info($table, $fp = 0, $method)
{

    require '../lib/config.php';

    connectcp();

    [b]$tabledump2 = mysql_query("SHOW CREATE TABLE $table");
    $tabledump2 = mysql_fetch_array($tabledump2);
$tabledump = "DROP TABLE IF EXISTS $table;\n" . $tabledump2['Create Table'] . ";\n\n";[/b]
if ($fp)
{
	fwrite($fp, $tabledump);
}
else
{
	echo $tabledump;
}

    // get data
    $rows = mysql_query("SELECT * FROM $table");
    $numfields=mysql_num_fields($rows);
    while ($row = mysql_fetch_array($rows))
    {
            $tabledump = "INSERT INTO $table VALUES(";

            $fieldcounter = -1;
            $firstfield = 1;
            // get each field's data
            while (++$fieldcounter < $numfields)
            {
                    if (!$firstfield)
                    {
                            $tabledump .= ', ';
                    }
                    else
                    {
                            $firstfield = 0;
                    }

                    if (!isset($row["$fieldcounter"]))
                    {
                            $tabledump .= 'NULL';
                    }
                    else
                    {
                            $tabledump .= "'" . mysql_escape_string($row["$fieldcounter"]) . "'";
                    }
            }

            $tabledump .= ");";

            if ($fp)
            {
                    fwrite($fp, $tabledump);
            }
            else
            {
            echo $tabledump;
            }
    }

    mysql_free_result($rows);

}

[calvin_leonhart]

Well, I've come across another little problem. When I try to download the SQL backup. the file size is 0 bytes. 😕

Code:

                if ($method == "download") {

                    header("Content-disposition: attachment; filename=../lib/backup/backup-$date.sql");
                    header("Content-type: unknown/unknown");

                    // GET CURRENT DATE

                    $date = date("m-d-y_H-i");

                    // WRITE TEMP FILE

                    $filehandle = fopen("../lib/backup/backup-$date.sql", 'w');
                    $result = mysql_query('SHOW tables');
                            while ($currow = mysql_fetch_array($result, MYSQL_NUM)) {
                                    sql_info($currow[0], $filehandle, $method);
                                    fwrite($filehandle, "\n\n\n");
                            }
                    fwrite($filehandle, "### DATABASE BACKUP COMPLETED ###");
                    fclose($filehandle);

    	}