Replicate an InnoDB table into a FEDERATED table

Question

I would like to have a new table with FEDERATED engine, created (cloned) from another existing InnoDB table. Something like this:

CREATE TABLE balance_live
  AS SELECT * FROM balance
  ENGINE=FEDERATED
  CONNECTION='mysql://user:pass@8.8.8.8:3306/db/balance'

But this doesn't work for some reason.

score 1 · Accepted Answer · answered Sep 18 '20 at 23:29

1

You need an actual table definition, specified explicitly. It's not a view, it's a table. See here for the correct syntax: https://dev.mysql.com/doc/refman/8.0/en/federated-create-connection.html

Do SHOW CREATE TABLE balance; on the remote host and use that as the definition, just change ENGINE=FEDERATED and add the CONNECTION clause.

answered Sep 18 '20 at 23:29

Gordan Bobić

1,748
13
16

1

`SHOW CREATE TABLE balance;` was exactly what I needed! Thank you! – Reza Hajianpour Sep 18 '20 at 23:40

score 0 · Answer 2 · answered Sep 18 '20 at 23:29

As mysql manual on creating federated tables says:

Create the table on the local server with an identical table definition, but adding the connection information that links the local table to the remote table.

You cannot use the as select ... , since in the from clause you can only specify a local table. It is the federated table that creates the link to the remote table.

Just copy the table definition over from the remote table.

Replicate an InnoDB table into a FEDERATED table

2 Answers2