从备份还原数据库时出现MySQL语法错误

时间：2013-04-16 作者：Orun

对Wordpress安装执行了错误的导入，并决定从几分钟前创建的mqSQL备份（完全导出）还原。

删除了数据库中的所有表，然后从中进行了导入。sql文件。

我在PHPmyAdmin中遇到以下错误，发现44个表中只有22个被导入，因此我假设导入已中止。

我的MySQL db备份是否已损坏？导出/下载似乎进展顺利。我的错误是#1064，您的SQL语法有错误，请检查手册。。。（见图）

字符集似乎是UTF-8，我正在UTF-8中导入

-- Host: localhost
-- Generation Time: Apr 15, 2013 at 07:01 PM
-- Server version: 5.5.23
-- PHP Version: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
sql import error

2 个回复
最合适的回答,由SO网友:Orun 整理而成

在检查我的SQL文件时发现了这一点。它以SQL开始，接近尾声时改为PHP。由于某种原因，下载被搞砸了，SQL导出开始输出我所在的PHPmyAdmin页面的PHP！（甚至不仅仅是客户端HTML，它还吐出了服务器端PHP）。

无论如何，我的备份已损坏。我不得不从另一个数据库备份中恢复，现在我需要拼凑过去几天的内容。

以下是我的SQL文件底部的代码，仅供参考（前面几行是常规SQL输出，19 MB的文本内容）。

...
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <link rel="icon" href="./favicon.ico" type="image/x-icon" />
    <link rel="shortcut icon" href="./favicon.ico" type="image/x-icon" />
    <title>phpMyAdmin</title>
    <link rel="stylesheet" type="text/css" href="phpmyadmin.css.php?server=1&amp;token=6a3930a6cce9b6bf3d9810d8f65e3f1c&amp;js_frame=right&amp;nocache=5412529155" />
    <link rel="stylesheet" type="text/css" href="print.css" media="print" />
    <link rel="stylesheet" type="text/css" href="./themes/pmahomme/jquery/jquery-ui-1.8.custom.css" />
    <meta name="robots" content="noindex,nofollow" />
<script src="./js/cross_framing_protection.js?ts=1344778698" type="text/javascript"></script>
<script src="./js/jquery/jquery-1.4.4.js?ts=1344778698" type="text/javascript"></script>
<script src="./js/update-location.js?ts=1344778698" type="text/javascript"></script>
<script src="./js/config.js?ts=1344778698" type="text/javascript"></script>
<script src="./js/functions.js?ts=1344778698" type="text/javascript"></script>
<script src="./js/jquery/jquery.qtip-1.0.0.min.js?ts=1344778698" type="text/javascript"></script>
<script src="./js/messages.php?lang=en&amp;db=advdly_adaily&amp;token=6a3930a6cce9b6bf3d9810d8f65e3f1c" type="text/javascript"></script>
<script type="text/javascript">
// <![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow this)
if (typeof(parent.document) != \'undefined\' && typeof(parent.document) != \'unknown\'
    && typeof(parent.document.title) == \'string\') {
    parent.document.title = \'www.advocatedaily.com / localhost / advdly_adaily / wp_rg_form | phpMyAdmin 3.4.11.1\';
}

// ]]>
</script>
        <meta name="OBGZip" content="true" />
                <!--[if IE 6]>
        <style type="text/css">
        /* <![CDATA[ */
        html {
            overflow-y: scroll;
        }
        /* ]]> */
        </style>
        <![endif]-->
    </head>

    <body>
            <div id="prefs_autoload" class="notice" style="display:none">
        <form action="prefs_manage.php" method="post">
            <input type="hidden" name="token" value="6a3930a6cce9b6bf3d9810d8f65e3f1c" />
            <input type="hidden" name="json" value="" />
            <input type="hidden" name="submit_import" value="1" />
            <input type="hidden" name="return_url" value="export.php?" />
            Your browser has phpMyAdmin configuration for this domain. Would you like to import it for current session?            <br />
            <a href="#yes">Yes</a> / <a href="#no">No</a>
        </form>
    </div>
    <div id="serverinfo">
<a href="main.php?token=6a3930a6cce9b6bf3d9810d8f65e3f1c" class="item">        <img class="icon" src="./themes/pmahomme/img/s_host.png" width="16" height="16" alt="" /> 
localhost</a>
        <span class="separator"><img class="icon" src="./themes/pmahomme/img/item_ltr.png" width="5" height="9" alt="-" /></span>
