CODEWITHSUNDEEP
mysqlsqlinnodbfederated
Reza Hajianpour
Reza Hajianpour

Reputation: 851

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.

Upvotes: 0

Views: 578

Answers (2)

Shadow
Shadow

Reputation: 34254

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.

Upvotes: 0

Gordan Bobić
Gordan Bobić

Reputation: 1888

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.

Upvotes: 1

Related Questions