If you are working with Trellis LEMP Stack on a Mac to create beautiful and professional WordPress themes on blazing fast and secure servers you are bound to wind up needing to manipulate the database. Many developers are used to PHPMyAdmin. Well, you should not install it and it has not been installed on Trellis. Install Sequel Pro instead on your Mac instead. With Trellis and Sequel Pro you will manipulate databases with ease.
Sequel Pro is a great GUI program that you can use to connect to your local and remote server’s database server. It is very versatile. You can use it to connect to database servers anywhere instead of just locally on one box. And as Trellis is a smart LEMP stack they decided they did not need PHPMyAdmin as they could approach any box using Sequel Pro.
Let’s discuss here how you can make a connection locally.
Trellis and Sequel Pro
As you know Trellis runs on a custom Vagrant box, preferably on VirtualBox. And so you can access the box using ssh vagrant. It will then allow you to access the box with your own ssh key. When you want to connect to the database using SSH tunneling you need to add several details.
Click the ssh tab after you clicked to add another favorite or chose another connection from the menu. Then add the following details. You can leave open the others:
- mysql host: 127.0.0.1
- username: root
- password: password as entered in vault.yml
- ssh host: 127.0.0.1
- ssh user: vagrant
- ssh key: under yoursite.com > .vagrant > machines > default > virtualbox > private_key
- ssh port: 2222
The port is here not 22, but 2222 and you should locate the key clicking the blue key at the path I indicated above. Other details are not to hard to understand though you may pick the local network ip on your box instead of the localhost 127.0.0.1 . And well, that would not work either as you SSH tunnel your way in in this case.
You should now be able to connect. See here screenshot below:
You can do a test or save and connect right away. It will not load the database right away. You will need to choose it from the top left dropdown menu stating choose “Choose Database..” I did test and then saved and then connected. That is the best way really.
And then you will be connected. Trellis and Sequel Pro are working hand it hand as they should be. You can then start adjusting database data, import, export and so on.
Known Hosts Issues
Sometimes your known hosts setup has changed and then you won’t be able to connect. Sequel Pro error message may not be clear in console as you need to scroll to see it. Do scroll. It will tell you if that is the case or not. Make sure to edit the known hosts file and to remove the offending line using nano or another editing tool. For OSX users:
Once opened remove the line and then try again.
NBB For Windows users I refer to MySQL Workbench
Thanks for writing this up! I’m finally connected.
The docs over at roots.io aren’t the most helpful for beginners. This was.
You’re welcome Josh. Happy to hear that the blog post was useful!
Thanks for this article Jasper – I’d been having perpetual issues across multiple trellis instances with the SSH key not being validated – it wasn’t totally clear from the roots.io article that the path at trellis > .vagrant > machines > default > virtualbox > private_key was the correct key to use.
Up and running now!
Glad to hear that Ben!