Connect to a Remote MySQL Database in Rails

Using a remote database with rails is useful for collaborating on projects or for keeping all your data in one place. Here's how to get it set up from scratch.

Whether it's to share a development database among team members, or to keep a production database on a server separate from the application, or for any other reason, it can be useful to run a rails app by connecting to a remote database.

The Remote Database

First, we need to set up the remote database. I'm going to assume that you have a remote server with MySQL installed and access to the root MySQL user. I'm also going to assume that you are cool with setting up MySQL first and creating the user and database within the mysql program.

We're talking specifically about MySQL here, but you could apply a similar strategy in rails using PostgreSQL or another type of database.

Gathering Information

You're going to need the following:

  • MySQL username and password for a user with super privileges
  • Public IP address of your server
  • Port MySQL is using
  • Path to the MySQL socket file on your server

If using a dedicated Ubuntu server, along with the typical apt MySQL installation, the last two items can be found in the /etc/mysql/my.conf file.

Logging In

Log in as a user with super privileges:

$ mysql -u [username] -p

The -p means you are going to be prompted for a password. Enter your password (your typing won't show up) and hit Enter. You'll know you are in the mysql program because you'll see the mysql> command prompt.

Create Database

Next, let's create the database (as the admin user):

mysql> CREATE DATABASE [db_name]

The [db_name] value is the name of your rails database. Since it doesn't exists, you can call it whatever you want.

Create User

We could use the admin mysql user to connect to this database, but let's keep it a little more secure and create a user for this role:

mysql> CREATE USER '[username]'@'%' IDENTIFIED BY '[password]';

You can pick the username and password, but make sure you remember them.

Next, you need to give that user remote privileges to your database:

mysql> GRANT ALL PRIVILEGES ON [db_name].* TO '[username]'@'%' WITH GRANT OPTION;
mysql> exit;

Open Up Ports

The last thing to do is make sure the port you are going to use to connect is open. Just as an example, I use ufw to manage firewall settings, so I'd do something like:

$ sudo ufw allow 3306

Rails App

Now you need to add the remote database setting to your rails app, which you probably know goes in config/database.yml. It's going to look something like this:


adapter: mysql2
encoding: utf8
pool: 5
host: [mysql_ip]
username: [mysql_username]
password: [mysql_password]
port: [mysql_port]
socket: [path_to_mysql_socket_file]
database: [db_name]

The rails_env is likely either development or production, while the others are values you either just created or found.

You should be up and running now! If you can start your server, then you know you're okay. You can also try to reset or migrate the database.

Let's Connect

Keep Reading

List All Databases on MySQL Server

It's not that hard, but I keep forgetting how to do it, so I wrote it down.

May 30, 2013

A has_many Relationship within a Single Model in Rails

Here are a couple methods for dealing with uni-directional many-to-many associations in Rails.

Apr 04, 2015

Rollback A Deleted Rails Database Migration

You got yourself into that pickle where you created a migration, migrated, deleted the migration, then tried to rollback. Here's how you get around it!

Mar 08, 2016