12

In MySQL query:

SELECT host
FROM information_schema.processlist
WHERE ID = connection_id( )
LIMIT 0 , 30

The result of ^ this query is: localhost.

SELECT @@hostname;

The result of ^ this query is: localhost.

But I need to get ipaddress like 192.168.1.2.

Question: How to get this result using mysql query?

BlitZ
  • 12,038
  • 3
  • 49
  • 68
Jesika
  • 143
  • 1
  • 1
  • 7
  • You cannot do that with mysql if your name resolving is working. [Source](http://stackoverflow.com/a/10574565/986959) – user986959 Feb 14 '14 at 11:11
  • 1
    @c vijila Are you succeeded on getting the ip address there yet instead of "localhost"? Would be great if you update us if something works there. – unknown Nov 03 '14 at 08:49
  • See this solution: http://stackoverflow.com/questions/26712867/how-to-get-the-ip-address-mysql/26713089#26713089 – photocurio May 04 '17 at 14:35

5 Answers5

11

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();
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • This will also resolve the name "localhost" if you are running it in local machine. Is there any idea to get the IP? – unknown Nov 03 '14 at 08:47
0

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

Will give you the host name .You will get IP address( like 192.168.1.2.) if name resolution is not enabled, which it is usually not.

Nisham Mahsin
  • 1,399
  • 5
  • 19
  • 43
0

With the help of this post and is great split function you can rewrite it like this :

Select user,CONCAT(Split_fn(host, "-", 2),'.',Split_fn(host, "-", 3),'.',Split_fn(host, "-", 4),'.',Split_fn(Split_fn(host, "-", 5),".",1)) as Ip_Address from information_schema.PROCESSLIST where ID = CONNECTION_ID()
John
  • 974
  • 8
  • 16
  • This always gives me an error. Split_fn is no default MySQL function... In my case, the host is `localhost:52321`, so it cannot be split at any '-' or '.' characters. – Philipp May 08 '21 at 12:36
-1

I found that many MySQL servers use the BIND_ADDRESS global variable. This variable returns the IP address(es) that the server listens to. However, in my tests, this value was usually "127.0.0.1".

SHOW VARIABLES LIKE 'HOSTNAME';     -- Returns: MyHostName
SHOW VARIABLES LIKE 'BIND_ADDRESS'; -- Returns: 127.0.0.1

Alternative Syntax:

SELECT @@hostname, @@bind_address;  -- Returns: MyHostName, 127.0.0.1

Note:

This is not a bulletproof solution. Depending on server configuration, the key BIND_ADDRESS (or @@bind_address) can also return the value *.

Philipp
  • 10,240
  • 8
  • 59
  • 71
-7

Please tell me whether you need to get IP from visitor of the site, or something else. If you just need IP of the visitor, you could use php remote_addr function.

$_SERVER['REMOTE_ADDR'];

Is it something like a table where IP adresses are stored? I think that your query is looking for host, not for IP. But I am not sure if I understood you properly.

user3162968
  • 1,016
  • 1
  • 9
  • 16