CODEWITHSUNDEEP
mysqlsql
DaJunkie
DaJunkie

Reputation: 576

Retrieve client ip address in mysql

I'm trying to get with a simple SQL statement the IP address of the client. I do not want to use PHP or other techniques. Only pure SQL. When I use 

SELECT USER();

I get

[email protected]

When I use 

SELECT CURRENT_USER();

I get

dbouser@%

But how do I get the plain IP? Thanks a lot in advance.

Upvotes: 31

Views: 57723

Answers (5)

Abhiram
Abhiram

Reputation: 1

maybe use this to get IP address from the client and then insert it into the table $ip = $_SERVER['REMOTE_ADDR'];

Upvotes: -1

Eric Beauchemin
Eric Beauchemin

Reputation: 96

@mvf - instead of reverse you could do:

SELECT SUBSTRING_INDEX(USER(), '@', -1) AS ip;

Upvotes: 5

filimonov
filimonov

Reputation: 1734

SELECT REVERSE(SUBSTRING_INDEX(REVERSE(USER()),'@',1)) as ip;
SELECT SUBSTRING(USER(), LOCATE('@', USER())+1) as ip;

Upvotes: 4

Ali
Ali

Reputation: 617

To get the IP address only without the port number.

select SUBSTRING_INDEX(host,':',1) as 'ip' from information_schema.processlist WHERE ID=connection_id();

Upvotes: 10

MarkR
MarkR

Reputation: 63538

You will only get the IP address of the client process communicating with MySQL. Assuming this is what you want:

select host from information_schema.processlist WHERE ID=connection_id();

Will give you the host name (or IP address if name resolution is not enabled, which it is usually not) connecting to the mysql server on the current connection.

Upvotes: 57

Related Questions