Search code examples
mysqlsqlinnodbfederated

Replicate an InnoDB table into a FEDERATED table

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.

Solution

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