为什么我的数据库备份脚本无法在php中使用? [英] Why is my database backup script not working in php?

问题描述

我正在使用David Walsh的数据库备份脚本( http://davidwalsh.name/backup -mysql-database-php )将我的MYSQL数据库作为.sql文件备份到我的服务器.

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.

我创建了一个名为backup的用户，并赋予了它所有特权(只是为了确保).然后，我将代码放入php文件中，并设置cron作业以运行php文件.

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.

这是代码:

/* 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','*');

cron作业运行时，备份无法正常工作，并且我收到一封电子邮件，显示以下错误:

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

警告: mysql_fetch_row():提供的参数 不是有效的MySQL结果资源 在 /home5/ideapale/public_html/amatorders_basic/admin/backup.php 在第 18

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

第18行是以下代码:

while($row = mysql_fetch_row($result))

当我在phpMyAdmin中运行SQL(SHOW TABLES)时，它工作正常，并向我显示了所有表的列表.但是由于某种原因，当php文件尝试运行SQL时，我收到了错误消息.

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?

推荐答案

除非您的数据库只是一个玩具数据库(相当于"hello world"脚本)，否则这将无法以SQL脚本的形式备份您的数据库.

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.

该脚本令人震惊.您不应该使用它来备份数据库.该脚本之前已发布: PHP数据库转储脚本-是否存在任何问题?

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?

• 在mysql_connect()或mysql_queries()之后没有错误检查.您可能只是输入了错误的密码或其他密码，但您永远不会知道，因为脚本无法验证连接是否成功.

• 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.

如果您的数据库包含任何NULL，它将不会产生正确的INSERT语句.

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

字符集未处理.

addslashes()不适用用于转义数据.

addslashes() is not suitable for escaping data.

表名没有定界.

不备份视图，过程，函数或触发器.

Does not back up views, procedures, functions, or triggers.

mysql_query()缓冲结果，因此，如果您的表具有数千行或更多行，则将超出PHP内存限制.实际上，该脚本将一系列INSERT语句串联到单个PHP变量中.因此，在完成之前，您将在内存中表示整个数据库.

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.

没有人可以使用该脚本.这完全是垃圾，我不能轻易地说出来.

No one should ever use that script. It's utter garbage, and I do not say that lightly.

只需使用shellexec()运行mysqldump.

@ÁlvaroG. Vicario很好，您甚至不需要使用PHP即可完成此任务.我以为您需要从PHP脚本进行备份.这是我从cron脚本创建备份的方法:

@Á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:

创建一个shell脚本，您可以随意调用它，例如mymysqldump.sh.这是我的写法:

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

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

mysqldump -h ${BACKUP_HOST} ${BACKUP_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.

创建一个特殊的操作系统用户，该用户将从cron运行备份.您的系统可能有一个特殊的用户"mysql"或"_mysql"来运行MySQL Server，但是该用户可能配置为没有有效的主目录.您需要一个具有主目录的用户.我们称之为"mybackup".

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".

在该用户的主目录下，创建具有以下内容的文件.my.cnf:

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

[mysqldump]
user = alupto_backup
password = xyzzy

其中"alupto_backup"和"xyzzy"是MySQL用户名及其密码(请针对您的环境进行更改).设置此文件的所有权和模式，以便只有其所有者才能读取它:

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

在该用户的主目录下创建一个bin目录，并将我们的Shell脚本放入其中.

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

mkdir ~mybackup/bin
mv mymysqldump ~mybackup/bin

现在，您可以运行shell脚本进行测试:

Now you can run the shell script to test it:

sh ~mybackup/bin/mymysqldump

现在为该用户创建一个cron文件:

Now create a cron file for this user:

crontab -u mybackup

@daily ~mybackup/bin/mymysqldump

应该的.

这篇关于为什么我的数据库备份脚本无法在php中使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！