将包含数据的表从一个MySQL服务器复制到另一个

I have a MySQL DB on a computer, and the same MySQL DB on a different server. I need them to be exactly the same in term of structure and contained data and I've come to the point where the only way I can do that is by truncating one table and then inserting into it all the rows of the other (exactly the same) table.

我在计算机上有一个MySQL数据库，在不同的服务器上有相同的MySQL数据库。我需要它们在结构和包含数据方面完全相同，我已经到了这样的地步，我能做到的唯一方法是截断一个表然后插入另一个表的所有行（正好是相同）表。

I want this to happen through a MySQL query and not by making backups and then importing it, not by database migrations or such, but by a query, because I plan using this query in a VB project and use it whenever there is a change in any of the two tables.

我想通过MySQL查询而不是通过备份然后导入它来实现这一点，而不是通过数据库迁移等，而是通过查询，因为我计划在VB项目中使用此查询并在每次更改时使用它这两个表中的任何一个。

I know that if the tables where on the same server the query would have been as follows:

我知道如果同一服务器上的表的查询结果如下：

INSERT INTO db.table1 SELECT * FROM db.table2

But I don't know how to write the SELECT clause and how to tell it that .table2 is on another server.

但我不知道如何编写SELECT子句以及如何告诉它.table2在另一台服务器上。

I think it should be something like this

我认为它应该是这样的

INSERT INTO db.table1 SELECT * FROM (ServerName/IP).db.table2

But can't quite figure it out by myself, any ideas?

但不能完全弄清楚自己，任何想法？

1 个解决方案

#1

You can setup federated tables, which is basically linking a table on one server to a table on another. Then use the federation to do your data transfers.

您可以设置联合表，它基本上将一个服务器上的表链接到另一个服务器上的表。然后使用联合进行数据传输。

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the federated database and is defined like this:

首先，您必须使用FEDERATED表在远程服务器上拥有一个表。假设远程表位于联邦数据库中，并且定义如下：

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

Next, create a FEDERATED table on the local server for accessing the remote table:

接下来，在本地服务器上创建一个FEDERATED表以访问远程表：

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

Then you can query it like any other table.

然后你可以像任何其他表一样查询它。

There are however a decent number of limitations you should read about including the remote password being stored in plain text. If this was a temporary setup purely for a once off copy, and the server isn't available to the public you have already minimised most of the risk associated with it though.

但是，您应该阅读有关包括以纯文本格式存储的远程密码的大量限制。如果这是一个纯粹用于一次性复制的临时设置，并且服务器不可用于公众，那么您已经将与之相关的大部分风险降至最低。

#1