Show Database Use database show tables Describe <table> All good and well, but is it possible to show the current connections host. Not connection_id, but the IP Address or Name of the host.
Add a comment |
5 Answers
To get current host name :-
select @@hostname; show variables like "%host%"; To get hosts for all incoming requests :-
select host from information_schema.processlist; Based on your last comment,
I don't think you can resolve IP for the hostname using pure mysql function,
as it require a network lookup, which could be taking long time.
However, mysql document mention this :-
resolveip google.com.sg docs :- http://dev.mysql.com/doc/refman/5.0/en/resolveip.html
6 Comments
Craig Stewart
I don't have a remote host to test on ATM, but "SELECT @@hostname" gives my local host. If the MySQL session is connected to a different host will it show the remote hosts name? Can I get IP?
ajreal
Yes, it will show different host if you come from other host. However, to resolve the corresponding IP is bit hard ...
Craig Stewart
Is it possible to get IP from My-SQL? That would be great. Up one vote for @@Hostname, thank you!! Will accept if not possible on IP
Craig Stewart
Using command line, get the current connected remote host IP within SQL. Reason, longevity of a script I can't be 100% sure within my code that we're connected to the same host!!
ajreal
Malaysian, but Singapore PR.
|
Maybe
mysql> show processlist; 
1 Comment
Vijay Kumar Kanta
This is a bad suggestion if you're trying to access remotely.
I think you try to get the remote host of the conneting user...
You can get a String like 'myuser@localhost' from the command:
SELECT USER() You can split this result on the '@' sign, to get the parts:
-- delivers the "remote_host" e.g. "localhost" SELECT SUBSTRING_INDEX(USER(), '@', -1) -- delivers the user-name e.g. "myuser" SELECT SUBSTRING_INDEX(USER(), '@', 1) if you are conneting via ip address you will get the ipadress instead of the hostname.
Comments
show variables where Variable_name='hostname'; That could help you !!
3 Comments
Vitor Canova
Interesting mine return nothing with this.
Hartmut Holzgraefe
This returns the server hostname, not the client hostname for the connection, as requested ...
gouthV_
@HartmutHolzgraefe ... in the question, it was asked for the Host name only, not the client..
Well, I did a boot at MySQL and all start working as expected... app server connecting to MySQL, all in different servers, Ubuntu Linux.
$ mysql -u sysprod -p -h dbprod --protocol=TCP Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.30-0ubuntu0.22.04.1 (Ubuntu) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> select user(); +----------------------------+ | user() | +----------------------------+ | sysprod@dbprod | +----------------------------+ 1 row in set (0.00 sec)