将具有数据的表从一个MySQL服务器复制到另一个 [英] Copy a table with data from one MySQL server to another
问题描述
我在计算机上有一个MySQL DB,在不同的服务器上有相同的MySQL DB。我需要他们在结构和包含的数据完全相同我已经到了这样的点,我可以做的是,通过截断一个表,然后插入到其他所有的行(完全是相同)表。
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查询而不是通过备份然后导入它,而不是通过数据库迁移等,而是通过查询,因为我计划使用这个查询在一个VB项目中,并且每当两个表中的任何一个发生变化时使用它。
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.
我知道如果表在同一个服务器上的查询将如下:
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
不知道如何写 SELECT
子句,以及如何告诉它.table2在另一个服务器上。
But I don't know how to write the SELECT
clause and how to tell it that .table2 is on another server.
我认为应该是这样的
INSERT INTO db.table1 SELECT * FROM (ServerName/IP).db.table2
但是我自己不能想出任何想法?
But can't quite figure it out by myself, any ideas?
推荐答案
您可以设置联合表,它基本上将一个服务器上的表链接到另一个服务器上的表。然后使用联合来进行数据传输。
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.
首先,您必须在远程服务器上有一个表,一个FEDERATED表。假设远程表在联合数据库中,并且定义如下:
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:
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;
接下来,在本地服务器上创建一个FEDERATED表,表:
Next, create a FEDERATED table on the local server for accessing the remote table:
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.
这篇关于将具有数据的表从一个MySQL服务器复制到另一个的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!