MySQL Workbench 6.1: Updating accounts using the old (pre-4.1.1) authentication protocol

In MySQL each ‘user’ has its own password hash. To provide better security, pasword hashes were extended from 16 to 41 bytes in MySQL 4.1.

This change created a situation. If the user was created prior to version 4.1 and the server updated to a newer version, the password hash that was stored in the database is left in the old, deprecated format. This is because MySQL doesn’t store passwords in plain text so there’s no way to automatically regenerate a password hash. For this case, we consider two scenarios:
– If the secure_auth server option is disabled, you can login and update your password. You may also need to enable the allow old_password option in the Workbench advanced options tab for the connection.
– If secure_auth is enabled, you do not have possibility to log in to the database and the only thing you can do is to disable that option or log in as different user (such as root) to change the password. In this case, attempt to log on to the server may fail with message:
“ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled)”
as in the screenshot below:

login_to_server

or, if you try log on in the Workbench, like this:

wb_login_to_server

In Workbench 6.1 an easy way to update your account to a format compatible with the new type of authentication has been added. Just log in to your account or some account that has permissions to manage other users (eg root) and go to the ‘Users and Privileges’ tab. Select the user whose account you want to update from the user list on the left. You should see ‘This account is using the pre-mysql-4.1.1 password hashing type. (…)’ in red in the lower right corner of the screen and the button [Upgrade] on the right, as the screenshot below:

upgrade_account

Enter a new or the current password and click the [Upgrade] button. After this operation, the user can login to his account without any further problems.

If you’re using a server 5.6 or later, we also offer a second way – generating a random password and marking it as expired. For this purpose, after selecting the user, leave the ‘password’ and ‘confirm a password’ fields untouched and immediately click on the [Upgrade] button. When you see the message shown in the screenshot below:

reset_to_expired

click on the [Reset to expired] button, it will generate a new random password, update your account and mark it as expired. The user will be able to log in to his account using the new password, but before being able to do anything else, they will need to change his password first.

Video Tutorial: Setup a Restricted SQL Server Account for Migrations with MySQL Workbench

Some users have asked us what is the minimum set of privileges that your Microsoft SQL Server user needs to successfully migrate databases from SQL Server using the MySQL Workbench Migration Wizard. Even though we don’t execute any query that alters anything in your source RDBMS servers, it’s never a bad idea to add an extra security barrier around it.

In short, you need the VIEW ANY DEFINITION permission for the server and the CONNECT andSELECT permissions for the database(s) you want to migrate. But to make this easier for you, we have created a video tutorial showing how to create a user with these permissions using the Microsoft SQL Server Management Studio.

Sergio de la Cruz

How-To: Database Migration from Sybase Adaptive Server Enterprise using MySQL Workbench

In this tutorial I’m going to show you how to migrate your Sybase Adaptive Server Enterprise databases to MySQL.

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

  • You have MySQL Workbench 6.0 installed.
  • You have a running Sybase Adaptive Server Enterprise database somewhere in your network. I’ll be using the pubs3 sample database that Sybase distributes with Adaptive Server Enterprise 15.
  • You have installed the Sybase Adaptive Server Enterprise ODBC drivers in the same PC where MySQL Workbench is running. The ODBC drivers are distributed with theSybase Adaptive Server Enterprise SDK and included in the Adaptive Server Enterprise Developer Edition.
  • 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 the same PC where MySQL Workbench is running.

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:

MySQL Worbench Main Screen

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

Home Screen of the MySQL Workbench Migration Wizard

In this tutorial we’ll be using an ODBC DSN connection to connect to our source Sybase Adaptive Server Enterprise database. Click on the “Open ODBC Administrator” and create a new connection under the User DSN tab in the window that appears. Select the Sybase Adaptive Server Enterprise driver and set the parameters for your source connection. Here’s how it looks in my PC:

Defining an ODBC connection

Set up the parameters to connect to your source database

Back in the Migration Wizard, move to the next page by clicking on the “Start Migration”button. Now select “Sybase ASE” as the Database System and ODBC Data Source as theConnection Method. Then select the ODBC connection that you defined in the previous step from the DSN combo box. Put your user name, password (optional, will be asked later if you don’t set it) and database name (optional, you will be given the option to select it later from a list). You can take a look at this picture to get an idea of what you should have:

