将 WordPress 数据库迁移到主机，无需外部 mysql 访问

发布于 2024-12-23 07:50:09 字数 2557 浏览 1 评论 0原文

我正在尝试将大量网站迁移到不提供对 mysql 数据库的外部访问的新主机。

我已经从原始主机导出了一个备份，其中包括以下样式的 mysql 备份：

-- MySQL dump 10.11
--
-- Host: localhost    Database: blueball_wrdp1
-- ------------------------------------------------------
-- Server version       5.0.92-community

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `wp_commentmeta`
--

DROP TABLE IF EXISTS `wp_commentmeta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL auto_increment,
  `comment_id` bigint(20) unsigned NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` longtext,
  PRIMARY KEY  (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

我正在尝试将其导入新主机，该主机创建了一个同名的数据库。我能想到的最简单的方法是使用这样的 php：

<?php

    $con = mysql_connect('account.host.com', 'account_wrdp1', '1234567890');

    if (!$con){
      die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("account_wrdp1", $con);

    if (mysql_query( file_get_contents('account_wrdp1.sql') ,$con)){
      echo "Tables created and populated.";
    } else {
      echo "Error populating database: " . mysql_error();
    }

    mysql_close($con);

?>

但是当我这样做时，我收到错误：

Error creating database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL auto_in' at line 25

NB 起初我在元行上遇到错误，如 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@ CHARACTER_SET_CLIENT */; 所以我用 sed 将它们全部删除，这就是我看到这个新错误的时候。

所以我的问题是，为什么看似良好的 MySQL 转储会产生错误？

有没有一种更简单的方法让我解决整个问题 - mysql 服务器似乎是我的网站托管的一个单独的虚拟服务器，而且我绝对没有 shell 访问权限等...

原文

I am trying to migrate lots of websites to a new host which does not provide external access to the mysql database.

I have exported a backup from the originating host, which includes a mysql backup in this style:

-- MySQL dump 10.11
--
-- Host: localhost    Database: blueball_wrdp1
-- ------------------------------------------------------
-- Server version       5.0.92-community

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `wp_commentmeta`
--

DROP TABLE IF EXISTS `wp_commentmeta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL auto_increment,
  `comment_id` bigint(20) unsigned NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` longtext,
  PRIMARY KEY  (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

I am trying to import it into the new host, which has an identically named database created. The easiest way I can think to do this is with php like this:

<?php

    $con = mysql_connect('account.host.com', 'account_wrdp1', '1234567890');

    if (!$con){
      die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("account_wrdp1", $con);

    if (mysql_query( file_get_contents('account_wrdp1.sql') ,$con)){
      echo "Tables created and populated.";
    } else {
      echo "Error populating database: " . mysql_error();
    }

    mysql_close($con);

?>

But when I do that I am getting error:

Error creating database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL auto_in' at line 25

N.B. At first I was having errors with the meta lines like /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; so I stripped them all out with sed, which is when I am seeing this new error.

So my question, is why is the seemingly good MySQL dump creating an error?

And is there a much easier way for me to approach the whole problem - being that the mysql server seems to be a separate virtual server to the one my website is hosted on, and I have absolutely no shell access etc...

分享到QQ

分享到微博