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_user with host set to %
- I've created the following table in the test database:
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.
Asked by Martin Dimitrov
(169 rep)
Jul 20, 2019, 06:29 AM