[MySQL] MySQL 마이그레이션

MySQL 서버에서 다른 MySQL 서버로 전체 데이터베이스 중에 테이블 한개 도는 여러개를 복사하는 방법에 대해 기술해보겠습니다. 그 중 MySQL Workbench의 Migration Wizard 기능을 이용해서 서버간 데이터를 복사하는 방법입니다.

MySQL Workbench의 Migration Wizard 기능을 이용하여 MySQL 서버간에 데이터를 복사하는 방법 혹은 MySQL Workbench의 Data Export와 Data Import(또는 커맨드라인에서 Data Export와 Data Import)를 사용하는 방법도 있습니다. 후자의 경우가 더 일반적입니다.

물론 속도의 차이가 있지만 편리함으로 다지자면 MySQL Workbench의 Migration Wizard 기능을 이용하는 것이 많이 편리합니다. 그래서 그 방법을 정리해보려고 합니다.

MySQL Workbench의 Mgration Wizard 기능을 사용하는 방법은 아래 링크에서 확인할 수 있습니다.

http://dev.mysql.com/doc/workbench/en/wb-migration-overview-steps.html

10.2.1 A Visual Guide to Performing a Database migration

This example will migrate a Microsoft SQL Server database to MySQL, and include a screenshot for each step.

From MySQL Workbench, choose Database, Migrate to open the migration wizard.

10.2 MySQL Workbench migration: Start

Read the migration wizard overview:

10.3 MySQL Workbench migration: Overview

It describes the prerequisites and requirements that should be understood before proceeding further. The Open ODBC Administrator option will load odbcad32.exe, and is used to confirm that the ODBC Driver for SQL Server is installed, and to make configuration changes if needed.

Click Start Migration to continue.

Source Selection

Select the source RDBMS that is migrating to MySQL. Choose the Database System that is being migrated, and the other connection parameters will change accordingly.

10.4 MySQL Workbench migration: Source Selection (Parameters)

Target Selection

The target is the MySQL database that will contain the newly migrated database. The current Workbench MySQL connections will be available here, or you can choose Manage DB Connections to create a new connection.

10.5 MySQL Workbench migration: Target selection

Fetch Schemas List

The Schemas list is retrieved from both the source and target RDBMS. This is an automated and informational step that reports connection related errors and/or general log information. press Next to continue.

10.6 MySQL Workbench migration: Fetch Schemat list

Schemas Selection

Choose the schemata you want to migrate.

"Schema Name Mapping Method" options while migrating Microsoft SQL Server:

• Keep schemata as they are: Catalog.Schema.Table -> Schema.Table; This will create multiple databases, one per schema.
• Only one schema: Catalog.schema.Table -> Catalog.Table: Merges each schema into a single database.
• Only one schema, keep current schema names as a prefix: Catalog.Schema.Table -> Catalog.Schema_table: Preserves the schema name as a prefix.

10.7 MySQL Workbench migration; Schemata Selection

Reverse Engineer Source

The source metadata is fetched from the source RDBMS, and reverse engineered. This is an automated and informational step that reports related errors and/or general log information. View the logs and then press Next to continue.

10.8 MySQL Workbench migration: Reverse Engineer Source

Source Objects

The discovered objects from the Reverse Engineer Source stage are revealed and made available. This includes Table, View, and Routine objects, with only the Table objects being selected by default.

10.9 MySQL Workbench migration: Source Objects

Migration

The migration process now converts the selected objects into MySQL compatible objects. view the logs and then proceed.

10.10 MySQL Workbench migration: Migration

Manual Editing

There are three sections to edit here, which are selected via the View select box on the top right. The Show Code and Messages button is available with every view, and it will show the generated MySQL code that corresponds to the selected object.

• Migration Problems: This will either report problems or display "No mapping problems found." It is an informational screen.
• All Objects: An object view that allows you to view and edit the object definitions. Double-click on a row to modify a target objects name.
• Column Mapping: Shows all of the table column mappings, and allows you to individually review and fix the mapping for all column types, default values, and other attributes.

10.11 MySQL Workbench migration: Manual Editing (Migration problems)

10.12 MySQL Workbench migration: Manual Editing (All objects)

10.13 MySQL Workbench migration: Manual Editing (Column Mappings)

Target Creation Options

The schema may be created by either adding it to the target RDBMS, creating an SQL script file, or both.

10.14 MySQL Workbench migration: Target Creation Options

Create Schema

Now the schema is created. The complete log is also available here.

10.15 MySQL Workbench migration: Create Schema

