To access MariaDB on Trellis LEMP using Sequel Pro from you local box is easy once you know how. Like with most things in life really. But the main thing is that you need to know the proper way to access the database once you have set up SSH access properly with Sequel Pro (see this article on more on SSH access via Sequel Pro).
No root Access to Database
The issues is that MariaDB with standard setup does not allow root access unless you are root on the system and then you can login without a password as the standard MariaDB setup uses a plugin to check whether you are root and then automatically grants you access. And we normally log in as a non-root. So now what?
Database User & Password
If you are used to accessing your VPS with the root user for the database you will fail getting access. So instead of adding root and password you should add the database user for the database in question and the password there. That way you can avoid the need root ssh-ing into the box which you normally do not want nor should really want to.
Figuring out the Username
To figure out the username you normally just need the name or your project and whether it is development, production or staging. Normally it is example_com based on your chosen example.com project name.
Or – to be really certain – you ssh into your box, change to root, mysql -u root into the database server and then check which user is for the database.
So you would do
ssh admin@domain.com sudo su mysql -u root use mysql;
NB vagrant ssh for local access but we are accessing production in this example
Check Existing Users in Database
And then you would check for all users to figure the user for the database:
MariaDB [mysql]> select user,plugin FROM user; +---------------------+-------------+ | user | plugin | +---------------------+-------------+ | root | unix_socket | | root | | | root | | | root | | | sub_domain_com | | +---------------------+-------------+ 5 rows in set (0.01 sec)
As you can see the root has unix_socket access and the only non root user is sub_domain_com (name changed) . That is the user you should use together with the password you added in your vault.yml. So normally is is domain_com as the user.
Database Access Granted
Once you change the database user and add the correct password you can acces the database and make a backup for example. Or do other manipulations like you would normally do in the database with Sequel Pro.
