Sequel pro + ssh to vagrant

[stejmurphy]

Can anyone provide insight into why this is no longer working? Following the above layout

Fresh install of Trellis/Bedrock on 10.11.6

Edit: Fixed. My issue was the SSH host. You need the IP address of the VM.

ssh into the vm.

run ifconfig.

copy IP to SSH Host.

test

Derp.

[Simeon]

Bump.

If you’re having trouble with this, it may be as simple as updating Sequel Pro. I was having connection issues that were solved simply by downloading the latest version.

[kalumbox]

It’s been months since I’ve been trying to connect to my DB with sequel pro and your instructions worked right away, thanks a lot buretta!

[Jack_Vial]

It’s worthing noting that if you have a VM started and then start a new another VM then the SSH port will be different so your saved Sequel Pro settings will not work. Check the vagrant ssh settings using “vagrant ssh-config” from your project root and make sure the port number matches your saved connection settings.

[patrice]

I use the hostname instead of the ip adress for the SSH Host, as your hostname is declared in your /etc/host file.

[gabrielgiordan]

Got to working on Windows 10 and MySQL Workbench.

Use your vault_mysql_root_password located in your \group_vars\development\vault.yml for the MySQL Password

Here are the other configurations:

[ben]

Thanks for all of the replies and info in this thread. I went ahead and added a doc on the site that covers how to connect to your databases (both local and remote):

Database Access | Trellis Documentation | Roots

[gabrielgiordan]

That’s cool! Thanks!

[jasperfrumau]

Thanks for the documentation. Wrote about accessing the database in Trellis locally with Sequel Pro here myself a while ago. Shouldn’t the documentation also mention port 2222 for local access?

[ben]

It’s not necessary when you use the hostname of the box directly (example.dev) instead of 127.0.0.1.

[jasperfrumau]

Did not know that. Thanks for telling me @ben .

[gabrielgiordan]

Hey guys, please, if I set sshd_permit_root_login: false how can I connect to the remote and local database? I’m trying here but no success whatsoever.

I’ve figure out how to connect into the mysql using the ssh admin user and the vault_users password located into the group_vars/production/vault.yml

But for this I needed to use the sudo command… any way to configure this into the MySQL Workbench or Sequel Pro?

admin@vps0695:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
admin@vps0695:~$ sudo mysql -u root -p
[sudo] password for admin:
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 85
Server version: 10.0.27-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

[cclass]

did you figure this out?

with sshd_permit_root_login set to false, i can ssh into the remote server with the admin user, but cannot connect to mysql – Access denied for user 'admin'@'localhost'.

if i allow root login, i can ssh as root and connect to mysql just fine.

however in Sequel Pro, i am unable to connect in either case (using username root and password value of vault_mysql_root_password in group_vars/production/vault.yml. i get the same error for either admin or root as username (ssh connects fine, access denied for mysql).

Site is up an running on the remote server, no issues when deploying or provisioning to remote server.

[gabrielgiordan]

Hey @cclass

Unfortunately I did not figure this out yet, for now I reenabled sshd_permit_root_login and I’m working with this. Soon I will try again, maybe SSH into it and creating a single user only for connecting to MariaDB or changing user DB permissions

[craigpearson]

Hey,

@gabrielgiordan and @cclass, did you try my solution mentioned here: Cannot connect to MySQL

[cclass]

replied on that thread as well, but i’m not having any issues locally (lmk if i read your solution wrong which sounded to me only relevant to the local env)

[gabrielgiordan]

Hey @cclass

Here is an alternative to connect to DB that I got to working on the production server:

The MySQL password is located on production/vault.yml db_password

[ericgauvin]

I’m trying to setup sequel pro for local vagrant connection. I’ve followed these configurations very carefully and tried many other things. Looks like the ssh connection is working but then the MySQL connection is denied.

Here’s the message I get:

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Access denied for user ‘root’@‘localhost’

Maybe something to do with this plugin?

https://discourse.roots.io/t/cannot-connect-to-mysql/8318/3?u=craigpearson

[ericgauvin]

I got this to work with these settings. It this legit? Maybe you want to add it into the documentation. Seems to work. (basically connecting to mysql as a user and not as root)

Cannot connect to MySQL

Yes. This worked for me too. SETUP SEQUEL PRO LOCAL ---------------------- (from documentation) Connection type: SSH MySQL host: 127.0.0.1 Username: root Password: devpw (use the value of vault_mysql_root_password from group_vars/development/vault.yml) SSH Host: example.dev SSH User: vagrant SSH Key: Select the following file from your Trellis directory: .vagrant/machines/default/virtualbox/private_key (alternate) Connection type: SSH MySQL host: 127.0.0.1 Username: example_dev (use the valu…

[ben]

root will no longer work, I’ve updated the docs to reflect this

Database Access | Trellis Documentation | Roots