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
- Review the migrated objects and apply changes if it is necessary to
- Migrate the source data to the target database
Migrating Database via MySQL Workbench
- 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
- MySQL migration wizard will open the ODBC manager available for your system
- Choose Database > Migrate menu item to open the migration wizard. Click
"Start Migration" to continue with the process.
- 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.
- 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.
- Choose the target schema and mapping method on the "Schemas Selection" page.
Click "Next" button.
- Select the discovered objects to migrate on "Source Objects" page
and "Next" button
- 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.
- Select how the schema will be created on "Target Creation Options" page
and click "Next" button
- 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
Finally, the final report logging the overall migration process is displayed on
"Migration Report" page.