In MySQL Workbench 6.0 two new additions have been made to the supported RDBMS sources list in the Migration Wizard: Sybase SQLAnywhere and SQLite. In this tutorial I’m going to show you how to migrate your Sybase SQLAnywhere databases to MySQL.

As usual, we’ll start with a couple assumptions:

• You have MySQL Workbench 6.0 installed.
• You have a running Sybase SQLAnywhere database in your local computer (i.e. the computer where you are running MySQL Workbench. I’ll be using the demo database that Sybase distributes with SQLAnywhere 12.
• A running MySQL Server instance with proper user access is available and you are able to connect to it from MySQL Workbench. The Migration Wizard supports MySQL versions from 5.1 onwards so make sure you have a supported version. For this tutorial I’m using MySQL Server 5.6.12 CE installed in a virtual box inside my home network.

One cool new feature we’ve added to the MySQL Migration Wizard in the 6.0 release is its ability to use Python DB API modules to communicate with the source RDBMSes. We are not abandoning ODBC, but we want you to have more options (= more freedom) for connecting to your source RDBMSes. As it turns out, some RDBMSes don’t provide ODBC drivers compatible with iODBC, the ODBC driver manager that we officially support for Linux and Mac OS X. And, saddly, one of these RDBMSes is Sybase SQLAnywhere.

If you are a Windows user, you’ll be better off by downloading the SQL Anywhere ODBC driver from the Sybase website and using it instead of the sqlanydb module.

In this post I’m going to be running MySQL Workbench in my Ubuntu 12.04 desktop, so I’ll be installing a Python DB API driver module to communicate with my local Sybase SQLAnywhere server. The MySQL Migration Wizard has support for sqlanydb, the official Sybase SQLAnywhere Python DB API module. You can download and install it from its website. One you have extracted the tarball, type “sudo python setup.py install” (without the quotes) in a command line terminal and you should be done. To verify that it was properly installed, type this:

python -c "import sqlanydb"

If everything went well, you should not see any error message when you execute this command. If you do see an error message, please refer to the sqlanydb installation page for further instructions on how to properly install it.

Open MySQL Workbench and start the Migration Wizard

You will find the Migration Wizard icon in the sidebar in Workbench’s main screen, under the name “Database Migration”. Click on it to start the Migration Wizard:

A new tab showing the Overview page of the Migration Wizard should appear.

Set up the parameters to connect to your source database

Move to the next page by clicking on the “Start Migration” button. Now select “Sybase SQLAnywhere” as the Database System and sqlanydb as the Connection Method. You should as well type all the other parameters that allow you to connect to your source SQLAnywhere database. You can take a look at this picture to get an idea of what you should have:

When you have entered all the relevant parameters, click on the “Test Connection” button to verify that you can connect to your SQL Anywhere database. Once you have successfully tested your connection, move to the next page by clicking on the “Next” button.

Set up the parameters to connect to your target database

Now you have to set the parameters to connect to your target MySQL server. If you have already created a MySQL Workbench connection to it, you can select it from the Stored Connection selector. Remember to test your connection parameters and make sure that you can connect before moving to the next page.

Click on the “Next” button to move to the next page. The Migration Wizard will communicate to your SQLAnywhere instance to fetch a list of the catalogs and schemata. If you left blank the Database field in the Source Selection page it will retrieve all of the catalogs in the server. Otherwise it will just fetch the schemata corresponding to the catalog you explicitly typed.

Verify that all tasks finished successfully and click on the “Next” button to move forward. You will be given a list of catalogs and their corresponding schemata to select the ones to migrate. Keep in mind that you can only migrate schemata from one catalog at a time.

Beware that the concept of schema in SQLAnywhere is more close to the definition of schemata in Sybase Adaptive Server Enterprise. A schema is more of a group of users that owns certain database objects than a logical grouping or classification of objects.

The Schema Selection page will look like this:

The real juice in the demo sample database is in the “GROUPO”. Now take a look at the“Schema Name Mapping Method” group box. MySQL only supports one schema in each database (to be more precise, a MySQL database is a schema) so we have to tell the Migration Wizard how to handle the migration of schemas in our source database. We can either keep all of the schemas as they are (the Migration Wizard will create one database per schema), or merge them into a single MySQL database. The two last options are for specifying how the merge should be done: either remove the schema names (the Migration Wizard will handle the possible name colisions thay may appear along the way) or either adding the schema name to the database object names as a prefix. Let’s select the second option (it’s the default option) since we only have one schema and we are not particularly interested in keeping its meaningless name.

Select the objects to migrate

Move to the next page using the “Next” button. You should see the reverse engineering of the selected schema in progress. At this point the Migration Wizard is retrieving relevant information about the involved database objects (table names, table columns, primary and foreign keys, indices, triggers, views, etc.). You will be presented a page showing the progress as shown in the image below:

In the next page (the Source Objects page) you will have a list with the objects that were retrieved and are available for migration. If you click on the “Show Selection” button you will be able to select exactly which of the retrieved table objects you want to migrate, as shown here:

The items in the list to the right are the ones to be migrated. Note how you can use the filter box to easily filter the list (wildcards are allowed). By using the arrow buttons you can filter out the objects that you don’t want to migrate. At the end, don’t forget to clear the filter text box to check the full list of the selected objects. We are going to migrate all of the table objects, so make sure that all of them are in the “Objects to Migrate” list and that the“Migrate Table Objects” checkbox is checked. Most of the time you’ll want to migrate all objects in the schema anyway, so you can just click “Next”.

Review the proposed migration

Move to the next page. You will see the progress of the migration there. At this point the Migration Wizard is converting the objects you selected into their equivalent objects in MySQL and creating the MySQL code needed to create them in the target server. Let it finish and move to the next page. You may have to wait a little bit before the Manual Editing page is ready but you’ll end up with something like this:

As you can see in the image above, there is a combo box named View. By using it you can change the way the migrated database objects are shown. Also take a look at the Show Code and Messages button. If you click on it you can see (and edit!) the generated MySQL code that corresponds to the selected object. Furthermore, you can double click in a row in the object tree and edit the name of the target object. Suppose you want your resultant database to have another name. No problem: double click on the “demo” row and rename it.

Note how the Migration Wizard had to change some incompatible settings from the source database to make it work with MySQL. In this case, you can see that the “demo” database has two foreign key constraints with duplicated names, so they had to be changed to make them unique. Every time the Migration Wizard does something like that, you will see a warning message next to the affected objects. At the end of the migration process you will be given a summary that will include this warnings too, so don’t forget to look at that summary too.

An interesting option in the View combo box is the “Column Mappings” one. It will show you all of the table columns and will let you individually review and fix the mapping of column types, default values and other attributes.

Run the resulting MySQL code to create the database objects

Since the warnings we got are absolutely fine, we can move to the Target Creation Options page. It will look like this:

As you can see there, you are given the options of running the generated code in the target RDBMS (your MySQL instance from the second step) or just dumping it into a SQL script file. Leave it as shown in the image and move to the next page. The migrated SQL code will be executed in the target MySQL server. You can view its progress in the next page: the Create Schemata page.

Once the creation of the demo database finishes you can move to the Create Target Results page. It will present you a list with the created objects and whether there were errors while creating them. Review it and make sure that everything went OK. It should look like this:

You can still edit the migration code using the code box to the right and save your changes by clicking on the “Apply” button. Keep in mind that you would still need to recreate the objects with the modified code in order to actually perform the changes. This is done by clicking on the “Recreate Objects” button. You may need to edit the generated code if its execution failed. You can then manually fix the SQL code and re-execute everything. In this tutorial we are not changing anything, so leave the code as it is and move to the Data Transfer Setup page by clicking on the “Next” button.

Transfer the data to the MySQL database

The next steps in the Migration Wizard are related to the transference of data from the source SQLAnywhere database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.

Leave the default values for the options in this page as shown in the above image and move to the actual data transference by jumping to the next page. It may take a little while to copy the data. At this point the corresponding progress page will look familiar:

Once it finishes, move to the next page. You will be presented a report page summarizing the whole process:

A little verification step

Now that the “demo” database was successfully migrated, let’s see the results. Open your MySQL Server instance and query the newly created “demo” database. You can try something like “SELECT * FROM demo.Contacts;”. You should get something like this:

And why not take it a step further and create an EER diagram from the migrated database? Click on the “>” icon next to the Models section in the main screen of MySQL Workbench and select “Create EER Model From Existing Database” from the menu that will appear. Follow through the wizard steps. Once done go to Arrange –> Autolayout in the main menu to accomodate your EER model and you should get something like this:

Conclusions

By now you should have a pretty good idea of the capabilities of the Migration Wizard and should be ready to use it for your own migrations. The official documentation is also there for you and you can always ask any question in the comments of this post or in the migration official forum.

Sergio de la Cruz