How to configure rails to connect to mysql database

Next thing that you have to learn is how to configure your newly created rails project to be able to connect to your existing installation of mysql. What I mean by existing installation is the mysql version which came with your installation of Wampserver. So that we won’t have to install it again and waste our time.

 

Optional Files

 

Gemfile

First thing that you need to do is to navigate to the rails project that you created on the last tutorial. Mine is in:

c:wampwwwRailsProject

image

Open up the gemfile using a text editor like notepad++.

source 'http://rubygems.org'
 
gem 'rails', '3.0.5'
 
# Bundle edge Rails instead:
# gem 'rails', :git => 'git://github.com/rails/rails.git'
 
gem 'mysql2'
 
# Use unicorn as the web server
# gem 'unicorn'
 
# Deploy with Capistrano
# gem 'capistrano'
 
# To use debugger (ruby-debug for Ruby 1.8.7+, ruby-debug19 for Ruby 1.9.2+)
# gem 'ruby-debug'
# gem 'ruby-debug19', :require => 'ruby-debug'
 
# Bundle the extra gems:
# gem 'bj'
# gem 'nokogiri'
# gem 'sqlite3-ruby', :require => 'sqlite3'
# gem 'aws-s3', :require => 'aws/s3'
 
# Bundle gems for the local environment. Make sure to
# put test-only gems in this group so their generators
# and rake tasks are available in development mode:
# group :development, :test do
#   gem 'webrat'
# end

All you have to do with that file is to find the word ‘mysql2’. And change it to ‘mysql’.

Mysql2 and mysql  are both gems  that are used to talk to the mysql database. But most of the people are having trouble installing mysql2 gem. And that includes me, or even you. Here’s the error that I got when I tried to install mysql2 gem.

image

 

Install Mysql Gem

Next thing that you have to do is to install the mysql gem. But before you type the commands below, make sure that the ruby/bin is included in your environment variables. Environment variables are folders which are accessed by your operating system to execute certain commands. Its like extending what you can do with the command line.

gem install mysql

If you don’t know how to setup environment variables then check out this tutorial. Then insert the path below:

C:Ruby192bin

Here’s what it looks like after successfully installing the mysql gem:

image

 

 

Database.yml

Next, open up the config folder and open the database.yml file using a text editor:

development:
adapter: mysql2
encoding: utf8
reconnect: false
database: RailsProject_development
pool: 5
username: root
password:
host: localhost
port: 3306

We are only concerned with the development section. So I only included that part. But don’t delete those parts that you don’t see. As it might cause errors.

Edit the username, password, host, and port to match the actual values which is true to your installation.

Most of the time, here’s what you have to enter:

adapter: mysql
username: root
password:
host: localhost
port: 3306

The database is simply the database that you’re going to use for the project. You can create one by going to phpmyadmin, or if you’re geeky enough in the mysql console.

If you have changed the port and not using the default which is 3306. Then change the port number in the database.yml file to match the port used by your  mysql installation. Check out this tutorial, if you don’t know what is the port used by mysql.

 

Testing

Next, lets test if the configuration did work. Open up command line and navigate to your rails project:

cdwampwwwRailsProject

Then type:

rake db:schema:dump

What it does is to copy the structure of the mysql database that you have specified in your project into a file called schema.rb. Which can be found on db folder inside your rails project.

Here’s what the generated file looks like:

ActiveRecord::Schema.define(:version => 20110404033228) do
 
  create_table "users", :force => true do |t|
    t.string   "fname",      :limit => 30
    t.string   "lname",      :limit => 20
    t.string   "email",                    :default => "", :null => false
    t.string   "password",   :limit => 49
    t.datetime "created_at"
    t.datetime "updated_at"
  end
 
end

If you check out phpmyadmin. You will see that it’s the same. That file is used for database migrations. I can compare it to the sql file that is generated when you export a mysql database usin phpmyadmin.

image

 

Mysql DLL fix

If the configuration didn’t work for you. Then you have to download the optional files I included above. Choose either of the two, since they both point to the same file.

Navigate to the ruby folder:

C:Ruby192bin

And paste the LibMysql.dll into that location. You are now good to go. Try the test again, and if its generate the correct schema, then go give yourself a tap in the back.

One thought on “How to configure rails to connect to mysql database

  1. Pingback: How to create database migrations in rails « Data Integrated Entity

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s