Ruby on Rails is a great thing. Migrations in particular, allow for schema-agnostic database setups in production and development (so using MySQL for development, and then using something like SQLite for production is extremely easy to do).
Migrations really start to rock when you run into pitfalls that come with using other databases (and need to ditch them quickly). SQLite in particular, has some concurrency problems that if not taken care of, may turn your cheery development release into a night of swearing, and sweat. Not to fault SQLite (as it really is a performance monster if you can use it), there have been some attempts at making concurrency better in Rails.
In the mean time, using a fully fledged database like MySQL can quickly turn your application around if it is running into concurrency problems. The question though, is how.
The first step is to get MySQL setup via a package manager, or compile it. Since there are potentially hundreds of ways (with different combinations) of doing it, you may need to read up on the specific method required for your distribution or operating system.
Debian based distributions use: apt-get install mysql
Fedora/Red Hat/CentOS distributions use: yum install mysql
(Note that you may need to look up the precise package name since there may be multiple versions of MySQL available with apt-cache search mysql and yum search mysql respectively).
After finishing up, (and making sure the root password is changed with mysqladmin -u root password newpassword1, etc), creating the database with `CREATE DATABASE myproject`, making sure MySQL is listening only where you want it to, and making another user for only that database (using GRANT), its time to edit database.yml.
Editing database.yml and setting development and production to point at your SQL database will do it here (or simply adding a space behind db:migrate and typing RAILS_ENV=production might do it too). The normal database.yml should be commented enough to show you the light–but beware, database.yml is very picky about formatting. In particular, I hear that it is very picky with spaces versus tabs (although i’ve never run into that problem using RadRails, as it appears tabs are converted to spaces).
You’ll need to add the mysql gem:
$ sudo gem install mysql
Now the goodness of migrations come along. Go into your rails project root directory (that would be where you see “config, app” and others).
$ rake db:migrate
(or as above, rake db:migrate RAILS_ENV=production)
If it worked without any errors, you’re on the path to glory. If not, look at what error got dumped out (sans the huge trace that follows), and take a stick to your problem.
Here is the stickler. Although you can migrate SQLite data dumps by hand using:
$ sqlite3 database.db .dump > dump-file
$ perl -pne s/”//g dump-file > dump-no-double-quote
$ mysql -u someuser -p somedatabase < dump-no-double-quote
And replace all the things you get errors with (note that if you have things like booleans in SQLite, they’re represented in the database as t/f, and MySQL represents them as 1/0)..it becomes a pain with a huge amount of data.
I have not tested it out (as I did the above search and replace pecking), but you may want to try this instead.
1For the security oriented, you may want to either erase the history file with `history -c`, or do the password change the old way — via the MySQL client (documentation everywhere for that).