Get MySQL server IP address in SQL

--bcaec5014c511adb0a049d88507f
Content-Type: text/plain; charset=ISO-8859-1

Anyone knows how to get the server* IP address* thru SQL?

--
Claudio


*mysql> select * from GLOBAL_variables where variable_name like 'hostname';*
*+---------------+----------------+*
*| VARIABLE_NAME | VARIABLE_VALUE |*
*+---------------+----------------+*
*| HOSTNAME | haarlemeer |*
*+---------------+----------------+*
*1 row in set (0.01 sec**)*

*mysql> select * from GLOBAL_variables where variable_name like '%ip%';*
*+------------------------+----------------+*
*| VARIABLE_NAME | VARIABLE_VALUE |*
*+------------------------+----------------+*
*| SSL_CIPHER | |*
*| SLAVE_SKIP_ERRORS | OFF |*
*| SKIP_SHOW_DATABASE | OFF |*
*| SKIP_NAME_RESOLVE | OFF |*
*| NAMED_PIPE | OFF |*
*| SKIP_NETWORKING | OFF |*
*| SQL_SLAVE_SKIP_COUNTER | |*
*| SKIP_EXTERNAL_LOCKING | ON |*
*+------------------------+----------------+*
*8 rows in set (0.00 sec)*

--bcaec5014c511adb0a049d88507f--

Re: Get MySQL server IP address in SQL

--------------enig7D88C977009576D1B3B83848
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable


Am 03.03.2011 00:31, schrieb Claudio Nanni:
> Anyone knows how to get the server* IP address* thru SQL?

no, because it is nonsense and has nothing to do with a db-server

if you connect via tcp you know the ip

and even if not - what should give you mysqld
if the host has more than one ip?


--------------enig7D88C977009576D1B3B83848
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk1u2eAACgkQhmBjz394AnmeCwCfbeTugpfTCFD53T2ZPek9 y/Pr
OLAAn1FwFfOUdDfrULeu1gftPtjCBnRc
=BiKf
-----END PGP SIGNATURE-----

--------------enig7D88C977009576D1B3B83848--

Re: Get MySQL server IP address in SQL

On 3/2/2011 5:59 PM, Reindl Harald wrote:
> Am 03.03.2011 00:31, schrieb Claudio Nanni:
>> Anyone knows how to get the server* IP address* thru SQL?
> no, because it is nonsense and has nothing to do with a db-server
>
> if you connect via tcp you know the ip
>

Isn't that kind of like going to someones home, knocking on their door,
and asking, "Where do you live?"

Chris W

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Get MySQL server IP address in SQL

On Thu, 2011-03-03 at 13:43 -0600, Chris W wrote:
>
> On 3/2/2011 5:59 PM, Reindl Harald wrote:
> > Am 03.03.2011 00:31, schrieb Claudio Nanni:
> >> Anyone knows how to get the server* IP address* thru SQL?
> > no, because it is nonsense and has nothing to do with a db-server
> >
> > if you connect via tcp you know the ip
> >
>
> Isn't that kind of like going to someones home, knocking on their door,
> and asking, "Where do you live?"
>
> Chris W
>

What if this is a "load balanced" cluster? Doesn't that setup query
"serverA", and in turn, "serverA" finds the least busiest server in the
"cluster", which could be "serverY", therefore you would have no idea
which server the query was run on?

But, as far as I can tell, you could only get the "server_id" (which
would have to be unique anyway in the cluster), so you could just add
this to the query:
SELECT @@global.server_id

Then you can figure out elsewhere what 'server_id' corresponds to what
server ip address.

Just trying to think of other solutions on why the OP would want this
data...

Steve




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Get MySQL server IP address in SQL

--20cf3054a01baef81b049d9a9a0f
Content-Type: text/plain; charset=ISO-8859-1

Hi Steve,

Thanks for your tip.

