Setting up Ruby on Rails Database Connection using MySQL on Ubuntu Feisty Fawn
MySQL is a rock solid database, used all around the world, for all kinds of situations. Its use escalated with the usage of PHP and together became known as the LAMP (Linux Apache MySQL and PHP) stack.
Warning: By default, root access to MySQL is open. We will deal with this later.
Installation
Enter into the Terminal:
sudo apt-get install mysql-server
Edit the file 
RAILS_ROOT/config/database.yml
development environment. The environment you are deploying your database should look like so:
development:
adapter: mysql
database: railsapp_development
username: root
password:
host: localhost
Note: Modify to fit your needs
If you don’t have a database created yet, execute:
mysqladmin -u root create railsapp_development
Note: Where railsapp_development should be the same as the database field we configured.
And there you have it! Your Rails application will now work with MySQL.
Continue if you want to learn more about MySQL.
Locking down MySQL
By default 3 accounts are associated to the ‘root’ user. Each one without a password. Definitely not safe.
So we’ll remove 2 of those accounts, and give a password to the remaining one.
Lets enter MySQL:
mysql -u root
use mysql;
DELETE FROM user WHERE User = 'root' && Host != 'localhost';
UPDATE user SET Password=PASSWORD('new-password') WHERE User='root';
FLUSH PRIVILEGES;
Note: Make sure to specify the password in place of ‘new-password’!
Now in order to access MySQL via root, you’ll have to use:
mysql -u root -p
And you should be prompted for your password.
Note: People tend to append your password after the -p flag. Do not do this. It will make the password visible via ps.
Even man mysql states:
Specifying a password on the command line should be considered insecure. See Section 7.6, “Keeping Your Password Secureâ€.
If you immediately want to access your railsapp_development database, you can just specify it like so.
mysql -u root -p railsapp_development
Easy MySQL Administration
Coming from a PHP background, I am familiar with phpMyAdmin, but there are other solutions out there. The problem with phpMyAdmin is that it requires the installation of both Apache and PHP, which opens more ports on your computer, and adds uneeded extra software on your computer.
Another solution is to use mysql-admin (sudo apt-get install mysql-admin 
), though I haven’t tried using it.
phpMyAdmin can be installed via:
sudo apt-get install phpMyAdmin
Conclusion
There you have it, your database is now set up!
Faustino said,
May 18, 2007 @ 4:32 am
And how I can connect mysql with ruby ????
aizatto said,
May 18, 2007 @ 9:38 am
Do you specifically want to connect it with Ruby (sans Rails) alone? Give me some time and I can write up a quick tutorial!
Pat said,
August 10, 2007 @ 7:33 am
Thanks for the clear instructions. I have RoR running fine with WEBrick on port 3000 using mysql. And I have Apache2 set up to run RoR, at least I get the default application description screen. But when I go to my “Rolling with Ruby on Rails” tutorial app’s controller page http://localhost/recipe I get a “Something went wrong” message. Again doing this with WEBrick works fine so I thought mysql was configured properly. BTW: I also configured for external access to mysql as you described above. Since I get the message only when trying to access mysql I suspect I have something screwed up with regard to the Apache+Rails+mysql combination. Any hints would be much appreciated.
peter retief said,
September 29, 2007 @ 2:22 pm
Before you can login to the mysql client (mysql -u root -p) you have to install the mysql client - like so (for mysql 5)
sudo apt-get install mysql-client-5.0
Testing said,
February 29, 2008 @ 11:58 am
Test Commmmmmmmmmmmmmmmmmmmmmmad