Why is my database backup script not working in php?

Question

I am using the Database Backup script by David Walsh(http://davidwalsh.name/backup-mysql-database-php) to backup my MYSQL database as a .sql file to my server.

I created a user named backup and gave it all privileges(just to make sure). Then I put the code into a php file and setup a cron job to run the php file.

This is the code:

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{

    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);

    //get all of the tables
    if($tables == '*')
    {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result))
        {
            $tables[] = $row[0];
        }
    }
    else
    {
        $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

    //cycle through
    foreach($tables as $table)
    {
        $result = mysql_query('SELECT * FROM '.$table);
        $num_fields = mysql_num_fields($result);

        $return.= 'DROP TABLE '.$table.';';
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
        $return.= "\n\n".$row2[1].";\n\n";

        for ($i = 0; $i < $num_fields; $i++) 
        {
            while($row = mysql_fetch_row($result))
            {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j<$num_fields; $j++) 
                {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                    if ($j<($num_fields-1)) { $return.= ','; }
                }
                $return.= ");\n";
            }
        }
        $return.="\n\n\n";
    }

    //save file
    $handle = fopen('../backup/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

backup_tables('localhost','alupto_backup','pass','*');

When the cron job runs, the backup does not work and I receive an email with the error that appears:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home5/ideapale/public_html/amatorders_basic/admin/backup.php on line 18

On line 18 is this code:

while($row = mysql_fetch_row($result))

When I run the SQL(SHOW TABLES) in phpMyAdmin, it works fine and shows me a list of all the tables. But for some reason, I am receiving an error when the php file tries to run the SQL.

Why is my database backup script not working?

Answer 1

Use mysql_error() function to see what is that MySQL complains about.

Answer 2

You have to make sure that mysql_query does not first return a non-stream. Try this:

....

if (false !== ($result = mysql_query('SHOW TALBES')) {
    while($row = mysql_fetch_row($result))
    {
        $tables[] = $row[0];
    }
} else {
    // see Mchl's point about mysql_error
}

Answer 3

The script looks very weak IMHO. You should consider using mysqldump it it's available in your system.

Update

The basic command line would be:

mysqldump -hYOURHOSTNAME -uYOURUSER -pYOURPASSWORD infocap > dump.sql

You can test mysqldump in your local computer and, once you're happy with the results, either create a shell script or add it directly as a cron task.

Answer 4

This will not work to back up your database as an SQL script, unless your database is just a toy database, the equivalent of a "hello world" script.

That script is appalling. You should not use it to back up a database. That script has been posted before: PHP Database Dump Script - are there any issues?

• No error checking after mysql_connect() or mysql_queries(). You probably just gave a wrong password or something, but you'd never know because the script doesn't verify the connect was successful.

• It won't produce the right INSERT statement if your database contains any NULLs.

• Character sets are not handled.

• addslashes() is not suitable for escaping data.

• Table names are not delimited.

• mysql_query() buffers results, so if you have a table with thousands of rows or more, you'll exceed your PHP memory limit. In fact, the script concatenates the series of INSERT statements into a single PHP variable. So before it finishes, you will have your entire database represented in memory.

You should never use that script. It's utter garbage, and I do not say that lightly.

Just use shellexec() to run mysqldump.

@Álvaro G. Vicario has a good point, there's no need for you to even use PHP for this task. I was assuming you need to make a backup from a PHP script. Here's how I would create a backup from a cron script:

Create a shell script, it can be called whatever you want, e.g. mymysqldump.sh. Here's how I would write it:

:
: ${HOST:="localhost"}
: ${DATABASE:="mydatabase"}
: ${BACKUP_DIR:="/opt/local/var/db/mysql5/backups"}
: ${BACKUP_FILE:="${DATABASE}-`date +%Y%m%d%H%M%S`"}

mysqldump -h ${HOST} ${DATABASE} > ${BACKUP_DIR}/${BACKUP_FILE}

Of course customize the values of the variables as needed for your environment.

You may notice that the username and password are not in this file. Please don't put passwords into scripts in plain text so everyone can read them. Instead, we'll put them in an options file to make it more secure.

Create a special operating system user who is going to run the backup from cron. Your system may have a special user "mysql" or "_mysql" to run the MySQL Server, but this user may be configured to have no valid home directory. You need a user that has a home directory. Let's call it "mybackup".

Under that user's home directory, create a file .my.cnf with the following content:

[mysqldump]
user = alupto_backup
password = xyzzy

Where "alupto_backup" and "xyzzy" are the MySQL username and its password (change these for your environment). Set the ownership and mode of this file so that only its owner can read it:

chown mybackup .my.cnf
chmod 600 .my.cnf

Create a bin directory under this user's home and put our shell script into it.

mkdir ~mybackup/bin
mv mymysqldump ~mybackup/bin

Now you can run the shell script to test it:

sh ~mybackup/bin/mymysqldump

Now create a cron file for this user:

crontab -u mybackup

@daily ~mybackup/bin/mymysqldump

That should be it.