The Source Selection Page

When you have entered all the relevant parameters, click on the “Test Connection” button to verify that you can connect to your source 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.

MySQL Workbench Migration Wizard | Target Selection Page

Click on the “Next” button to move to the next page. The Migration Wizard will communicate to your Adaptive Server Enterprise 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.

MySQL Workbench Migration Wizard | Fetch Schemata List Page

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 Adaptive Server Enterprise is more close to the definition of schemata in Microsoft SQL Server. 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:

MySQL Workbench Migration Wizard | Schemata Selection Page

As you can see, the pubs3 sample database has only one schema named “dbo”. 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:

MySQL Workbench Migration Wizard | Reverse Engineer Page

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:

MySQL Workbench Migration Wizard | Source Objects Page

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:

MySQL Workbench Migration Wizard | Manual Editing Page

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 “pubs3? row and rename it.

MySQL Workbench Migration Wizard | Manual Editing Page (Expanded)

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.

MySQL Workbench Migration Wizard | Manual Editing Page (Columns View)

Run the resulting MySQL code to create the database objects

Check any warning or error you get there (you should get none) and move to the Target Creation Options page. It will look like this:

MySQL Workbench Migration Wizard | Target Creation Options Page

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 pubs3 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:

MySQL Workbench Migration Wizard | Create Target Results Page

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 Adaptive Server Enterprise database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.

MySQL Workbench Migration Wizard | Data Transfer Setup Page

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:

MySQL Workbench Migration Wizard | Bulk Data Transfer Page

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

MySQL Workbench Migration Wizard | Migration Report Page

A little verification step

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

Querying a migrated table

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

 

How-To: Database Migration from Sybase SQLAnywhere using MySQL Workbench

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:

MySQL Workbench Home Screen

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

Home Screen of the MySQL Workbench Migration Wizard

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:

MySQL Workbench Migration Wizard | Source Selection Page

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.

MySQL Workbench Migration Wizard | Target Selection 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.

MySQL Workbench Migration Wizard | Fetch Schemata List Page

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:

MySQL Workbench Migration Wizard | Schemata Selection Page

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:

MySQL Workbench Migration Wizard | Reverse Engineer Page

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:

MySQL Workbench Migration Wizard | Source Objects Page

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:

MySQL Workbench Migration Wizard | Manual Editing Page

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.

MySQL Workbench Migration Wizard | Manual Editing Page (Expanded)

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.

MySQL Workbench Migration Wizard | Manual Editing Page (Columns View)

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:

MySQL Workbench Migration Wizard | Target Creation Options Page

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:

MySQL Workbench Migration Wizard | Create Target Results Page

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.

MySQL Workbench Migration Wizard | Data Transfer Setup Page

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:

MySQL Workbench Migration Wizard | Bulk Data Transfer Page

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

MySQL Workbench Migration Wizard | Migration Report Page

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:

Querying a migrated table

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:

EER Diagram of the Migrated Demo Database

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

Installing a driver for Microsoft SQL Server and Sybase ASE in Linux and Mac

In a recent post we showed you how to migrate a SQL Server database to MySQL. There, we used the oficial Microsoft ODBC driver and that’s OK if you are running MySQL Workbench in Windows. But what if your desktop OS is some Linux variant or Mac OS X?

It turns out that Microsoft has recently released an ODBC driver for Linux. However, you can’t use this driver with MySQL Workbench for Linux. (Actually you can, but you would have to rebuild Workbench). The main reason is that this ODBC driver was linked against unixODBC (an ODBC driver manager), while Workbench uses another ODBC driver manager: iODBC and the two of them can’t coexist in the same system.

So for Linux and Mac we prefer to go in a different direction and use FreeTDS, an alternate ODBC driver for Microsoft SQL Server. As a bonus, the very same driver can be used for connecting to Sybase Adaptive Server Enterprise, an RDBMS also supported in the MySQL Workbench Migration Wizard.

