How do I dump only results of one table in a database in MySQL and replicate to another remote empty table with the same structure?

Question

It's a long sentence, anyone knows?

Answer 1

Some thing like this:

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename
WHERE 1=0

I'm not sure this works with My SQL but you get the idea.

This DOES NOT duplicate PK, FK, Indexes, Stored Procedures, or anything else. Just the columns and data types.

W3Schools

D'oh

I must have misunderstood your question. It is almost 3am way past bedtime.

Ok so there is probably a better way to do this, but this would get the job done.

SELECT  CONCAT('INSERT INTO (COL1, COL2) VALUES (',COL1,COL2,');');

You will need to add ' for some data types so.

SELECT  CONCAT('INSERT INTO (COL1, COL2) VALUES (',COL1,''' ''',COL2,');');

The take the output and run on the remote db.

Answer 2

To create the dump:

mysqldump --user [username] --password=[password] --no-create-info [database name] [table name] > /tmp/dump.sql

To restore:

mysql --u [username] --password=[password] [database name] [table name] < /tmp/dump.sql