Migrate to MySQL with MySQL Workbench

MySQL, a free open-source relational DBMS, has garnered extensive adoption among global enterprises owing to due remarkable speed, scalability, efficiency and user-friendly interface. The database management system is capable of handling large databases in hi-load environments with concurrent user access. Other benefits of MySQL are: high performance, reliable security, integration with Web, compatibility with all popular hardware platforms and OS, wide range of free APIs, tools and frameworks.

Many companies and organizations using proprietary DBMS may opt to migrate to MySQL driven by advantages pointed above. This article explores migration from other databases to MySQL using MySQL Workbench, the popular database management tool having easy to use interface. It has been designed for such database administration tasks as server configuration, user administration, database backup.

MySQL Workbench cam migrate from the following data sources: SQL Server 2000 and higher, MS Access 2007 and higher, MySQL Server 5.6 and higher, PostgreSQL 8.0 and higher, SQLite and some other generic databases.

Key features of MySQL Workbench

• Manage database schemas and objects
• Compose and optimize SQL queries
• Configure MySQL server
• Manage users and permissions
• Import and export MySQL script files
• Run database backup and recovery
• Migrate from PostgreSQL, SQL Server, MS Access and SQLite to MySQL

Generic Steps of Database Migration

• Connect to the source DBMS and list all available databases/schemas
• Prepare selected database/schemas for migration to the target DBMS. This step also includes renaming of objects/schemas depending on the name mapping method chosen for the database migration.
• Migrates objects (schema, table definitions, indexes, foreign keys, views, triggers, stored procedures and functions) from the source database into MySQL equivalents
• Review the migrated objects and apply changes if it is necessary to correcting errors
• Migrate the source data to the target database

Migrating Database via MySQL Workbench

1. Install ODBC Driver for the database you need to migrate to MySQL. For example, SQL Server can be migrated using the native Microsoft SQL Server driver on Windows or with FreeTDS on Linux and macOS. PostgreSQL can be migrated using the psqlodbc ODBC driver.
2. MySQL migration wizard will open the ODBC manager available for your system
3. Choose Database > Migrate menu item to open the migration wizard. Click "Start Migration" to continue with the process.
   
4. Choose the source database from "Database System" drop-down on "Source Selection" page and enter required connection details. Activate "Store connection for future usage as" option to save the connection details for next use. Click "Test Connection" to validate entered parameters and then click "Next" button.
5. Select the current MySQL connection or create a new one on the "Target Selection" page. Choose "Manage DB Connections" > "Input Details" to create new connection. Click "Test Connection" to validate entered parameters and then click "Next" button.
6. Choose the target schema and mapping method on the "Schemas Selection" page. Click "Next" button.
   
7. Select the discovered objects to migrate on "Source Objects" page and "Next" button
   
8. On the "Manual Editing" page select "View > All Objects" on the top right to edit the settings. Pay attentions to "Migration Message" part of the window as it contains information about possible problems.
9. Select how the schema will be created on "Target Creation Options" page and click "Next" button
10. On "Data Transfer Setup" configure the following options:
    • Online copy of table data to target RDBMS - select it to copy the data to the target RDBMS
    • Create a batch file to copy the data at another time - select it to dump the data into a script file that can be executed time or used as a backup
    • Create a shell script to use native server dump and load abilities for fast migration - select it to generate a host-side script that creates a local ZIP archive containing everything to be migrated directly on the target host. That data can be imported into MySQL database using LOAD DATA statement.

After completing these steps MySQL Workbench either transfer the data to MySQL server (default option), create MySQL script for the online data transfer or generate shell script that generates ZIP archive containing both the transfer script and data to migrate on the target host. You can see the results of the data migration on "Bulk Data Transfer" page.

Finally, the final report logging the overall migration process is displayed on "Migration Report" page.