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:[email protected]:3306/db/balance'

But this doesn't work for some reason.

Answer 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.

Answer 2

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.