pre: my question is not how to generate this tables.
I just wonder, if there is a deeper reason that MySql does not offer a statement like this
CREATE TABLE my_fed_db.foobartab clone
ENGINE=FEDERATED
CONNECTION='my_remote[/localname]';
so all fields are the same definition (name, type, collation), the name of the table is the same and also DEFAULT CHARSET is the same.
Did I just not find the syntax, is it something with security? Or is it just not implemented?
I just scripted this, but I wonder if I am the only one that could use that, or - are there hidden problems with a full clone of definition, I just will run into later.
It's just not implemented.
I was told by a former engineering manager for MySQL that FEDERATED was developed specifically for one use case - to support high INSERT rates, like for an application that is logging high-traffic activity at a rate that a single MySQL instance cannot sustain.
The implementation was done in MySQL 5.0, around 2005. But it was labeled a "proof of concept" implementation (i.e. experimental), and it has not been developed much since then.
Basically it was created for a specific customer of MySQL, a telecom company as I was told.
I have never found a good use for the FEDERATED storage engine in its current implementation, that couldn't be handled better by sharding (coding your application to connect directly to multiple database server instances) or using a specialized proxy solution like ProxySQL.
Other solutions favored these days for high-rate INSERT workloads are the Elastic Stack, or time-series databases like InfluxDB, or data streaming platforms like Apache Kafka.