Connecting to a remote MySQL database

A Crane Cloud case study.

Photo by Caspar Camille Rubin on Unsplash

Connecting to a MySQL database is pretty straight forward, provided you have the credentials. When you create a database with Crane Cloud, we return a list of credentials needed to connect with your database from anywhere.

In this quick guide, I’ll be going over a few things you can do with your new remote database.

Database credentials from Crane Cloud

Connect.
Since the database is not on your local machine, you’ll need to connect to the machine it is hosted on (Host). The location is a combination of the host and the port (Port) the database service is listening on. The full url includes the particular database you want to connect to (Name). Now that you’ve identified the resource, you only need to authenticate with a username (User) and password (Password).

Open a CLI and type the following command, replacing with your specific credentials.

mysql -u <User> -p -P <Port> -h <Host> -D <Name>

Take note of -p (lowercase) for the password, and -P (uppercase) for the port. You could provide the password within the same command, but it’s advisable you don’t. This is mainly for security reasons, but I’ve also come to learn that some passwords can be misinterpreted for commands (especially auto-generated ones, which can have “weird” characters).

Press Enter and type the password when prompted. You may not be able to see the password as you type (or when you paste) it, but have confidence that the input is being received. Press Enter. You should be able to access the db console when everything checks out.

Successful db connection

Fresh database.
If it’s the first time you’re creating the database, you will want to go ahead and add the tables to structure your data. For simple apps, I find it really time-saving to manually create the tables at the MySQL prompt. However, for most applications, it may be preferred to use an ORM in your code to automatically run and apply migrations of the changes to your models/schema.

Migrating data from an existing database.
If you have a database hosted elsewhere, and are interested in migrating your data to Crane Cloud (because it’s way cooler) — it is very possible.

This process really depends on how active your database/app is. So please do your homework before migrating data. In this article I only go over very simple dumps and imports.

First, you’ll need to dump your data to a file:

mysqldump -u <User> -p -h <Host> -P <Port> -D <Name> > db.sql

Then import that file into your fresh db at Crane Cloud:

mysql -u <User> -p -h <Host> -P <Port> -D <Name> < db.sql

Run these commands at the terminal, not at the mysql prompt. If you’re at the mysql prompt, exit or use a different terminal window. Also take note of the > for export (redirecting output to a file) and the < for import (reading input).

That’s it, you should be good to go.