How to install, configure and use MySQL server with mysql2 gem in a rails application.


Install MySQL server in your PC. For Ubuntu/Mint

sudo apt-get install mysql-server

Go to your rails application folder and add the gem in your Gemfile

gem 'mysql2'

Then execute

bundle install

If you get a mysql.h is missing or other similar build errors, then you should install the mysql-devel package of your linux distribution.

For Fedora

sudo yum install mysql-devel

For Ubuntu/Mint

sudo apt-get install libmysqlclient-dev

You need to set a password for a root account in MySQL Server. (On Ubuntu/Mint distribution the password could be set during the installation of the MySQL deb package). To setup a root password for first time,

mysqladmin -u root password YOUR-NEW-PASSWORD

therefore for password 1234, you execute

mysqladmin -u root password 1234

Connect to MySQL as root

mysql -h localhost -u root -p

Create a database that the rails application is going to use (i.e. my_db)

mysql> create database my_db;

Grant ALL privileges (all normal stuff) for your username, let’s say sexy, in my_db

mysql> grant ALL on my_db.* to 'sexy'@'localhost' IDENTIFIED BY '1234';

In case you want to change sexy’s password to 5678

mysql> set password for sexy = password('5678');
mysql> exit;

Go to your rails application root folder and edit your config/database.yaml file (i.e. for the development db)

# config/database.yaml
# databse.yaml
    adapter: mysql2
    username: sexy
    password: "1234"
    database: my_db
    host: localhost
    pool: 5
    timeout: 5000

then execute

bundle exec rake db:schema:load

If you get the following error

rake aborted! can't convert Fixnum into String

put every numeric value you have in "", this is done because the YAML parser has to guess what data type each value in your database.yml file is, without any other contextual information, thus considering a password value of 1234 to be an integer! [1]

A final tip. Assuming that the root password you set in the previous step was 1234, then to backup your database before doing any crazy stuff, execute

mysqldump -u root -p1234 --all-databases > full-dump.sql


[1] Can’t convert fixnum to string during rake dbcreate