CODEWITHSUNDEEP
mysqlsqldatabase
Craig Stewart
Craig Stewart

Reputation: 1501

Show MySQL host via SQL Command

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.

Upvotes: 114

Views: 284027

Answers (5)

ajreal
ajreal

Reputation: 47321

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

Upvotes: 234

Daniel Carvalho
Daniel Carvalho

Reputation: 131

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)

Upvotes: 0

gouthV_
gouthV_

Reputation: 395

show variables where Variable_name='hostname';

That could help you !!

Upvotes: 8

Radon8472
Radon8472

Reputation: 4971

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.

Upvotes: 11

Adrian Cornish
Adrian Cornish

Reputation: 23886

Maybe

mysql> show processlist;

Upvotes: 19

Related Questions