It's not a cluster,
I am building a tool to monitor mysql performances and this information(*IP*)
is required in a stored procedure on the mysql-agents installations.
Being it possible to have multiple mysql instances on the same server the
only unique identifier would be the couple [IP-PORT],
the port you can get it easily, the IP I did not find it.
I dont trust the [hostname] since is slightly more subject to be changed,
while the ip on eth0(:0) is a bit more unlikely to be changed.
I would like this information when then you collect data in the same vault.
Also the IP has some risky values:
*IP*: 127.0.0.1, 192.x.x.x, 10.x.x.x
While the hostname risky/not unique values would be:
*HOSTNAME*: any non official DNS name

While IP/HOSTNAME should be unique on any two systems in the "world", the
PORT is used to distinguish instances on the same host.

Thanks

Claudio


2011/3/3 Steve Staples

> On Thu, 2011-03-03 at 13:43 -0600, Chris W wrote:
> >
> > On 3/2/2011 5:59 PM, Reindl Harald wrote:
> > > Am 03.03.2011 00:31, schrieb Claudio Nanni:
> > >> Anyone knows how to get the server* IP address* thru SQL?
> > > no, because it is nonsense and has nothing to do with a db-server
> > >
> > > if you connect via tcp you know the ip
> > >
> >
> > Isn't that kind of like going to someones home, knocking on their door,
> > and asking, "Where do you live?"
> >
> > Chris W
> >
>
> What if this is a "load balanced" cluster? Doesn't that setup query
> "serverA", and in turn, "serverA" finds the least busiest server in the
> "cluster", which could be "serverY", therefore you would have no idea
> which server the query was run on?
>
> But, as far as I can tell, you could only get the "server_id" (which
> would have to be unique anyway in the cluster), so you could just add
> this to the query:
> SELECT @@global.server_id
>
> Then you can figure out elsewhere what 'server_id' corresponds to what
> server ip address.
>
> Just trying to think of other solutions on why the OP would want this
> data...
>
> Steve
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>
>


--
Claudio

--20cf3054a01baef81b049d9a9a0f--

RE: Get MySQL server IP address in SQL

Do you have to worry about named pipes?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Claudio Nanni [mailto:claudio.nanni@gmail.com]
>Sent: Thursday, March 03, 2011 4:21 PM
>To: sstaples@mnsi.net
>Cc: mysql@lists.mysql.com
>Subject: Re: Get MySQL server IP address in SQL
>
>Hi Steve,
>
>Thanks for your tip.
>
>It's not a cluster,
>I am building a tool to monitor mysql performances and this information(*IP*)
>is required in a stored procedure on the mysql-agents installations.
>Being it possible to have multiple mysql instances on the same server the
>only unique identifier would be the couple [IP-PORT],
>the port you can get it easily, the IP I did not find it.
>I dont trust the [hostname] since is slightly more subject to be changed,
>while the ip on eth0(:0) is a bit more unlikely to be changed.
>I would like this information when then you collect data in the same vault.
>Also the IP has some risky values:
>*IP*: 127.0.0.1, 192.x.x.x, 10.x.x.x
>While the hostname risky/not unique values would be:
>*HOSTNAME*: any non official DNS name
>
>While IP/HOSTNAME should be unique on any two systems in the "world", the
>PORT is used to distinguish instances on the same host.
>
>Thanks
>
>Claudio
>
>
>2011/3/3 Steve Staples
>
>> On Thu, 2011-03-03 at 13:43 -0600, Chris W wrote:
>> >
>> > On 3/2/2011 5:59 PM, Reindl Harald wrote:
>> > > Am 03.03.2011 00:31, schrieb Claudio Nanni:
>> > >> Anyone knows how to get the server* IP address* thru SQL?
>> > > no, because it is nonsense and has nothing to do with a db-server
>> > >
>> > > if you connect via tcp you know the ip
>> > >
>> >
>> > Isn't that kind of like going to someones home, knocking on their door,
>> > and asking, "Where do you live?"
>> >
>> > Chris W
>> >
>>
>> What if this is a "load balanced" cluster? Doesn't that setup query
>> "serverA", and in turn, "serverA" finds the least busiest server in the
>> "cluster", which could be "serverY", therefore you would have no idea
>> which server the query was run on?
>>
>> But, as far as I can tell, you could only get the "server_id" (which
>> would have to be unique anyway in the cluster), so you could just add
>> this to the query:
>> SELECT @@global.server_id
>>
>> Then you can figure out elsewhere what 'server_id' corresponds to what
>> server ip address.
>>
>> Just trying to think of other solutions on why the OP would want this
>> data...
>>
>> Steve
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>>
>>
>
>
>--
>Claudio




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Get MySQL server IP address in SQL

