Replicate a single table

Question

Is it possible to replicate a single table?

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. — Dan Head, Jun 03 '10 at 01:42

score 16 · Accepted Answer · edited Sep 11 '20 at 18:32

16

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.

edited Sep 11 '20 at 18:32

Community

1
1

answered Jun 03 '10 at 01:36

halfdan

33,545
8
78
87

score 1 · Answer 2 · answered Jan 15 '14 at 16:28

1

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.

answered Jan 15 '14 at 16:28

Rainer Stumbaum

11
1

5

Some references to the docs would make your answer more useful! – Raad Jan 15 '14 at 16:47

score 0 · Answer 3 · edited Mar 02 '23 at 12:23

0

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.

edited Mar 02 '23 at 12:23

JustSightseeing

1,460
3
17
37

answered Feb 24 '23 at 13:33

Pradhuman Baid

1

score -1 · Answer 4 · edited Nov 03 '15 at 00:58

-1

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

edited Nov 03 '15 at 00:58

Klerisson

312
1
2
17

answered Nov 26 '12 at 16:18

Scarecrow

205
1
7

11

Note: this has nothing to do with replication, it's just "copying" – Hut8 Jul 17 '13 at 20:42
3

@LaceCard What is the difference? – Scarecrow Jul 17 '13 at 23:10
6

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. http://en.wikipedia.org/wiki/Replication_(computing) – Hut8 Jul 18 '13 at 02:23
6

@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 – Scarecrow Jul 20 '13 at 22:02
Create with like creates all the same indexes – ysth Jun 06 '17 at 02:26
This is not replication pls. If you are considering Mysql Replication, check out https://dev.mysql.com/doc/refman/5.7/en/replication.html – Asuquo12 Dec 29 '17 at 07:06

Replicate a single table

4 Answers4

Linked