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