Sequel pro + ssh to vagrant

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

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:

2 Likes

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):

8 Likes

That’s cool! Thanks!

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?

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

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

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)]>
1 Like

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.

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

Hey,

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

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)

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

1 Like

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

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)

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

4 Likes

Why was this disabled? I use Trellis to run multiple WP sites on one host and find using the root account to manage dumps and other maintenance tasks much easier than managing multiple user accounts. This is especially true during development. I can understand disabling root access on a production server but would like the option to override that behaviour.

Could I do this by editing the MariaDB role?

Ok, sorry for the above comment. I read through everything and found out that this is default MariaDB behaviour and nothing to do with Trellis.

As others have mentioned in other posts this is due to the root login using the auth plugin unix_socket. You can restore the old root password login behaviour following this post:

I don’t know Ansible well enough to setup to run a script during provisioning but I imagine that it’s possible.

What I ended up doing to get my backup scripts running again quickly was to just add another mysql user with full privileges. I just added this:

- name: Add admin user
  mysql_user:
    name: admin
    host: "{{ item }}"
    password: "{{ mysql_root_password }}"
    check_implicit_admin: yes
    priv: '*.*:ALL,GRANT'
    state: present
  with_items:
    - 127.0.0.1
    - ::1
    - localhost

to “roles/mariadb/tasks/main.yml”.

Probably not the best idea from a security perspective I but I don’t see it as any worse than before when mysql root password logins were allowed. And since I only allowed local access to the db.

1 Like

For anyone else unable to get the above suggestions working, this article by the Roots team may help: Simplifying Trellis - blog - Roots Discourse

The gist:

Another example is db_name and db_user. We always documented and recommended the use of the site name for these values. So if your site is named roots.io then it makes sense to have a database called roots_io_production and a user called roots_io (MySQL doesn’t like dots).

Trying to connect to my local server via Sequel Pro. Followed the doc’s and got this:

Used command:  /usr/bin/ssh -v -N -S none -o ControlMaster=no -o ExitOnForwardFailure=yes -o ConnectTimeout=10 -o NumberOfPasswordPrompts=3 -o TCPKeepAlive=no -o ServerAliveInterval=60 -o ServerAliveCountMax=1 vagrant@syracusesedationandfamilydentistry.test -L 62303:127.0.0.1:3306

OpenSSH_7.6p1, LibreSSL 2.6.2
debug1: Reading configuration data /Users/josephroberts/.ssh/config
debug1: /Users/josephroberts/.ssh/config line 2: Applying options for *
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 48: Applying options for *
debug1: Control socket " none" does not exist
debug1: Connecting to syracusesedationandfamilydentistry.test [192.168.50.5] port 22.
debug1: fd 6 clearing O_NONBLOCK
debug1: Connection established.
debug1: identity file /Users/josephroberts/.ssh/id_rsa type 0
debug1: key_load_public: No such file or directory
debug1: identity file /Users/josephroberts/.ssh/id_rsa-cert type -1
debug1: Local version string SSH-2.0-OpenSSH_7.6
debug1: Remote protocol version 2.0, remote software version OpenSSH_7.2p2
debug1: match: OpenSSH_7.2p2 pat OpenSSH* compat 0x04000000
debug1: Authenticating to syracusesedationandfamilydentistry.test:22 as 'vagrant'
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: algorithm: curve25519-sha256@libssh.org
debug1: kex: host key algorithm: ssh-ed25519
debug1: kex: server->client cipher: chacha20-poly1305@openssh.com MAC: <implicit> compression: none
debug1: kex: client->server cipher: chacha20-poly1305@openssh.com MAC: <implicit> compression: none
debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
debug1: Server host key: ssh-ed25519 SHA256:YD4Fbjv6igUaXf2u7OfZtgYndkdf31qK0IejP3tyuGk
debug1: read_passphrase: can't open /dev/tty: Device not configured
debug1: permanently_drop_suid: 501
Host key verification failed.

Thoughts?