From: Jeremy Bopp <jeremy@...>
Date: 2010-11-01T22:01:08+09:00
Subject: Re: access remote mysql DB

On 11/01/2010 03:40 AM, Dave Lilley wrote:
> I have been given a job to put a local mysql to a remote server and then
> connect to it.
> 
> the thing is I've not done it before and am hitting an obstacle to make
> this work.
> 
> 1. it isn't a web server so you don't go http://example.com it is simply
> example.com USERNAME PASSWORD (once access is granted you can then
> connect to the mysql as you normally would).
> 
> my issue is how to instigate that initial connection.
> 
> I've tried this ...
> 
> link = Net::HTTP.start('example.com') {|http|
>        req = Net::HTTP::Get.new('example.com')
>        http.req.basic_auth 'uname', 'p/word'
>        http.response = http.request(req)
>        print response.body
>      }
> 
>     @db = Sequel.connect(:adapter => 'mysql', :host => link, :database
> => 'DB_to_use', :user => 'DB_name', :password => 'p/w')
> 
> only to have the index.htm be put to STD OUT.

Using Net::HTTP implies that you're using HTTP.  HTTP is what you use to
access a web site, not a MySQL server.

> also tried ....
> 
> link = Net::HTTP.new('example.com',uname, p/w)
> 
>     @db = Sequel.connect(:adapter => 'mysql', :host => link, :database
> => 'DB_to_use', :user => 'DB_name', :password => 'p/w')
> 
> 
> only to have reference to name or service not known

In this case the link variable is a reference to a Net::HTTP object.
Again, you're trying to use HTTP to connect to this server for some
reason.  I think you just want to set link to 'example.com', a String.

> the example.com is the url through which i gain access to the database
> at the back.
> 
> anyone able to me I'd be grateful.
> 
> note before posting this I have edited the above to this....
> 
>     Net::HTTP.new('example.com') {|http|
>        req = Net::HTTP::Get('example.com')
>        http.req.basic_auth 'uname', 'f0ur2010'
>        http.response = http.request(req)
>        print response.body
>      }

This part is still hitting the HTTP server running at example.com.
Furthermore, this code does not appear to participate in any meaningful
way with what follows.

>     @db = Sequel.connect(:adapter => 'mysql', :host => 'example.com',
> :database => 'DB_to_use', :user => 'DB_name', :password => 'p/w')
> 
>     row = @db[:cust_data]
>       row.each {|t| puts t}

This part looks pretty good to me.  Here you have passed the String
example.com to the Sequel.connect method, so that should work.

> getting this -
> 
> /usr/lib/ruby/1.8/sequel/adapters/mysql.rb:101:in `real_connect':
> Mysql::Error: Access denied for user 'db_name'@'example.com' (using
> password: YES) (Sequel::DatabaseConnectionError)
>         from /usr/lib/ruby/1.8/sequel/adapters/mysql.rb:101:in `connect'

This looks like an authentication problem to me and doesn't appear to be
a problem with Ruby itself.  I'm no expert with MySQL configuration, but
you should check to make sure that you can use the same credentials and
connection settings using the native MySQL client or some other SQL tool
you know to be good.  Make sure to try it out on the same machine from
which you're trying to access the server using your script.  Once you
resolve the authentication issues, I think your Ruby code should work.

-Jeremy