mysql复制表 链接服务器,将具有数据的表从一个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.

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

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

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?

解决方案

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:

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:

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.