In spite that you can find FreeTDS in the software repositories of several modern Linux distributions, you are encouraged to download it and compile it yourself. Most distros provide fairly old FreeTDS versions and virtually all of them come linked against unixODBC, which is not supported by Workbench. Fortunately, the process for compiling and installing it is pretty straightforward. Here are the steps you should follow:

  1. Go to the FreeTDS homepage and download a recent version of the driver’s source code. Make sure that you have at least version 0.92. We advise you to use the latest stable version rather than a nightly snapshot. Save this tarball in a directory where you have write permissions (something like /home/<your_user>/freetds would do).
  2. If you are in Linux, make sure that you have installed the essential build tools (C/C++ compiler, linker, etc.) and the development files for iODBC. Try running this from the command line if your are using a Debian based distro like Ubuntu:
    $> sudo apt-get install build-essential libiodbc2-dev

    If your distro uses rpm packages (Red Hat, Fedora, etc.) the command would be like this:

    $> su -
    $> yum install make automake gcc gcc-c++ libiodbc-devel

    Mac users can skip this step since the apropriate development files are already included in their system.

  3. Since there are a couple of options you would have to pass to the driver’s configure script, we have created a script file to help you pass this with no pain. You can find this script file in /usr/share/mysql-workbench/extras/build_freetds.sh if you are in Linux or inMySQLWorkbench.app/Contents/SharedSupport/build_freetds.sh if you use a Mac. Locate this helper script and copy it to the same directory where you put the driver’s tarball.
  4. Now it’s the time to actually build the driver. If you are not already there, cd to the directory where you put the helper script and run the helper script from there:
    $> ./build_freetds.sh

    Wait until the compilation finishes.

  5. Now that the driver is built, you should proceed to install it by running make install as root. And there you go, you should have the FreeTDS driver installed inside the /usr/lib or the /usr/local/lib directory. It will be named libtdsodbc.so.
  6. Proceed to register the installed FreeTDS driver as explained here.

Now you should be ready to use the driver to migrate Microsoft SQL Server and Sybase ASE databases to MySQL using the MySQL Workbench Migration Wizard.

Sergio de la Cruz

How-To: Migrate PostgreSQL databases to MySQL using the MySQL Workbench Migration Wizard

MySQL Workbench 5.2.41 introduced the new Migration Wizard module. This module allows you to easily and quickly migrate databases from various RDBMS products to MySQL. As of Workbench 5.2.44 you can migrate databases from Microsoft SQL Server, PostgreSQL and Sybase Adaptive Server Enterprise. It also provides for generic migrations, i.e. migrations from other RDBMSes that are not explicitely supported, provided that they have a well behaved ODBC driver. More on this in an upcoming post…

Additionally, you can use the Migration Wizard to perform MySQL to MySQL database migrations, which can be used for tasks such as copying a database across servers or migrating data across different versions of MySQL.

We have already described in a previous post how to use the Migration Wizard to migrate a Microsoft SQL Server database to MySQL. In this post we are going to migrate a PostgreSQL database to MySQL using the Migration Wizard.

So lets get our hands dirty and run through the Migration Wizard in order to migrate a PostgreSQL database to MySQL. In the rest of this post I assume that you have:

  • A running PostgreSQL instance in which you have proper access to the database you want to migrate. (I’ll call this database from now on the source database). I have a PostgreSQL instance running in a computer (an Ubuntu 12.04 box) in my local network. I have installed on top of it the Pagila Sample Database from pgFoundry. I’m using the standard postgres user, which has full privileges. You can use whatever PostgreSQL version you have at hand, but keep in mind that the Migration Wizard officially supports PostgreSQL 8.0 and newer so older PostgreSQL versions might not work.
  • A running MySQL Server instance with proper user access. The Migration Wizard supports MySQL versions from 5.0 onwards so make sure you have a supported version. For this tutorial I’m using MySQL Server 5.5.27 CE installed in the very same Ubuntu PC where PostgreSQL is running.
  • MySQL Workbench 5.2.44 or newer. This time I’m going to use Workbench for Linux. I have Workbench running in another computer with Ubuntu 12.10 installed.

Lets start now…

Download, compile (Linux and Mac only) and install a PostgreSQL ODBC driver

You need to install an ODBC driver for PostgreSQL in the machine where you installed MySQL Workbench. Please follow the instructions here.

Open MySQL Workbench and start the Migration Wizard

From the main MySQL Workbench screen you can start the Migration Wizard by clicking on the Database Migration launcher in the Workbench Central panel or through Database > Migrate in the main menu.

