This post is a part of a mini-series!
Day 6
1) Installing link
2) Test a Rails App! link
3) Configuring Webserver link
4) Configuring MySql (You Are Here)


Ok, sqlite is only for non-high volume traffic. So let's switch to using MySql. The first step is to install MySql, which you can see how is done here.

Then comes the configuration:
1) Create a MySql user
2) Create the 3 MySql databases for your project
3) Create your rails app to use mysql as the database
4) Configure your database.yml


So a little over a month ago, Martyr2, here on the forums, really helped me out with some pointers and advice on MySql, and this blog will have that post to give reference and credit.
ref: http://www.dreaminco...sources-listed/


To complete this tutorial, you obviously need MySql installed, so if you haven't already seen it, check out that section in my walk though for installing MySql (it's the third section down).


:: Configuring MySql

log into mysql:

$  mysql -u root -p

1) Create the databases for your rails project

mysql >  CREATE DATABASE mt_development;
mysql >  CREATE DATABASE mt_test;
mysql >  CREATE DATABASE mt_production;

Hint: The command $ rake db:create:all will do this for you automatically if the user specified in your database.yml file has database creation privs.

2) Setup the user account to have access to those databases

mysql >  CREATE USER 'user1'@'localhost' IDENTIFIED BY 'fakepass';
mysql >  GRANT ALL ON mt_development.* TO [email protected] IDENTIFIED BY 'fakepass';
mysql >  GRANT ALL ON mt_test.* TO [email protected] IDENTIFIED BY 'fakepass';
mysql >  GRANT ALL ON mt_production.* TO [email protected] IDENTIFIED BY 'fakepass';
mysql >  exit

3) Test

$  mysql -u user1 -p

mysql >  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mt_development        |
| mt_production         |
| mt_test               |
+--------------------+
4 rows in set (0.00 sec)

mysql >  use mt_test;
Database changed

mysql >  CREATE TABLE myTable (column1 TEXT);
Query OK, 0 rows affected (0.03 sec)

mysql >  show tables;
+----------------+
| Tables_in_test |
+----------------+
| myTable        |
+----------------+
1 row in set (0.00 sec)

Now lets undo what we did.

mysql >  DROP TABLE myTable;
mysql >  show tables;
Empty set (0.00 sec)

mysql >  exit

If that all went well, then you now have a solid database structure setup on your machine to be used with your rails application named 'mt' ('my test' I guess).



:: Configure a Rails App for MySql:

Make a new rails app using mysql, and install mysql2 gem if necessary/desirable:

$  gem install mysql2
$  gem install mysql2 -v 0.2.7    ~~~ The latest version happens to be bugged...
$  gem install rails

$  rails new mt -d mysql
$  cd mt
$  cat config/database.yml

#
# Install the MySQL driver:
#   gem install mysql2
#
# And be sure to use new-style password hashing:
#   http://dev.mysql.com/doc/refman/5.0/en/old-client.html
development:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: mt_development
  pool: 5
  username: root
  password:
  host: localhost

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: mt_test
  pool: 5
  username: root
  password:
  host: localhost

production:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: mt_production
  pool: 5
  username: root
  password:
  host: localhost

That thing that we catted is what let's us customize our database. Change the username to user1, the password to fakepass and make sure the database names have been given the same names as those you created in the earlier steps. Then you should be good to go.

Due to a bug we must make sure our Gemfile requires mysql2 version 0.2.7 or less:
(vi Gemfile)

...
gem 'mysql2', '~> 0.2.7'  # added the version specifier to this line
...

Now you'll need to install that gem bundle just in case it's not already installed:

$  bundle install

Because of a bug, you MUST NOT use the latest version of ruby. If you are coming from a windows background, this might seem counter intuitive, but in the open source community, progression moves backwards. Every time a new version is released, it means there are more bugs in it, and fewer bugs in prior releases :detective: .

$  rvm 1.8.7

Make a model

$  rails g scaffold Hello hello:string

Bring the database up for all your models:

$  rake db:migrate

Test it out:

$  rails s

Troubleshooting:
"warning: already initialized constant RAKEVERSION"
You will get a whole ton of bugs that contain that sort of text if you try to rake your application using ruby version 1.9.2. Use rvm use 1.8.7 and then proceed with the raking. Afterwards, you may switch back to rvm use 1.9.2

If that goes over ok, you should be able to go to http://localhost:3000/hellos and see your application. Try using the CRUD interface to really test that mysql is working.
Create a "hello" with the string "hello world of pain" using the html interface of your rails app.



Then, meet me in mysql to check for that data in your development database.

$  mysql -u user1 -p
:fakepass
mysql >  use mt_development;
mysql >  show tables;

+--------------------------+
| Tables_in_mt_development |
+--------------------------+
| hellos                   |
| schema_migrations        |
+--------------------------+
2 rows in set (0.00 sec)

mysql >  SELECT * FROM hellos;

+----+----------------------+---------------------+---------------------+
| id | hello                | created_at          | updated_at          |
+----+----------------------+---------------------+---------------------+
|  1 | hello world of pain  | 2011-06-05 11:49:24 | 2011-06-05 11:49:24 |
+----+----------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Fantastic! It's showing that for me, because on the html side of my rails app, I created that 'hello' with the message "hello world of mysql".

I hope this little exercise helped to tie everything together.

Oh yeah, before you go, try it out with the nginx server we setup in the last post. It's OK to use version 1.9.2 now:

$  rvm 1.9.2
$  /etc/init.d/nginx restart
$  /etc/init.d/thin start

http://localhost/hellos