MySQL: Moving database from SQL Server to MySQL

This guide is intended for people who want to move a Windows MS SQL Server FogBugz database over to a non-Windows MySQL installation.

If you are upgrading from a previous version of FogBugz and switching to MySQL, please install the upgraded FogBugz code against the old database if this is applicable or feasible, log in once so FogBugz can do the upgrade, and then proceed to converting your database.

  1. Make sure that you're running the latest version of FogBugz before starting. You must be running at least FogBugz version 3.0.15.
  2. Install MySQL (tested with v.4.0.9) and MyODBC (tested with v.2.5, 3.51) on your Windows server. We do not give instructions on how to set up MySQL or MyODBC and assume you have the knowledge to do so yourself. (We did it in a few minutes and had no problems whatsoever. Also we recommend using the GUI Control Center for MySQL which is very much like MSSQL Enterprise Manager).
  3. Install and run MsSql2MySql. More information about MsSql2MySQL is in a MySQL.com guide to SQL Server-MySQL migration.
  4. Click Settings and enter the database connection information.
  5. Click connect.
  6. Click on your MS SQL db.
  7. Click  Select All and then Start. Bam! Your db is thrown to MySQL. If not, bang head against wall and go back to step 1.
  8. The Attachment table has a column called sData.  The data type for this should be LONGBLOB not BLOB.  Also, make sure the type for s column in BugEvent is LONGTEXT.  (mssql2mysql isn't so smart about getting column definitions correct).
  9. In a DOS window, navigate to your mysql\bin directory and enter:

    mysqldump fogbugz > createfb.sql

    (This assumes your database name is "fogbugz")

If you are switching from FB for Windows to FB for  Unix or Mac:

  1. Install Fogbugz for Unix or Mac fully and get it working (even add a test case).  Remember the fogbugz user and password that you set for the database.
  2. Once its working, log into mysql as root.  The instructions assume your fogbugz database is called 'fogbugz'.  If not, replace 'fogbugz' here with the name of your database.

    mysql> drop database fogbugz;
    mysql> create database fogbugz;
    mysql> use fogbugz;
    mysql> source /path/to/createfb.sql
    mysql> grant all privileges on fogbugz.* to fogbugzuserhere identified by 'fogbugzpasswordhere';
    mysql> grant all on fogbugz.* to fogbugzuserhere@'localhost' identified by 'fogbugzpasswordhere';

    Note the ' around the 'localhost' part.  You can replace localhost by the server name if your fogbugz server is on a different machine.
  3. Then go back into fogbugz through your web browser. You will have to enter your order info to install licenses again.