wb_initial_screen

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

Overview Page of the MySQL Workbench Migration Wizard

Read carefully the Prerequisites section. You can read there that you need an ODBC driver for your source RDBMS installed. If you have installed the psqlODBC driver as explained in the previous section, you are good to go.

Set up the parameters to connect to your source database

Click on the Start Migration button in the Overview page to advance to the Source Selection page. In this page you need to provide the information about the RDBMS you are migrating, the ODBC driver to use and the parameters for the connection. The name of the ODBC driver is the one you set up when you registered your psqlODBC driver with the driver manager (psqlODBC, for example).

If you open the Database System combo box youll find a list of the supported RDBMSes. Select PostgreSQL from the list. Just below it theres another combo box named Stored Connection. It will list saved connection settings for that RDBMS. You can save connections by marking the checkbox at the bottom of the page and giving them a name of your preference.

The next combo box is for the selection of the Connection Method . This time we are going to select ODBC (manually entered parameters) from the list since we are going to manually type the parameters for our PostgreSQL connection. Other alternatives are ODBC data sources and ODBC connection strings.

Now its the time for putting the parameters for your connection. In the Driver text field, type the ODBC driver name from the previous step (psqlODBC). Fill the remaining parameters (Hostname, Port, Username, Password and Database) with the appropriate values. The psqlODBC driver does not allow to connect without specifying a database name, so make sure you put the name of your database before attempting to connect. At this point you should have something like this:

Source Selection Page of the MySQL Workbench Migration Wizard

Click on the Test Connection button to check the connection to your PostgreSQL instance. If you put the right parameters you should see a message reporting a successful connection attempt.

Set up the parameters to connect to your target database

Click on the Next button to move to the Target Selection page. Once there set the parameters to connect to your MySQL Server instance. When you are done click on the Test Connection button and verify that you can successfully connect to it.

Target Selection Page of the MySQL Workbench Migration Wizard

Select the schema(ta) to migrate

Click on the Next button to move to the next page. The Migration Wizard will communicate to your PostgreSQL instance to fetch a list of the schemata in your source database.

Fetching schema list in the MySQL Workbench Migration Wizard

Verify that all tasks have successfully finished and click on the Next button to move forward. You will be given a list of schemata to select the ones to migrate. The Schema Selection page will look like this:

Schema Selection Page of the MySQL Workbench Migration Wizard

Select the Pagila sample database from the list and its default schema public. Now look at the options below. A PostgreSQL database is comprised of one catalog and one or more schemata. 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 schemata in our source database. We can either keep all of the schemata 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. Lets select the second option since we only have one schema and we are not particularly interested in keeping its meaningless public 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.

Reverse Engineering the Source Schemata the MySQL Workbench Migration Wizard

It may take some time, depending on how fast is your connection to the server, your PostgreSQL server load and your local machine load. Wait for it to finish and verify that everything went well. Then move to the next page. In the Source Objects page you will have a list with the objects that were retrieved and are available for migration. It will look like this:

Source Objects Page of the MySQL Workbench Migration Wizard

As you can see the Migration Wizard discovered table objects in our source database. If you click on the Show Selection button you will be given the oportunity to select exactly which of them you want to migrate as shown here:

Source Objects Page of the MySQL Workbench Migration Wizard (expanded)

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 there too). By using the arrow buttons you can filter out the objects that you dont want to migrate. At the end, dont 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 youll 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 might have to wait a little bit before the Manual Editing page is ready but you’ll end up with something like this:

Manual Editing Page of the MySQL Workbench Migration Wizard

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 Pagila row and rename it.

Manual Editing Page of the MySQL Workbench Migration Wizard (All Objects)

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, defalt values and other attributes.

Manual Editing Page of the MySQL Workbench Migration Wizard (Column Mappings)

Run the resultant MySQL code to create the database objects

Move to the Target Creation Options page. It will look like this:

Target Creation Page of the MySQL Workbench Migration Wizard

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 PostgreSQL code will be executed in the target MySQL server. You can view its progress in the Create Schemata page:

Create Schemata Page of the MySQL Workbench Migration Wizard

Once the creation of the schemata and their objects 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:

Create Target Results Page of the MySQL Workbench Migration Wizard

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 for the transference of data from the source SQL Server database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.

