So you’ve been running Instiki, for a while, with the default SQLite database engine. Now you’ve decided to migrate to a beefier alternative, like MySQL. Here’s how to migrate your data (based on an article by Al Hoang).
Make sure you’re running Instiki 0.19 or later, and have run
% ruby bundle exec rake upgrade_instiki
This upgrades the database schema to the latest version. The previous schema (used in earlier versions of Instiki) could cause (data loss!) problems for some users, when migrating from SQLite3 to MySQL.
Add the line
gem "mysql", "~> 2.9.1"
to the end of the Gemfile and re-run
% ruby bundle
which will install the
mysql gem for you.
Set an environment variable in your shell.
sh and its cousins (
zsh, etc) type
csh and its cousins (
tcsh, etc) type
% setenv RAILS_ENV production
Export your database to
% ruby bundle exec rake db:fixtures:export_all
production: adapter: sqlite3 database: db/production.db.sqlite3
with something along the lines of
(The precise details will depend on your setup.)
Create a database in MySQL, with the same name as the one you gave in
database.yml, using something like
% echo "create database your_db_name" | mysql -u your_db_username -p your_db_password
Initialize the database tables and reimport your data
% ruby bundle exec rake db:migrate % ruby bundle exec rake db:fixtures:import_all
There’s one more issue that you may want to consider. Under SQLite3, string comparisons are (by default) case-sensitive. Thus, you can have a page named ‘Foo’ and a page named ‘foo’, and they will be treated as distinct by Instiki. Under MySQL, the opposite is true. String comparisons are, by default, case-insensitive.
Neither behaviour is “more correct”; they’re just different. If you want to retain the case-sensitivity of page names, when you migrate to MySQL, you can change the collation on the relevant database columns. The relevant SQL commands are
ALTER TABLE `pages` MODIFY `name` varchar(255) COLLATE latin1_bin DEFAULT NULL; ALTER TABLE `wiki_references` MODIFY `referenced_name` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT ''; ALTER TABLE `wiki_files` MODIFY `file_name` varchar(255) COLLATE latin1_bin NOT NULL;
ALTER TABLE `pages` MODIFY `name` varchar(255) COLLATE utf8_bin DEFAULT NULL; ALTER TABLE `wiki_references` MODIFY `referenced_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT ''; ALTER TABLE `wiki_files` MODIFY `file_name` varchar(255) COLLATE utf8_bin NOT NULL;
depending on what encoding your database was created with. (MySQL defaults to
latin1 so, even though Instiki uses UTF-8, the database may or may not see it that way.)