I am trying to include just one table from another MariaDB server (lets call it A) into the database on another (even physical) server (lets call it B). And I came up with the conclusion that SPIDER engine might be a good option. But I can't make it work. Here is what I did so far:
on server A
- I installed the SPIDER engine even though I don't think it is necessary
- Port 3306 is visible from outside
- MariaDB is configured to allow connections from everywhere
- I've created a user
test_userwith host set to% I've created the following table in the
testdatabase:CREATE TABLE sss ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ) ENGINE=InnoDB;
on server B
- I installed the SPIDER engine
I've created the following
SERVER:CREATE SERVER dataNode1 FOREIGN DATA WRAPPER mysql OPTIONS ( HOST 'IP-server-A', DATABASE 'test', USER 'test_user', PASSWORD 'passwd', PORT 3306);
Then I created the table as follows:
CREATE TABLE sss ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ) ENGINE=Spider COMMENT='wrapper "mysql", srv "dataNode1", table "sss"';
I manage to connect with test_user from server B to server A:
mysql -h IP-server-A -u test_user -ppasswd
But when I log with root to MariaDB on server B and I try to access the table sss, I get:
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
What have I missed? Thank you.
