I’m unable to connect to MySQL using the value of vault_mysql_root_password in group_vars/production/vault.yml
I am able to deploy and provision the server without any errors.
Trying to connect to the remote database using Sequel Pro and following the instructions here: https://roots.io/trellis/docs/database-access/, I get the error Access denied for user 'root'@'localhost'.
sshd_permit_root_login is set to false.
SSHing into the server as the admin_user (admin), I get the same error when either trying to connect with the admin user or as root while supplying the password BUT I can connect from the admin_user if I use sudo (sudo mysql).
I reprovisioned the server with sshd_permit_root_login set to true, and am able to connect to mysql by SSHing into the server as root but still cannot connect using Sequel Pro.
I’ve tried using several mysql username/pw combinations (such as the admin_user and vault_userspassword) without any luck.
I have even wiped the remote server, changed the password for vault_mysql_root_password and reprovisioned.
I’m sure I’m missing something small but I have gone over the docs several times and have used trellis (an older version) before without this issue
It appears there is an issue with the pluginunix_socket and the vagrant user not being allowed to login as root because unix_socket requires sudo. That explains why vagrant access for the WP user still functions. But connections to the mysql root user from the vagrant user, i.e. when accessing your local DB via a GUI, is not allowed
I’m still understanding what’s exactly happening I’m no expert with mysql / mariadb.
A temporary fix is to do the following when SSH’d into your local vagrant:
// Login as sudo enter password from group_vars/development/vault.yml -> vault_mysql_root_password
sudo mysql -u root -p
// Self explanitory
use mysql;
// Remove the unix_socket plugin for the root user
UPDATE user SET plugin = '' WHERE user = 'root' ;
// Update the root password swapping vault_mysql_root_password for your password
UPDATE user SET password = PASSWORD ( 'vault_mysql_root_password' ) WHERE user = 'root';
// Flush privileges
flush privileges;
Can anyone else confirm that with the latest HEAD version of Trellis this is the case for them too? A quick check would be to, grab the latest Trellis:
// SSH into vagrant
vagrant ssh
// Test mysql access by entering root password when prompted i.e.
mysql -u root -p
The returned error no matter if the password is correct or not is: ERROR 1698 (28000): Access denied for user 'root'@'localhost' (using password: YES)
This can also be carried out on your live server too. When mysql is installed on Ubuntu 15+ it prefers the unix_socket plugin for mysql root login (i.e. you can only log in as root for mysql with a sudo account). This problem only came to light on my local environment as our database is held with RDS so is outside of the Trellis playbooks.
Removing the plugin for the mysql user will allow you to login as root on mysql under any system user, whether that be vagrant on local or web on production.
I’m still looking into this to see if it’s a bug or expected behaviour and will raise a PR.
In the meantime there’s also this script which can be ran on the target instance. This does the legwork for you: http://c.zxq.io/savemariadb.sh
Seems to be more of a security thing than anything else.
With the latest version of Trellis as @craigpearson had said above vagrant ssh then mysql -u root -p fails, but sudo mysql -u root -p works fine, you just hit enter when prompted for password because the sudo password for vagrant user is blank.
Alternatively if you mysql -u example_com -p and enter your single site users password it works fine. I think logging in remotely or using a GUI as the user of the database instead of using root would probably work fine as well judging by this quick test. It is probably a whole lot more secure in general to be storing / saving / using the single database user password and login then doing it as root anyways.
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 value of DB_USER from site/.env)
Password: devpw (use the value of env: db_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
SETUP SEQUEL PRO REMOTE
-----------------------
(from documentation)
Connection type: SSH
MySQL host: 127.0.0.1
Username: root
Password: productionpw (use the value of vault_mysql_root_password from group_vars/<environment>/vault.yml)
SSH Host: example.com
SSH User: web
(alternate)
Connection type: SSH
MySQL host: 127.0.0.1
Username: example_dev (use the value of DB_USER from site/.env)
Password: productionpw (use the value of env: db_password from group_vars/<environment>/vault.yml)
SSH Host: example.com
SSH User: web