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?

2 Answers 2

0

See manual

No data is stored on the local tables.

So if the original datatable has data the federated has too

2
  • And how this is related to my question? I'm asking about mysql.servers table :)
    – RJS
    Commented Jun 26, 2020 at 12:02
  • the table is always empty, you get data when you call it from the remote server, so you can't tell from that if someone has used it, you can always enable logging, so yu know all querys that come your way.
    – nbk
    Commented Jun 26, 2020 at 12:05
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.