Data Transfer Setup Page of the MySQL Workbench Migration Wizard

There are two sets of options here. The first one allows you to perform a live transference and/or to dump the data into a batch file that you can run later. The other set of options gives you a way to tune up 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 will take a little while to copy the data. At this point the corresponding progress page will look familiar:

Creating Target Schemata in the MySQL Workbench Migration Wizard

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

Report Page of the MySQL Workbench Migration Wizard

And that should be it. Click on the Finish button to close the Migration Wizard.

A little verification step

Now that the Pagila database was successfully migrated, let’s see the results. Open an SQL Editor page associated with your MySQL Server instance and query the Pagila database. You can try something like SELECT * FROM pagila.actors. You should get something like this:

Querying the target database to verify the migration

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. Live long and prosper!

Sergio de la Cruz

Set up and configure PostgreSQL ODBC drivers for the MySQL Workbench Migration Wizard

If you plan to use the MySQL Workbench Migration Wizard to migrate databases from PostgreSQL to MySQL you first need to configure an ODBC driver to connect to your PostgreSQL server.

In this post I’ll cover how to set up and configure psqlODBC, the official ODBC driver for PostgreSQL.

Installing the psqlODBC Driver

The procedure is different for every platform so make sure to follow the instructions that correspond to the OS where you have MySQL Workbench installed. The driver needs to be installed in that machine, as explained in the MySQL Workbench documentation.

Important: Always install the 32 bit version of the driver, even on x64 systems, as MySQL Workbench is a 32 bits application.

Windows

