php - 使用 php 脚本备份和上传数据库。 (上传到数据库时出现问题: sql syntax will not work because of foreignkey connection

Question

我在通过表之间的键连接上传数据库时遇到问题。

只要表之间没有连接，脚本就可以正常工作。

我必须在 php 类中包含什么 MySql 命令才能删除所有外键？因此，我可以删除表，然后在创建所有表后将键连接添加回表中。

这是我正在使用的类:

<?php
class BackupDB

    {
    private $host = '';
    private $username = '';
    private $passwd = '';
    private $dbName = '';
    private $charset = '';
    function __construct($host, $username, $passwd, $dbName, $charset = 'utf8')
        {
        $this->host = $host;
        $this->username = $username;
        $this->passwd = $passwd;
        $this->dbName = $dbName;
        $this->charset = $charset;
        $this->initializeDatabase();
        }

    protected
    function initializeDatabase()
        {
        $conn = mysql_connect($this->host, $this->username, $this->passwd);
        mysql_select_db($this->dbName, $conn);
        if (!mysql_set_charset($this->charset, $conn))
            {
            mysql_query('SET NAMES ' . $this->charset);
            }
        }

    /**
     * Backup the whole database or just some tables
     * Use '*' for whole database or 'table1 table2 table3...'
     * @param string $tables
     */
    public

    function backupTables($tables = '*', $outputDir = '.')
        {
        try
            {
            /**
             * Tables to export
             */
            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);
                }

            $sql = 'CREATE DATABASE IF NOT EXISTS ' . $this->dbName . ";\n\n";
            $sql.= 'USE ' . $this->dbName . ";\n\n";
            /**
             * Iterate tables
             */
            foreach($tables as $table)
                {
                echo "Backing up " . $table . " table...";
                $result = mysql_query('SELECT * FROM ' . $table);
                $numFields = mysql_num_fields($result);
                $sql.= 'DROP TABLE IF EXISTS ' . $table . ';';
                $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE ' . $table));
                $sql.= "\n\n" . $row2[1] . ";\n\n";
                for ($i = 0; $i < $numFields; $i++)
                    {
                    while ($row = mysql_fetch_row($result))
                        {
                        $sql.= 'INSERT INTO ' . $table . ' VALUES(';
                        for ($j = 0; $j < $numFields; $j++)
                            {
                            $row[$j] = addslashes($row[$j]);
                            $row[$j] = str_replace("\n", "\\n", $row[$j]);
                            if (isset($row[$j]))
                                {
                                $sql.= '"' . $row[$j] . '"';
                                }
                            else
                                {
                                $sql.= '""';
                                }

                            if ($j < ($numFields - 1))
                                {
                                $sql.= ',';
                                }
                            }

                        $sql.= ");\n";
                        }
                    }

                $sql.= "\n\n\n";
                echo " OK" . "<br />";
                }
            }

        catch(Exception $e)
            {
            var_dump($e->getMessage());
            return false;
            }

        return $this->saveFile($sql, $outputDir);
        }

    /**
     * Save SQL to file
     * @param string $sql
     */
    protected
    function saveFile(&$sql, $outputDir = '.')
        {
        if (!$sql) return false;
        try
            {
            $outputfilename = $outputDir . '/db-backup-' . $this->dbName . '-' . date("d.m.Y_H.i.s") . '.sql';
            $result = mysql_query('INSERT INTO backuplog (backup) VALUES ("' . $outputfilename . '")');
            $handle = fopen($outputfilename, 'w+');
            fwrite($handle, $sql);
            fclose($handle);
            echo '<span class="message">Zapisano ' . $outputfilename . ' link do bazy. </span>';
            echo '<span class="message">Właśnie pobrano kopie zapasową. Dziękujemy Serdecznie. Życzymy miłego dnia.</span>';
            }

        catch(Exception $e)
            {
            $result = mysql_query('DELETE FROM backuplog WHERE backup ="' . $outputfilename . '"');
            var_dump($e->getMessage());
            echo '<span class="error">Notacja.Udało się pobrać bazedanych... ale #NIE zaladowano linku do formularza, aby odzyskać dane trzeba ręcznie wpisać nazwę folderu/orazpliku.sql</span>';
            echo '<span class="error">UWAGA! Wystąpił błąd podczas zapisywania danych w bazie... Zadanie nie ukończone.</span>';
            return false;
            }

        return true;
        } //end f
    public

    function loadDB($filename)
        {

        // $result=exec('mysql --user='.$this->user.' --password='.$this->pass .'<DatabaseBackUp/'.$filename);
        // mysql -u user_name -p <file_to_read_from.sql

        $templine = '';

        // Read in entire file

        $lines = file($filename);

        // Loop through each line

        foreach($lines as $line)
            {

            // Skip it if it's a comment

            if (substr($line, 0, 2) == '--' || $line == '') continue;

            // Add this line to the current segment

            $templine.= $line;

            // If it has a semicolon at the end, it's the end of the query

            if (substr(trim($line) , -1, 1) == ';')
                {

                // Perform the query

                if (!mysql_query($templine))
                    {
                    echo "Błąd ładownia pliku.sql";
                    return false;
                    } //echo $templine //TEST!

                // Reset temp variable to empty

                $templine = '';
                }
            }

        return true;
        } //end function
    } //End calss
