如何将一个服务器上的表中的少量记录复制到PHP和MySQL中另一台服务器上具有相同结构的表中？

I've two same MySQL databases present at two different servers. Initially both the databases have the exactly same structure and contained the same data.

我在两个不同的服务器上有两个相同的MySQL数据库。最初,两个数据库都具有完全相同的结构并包含相同的数据。

But later on few new records(new users) got added to both the tables. This created the difference in the data present in these tables.

但后来很少有新记录(新用户)被添加到两个表中。这创造了这些表中存在的数据的差异。

Now the issue is somehow the old data(which is present since when the structure and data of both the databases were same) from one table got tampered by some unauthorized means and in one field contains blank values.

现在的问题是,某个表中的旧数据(存在于两个数据库的结构和数据相同时存在)被某些未经授权的方式篡改,并且在一个字段中包含空值。

Now I want that data back as of previous(like initial). For this I've to fire a query which would take the missing data from the same table from one server and add it to the same table on another server where the issue of data tampering occurred.

现在我想要恢复之前的数据(如初始)。为此,我将触发一个查询,该查询将从一个服务器获取同一个表中的缺失数据,并将其添加到发生数据篡改问题的另一个服务器上的同一个表中。

If there is some way by writing some script in PHP which could do this job that will also be fine for me.

如果有一些方法可以通过在PHP中编写一些可以完成这项工作的脚本来完成这项工作。

Can some one please suggest me how should I achieve this?

有人可以建议我怎么做到这一点?

If you want any more details regarding the issue I'm facing please do let me know.

如果您想了解我正面临的问题的更多细节,请告诉我。

Thanks.

2 个解决方案

#1

Why not export the data from the other server?

为什么不从其他服务器导出数据?

Then import the table into the current server (with different name or make new database)

然后将表导入当前服务器(使用不同的名称或创建新数据库)

UPDATE

No it will not replace the table. You can create another database and import it into the new database and not the existing.

不,它不会取代桌子。您可以创建另一个数据库并将其导入新数据库而不是现有数据库。

Or you edit the export file and changed the name then import it.

或者您编辑导出文件并更改名称然后导入它。

Example:

If this is your export file:

如果这是您的导出文件:

CREATE TABLE IF NOT EXISTS `Customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

Change the name (notice I change customer to customer2)

更改名称(通知我将客户更改为customer2)

THEN do the import.

那么进口。

CREATE TABLE IF NOT EXISTS `Customer2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

UPDATE 2

Now you have current table with a blank column, and a "backup" table2.

现在您有一个包含空白列的当前表和一个“备份”表2。

Both tables should have a unique id or some sort.

两个表都应该具有唯一的ID或某种类型。

This is the basic scenario:

这是基本场景:

Loop through all the records in table1 get the unique id column value.

循环遍历table1中的所有记录,获取唯一的id列值。

Use that id to look up the value of the blank column in table2.

使用该id查找table2中空白列的值。

Update the blank column in table1 from the value in table2.

从table2中的值更新table1中的空白列。

SELECT `id` FROM `table1` WHERE 1

SELECT `blankcolumn` FROM `table2` WHERE `id` = $row['id']

UPDATE `table1` SET `blankcolumn` = `table2`.`blankcolumn`

#2

I would create a RESTful API, allowing one server to GET, PUT, DELETE stuff to the other server using JSON.

我将创建一个RESTful API,允许一台服务器使用JSON将其他东西GET,PUT,DELETE。

So one server has a URL that you can submit some JSON (or XML if you really want) data to it.

因此,一个服务器有一个URL,您可以向其提交一些JSON(或XML,如果您真的需要)数据。

Then the other server performs a CURL request and sends the data over.

然后另一台服务器执行CURL请求并发送数据。

You probably want to wait until you have enough data to send at once so you are not constantly making requests between the servers.

您可能希望等到有足够的数据一次发送,这样您就不会经常在服务器之间发出请求。

You can password protect the system also so nobody else that figures this out can edit your database :P

你也可以密码保护系统,所以没有其他人可以编辑你的数据库:P

There is a lot to go into, but I think if you look up how to create a REST API in PHP, and also how to make CURL requests, you will find it is quite straightforward. You can also communicate to such a system using JavaScript AJAX calls which opens some more possibilities.

还有很多内容,但我认为如果您查看如何在PHP中创建REST API,以及如何生成CURL请求,您会发现它非常简单。您还可以使用JavaScript AJAX调用与这样的系统进行通信,从而打开更多可能性。

Some database types and database service providers (they run the db for you) can scale databases to multiple instances to do this sort of thing. That might be worth looking into as well. There is usually a delay before the databases match each other - this explains a lot of weirdness on Social Media and Auction/Classifieds websites where a post might not show up for you for a little while.

一些数据库类型和数据库服务提供程序(它们为您运行数据库)可以将数据库扩展到多个实例来执行此类操作。这也许值得研究。在数据库相互匹配之前通常会有一段延迟 - 这解释了社交媒体和拍卖/分类广告网站上的一些奇怪现象,其中一些帖子可能暂时不会显示给您。

If the data has to do with something really really important like financial transactions, where having some money go missing for a while is unacceptable, you may need to get much more serious about this.

如果数据与金融交易这样真正非常重要的事情有关,那么在一段时间内遗失一些钱是不可接受的,你可能需要对此更加认真。