Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Copy a table with data from one MySQL server to another

Tags:

copy

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?

like image 310
user3218392 Avatar asked May 23 '14 06:05

user3218392


People also ask

How do I copy a table from one server to another in MySQL?

If we want to copy tables or databases from one MySQL server to another, then use the mysqldump with database name and table name. Run the following command at the source host. This will dump the complete database into dump. txt file.

How do I copy a table from one server to another?

Enter the data source, server name and select the authentication method and the source database. Click on Next. Now, enter the destination, server name, authentication method and destination database then click on Next. Select 'Copy data from one or more tables or views' option in the next window and click on Next.

How do I copy a table data from one database to another database?

Using SQL Server Management StudioOpen the table with columns you want to copy and the one you want to copy into by right-clicking the tables, and then clicking Design. Click the tab for the table with the columns you want to copy and select those columns. From the Edit menu, click Copy.


1 Answers

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.

like image 159
Alex.Ritna Avatar answered Sep 20 '22 05:09

Alex.Ritna