Moving to MySQL

This document is a work in progress, do not rely on this document to be accurate yet.

1. Stop your web server

2. IMPORTANT Backup your production database and server (if you are really paranoid). YES. Do it!

3. Install MySQL Community Server -- instead of using the MySQL installer, consider following this guide, being sure to do your installs with the latest versions.

4. Before doing the migration, some nice things to do to minimize the amount of data copied over and to speed up the process. This list is in the recommended, but not required order. Feel free to do re-order depending on your needs

RAILS_ENV=production rake chore:cleanup_missing_manifests
RAILS_ENV=production rake chore:cleanup_old_managed_install_reports
RAILS_ENV=production rake chore:cleanup_system_profiles
RAILS_ENV=production rake chore:destroy_stale_item_records
RAILS_ENV=production rake chore:validate_models #This is important because the MySQL migrations require you to have completely valid entries
sqlite3 <database_name> vacuum

5. Pull the latest changes from MunkiServer, then apply Trey's branch, finally, get all the gems

git checkout master
git pull origin master (or jnraine if you have your own fork)
bundle install

6. Create a the production database in MySQL

# Connect to MySQL
mysql -u <your user> -p

# Create database for Munkiserver
mysql> CREATE DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_general_ci;

7. Edit your config/database.yml and replace the existing production section with something like this:

production:
  adapter: mysql2
  encoding: utf8
  host: localhost # Or the FQDN of your external MySQL Server
  database: <database_name>
  pool: 5
  username: <db username>
  password: <db password>

This Rails Guide might help.

8. IMPORANT! Apply the latest migrations to the MySQL database. This must be done before moving the data to MySQL since the migration removes default values from the schema not allowed in MySQL.

RAILS_ENV=production rake db:migrate

8. Increase your MySQL Server's max_allowed_packet to at least 32M

See http://stackoverflow.com/questions/2548730/heroku-problem-during-database-pull-of-rails-app-mysqlerror-mysql-server-has for more info.

9. Move the data from sqlite3 to MySQL

# Create temporary web service with production sqlite database
taps server sqlite://db/production.sqlite3 munkiserver munkiserver &

# Pull sqlite3 database into mysql
taps pull mysql2://<db username>:<db password>@localhost/<db name> http://munkiserver:munkiserver@localhost:5000

10. Start webserver and cross your fingers. If you run into problems email [email protected]

Troublehsooting

• If you run into any database corruption errors, such as "The database disk image is malformed", consider trying this database corruption fix

• If you run into an error like "Library not loaded: libmysqlclient.18.dylib" in working with MySQL, consider looking at the libmysqlclient.18.dylib fix