Hi Jeremy,
Thanks for the response. I went down the path of trying to use SSH port
forwarding. I had attempted to do that yesterday, but after doing some more
reading, I determined that I hadn't been setting up the parameters
correctly. However, I still couldn't get it to work. Instead of receiving
an error back from any attempt to access the database, it just hung... not
sure if that was a step forward or a step backward! :-)
I was finally able to get it to work by modifying the MySQL config file to
allow non-local access and restarting the MySQL server. I was hesitant to
do this, since the database was backing a live prototype, but it worked out
in the end.
I changed bind-address in the MySQL config file from:
bind-address = 127.0.0.1
To:
bind-address = 0.0.0.0
I then created two new MySQL users, '<new-user>'@'localhost' and
'<new-user>'@'%', gave the same password to both users, and granted both
users read-only (i.e. SELECT) permission. After that, I was able to
establish a remote connection from my local machine using the mysql client:
mysql --host=<aws_ec2_public_ip> --user=<new-user> --password <database>
I then used those same parameters for Sequel.connect():
OLD_DB = Sequel.connect(:adapter => 'mysql',
:host => '<aws_ec2_public_ip>',
:database => '<database>',
:user => '<new-user>',
:password => '<password>',
:max_connections => 16)
OLD_DB.fetch('show tables') {|r| puts "#{r}" }
For now, I'll adjust firewall permissions to make sure that MySQL doesn't
accept remote connections from just anywhere, as bind-address=0.0.0.0
dictates.
Thanks,
Barbara
On Wednesday, August 3, 2016 at 6:33:17 PM UTC-4, Jeremy Evans wrote:
>
> On Wednesday, August 3, 2016 at 2:48:41 PM UTC-7, Barbara Carradini wrote:
>>
>> I'm attempting to use Sequel.connect to connect to a remote MySQL
>> database hosted on an AWS EC2 instance. I need to migrate data from this
>> database to a local postgres database.
>>
>> Here's my code snippet:
>> ```
>> puts "Connecting to OLD database"
>> OLD_DB = Sequel.connect(:adapter => 'mysql',
>> :host =>
>> '<aws_ec2_public_ip>',
>> :database =>
>> '<database>',
>> :user => '<user>',
>> :password => '<password>',
>> :max_connections => 16)
>> puts "OLD_DB #{OLD_DB}"
>>
>> OLD_DB[:users].order(:id).each do |user|
>> puts "#{user}"
>> end
>> ```
>>
>> Here's I see on the command line:
>> ```
>> OLD_DB #<Sequel::MySQL::Database:0x007f9554927840>
>> rake aborted!
>> Sequel::DatabaseConnectionError: Mysql::Error: Can't connect to MySQL
>> server on '52.38.29.235' (61)
>> /Users/barbara/Dev/donorsee-v1/donorsee-api/tasks/db.rake:50:in `block (2
>> levels) in <top (required)>'
>> Mysql::Error: Can't connect to MySQL server on '52.38.29.235' (61)
>> /Users/barbara/Dev/donorsee-v1/donorsee-api/tasks/db.rake:50:in `block (2
>> levels) in <top (required)>'
>> ```
>>
>> I have confirmed that my local host is exchanging relevant packets with
>> the AWS EC2 instance (so it's not a firewall issue). The error message
>> leads me to believe that the MySQL server has not been configured to accept
>> connections from outside hosts. However, I've read conflicting
>> recommendations about how to resolve this issue and the options used to
>> start the MySQL server have thwarted my attempts to try out recommendations
>> that pertain to altering user hosts/permissions. I can alter the MySQL
>> config and restart the server if necessary. Perhaps I need to alter the
>> bind-address option?
>>
>> I have attempted to open an SSH tunnel to the AWS instance using
>> net-ssh-gateway and, even though I have opened the tunnel, I still can't
>> connect to the OLD_DB. Same error message.
>>
>> Has anyone encountered this situation?
>>
>
> You should probably try to get a connection working using the mysql
> command line tool. After you have that working, then try connecting via
> Sequel. Most likely, this is a MySQL issue and not a Sequel issue.
>
> If you have set bind-address and it doesn't include the IP address you are
> trying to connect to, that certainly sounds like a likely cause of the
> problem. However, if you have SSH access to the virtual machine, as long
> as the MySQL server is listening on localhost, you should be able to use
> SSH port forwarding to get access to it remotely.
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