If you are running MySQL Workbench on Windows download the MSI package for psqlODBC. Go to its download page (http://www.postgresql.org/ftp/odbc/versions/msi/) and pick the most recent file from the list. (The most recent one is at the bottom of the page.)

Unzip the downloaded file and install the provided MSI package. This is all you have to do. You can skip the rest of this post.

Linux

The Migration Wizard uses iODBC as a driver manager for all of its ODBC connections in Linux. This may give you some troubles because most Linux distributions provide ODBC drivers compiled against unixODBC. This is another driver manager not supported by MySQL Workbench so you won’t be able to use those drivers unless you compile them against iODBC. Here’s what you should do.

Make sure that you have iODBC installed. If you are using Debian, Ubuntu or another Debian based distro, type this command in your terminal:

$> sudo apt-get install iodbc libiodbc2-dev libpq-dev libssl-dev

For RPM based distros (RedHat, Fedora, etc.) type this command instead of the previous one:

$> sudo yum install iodbc iodbc-dev libpqxx-devel openssl-devel

Now we need to install the PostgreSQL ODBC drivers.

Download the psqlODBC source tarball from here. Use the latest version available for download. As of this writing the latest version corresponds to the file psqlodbc-09.01.0200.tar.gz. Extract this tarball to a directory in your hard drive and open a terminal and cd into that directory.

Type this in the terminal window:

$> ./configure --with-iodbc --enable-pthreads
$> make
$> sudo make install

Verify that you have the file psqlodbcw.so in the /usr/local/lib directory.

Mac OS X

To build psqlODBC in Mac OS X, you need to have Xcode and the “Command Line Tools” additional component installed, so that the gcc compiler is available. You can get Xcode for free in the AppStore and install the Command Line Tools from Preferences -> Downloads.

Download the psqlODBC source tarball from here. Use the latest version available for download. As of this writing the latest version corresponds to the file psqlodbc-09.01.0200.tar.gz. Extract this tarball to a directory in your hard drive and open a terminal and cd into that directory.

Type this in the terminal window:

$> ./configure --with-iodbc --enable-pthreads
$> CFLAGS="-arch i386 -arch x86_64" make
$> sudo make install

Registering your ODBC Driver (Linux and Mac OSX)

Open the iODBC Data Source Administrator application either by typing iodbcadm-gtk in the command line or by launching it from the Overview page of the MySQL Workbench Migration Wizard, using the Open ODBC Administrator button. From the main MySQL Workbench screen you can start the Migration Wizard by clicking on the Database Migration launcher in the Workbench Central panel or through Database > Migrate in the main menu.

Go to the ODBC Drivers tab. You should have something like this:

iodbcadm-gtkClick on the Add a driver button and fill the values as shown here:

psqlODBC_config

After you click OK you should have the psqlODBC driver registered.

Sergio de la Cruz

How-To: Guide to Database Migration from Microsoft SQL Server using MySQL Workbench

MySQL Workbench 5.2.41 introduces a new Migration Wizard module. This module allows you to easily and quickly migrate databases from various RDBMS products to MySQL. In this initial version, migrations from Microsoft SQL Server are supported, but it should also be possible to migrate from most ODBC capable RDBMS as well, using its generic RDBMS support. Additionally, you can use it to perform MySQL to MySQL database copies, which can be used for tasks such as copying a database across servers or migrating data across different versions of MySQL.

So let’s get our hands dirty and run through the Migration Wizard in order to migrate a Microsoft SQL Server database to MySQL. In the rest of this post I assume that you have:

  • A running SQL Server instance in which you have proper access to the database you want to migrate. (I’ll call this database from now on the source database). I have a remote SQL Server 2000 instance available and the sample Northwind database on top of it. I’m using the standard “sa” user, which has full privileges. You can use whatever SQL Server version you have at hand. Keep in mind that the Migration Wizard officially supports SQL Server 2000 and newer so older SQL Server versions might not work.
  • A running MySQL Server instance with proper user access. The Migration Wizard supports MySQL versions from 5.0 onwards so make sure you have a supported version. For this tutorial I’m using MySQL Server 5.5.15 CE installed in the same PC where MySQL Workbench is running.
  • MySQL Workbench 5.2.41 or newer for Windows. The Migration Wizard is also available in the Linux and Mac versions of MySQL Workbench, but running it from Windows will save us from installing an ODBC driver to connect to our SQL Server instance. Other blog posts will follow on how to proceed in those cases.

Let’s start now…

 Open MySQL Workbench and start the Migration Wizard

From the main MySQL Workbench screen you can start the Migration Wizard by clicking on the Database Migration launcher in the Workbench Central panel or through Database –> Migrate in the main menu.

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

2wb_migration_wizard_overviewRead carefully the Prerequisites section. You can read there that you need an ODBC driver for your source RDBMS installed.  Any recent version of Windows comes with some ODBC drivers installed. For Windows 2000 and earlier these can be installed with the Microsoft Data Access Components (MDAC).

You should check if you have an ODBC driver for SQL Server. Start the Windows ODBC Data Source Administrator from MySQL Workbench using the Plugins –> Start ODBC Administrator menu item or just open a Windows terminal and type odbcad32.exe. Once there, go to the Drivers tab. You should see something like this:

3windows_odbc_data_sources1

As you can see, I already have two SQL Server ODBC drivers installed. The first one listed here (named “SQL Server”) comes preinstalled with Windows (you should have it as well). This driver is frozen at the level of functionality provided by SQL Server 2000 and it should be enough for you if your database doesn’t make use of the new features and datatypes introduced after this SQL Server version. If you have a SQL Server instance in the same machine where you installed MySQL Workbench then you should also have the second driver listed in the image (named “SQL Server Native Client…”). This one comes with SQL Server and fully supports the companion SQL Server version. If you don’t have it listed, you can download and install the Microsoft SQL Server 2012 Native Client. This is compatible with SQL Server 2012 as well as with previous SQL Server versions.

Once you take your pick on the driver to use, write down somewhere its name as shown in the ODBC Data Source Administrator. You’ll need this name to connect to your SQL Server instance from the Migration Wizard. Let’s go back to the Migration Wizard (you can close the ODBC Data Source Administrator now) and start the migration process.

Set up the parameters to connect to your source database

Click on the Start Migration button in the Overview page to advance to the Source Selection page. In this page you need to provide the information about the RDBMS you are migrating, the ODBC driver to use and the parameters for the connection.

If you open the Database System combo box you’ll find a list of the supported RDBMSes. Select Microsoft SQL Server from the list. Just below it there’s another combo box namedStored Connection. It will list saved connection settings for that RDBMS. You can save connections by marking the checkbox at the bottom of the page and giving them a name of your preference.

The next combo box is for the selection of the Connection Method. This time we are going to select ODBC (native) from the list since we are using the native ODBC drivers provided by Microsoft. Other alternatives are ODBC data sources and ODBC FreeTDS (FreeTDS is a popular open source driver for Microsoft SQL Server and Sybase).

Now it’s the time for putting the parameters for your connection. In the Driver text field, type the ODBC driver name from the previous step.

In the Server field put the values that identify your machine and your SQL Server instance name. If you don’t recall these, go to SQL Server Management Studio and connect to your server. Then right click on the server icon in the Object Explorer and the name will be displayed in the new window that appears. If you have SQL Server Express Edition installed in your local machine and you haven’t change the server name, then the default“localhost\SQLEXPRESS” should work for you. Another option is to put your server IP address instead of the host name. You can also specify a port by adding a comma after the server name/IP (E.g “127.0.0.1,1433”). The instance name is optional and defaults to the default SQL Server instance in the source host.

Now put your credentials (user name and password) to connect to the server. If you know the name of the database you want to migrate, put it in the Database field. Otherwise leave it blank and you will be later given a list to select your database there. At this point you should have something like this:

4wb_migration_wizard_source_selection3

Click on the Test Connection button to check the connection to your SQL Server instance. If you put the right parameters you should see a message reporting a successful connection attempt.

Set up the parameters to connect to your target database

Click on the Next button to move to the Target Selection page. Once there set the parameters to connect to your MySQL Server instance. When you are done click on the Test Connection button and verify that you can successfully connect to it.

5wb_migration_wizard_target_selection

Select the schema(ta) to migrate

Click on the Next button to move to the next page. The Migration Wizard will communicate to your SQL Server instance to fetch a list of the catalogs and schemata. If you left blank theDatabase 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.

6wb_migration_wizard_schemata_fetching

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. The Schema Selection page will look like this:

7wb_migration_wizard_schemata_selection1

Select the Northwind sample database from the list and its default schema dbo. Now look at the options below. A SQL Server database is comprised of one catalog and one or more schemata. 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 schemata in our source database. We can either keep all of the schemata 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 since we only have one schema and we are not particularly interested in keeping its meaningless dbo 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.

8wb_migration_wizard_reverse_engineering

It may take some time, depending on how fast is your connection to the server, your SQL Server load and your local machine load. Wait for it to finish and verify that everything went well. Then move to the next page. In the Source Objects page you will have a list with the objects that were retrieved and are available for migration. It will look like this:

9wb_migration_wizard_object_selection1

As you can see the Migration Wizard discovered table and view objects in our source database. Note that only the table objects are selected by default to be migrated. You can select the view objects too, but you would have to provide their corresponding MySQL equivalent code later (no automatic migration is available for them yet) so let’s leave them off for now. The same applies for stored procedures, functions and triggers.

If you click on the Show Selection button you will be given the oportunity to select exactly which of them you want to migrate as shown here:

10wb_migration_wizard_object_selection_expanded1

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 as you can see in the image above). 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 might have to wait a little bit before the Manual Editing page is ready but you’ll end up with something like this:

