Import Remote Database Directly over SSH

Developing, maintaining and debugging web-applications often involves copying a remote database to a local or another remote machine. This post lists a number of methods that I find useful. It acts as a reference for myself which I happily update and improve based on comments and experience. My favourite method:  stream the database dump directly to the target machine.

I assume a MySQL or MariaDB database on a Unix or Mac OS machine but by adjusting the appropriate commands most of these methods apply to other databases as well.

Continue reading Import Remote Database Directly over SSH

Configuring MySQL with .my.cnf file

Connecting to a MySQL server often involves providing hostnames, usernames and passwords. Use a .my.cnf configuration file to provide defaults that simplify working with a MySQL server. 

Providing a default password reduces security. Take effort to make sure that the password cannot be read by other users on the system. If the server runs locally, use credentials that are only allowed to connect locally.

Continue reading Configuring MySQL with .my.cnf file

Using Key-based SSH Authentication

If you use Git or remote terminal session a lot, consider using key-based authentication. Key-based authentication is generally considered more secure than password-based authentication. 

In key-based authentication, two key-files are used. One is the public key and may be distributed to other parties that should be able to authenticate you and your information. The other is the private encryption key and should be kept secure.

Continue reading Using Key-based SSH Authentication

Restore, Clone or Backup your Homebrew Setup

Homebrew is a popular package manager for MacOS. It provides easy access to thousands of programs and applications. It is developed and maintained by an open-source community on Github. Use Homebrew bundle to backup and restore your Homebrew configuration. 

If you haven’t installed it yet, go take a look quick on brew.sh or just install it by running the following command in the MacOS terminal.

The installation should succeed without problems. Installing some nice tools is also easy:

To install a better alternative to the standard process viewer top.

Continue reading Restore, Clone or Backup your Homebrew Setup

Running JIRA Software securely behind Apache with ISPConfig 3.1

If you use both JIRA and ISPConfig on a server you might want to set-up a reverse proxy to serve the JIRA frontend securely on the standard HTTPS port just like other websites in your ISPConfig setup. ISPConfig uses Apache to serve websites whereas JIRA has its own web-server (Tomcat). It is not straight-forward to share a single port such as the HTTPS port 443 between two applications. The Apache Reverse-Proxy feature allows us to do so however.

Secure JIRA using Apache Reverse-Proxy
Secure JIRA using Apache Reverse-Proxy

Advantages of using a reverse-proxy instead of serving JIRA on a different port are as follows:

  • Single entry-point for all web traffic
  • Access- and certificate management in one place (Apache via ISPConfig)
  • Only one or two (HTTP/HTTPS) ports need to be opened to the public
  • No need to specify the port when navigating to the JIRA instance
  • Allows traffic and statistics monitoring through Apache

Continue reading Running JIRA Software securely behind Apache with ISPConfig 3.1

Free SSL certificates: use Let’s Encrypt with ISPConfig 3

View of the Chrome Certificate info pane for tomlankhorst.nlAs you might have noticed this site uses HTTPS. Obtaining SSL certificates was always a bit of a hassle. Finding a certificate authority, doing regular payment, renewals and installing the certificate on your server. Let’s Encrypt is an initiative to provide a better way of enabling encryption on websites. It is open, automated and above all: it offers free SSL certificates. Learn to use Let’s Encrypt on an ISPConfig 3.0 server.

I assume you already have an ISPConfig server up and running. You might have a number of sites that use plain HTTP of HTTPS that you want to secure with free SSL certificates. That’s good, we’re going to do the following:

  1. Obtaining certbot
  2. Requesting free SSL certificates
  3. Configuring SSL in ISPConfig
  4. Enabling automatic periodical renewal

Continue reading Free SSL certificates: use Let’s Encrypt with ISPConfig 3