0

I have no expirence with FEDERATED tables in MySQL. I have database with FEDERATED tables defined and I'm wonder if this tables are used in current env (I have no access to application/developers to ask/check).

The mysql.servers table (https://mariadb.com/kb/en/mysqlservers-table/) is empty. I wonder if connection info stored there is ephemeral or persitent after first connection to remote server/s.

My question is: if mysql.servers table is empty can I "safely" assume that this tables are not used by apps?

RJS
  • 101

2 Answers2

0

See manual

No data is stored on the local tables.

So if the original datatable has data the federated has too

nbk
  • 8,699
  • 6
  • 14
  • 27
0

As I had more time after work (and for proper rtfm:).

Simple test:

docker network create my

start two server instances (one with federated engine enabled)


docker run --name my1 --network my -e MYSQL_ROOT_PASSWORD=root --rm -d mysql:5.6
docker run --name my2 --network my -e MYSQL_ROOT_PASSWORD=root --rm -d mysql:5.6 --federated


wait for servers


MY="docker run --network my -e MYSQL_PWD=root -it --rm mysql:5.6 mysql" 
$MY -h my1 -e "show engines"
$MY -h my2 -e "show engines"


Create databases


$MY -h my1 -e "create database my1"
$MY -h my2 -e "create database my2"


Create sample table on my1 and put some data there


$MY -h my1 -e "create table my1.src (data varchar(10))"
$MY -h my1 -e "insert into my1.src values ('line 1')"
$MY -h my1 -e "insert into my1.src values ('line 2')"


Create federated table on my2 db


$MY -h my2 -e "create table my2.data (data varchar(10)) engine=federated connection='mysql://root:root@my1/my1/src'"


query my2.data table


$MY -h my2 -e "select * from my2.data"


check mysql.servers table (should be empty)


$MY -h my2 -e "select * from mysql.servers"


so mysql.servers is only populated with "create server" stament


$MY -h my2 -e "create server my1 foreign data wrapper mysql options (user 'root', host 'my1', port 3306, database 'my1');"
$MY -h my2 -e "select * from mysql.servers"


cleanup


docker stop my1
docker stop my2
docker network rm my

Thank you @nbk for your answer.

RJS
  • 101