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=arch...@jab.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=arch...@jab.org
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 sstap...@mnsi.net 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.na...@gmail.com -- Claudio
best practice: mysql_multi, VMs w/single instance per or doesn't matter?
I've always had a single physical server that is the qc mysql database for all our applications but it's now up to 85 schemas so I want to break it up along the same lines as production (where there's redundant pools of mysql servers by application class). my basic question is whether it's better to run multiple instances on the same host or run single instances on multiple VMs on the same physical server. I can see slight advantages/disadvantages to each but no obvious upside nor downside to either. remember, this is dev/qc, not prod, so I'm leaning toward VMs so I don't have to manage port #s in configs or expect developers to remember that (also, I don't have to modify scripts for multiple instances, paths, etc). not big reasons for sure but all else equal I'll go the less work route and the only upside to multi I see is not having to reload the box as VM host. any compelling argument for either approach?
Re: best practice: mysql_multi, VMs w/single instance per or doesn't matter?
i would use virtual machines because port/socket/configuration after running our whole infrastructure on vmware i can not understand how i could live without machine-snapshots and auto-failover :-) on hardware with virtualization support performance is also not a problem and ESXi is free without support on hardware matching the HCL Am 03.03.2011 22:52, schrieb Sid Lane: I've always had a single physical server that is the qc mysql database for all our applications but it's now up to 85 schemas so I want to break it up along the same lines as production (where there's redundant pools of mysql servers by application class). my basic question is whether it's better to run multiple instances on the same host or run single instances on multiple VMs on the same physical server. I can see slight advantages/disadvantages to each but no obvious upside nor downside to either. remember, this is dev/qc, not prod, so I'm leaning toward VMs so I don't have to manage port #s in configs or expect developers to remember that (also, I don't have to modify scripts for multiple instances, paths, etc). not big reasons for sure but all else equal I'll go the less work route and the only upside to multi I see is not having to reload the box as VM host. any compelling argument for either approach? signature.asc Description: OpenPGP digital signature
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: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 03, 2011 4:21 PM To: sstap...@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 sstap...@mnsi.net 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.na...@gmail.com -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best practice: mysql_multi, VMs w/single instance per or doesn't matter?
Just know that there is not-a-problem in running multiple instances on the same host, then all you have to do is to evaluate the performance factor. In your case I would not introduce the overhead of the VMs, but take advantage of this to learn how to manage multiple instances on the same host that is always useful. You can have a look at Giuseppe Maxia's MySQL Sandboxhttp://mysqlsandbox.net/ Or if you wish I can share my technique I use since 3.23. Cheers Claudio 2011/3/3 Reindl Harald h.rei...@thelounge.net i would use virtual machines because port/socket/configuration after running our whole infrastructure on vmware i can not understand how i could live without machine-snapshots and auto-failover :-) on hardware with virtualization support performance is also not a problem and ESXi is free without support on hardware matching the HCL Am 03.03.2011 22:52, schrieb Sid Lane: I've always had a single physical server that is the qc mysql database for all our applications but it's now up to 85 schemas so I want to break it up along the same lines as production (where there's redundant pools of mysql servers by application class). my basic question is whether it's better to run multiple instances on the same host or run single instances on multiple VMs on the same physical server. I can see slight advantages/disadvantages to each but no obvious upside nor downside to either. remember, this is dev/qc, not prod, so I'm leaning toward VMs so I don't have to manage port #s in configs or expect developers to remember that (also, I don't have to modify scripts for multiple instances, paths, etc). not big reasons for sure but all else equal I'll go the less work route and the only upside to multi I see is not having to reload the box as VM host. any compelling argument for either approach? -- Claudio
Re: Get MySQL server IP address in SQL
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 je...@gii.co.jp 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: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 03, 2011 4:21 PM To: sstap...@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 sstap...@mnsi.net 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.na...@gmail.com -- Claudio -- Claudio
RE: best practice: mysql_multi, VMs w/single instance per or doesn't matter?
There is almost no VM overhead these days. mySQL is disk I/O bound, not CPU bound. With VMWare you can setup your partitions to be raw disks (not virtual disk files) so you get native I/O. If you were to get some SSD's, I bet you would even see some significant performance increase too even over a true native system. Also consider sharding your tables to put some on raw/ssd/vmdk depending on how they're used. VMWare has options that are nearly bare-metal. There are other free options like KVM that are built right into the kernel. I personally use VirtualBox here at work for development, but I use VMWare Workstation at home. At previous jobs, we used VMWare Server (free) for all the UAT from the test servers themselves to the test guest OS (XP, Win7, OSX, Linux, browser variants, etc.) Virtual Machines are the ONLY way to go these days IMHO. It's silly to try and setup mySQL on different ports and go through all that hassle and configuration. With VM's you can just clone one to setup a new instance, you have fail-over, backups, they're easy to move to new hardware, they have console GUIs, intelligent shuffling of resources, maximizing hardware, minimizing costs (electric, carbon, space, etc). There are so many benefits and almost no detriments to a VM these days with computers as powerful as they are. Even updating the VMs (patching) is fairly straight forward with the major Linux distros (many even have web GUI front ends to push patches to all VMs, not to mention automated unattended updates if you desire) Just do it. DO IT! You won't ever look back, and like Reindl said, you'll wonder how you got this far without VMs. :-) -Daevid. There are only 11 types of people in this world. Those that think binary jokes are funny, those that don't, and those that don't know binary. -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 03, 2011 2:14 PM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: best practice: mysql_multi, VMs w/single instance per or doesn't matter? Just know that there is not-a-problem in running multiple instances on the same host, then all you have to do is to evaluate the performance factor. In your case I would not introduce the overhead of the VMs, but take advantage of this to learn how to manage multiple instances on the same host that is always useful. You can have a look at Giuseppe Maxia's MySQL Sandboxhttp://mysqlsandbox.net/ Or if you wish I can share my technique I use since 3.23. Cheers Claudio 2011/3/3 Reindl Harald h.rei...@thelounge.net i would use virtual machines because port/socket/configuration after running our whole infrastructure on vmware i can not understand how i could live without machine-snapshots and auto-failover :-) on hardware with virtualization support performance is also not a problem and ESXi is free without support on hardware matching the HCL Am 03.03.2011 22:52, schrieb Sid Lane: I've always had a single physical server that is the qc mysql database for all our applications but it's now up to 85 schemas so I want to break it up along the same lines as production (where there's redundant pools of mysql servers by application class). my basic question is whether it's better to run multiple instances on the same host or run single instances on multiple VMs on the same physical server. I can see slight advantages/disadvantages to each but no obvious upside nor downside to either. remember, this is dev/qc, not prod, so I'm leaning toward VMs so I don't have to manage port #s in configs or expect developers to remember that (also, I don't have to modify scripts for multiple instances, paths, etc). not big reasons for sure but all else equal I'll go the less work route and the only upside to multi I see is not having to reload the box as VM host. any compelling argument for either approach? -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL DBA certification exam
Hi, Folk: I have two questions about the DBA certification exam: 1.how many questions ? 2: how long is the exam? 3.what's the pass score? I could not find the answers on mysql web anybody knows? Thanks a lot
Isolation level per transaction?
HI, Folks: Can Isolation levels be set per transaction? I know isolation levels can be set per session or globally, but not sure at transaction level. if so , can anybody give me an example? Thanks a lot
