Strange Symphonies The best way to predict the future is to invent it

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 . For now we are using the 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!