<a href="db_structure.php?db=advdly_adaily&amp;token=6a3930a6cce9b6bf3d9810d8f65e3f1c" class="item">        <img class="icon" src="./themes/pmahomme/img/s_db.png" width="16" height="16" alt="" /> 
advdly_adaily</a>
        <span class="separator"><img class="icon" src="./themes/pmahomme/img/item_ltr.png" width="5" height="9" alt="-" /></span>
<a href="sql.php?db=advdly_adaily&amp;table=wp_rg_form&amp;token=6a3930a6cce9b6bf3d9810d8f65e3f1c" class="item">        <img class="icon" src="./themes/pmahomme/img/s_tbl.png" width="16" height="16" alt="" /> 
wp_rg_form</a>
</div>
<!-- PMA-SQL-ERROR -->
    <div class="error"><h1>Error</h1>
    <p><strong>SQL query:</strong>
<a href="tbl_sql.php?sql_query=SHOW+TABLE+STATUS+FROM+%60advdly_adaily%60+LIKE+%27wp_rg_form%27&amp;show_query=1&amp;db=advdly_adaily&amp;table=wp_rg_form&amp;token=6a3930a6cce9b6bf3d9810d8f65e3f1c"><span class="nowrap"><img src="./themes/pmahomme/img/b_edit.png" title="Edit" alt="Edit" class="icon" width="16" height="16" /> Edit</span></a>    </p>
    <p>
        <span class="syntax"><span class="inner_sql"><a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fshow.html&amp;token=6a3930a6cce9b6bf3d9810d8f65e3f1c" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">SHOW</span></a>  <span class="syntax_alpha syntax_alpha_reservedWord">TABLE</span>  <span class="syntax_alpha syntax_alpha_reservedWord">STATUS</span>  <span class="syntax_alpha syntax_alpha_reservedWord">FROM</span>  <span class="syntax_quote syntax_quote_backtick">`advdly_adaily`</span>  <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span>  <span class="syntax_quote syntax_quote_single">\'wp_rg_form\'</span></span></span>
    </p>
<p>
    <strong>MySQL said: </strong><a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Ferror-messages-server.html&amp;token=6a3930a6cce9b6bf3d9810d8f65e3f1c" target="mysql_doc"><img class="icon" src="./themes/pmahomme/img/b_help.png" width="11" height="11" alt="Documentation" title="Documentation" /></a>
</p>
<code>
#2006 - MySQL server has gone away
</code><br />
</div><script type="text/javascript">
//<![CDATA[
$(document).ready(function(){
// updates current settings
if (window.parent.setAll) {
    window.parent.setAll(\'en\', \'utf8_general_ci\', \'1\', \'advdly_adaily\', \'wp_rg_form\', \'6a3930a6cce9b6bf3d9810d8f65e3f1c\');
}
    // set current db, table and sql query in the querywindow
if (window.parent.reload_querywindow) {
    window.parent.reload_querywindow(
        \'advdly_adaily\',
        \'wp_rg_form\',
        \'\');
}

if (window.parent.frame_content) {
    // reset content frame name, as querywindow needs to set a unique name
    // before submitting form data, and navigation frame needs the original name
    if (typeof(window.parent.frame_content.name) != \'undefined\'
     && window.parent.frame_content.name != \'frame_content\') {
        window.parent.frame_content.name = \'frame_content\';
    }
    if (typeof(window.parent.frame_content.id) != \'undefined\'
     && window.parent.frame_content.id != \'frame_content\') {
        window.parent.frame_content.id = \'frame_content\';
    }
    //window.parent.frame_content.setAttribute(\'name\', \'frame_content\');
    //window.parent.frame_content.setAttribute(\'id\', \'frame_content\');
}
});

//]]>
</script>
</body>
</html>

SO网友:ckpepper02

是<!DOCTYPE... 在sql语句的开头？试图将html作为sql命令来读取时，它被阻塞了。

我会尝试制作文件的副本作为备份，然后从一开始就删除HTML代码。SQL备份文件通常以注释代码开始，然后是表创建语句，如：

DROP TABLE If EXISTS... CREATE TABLE blahblah ...

结束
标签： database   mysql   import   export   小码农  

相关推荐

如何查看我的WordPress.org站点MySQL查询生成的项目？

我的WordPress站点生成hughes MySQL查询。因此，我得到了“建立到数据库的连接时出错”。我使用共享主机。当我在我的主机（cpanel）上检查错误日志时，它显示一些插件产生了巨大的错误。当我问那个插件开发人员时，他提到这应该是你的主题问题，因为那个插件不会生成MySQL查询。那么，如何检查所有生成项目（主题、插件等）的博客MySQL查询？如果这些插件由于我的主题问题而产生错误，我可以在不更改主题的情况下修复它们吗？我不熟悉编码：（。谢谢