Replicate a single table

Question

Is it possible to replicate a single table?

Row

It is possible as stated below, however it is extremely fragile. Any data modification query on the replicated table that references any non-replicated tables table will likely break replication. – Gladdie

Answer 1

Yes this is possible. Have a look at the slave options of the MySQL manual. This still requires to create a complete binlog of the whole database though.

Absentminded

Answer 2

To sync specific tables again to one or more slaves rather use pt-table-checksum and then pt-table-sync

That should automatically identify the out-of-sync tables and only sync those.

Octavo

Some references to the docs would make your answer more useful! – Vulcanism

Answer 3

CREATE TABLE new_table_name
SELECT *
FROM original_table_name;

Use "*" if you want to select all columns from the original table, otherwise give specific columns name.

This will replicate table within same database.

Fourposter

Answer 4

I know this is an old question but this is for anyone who comes here looking for an answer:

CREATE TABLE table2 LIKE table1;

This will create a table with the same format and columns but no data. To transfer the data use:

INSERT INTO table2 SELECT * FROM table1;

EDIT:

It is important to note that this is an information transfer only. Meaning if you had indexes on table1 they are not transferred to table2. You will have to manually index table2

Eunuchize

Note: this has nothing to do with replication, it's just "copying" – Broz

@LaceCard What is the difference? – Eunuchize

There are many kinds of replication. In general, you have multiple servers, and when you write to one, the writes are automatically copied to others. It's used to increase scalability and availability. Your queries, while correct, will simply copy all the rows in one table to a different table with the same columns one time. en.wikipedia.org/wiki/Replication_(computing) – Broz

@LaceCard I see what you mean. I interpreted the question as just copying a table seeing as how replicate is synonymous with copying in English. I did not realize that it is an industry term. Thank you – Eunuchize

Create with like creates all the same indexes – Irreconcilable

This is not replication pls. If you are considering Mysql Replication, check out dev.mysql.com/doc/refman/5.7/en/replication.html – Becalm