Using sequel pro and connecting to local and remote databases

Soon, we will be doing migrations, saving data to the database, and other day-to-day workflows, but sometimes, it is nice to look into the database. For example, you may want to export Production and the environment that has your live data down to local to review some bug. This section will show how to use Sequel Pro to do secure, over SSH, connections to your database. This allows you to get to your database with almost zero risk other than SSH.

How to do it...

The following are the steps to connect sequel pro to local and remote databases:

1. Download and install Sequel Pro from http://www.sequelpro.com/.
2. Add a new connection to Homestead:
   
3. Add new connection to a remote Host:
   

How it works...

In the past, I have used phpMyAdmin, and it was better than just the command line. But Sequel Pro really was a game changer. For one, I did not have to install phpMyAdmin on my servers and risk issues related to security. Second, it is a good interface and makes it really easy to check out data, tables, do queries, and so on when needed.

So, what you saw previously was simply a setup for Homestead using the Standard tab and Port 33060, which is what Homestead forwards its MySQL port to.

When we deploy our first server, it will have SSH port 22 open, but never will I have MySQL open only on 127.0.0.1.

Tip

For the most part, you only want three ports open on your server: 22 for SSH, 80 to redirect web requests to SSL/HTTPS, and 443 to serve your website.

So, to connect to this we select the SSH tab, and enter the information for the database on the server, since we will be on the server after the SSH step. Then, we enter the information for SSH; in this case, I had to go into my home folder to use my SSH public key. If you did not set up a key on your server, then most likely, you are using a password, so enter that instead.

Tip

If you do not have the a.ssh folder in your Home directory (~/), then take a moment to create it. From the command line, run ssh-keygen –t rsa and just answer yes to all the questions. Do not add a password. You now have a public key.

It is really this simple. Now, you have this great UI to look into your database once in a while; though after using Laravel with php artisan migrate and eloquent, I am not in the database often.

See also

• SequelPro: http://www.sequelpro.com/.
• Generating Public Keys Mac and Windows: https://help.github.com/articles/generating-an-ssh-key/.