Create Target Results

The generated objects are listed here, along with the error messages if any exist.

The migration code may also be viewed and edited here. To make changes, select an object, edit the query code, and press Apply. Repeat this process for each object that will be edited. And then, press Recreate Objects to save the results.

The Recreate Objects operation is required to save any changes here. it will then execute the previous migration step (Create Schema) with the modified code, and then continue the migration process. This also means that the previously saved schema will be dropped.

10.16 MySQL Workbench Migration: Create Target Results

Data Transfer Setup

The next step transfers data from the source RDBMS to the target MySQL database. 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.

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.

10.17 MySQL Workbench Migration: Data Transfer Setup

Bulk Data Transfer

Depending on the selected option, this will either transfer the data to the target RDBMS (default), generate a simple script for the online data transfer, or generate script to execute on the source host that then generates a Zip file containing both the transfer script and data that will be executed on the target host. Optionally, view the logs to confirm.

10.18 MySQL Workbench Migration: Bulk Data Transfer

Migration Report

And finally, the migration report is available and summarizes the entire migration process.

10.19 MySQL workbench Migration: Migration Report

Pressing Finish will close the migration window. If you chose the online copy then the database can now be viewed within the MySQL Workbench SQL editor.

10.20 MySQL Workbench Migration: Viewing the Migrated Database

If a MySQL Workbench SQL Editor tab is already opened, then the schema list within the Object Browser must be refreshed in order to view the newly imported schema.

위 방법은 전체 중 테이블 하나 또는 몇개를 복하나는 방법은 설명되어 있지 않습니다. 그래서 그 방법은 다른 글로 설명할 것입니다.

MySQL Workbench의 Migration Wizard 기능은 원래 MySQL 서버간 데이터 복사를 목적으로 한 것이 아니라 버전이 낮은 서버에서 높은 서버로 스키마, 데이터, 스토어드프로시저, 트리거 등을 마이그레이션 할 목적으로 제공하는 것입니다. 물론 타 DBMS(MS-SQL과 다른 DBMS)와 ODBC로 연결하여 데이터를 마이그레이션하는 기능도 제공합니다. 그러나 타 DBMS는 스키마, 데이터만 자동으로 마이그레이션해주는데 스토어드프로시저, 트리거 등은 지원하지 않는다고 합니다.

MySQL Workbench의 Migration Wizard 기능을 다이어그램으로 설명합니다.

위 MySQL 설명자료에 가보면 위와 같은 그림이 있습니다. Client Side라는 것은 간단히 PC라고 생각하면 됩니다. (물론 MS 계열 서버라면 DBMS가 설치되어 있는 한쪽에 MySQL Workbench를 설치해서 실행해도 된다. Linux도 같다)

MySQL 서버간 카피할때 한가지 주의할 점이 있습니다. 카피한다고 하다가 저장할 DBMS에 있는 자료를 모두 삭제할 수도 있으므로 매우 주의해야 합니다.

Migration Wizard 기능을 이용할 수 있는 DBMS는 아래와 같습니다.

• Microsoft SQL Server 2000
• Microsoft SQL Server 2005
• Microsoft SQL Server 2008
• Microsoft SQL Server 2012
  
  
• MySQL Server 4.1 이상(복사되는 소스 DBMS) -> MySQL Server 5.1 이상(복사해서 저장할 DBMS)
  
  
• PostgreSQL 8.0 이상
  
  
• SQL Anywhere
  
  
• SQLite
  
  
• Sysbase Adaptive Server Enterprise 15.x 이상

오라클에서 MySQL로 마이그레이션은 지원하고 있지 않습니다.

Migration Wizard와 Data Export/Import 속도 차이

---

Migration Wizard와 Data Export / Import의 속도 차이가 있습니다. 편리한 점으로 보면 MySQL Workbench의 Migration Wizard가 훨씬 편합니다.

그러나 속도를 보면  MySQL Workbench의 Migration Wizard가 Data Export/Import를 수행한 것보다 거의 세배 정도 늦습니다. 다음은 Data Export / Import를 순차적으로 실행한 화면입니다.

아래 그림은 MySQL Workbench의 Migration Wizard를 실행한 화면입니다. 7분 40초가 걸렸습니다. 클릭하는 시간은 포함하면 약 8분이 소요됩니다.

Data Export / Import와 Migration Wizard의 결과를 보면 전자가 3배정도 빠름을 알 수 있습니다. 편리함이냐 속도냐를 기준으로한 비교 선택이 필요합니다.