What is migration in mysql workbench?

The MySQL Workbench Migration Wizard uses ODBC to connect to a source database, except for MySQL. You will need the ODBC driver installed that corresponds to the database you want to migrate from. For example, PostgreSQL can be migrated with the psqlodbc ODBC driver; Microsoft SQL Server can be migrated using the native Microsoft SQL Server driver on Windows or with FreeTDS on Linux and macOS.

The following figure shows the general components involved in an ODBC connection: An ODBC driver for MySQL server and the other relational database management system, the ODBC Driver Manager, and MySQL Workbench.

Figure 10.1 MySQL Workbench migration installation diagram

When specifying the source RDBMS, you can either use a data source configured externally or provide the individual connection parameters to MySQL Workbench. If you already have an ODBC Data Source configured in your system, then you can use that in MySQL Workbench.

Note

The migration process does not support source or target RDBMS connections through SSH.

A workaround is to set up an encrypted tunnel, and then treat the MySQL target as a standard TCP (unencrypted) connection.

The world's most popular open source database

10.2.3 Migrating from Unsupported (Generic) Databases

Most ODBC compliant databases may be migrated using the generic database support. In this case, code objects will not be retrieved from the source database; only tables and data.

When using the generic support, column data types are mapped using the following steps:

1. It searches for the first entry in the for the source type name. If the length/scale ranges of the entry matches the source column, it will pick that type. Otherwise, it continues searching.

2. If no matches were found in the generic table, then it tries to directly map the source type to a MySQL type of the same name.

3. If the source type name doesn't match any of the MySQL data types, then it is not converted and an error is logged. From here you can specify the target datatype in the Manual Object Editing step of the wizard.

The world's most popular open source database

10.8.10 Data Transfer and Migration Setup

Transfers data from the source RDBMS to the target MySQL database (see the figure that follows). The setup screen includes the following options:

Data Copy:

• Online copy of table data to target RDBMS: This (default) will copy the data to the target RDBMS.

• Create a batch file to copy the data at another time: The data may also be dumped to a file that can be executed at a later time, or be used as a backup. This script uses a MySQL connection to transfer the data.

• Create a shell script to use native server dump and load abilities for fast migration: Unlike the simple batch file that performs a live online copy, this generates a script to be executed on the source host to then generate a Zip file containing all of the data and information needed to migrate the data locally on the target host. Copy and extract the generated Zip file on the target host and then execute the import script (on the target host) to import the data into MySQL using a LOAD DATA call.

  This faster method avoids the need to traffic all data through MySQL Workbench, or to have a permanent network connection between the MySQL servers.

  Note

  This option was added in MySQL Workbench 6.3.0.

Options:

• Truncate target tables before copying data: In case the target database already exists, this will delete said data.

• Worker tasks: The default value is 2. This is the number of tasks (database connections) used while copying the data.

• Enable debug output for table copy: Shows debugging information.

Figure 10.63 MySQL Workbench Migration: Data Transfer Setup

10.2.2 Migrating from Supported Databases

When a supported RDBMS product is being migrated, the MySQL Workbench Migration Wizard will automatically convert as much information as it can, but you may still be required to manually edit the automatically migrated schema for difficult cases, or when the default mapping is not as desired.

Generally speaking, only table information and its data are automatically converted to MySQL. Code objects such as views, stored procedures, and triggers, are not. But supported RDBMS products will be retrieved and displayed in the wizard. You can then manually convert them, or save them for converting at a later time.

The following RDBMS products and versions are currently tested and supported by the MySQL Workbench Migration Wizard, although other RDBMS products can also be migrated with Section 10.2.3, “Migrating from Unsupported (Generic) Databases”:

• Microsoft SQL Server 2000 and later

• Microsoft Access 2007 and later

• MySQL Server 5.6 and higher as the source, and MySQL Server 5.6 and higher as the target

• PostgreSQL 8.0 and later

• SQL Anywhere

• SQLite

• Sybase Adaptive Server Enterprise 15.x and later

What is migration Workbench?

What does migration mean in SQL?

What does migration mean in database?

How do I migrate data in MySQL Workbench?