/* USE EXAMPLE

// DOWNLOAD DB FROM SERVER AS SQL FILE TO

$backupDatabase = new BackupDB(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$status = $backupDatabase->backupTables(TABLES, OUTPUT_DIR) ? 'OK' : 'KO';
echo "<br /><br /><br />Backup result: ".$status;

// RETRIVE DATA FROM SQL FILE TO DATABASE

$backupDatabase->loadDB("Path/to/mysqlfile.sql");
*/
?>

如果您知道如何从数据库中提取外键，将它们一个接一个地删除，删除所有表，然后重新放回键，请分享您的知识。再次感谢您的调查。

对于那些喜欢使用这个脚本的人来说，它工作得很好......如果!数据库中的表没有外键连接。希望这种状态很快就会改变。

输出的 .sql 文件的简短示例:

CREATE DATABASE IF NOT EXISTS DATABASEONE;

USE DATABASEONE;

DROP TABLE IF EXISTS st_glowne_st_pages;

CREATE TABLE `st_glowne_st_pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fk_glowne` varchar(50) COLLATE utf8_polish_ci NOT NULL,
  `fk_pages` varchar(50) COLLATE utf8_polish_ci NOT NULL,
  `kolejnosc` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`),
  KEY `fk_glowne` (`fk_glowne`),
  KEY `fk_pages` (`fk_pages`),
  CONSTRAINT `st_glowne_st_pages_ibfk_1` FOREIGN KEY (`fk_glowne`) REFERENCES `st_glowne` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `st_glowne_st_pages_ibfk_2` FOREIGN KEY (`fk_pages`) REFERENCES `st_pages` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

INSERT INTO st_glowne_st_pages VALUES("1","admin.php","pageadmin.php","1");
INSERT INTO st_glowne_st_pages VALUES("3","index.php","pageindex.php","1");
INSERT INTO st_glowne_st_pages VALUES("4","work.php","pagework.php","1");
INSERT INTO st_glowne_st_pages VALUES("7","register.php","pageregister.php","1");
INSERT INTO st_glowne_st_pages VALUES("8","login.php","pagelogin.php","1");

Answer 1

我自己没有尝试过，但显然你可以关闭检查。

设置 FOREIGN_KEY_CHECKS = 0;

如果您想稍后重新创建它们，则需要读取所有外键约束并将它们存储在备份文件中。

参见this answer寻找更多线索。

你确实应该考虑使用 sqldump ...