Moving MySQL Databases
One useful tool in Rails, with respect to creating portable databases, is the concept of migrations. Migrations are a topic in and of themselves; suffice to say, you can create, at a code level, specifications for creating tables. Migrations are useful to apply incremental changes to database schemas with production servers.
Migrations also allow you to specify seed data–you can create migrations that add data to certain tables, and other migrations that modify or delete data from tables.
Keeping your data safe using migrations is great–simply create a migration that inserts all the appropriate data, and you’re set! Anytime the data gets corrupted, you can roll-back the migration (to delete the data), and run the migration again, to re-add the data.
Problem is, this doesn’t work in websites where the data changes often–such as on a blog, forum, community site, or indeed, almost any kind of web application! What’s a Rails developer to do?
One answer is to use database-specific commands to export and re-import the data. Most databases allow you to export an entire schema–data, table specifications, indexes, everything–to a file, and to re-import such a file. Since most Rails applications prior to 2.0.2 use MySQL as their default database, we’ll focus on that.
To export a MySQL database, start your Rails command prompt. Run “mysql” and make sure you don’t get a “command not found” error.
Then, to export your database to a file, issue this command from the shell:
mysqldump -u username -ppassword database_name > FILE.sql
Replace username with your username, password with your password, and database_name with the database name. The resulting schema will be exported to FILE.sql, in whatever directory you’re in.
Then, move to your machine where you want to import this data, and issue the following command from the shell:
mysql -u username -ppassword database_name < FILE.sql
Again, replace username, password, and database_name with the appropriate values, and make sure FILE.sql is in the directory you’re issuing the command from.
And that’s it! Your entire database is quickly and seamlessly transported from one place to another! Easy, wasn’t it?
Tags: data portability, MySQL Posted in
(No Ratings Yet)
Loading ...
Leave a Reply