Mariadb – Remote connection showing up as from localhost on MariaDB 10.2

mariadb

I have a MariaDB 10.2.14 server running on a CentOS server (10.0.0.201) and a jobs server with various Java processes running on another CentOS server (10.0.0.193). MariaDB is configured such that the root user can only connect from localhost – the various job apps have their own users to connect with.

One of the jobs is currently running and takes quite a while – it moves a lot of data using an insert-select statement. If I do a show processlist in the database it tells me the connection has come from localhost and is running as root:

| 58684 | root             | localhost:57486  | db_name              | Query       |  23420 | Sending data ...

A netstat on the db server shows the connection with that client port is actually established from the jobs server:

netstat -n | grep 57486
tcp        0      0 10.0.0.201:3306         10.0.0.193:57486        ESTABLISHED

There is no job running on the DB server (it doesn't even have Java installed). Other connections from the job server show as I'd expect – with the remote IP and the application user name. Is anyone able to explain why this might be happening? Is the MariaDB server somehow proxying the remote request? Why would it be running as root?

Best Answer

I would expect a connection to show as coming from localhost if a remote user was querying a table that has the federated, federatedX, connect or spider storage engines (or possible other similar storage engines), and the table was configured to connect as the root user to the local MariaDB instance rather than a remote one.

You can find the storage engine used for a particular table with:

SHOW TABLE STATUS WHERE Name='your_table_name';

You can also find tables using a particular storage engine with:

SHOW TABLE STATUS WHERE Engine='name_of_engine';

You could also do something like:

SHOW TABLE STATUS WHERE Engine IN ('FEDERATED', 'FEDERATEDX', 'CONNECT', 'SPIDER');

Related Solutions

Mysql – NTFS Filesystem Fragmentation issue occur after migrating thesql 5.6 database to mariadb 10.2

This is a bug, which I filed and fixed, based on this question. BTW, we do have a bug system too, do not hesitate to use it directly.

Here is the bug report. https://jira.mariadb.org/browse/MDEV-13941

Basically, fragments are created because sparse file is being extended. The fix is not to create sparse files, unless user wants a table with page compression (which is an exotic feature that not many people would use). The fix will appear in the next 10.2 (i.e 10.2.10), mid-end October.

However, just the upgrade to 10.2.10 won't automatically fix the problem for existing tables. There is something else you need to do prior to installation of 10.2.10

Stop the server
Unset the "sparse flag" on .idb files. in elevated command prompt, type
```
fsutil sparse setflag C:\path\to\table.ibd 0
```
Defragment file with contig (or other tools)

MariaDB – ERROR 1130 (HY000) Not Allowed to Connect to Server: Granting Permission Issue

The GRANT statement doesn't work because the user@host combination root@'%' doesn't exist (yet). You can create it and then re-run the GRANT:

CREATE USER root@'%' IDENTIFIED BY 'your_secret_password_here'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

The WITH GRANT OPTION part gives the user the power to GRANT privileges to other users.

Note that in order to be able to connect from the other container, you may also have to remove or comment out any restrictions in your configuration (i.e. files like /etc/my.cnf, /etc/my.cnf.d/server.cnf, /etc/mysql/*.cnf) such as any bind-address settings:

bind-address  = 127.0.0.1

Comment out with a '#':

# bind-address  = 127.0.0.1

... and then restart MariaDB.

Best Answer

Related Solutions

Mysql – NTFS Filesystem Fragmentation issue occur after migrating thesql 5.6 database to mariadb 10.2

MariaDB – ERROR 1130 (HY000) Not Allowed to Connect to Server: Granting Permission Issue

Related Question