11wb_migration_wizard_object_editing1

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 Northwind row and rename it.

12wb_migration_wizard_object_editing_expanded1

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.

13wb_migration_wizard_object_editing_expanded2

Run the resultant MySQL code to create the database objects

Move to the Target Creation Options page. It will look like this:

14wb_migration_wizard_target_creation_options2

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 Create Schemata page:

15wb_migration_wizard_target__creation

Once the creation of the schemata and their objects 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:

16wb_migration_wizard_create_target_results1

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 for the transference of data from the source SQL Server database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.

17wb_migration_data_transfer_setup

There are two sets of options here. The first one allows you to perform a live transference and/or to dump the data into a batch file that you can run later. The other set of options gives you a way to tune up 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 will take a little while to copy the data. At this point the corresponding progress page will look familiar:

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

19wb_migration_report

And that should be it. Click on the Finish button to close the Migration Wizard.

A little verification step

Now that the Northwind database was successfully migrated, let’s see the results. Open an SQL Editor page associated with your MySQL Server instance and query the Northwind database. You can try something like “SELECT * FROM Northwind.categories”. You should get something like this:

20wb_northwind_sqlide

And why not create an EER diagram from the migrated database? Click on Create EER Model From Existing Database in the main screen of MySQL Workbench and 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:

21wb_northwind_eer.PNG

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. Live long and prosper!

Sergio de la Cruz