--bcaec51dd77d611157049d9b6cfd
Content-Type: text/plain; charset=ISO-8859-1

Hi Jerry,

let's say that support for agents on windows I leave it for later versions.
Seriously, apart from the certification books I never encountered one
windows installation based on named pipes in 9 years.
I would consider quite safe non supporting windows named pipes at this
stage.

thanks for your contribution,

Claudio

2011/3/3 Jerry Schwartz

> Do you have to worry about named pipes?
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
> E-mail: jerry@gii.co.jp
> Web site: www.the-infoshop.com
>
>
> >-----Original Message-----
> >From: Claudio Nanni [mailto:claudio.nanni@gmail.com]
> >Sent: Thursday, March 03, 2011 4:21 PM
> >To: sstaples@mnsi.net
> >Cc: mysql@lists.mysql.com
> >Subject: Re: Get MySQL server IP address in SQL
> >
> >Hi Steve,
> >
> >Thanks for your tip.
> >
> >It's not a cluster,
> >I am building a tool to monitor mysql performances and this
> information(*IP*)
> >is required in a stored procedure on the mysql-agents installations.
> >Being it possible to have multiple mysql instances on the same server the
> >only unique identifier would be the couple [IP-PORT],
> >the port you can get it easily, the IP I did not find it.
> >I dont trust the [hostname] since is slightly more subject to be changed,
> >while the ip on eth0(:0) is a bit more unlikely to be changed.
> >I would like this information when then you collect data in the same
> vault.
> >Also the IP has some risky values:
> >*IP*: 127.0.0.1, 192.x.x.x, 10.x.x.x
> >While the hostname risky/not unique values would be:
> >*HOSTNAME*: any non official DNS name
> >
> >While IP/HOSTNAME should be unique on any two systems in the "world", the
> >PORT is used to distinguish instances on the same host.
> >
> >Thanks
> >
> >Claudio
> >
> >
> >2011/3/3 Steve Staples
> >
> >> On Thu, 2011-03-03 at 13:43 -0600, Chris W wrote:
> >> >
> >> > On 3/2/2011 5:59 PM, Reindl Harald wrote:
> >> > > Am 03.03.2011 00:31, schrieb Claudio Nanni:
> >> > >> Anyone knows how to get the server* IP address* thru SQL?
> >> > > no, because it is nonsense and has nothing to do with a db-server
> >> > >
> >> > > if you connect via tcp you know the ip
> >> > >
> >> >
> >> > Isn't that kind of like going to someones home, knocking on their
> door,
> >> > and asking, "Where do you live?"
> >> >
> >> > Chris W
> >> >
> >>
> >> What if this is a "load balanced" cluster? Doesn't that setup query
> >> "serverA", and in turn, "serverA" finds the least busiest server in the
> >> "cluster", which could be "serverY", therefore you would have no idea
> >> which server the query was run on?
> >>
> >> But, as far as I can tell, you could only get the "server_id" (which
> >> would have to be unique anyway in the cluster), so you could just add
> >> this to the query:
> >> SELECT @@global.server_id
> >>
> >> Then you can figure out elsewhere what 'server_id' corresponds to what
> >> server ip address.
> >>
> >> Just trying to think of other solutions on why the OP would want this
> >> data...
> >>
> >> Steve
> >>
> >>
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> >> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
> >>
> >>
> >
> >
> >--
> >Claudio
>
>
>
>


--
Claudio

--bcaec51dd77d611157049d9b6cfd--