MTULabo
  • 製品紹介
  • 導入事例
  • FAQ
  • MTULabo
  • ダウンロード
評価版ライセンス申込 有償版利用申込(見積依頼) MTU Advance

手軽で速い!MySQLへのデータ移行術

公開日 2014-04-08
更新日 2014-09-30

概要

    • このトピックでは、MTUの目玉機能と、INSERT文と比較して20倍速いとされる MySQL の LOAD DATA INFILE 文を組み合わせることで、移動効率を飛躍的に高める方法について説明します。
    • この方法を使った弊社のテスト事例では、特別な環境を用意すること無く、1000万件(CSVファイルにして約1.4GB)のデータを270秒で移行することができました(評価版/有償版の使用にて)。
    • この方法ではデータ移動の経路上に名前付きパイプを置くので、処理中のストレージ使用を(ログ出力を除き)ゼロに抑える事も可能です。
    • sshにより転送中データの機密性が保たれますので、例えばオンプレミスのOracle DatabaseからAmazon RDS for MySQLへのデータ移行にも安心して応用できます。
    • 下図にこの方法の概要を示します。

01_concept

準備

  1. 読者がこの記事を検証するにあたっては、次のものを準備してください。
    準備するもの 入手先 概要
    MTU v4.2 サンプル版 こちら 64-bit版、32-bit版どちらでも可
    MySQL (手元になければ) こちら 説明では Linux 用の MySQL 5.6 データベースを使いますが、Windows 版でも適用可能です。MySQL command line client はWindows 版をご用意ください。
    Tera Term こちら MySQLデータベースが Internet 上に置かれている想定の下に ssh の port fowarding (ポート転送)を介して通信を保護する目的で使います。
    同様の機能を有する Windows に対応した他の ssh でも利用可能です。
  2. MTU は取扱説明書(4.28MB)に解説された方法に従ってインストールしてください。
  3. 検証に必要なデータを格納するためのテーブルを準備してください。データ移行元のOracle Databaseの任意のスキーマ(記事ではLARGE_STORE_Bとします)と、移行先のMySQL ユーザ(記事ではscottとします)へ次のSQLを適用するとこの検証に必要なテーブルを作ることができます。
    Oracle Database (移行元) MySQL (移行先)
    create table NON_PART_TABLE
    ( VC2_01 varchar2(10)
    , DTM_01 date
    , NUM_01 number(13)
    , VC2_02 varchar2(15)
    , NUM_02 number(8,2)
    , DTM_02 date
    , CHR_01 char(8)
    , VC2_03 varchar2(12)
    , NUM_03 number(15)
    , VC2_04 varchar2(20)
    );
    create table NON_PART_TABLE
    ( VC2_01 varchar(10)
    , DTM_01 datetime
    , NUM_01 decimal(13)
    , VC2_02 varchar(15)
    , NUM_02 decimal(8,2)
    , DTM_02 datetime
    , CHR_01 char(8)
    , VC2_03 varchar(12)
    , NUM_03 decimal(15)
    , VC2_04 varchar(20)
    );
  4. 移行元 Oracle Database で次の無名PL/SQLブロックを実行して検証用データを10万件作成して下さい。10万件はサンプル版で扱える最大の行数です。終わったらトランザクションをコミットして下さい。
    declare
    	epoch_time_b DATE;
    	epoch_time_e DATE;
    	rand_str varchar2(100);
    	const_A varchar2(52) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
    	const_B varchar2(156) := 'あいうえおわきくけこさしすせそたちつてとなにぬねのはひふけほまみむめもやゐゆゑよわをん!”#$%&’()';
    begin
    	epoch_time_b := to_date('1900-01-01','yyyy-mm-dd');
    	epoch_time_e := to_date('9999-12-31','yyyy-mm-dd');
    	for i in 1 .. 100000 loop
    		rand_str := DBMS_RANDOM.STRING('A',55);
    		insert into NON_PART_TABLE
    		(  "VC2_01" /* varchar2(10)    */
    		,  "DTM_01" /* date            */
    		,  "NUM_01" /* number(13)      */
    		,  "VC2_02" /* varchar2(15)    */
    		,  "NUM_02" /* number(8,2)     */
    		,  "DTM_02" /* date            */
    		,  "CHR_01" /* char(8)         */
    		,  "VC2_03" /* varchar2(12)    */
    		,  "NUM_03" /* number(15)      */
    		,  "VC2_04" /* varchar2(20)    */
    		) values
    		( 'A_' || to_char(i, 'fm00000000')
    		, epoch_time_b + (i/86400)
    		, i
    		, substr(rand_str,1,15)
    		, DBMS_RANDOM.VALUE * 1000000
    		, epoch_time_e - (i/86400)
    		, translate( substr(rand_str,16,8/3), const_A, const_B)
    		, substr(rand_str,24,12)
    		, i * 4 / 1023
    		, translate( substr(rand_str,36,20/3), const_A, const_B)
    		);
    	end loop;
    end;
    /
  5. MTUの環境変数を書き換えて下さい。空欄はすでにある値を取り除くという意味です。環境変数設定ファイル:env.bat は下記の場所にあります。
    C:\ProgramData\PLUMSIX\mtu_4.2\x86 (32-bit版)
    C:\ProgramData\PLUMSIX\mtu_4.2\x64 (64-bit版)
    環境変数名 出荷時初期値 書き換える値
    USERID SYSTEM/MANAGER SYSTEM/パスワード@DB別名
    SRC_USER SCOTT NON_PART_TABLE表を作成したスキーマ名
    STDOUT 0×0000 0×0001
    STREAM_LOCATOR named_pipe://\\.\pipe\{C}.{X}
    QUERYFIX qryfix
    QUERYVAR qryvar “select * into NON_PART_TABLE from NON_PART_TABLE;”
    DATE_MASK YYYYMMDDHH24MISS YYYY-MM-DD HH24:MI:SS
    ENCLOSER ~~
    RECLENGTH true
    CHARSETID 0 MySQL command line client のセッションで有効なキャラクタセットと互換性のあるものを表2から選んで下さい。
    NOBYTEORDERMARK true

    表1.環境変数の設定値

    • 環境変数 CHARSETID の値は、MySQL の character_set_database システム変数か、または LOAD DATA INFILE 文の CHARACTER SET 句へ指定したキャラクタセットと互換性のある、Oracle Database のCHARSETID を選んで下さい。互換性の無いものを選ぶと文字化けによるエラーが発生します。
    • 日本語用としてしばしば利用されるキャラクタセットの候補と、両DB間の互換性について表にまとめます。
      Oracle Database キャラクタセット CHARSETID MySQL キャラクタセット
      AL32UTF8 873 utf8,utf8mb4
      JA16SJISTILDE 838 sjis,cp932
      JA16EUC 830 ujis,eucjpms

      表2.Oracle Database と MySQL の文字コード互換性

    • キャラクタセットのシンボル文字列から未知の CARSETID を求めるには NLS_CHARSET_ID 関数をお使いください。使用例を示します。
      SQL> select NLS_CHARSET_ID('AL32UTF8') as CHARSETID from dual;
      
       CHARSETID
      ----------
             873
      
      SQL>
  6. Tera Term を起動し、「設定」→「SSH転送」→「追加」を選択し、ポート転送のルールを作成して下さい。「ローカルのポート」には、未使用のポートを任意に選ぶことが出来ます。下記画像にlocalhost:13306 への着信を宛先ホスト:3306 へ転送するようなルールの設定事例を示します。
    02_TetaTerm_SSH_FW
  7. サーバー側のOpenSSL 構成や、公開鍵/秘密鍵のの配置等sshを利用する為の準備は事前に済ませておいてください。その後、Tera Term で移行先サーバへログインします。データ移行が終わるまで、接続を維持し続けてください。
    03_TetaTerm_SSH_AUTH

実行

    1. これで準備が整いました。データ移動を開始してみましょう。まずは、MTU を実行する為のコマンドプロンプトを開きます。デスクトップに「PLUMSIX」フォルダがあります。「MTU v4.2 xx-bit サンプル版」→「コマンド・プロンプト (MTU)」と順に選択してください。
      04_MTU_PROMPT
    2. カレントディレクトリにある Query.bat を実行すると、次のような表示が現れて動作が一時的に停止します。停止は LOAD DATA INFILE コマンドの投入後データ移動が開始されるまで続きます。
      C:\ProgramData\PLUMSIX\mtu_4.2\x86>query
      Multi-threaded Unloader (SAMPLE) version 4.2.1
      (c) 2003 Plumsix Co.,Ltd. All rights reserved.
      Windows 8 Professional (build 9200), 64-bit WOW64
      Number of NUMA nodes 1
      Number of physical processor packages 1
      Number of processor cores 4
      Number of logical processors 8
      Number of processor L1/L2/L3 caches 8/4/1
      任意問合せファイル出力を実行します...
      クライアント・キャラクタセットID は 873 (AL32UTF8) です。
      ネット・サービス名:xxx_mtu01 へユーザ名:SYSTEM として接続します。
      しばらくお待ちください...
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
      With the Partitioning, OLAP and Data Mining options
      接続先は SYSTEM@MTU01.PLUMSYS (ネット・サービス名:xxx_mtu01) です。
      サーバー・タイプは DEDICATED です。
      監査接続IDは 7272793 です。
      サーバーの日付時刻は 2014-04-03 17:29:03 です。
      対象スキーマは LARGE_STORE_B です。
      任意問い合わせを読取りました。(QUERYVAR) select * from NON_PART_TABLE
      キー・バリュー対定義ファイルを読込みます。C:\ProgramData\PLUMSIX\mtu_4.2\x86\input\bind.dat
      :b_job                        :SALESMAN
      :b_sal                        :        1,300
      :b_hiredate                   :1980-01-01
      複数の接続を確立します。要求された並列度は 4 です。
      空領域サイズチェックの閾値は 1024Mバイトです。
      データファイルと制御ファイルを出力中です。[84G] C:\ProgramData\PLUMSIX\mtu_4.2\x86\output
      ストリームの位置:named_pipe://\\.\pipe\{C}.{X}
      TableName                     :      NumRows:  Elapse:Rows/s:Bytes:  B/s:   Len
      NON_PART_TABLE                :    Executing     (← ここで一時停止)
    3. もう一つコマンドプロンプトを開いてください。環境変数PATHなどを設定した後、MySQL コマンド・ライン・クライアントを起動します。
      Microsoft Windows [Version 6.2.9200]
      (c) 2012 Microsoft Corporation. All rights reserved.
      
      C:\ProgramData\PLUMSIX\mtu_4.2\x86>set path=C:\oracle\mysql-5.6.16-win32\bin;%path%
      
      C:\ProgramData\PLUMSIX\mtu_4.2\x86>mysql -u scott -p scott --compress --port 13306

      ここで指定したオプションの内、–compress と –port 13306 の説明をします。前者は、通信時のデータを圧縮して転送を効率化する狙いが有ります。後者は、ssh に転送してもらうポート番号を指定しています。前述の手順で Tera Term の「ローカルのポート」へ設定したものと同じ番号を指定してください。

    4. 下記に起動直後の事例を示します。
      C:\ProgramData\PLUMSIX\mtu_4.2\x86>mysql -u scott -p scott --compress --port 13306
      Enter password: *****
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 6
      Server version: 5.6.17-log MySQL Community Server (GPL)
      
      Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
      
      Oracle is a registered trademark of Oracle Corporation and/or its
      affiliates. Other names may be trademarks of their respective
      owners.
      
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
      
      mysql>
    5. 続いて MySQL コマンド・ライン・クライアントのプロンプトへ次のコマンドを与えると、データ移動が開始されます。INFILE 句の引数が名前付きパイプです。パイプ名に含まれているバック・スラッシュ(円記号)をエスケープする為、余分に一つ前置きする必要があります。
      LOAD DATA LOCAL INFILE '\\\\localhost\\pipe\\NON_PART_TABLE.dat' into table NON_PART_TABLE FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';
    6. データ件数が10万件と少ないので、このテストは2,3秒で終わります。MySQL コマンド・ライン・クライアントには引き続き次のような表示が現れ、MTUから受け取った行数とロードの経過時間等が示されます。
      mysql> LOAD DATA LOCAL INFILE '\\\\localhost\\pipe\\NON_PART_TABLE.dat' into table NON_PART_TABLE FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';
      Query OK, 100000 rows affected (2.66 sec)
      Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0
      
      mysql>
    7. 一方、MTU の Query.bat も同時に完了し引き続き次のような表示が現れます。
      ***** 前方省略 *****
      複数の接続を確立します。要求された並列度は 4 です。
      空領域サイズチェックの閾値は 1024Mバイトです。
      データファイルと制御ファイルを出力中です。[84G] C:\ProgramData\PLUMSIX\mtu_4.2\x86\output
      ストリームの位置:named_pipe://\\.\pipe\{C}.{X}
      TableName                     :      NumRows:  Elapse:Rows/s:Bytes:  B/s:   Len
      NON_PART_TABLE                :      100,000:   12.76: 7839.:  13M:1097k:  143. CAN
      処理対象となった表の総数      :            1
      ラウンドトリップ(回)        :          175
      出力バイト総数(kbytes)      :     14002.66
      所要時間(秒)                :        15.38
      処理速度(kbytes/秒)         :       910.44
      正常終了しました。
      
      C:\ProgramData\PLUMSIX\mtu_4.2\x86>
    8. MySQLが正しくデータを受け取れたかどうか確認してみましょう。次のようなSQLを実行してみて下さい。
      select count(*) from NON_PART_TABLE;
      select * from NON_PART_TABLE where NUM_01 between 1 and 3;
    9. 1つ目のSQLは件数を数えるためのもので、2つ目のSQLは実際のデータを3件分出力させるものです。実施例を下記に示します。
      mysql> select count(*) from NON_PART_TABLE;
      +----------+
      | count(*) |
      +----------+
      |   100000 |
      +----------+
      1 row in set (0.73 sec)
      
      mysql> select * from NON_PART_TABLE where NUM_01 between 1 and 3;
      +------------+---------------------+--------+-----------------+-----------+---------------------+--------+--------------+--------+--------------+
      | VC2_01     | DTM_01              | NUM_01 | VC2_02          | NUM_02    | DTM_02              | CHR_01 | VC2_03       | NUM_03 | VC2_04       |
      +------------+---------------------+--------+-----------------+-----------+---------------------+--------+--------------+--------+--------------+
      | A_00000001 | 1900-01-01 00:00:01 |      1 | ytGyCeSiagDwVNm | 940377.02 | 9999-12-30 23:59:59 | の#   | kyLFNtEjmnzf |      0 | んしこ’そゆ |
      | A_00000002 | 1900-01-01 00:00:02 |      2 | ELMwjBVNgjngXjm | 167965.87 | 9999-12-30 23:59:58 | みわ   | xEXxDEZjrLsm |      0 | $してけてや |
      | A_00000003 | 1900-01-01 00:00:03 |      3 | jJznbFFsbZbiCPx |  85970.00 | 9999-12-30 23:59:57 | をめ   | VVaNLZeAuCmi |      0 | ゑのめさしも |
      +------------+---------------------+--------+-----------------+-----------+---------------------+--------+--------------+--------+--------------+
      3 rows in set (0.03 sec)
      
      mysql>

まとめ

  • この検証を通じて、データ移動の効率化を高めているのの正体が名前付きパイプであることが分かりました。
  • MTUのSTREAM_LOCATORという環境変数を使うと名前付きパイプが有効化されます。この結果 Oracle Database から抽出したテキストデータを実体化すること無しに他のプログラムへリダイレクト可能となり、I/O待機や中間的なストレージが不要となります。
  • 10万件よりも多いデータ量でこの方法を繰り返し試した処 MySQL サーバ内の処理がボトルネックとなることが確認できました。従いまして LOAD DATA を行う前に索引を一旦削除するとか、整合性制約を無効にするといった作業を予め行っておくとよりデータ移動をスピーディーに行うことが出来ると思います。
  • 尚、この方法には次のような制限があります。
    1. 移行可能なデータ型は CHAR, VARCHAR2, NUMBER, DATE に限定。
    2. 上記に当てはまるデータ型であっても、データ中に2重引用符を含む場合は、文字列囲み用の文字に2重引用符以外を選ぶ必要があります。またエスケープ文字(円記号)が含まれる場合も同様です。MTU、およびLOAD DATA INFILE文のそれぞれに設定が必要です。
    3. LONG, LONG RAW, CLOB, NCLOB, BLOB については、LOAD DATA INFILE と互換性のあるフォーマットで出力できませんので、この方法を適用することが出来ません。
    4. 1番と3番以外のデータ型については未調査なので適用可能かどうか不明です。
    5. 今回のテストデータでは IS NULL となる値が無いので、MTU の環境変数QUERYVARには単純な問い合わせを使うことが出来ました。但し、現実問題として表には NULL が含まれる場合が殆どです。MTU 出力の NULL 表現と LOAD DATA INFILE 文の NULL 表現には直接の互換性がありませんので、NULL を含む場合に事例のような単純な問い合わせを使ってしまうと、MySQL側に正しくIS NULLであるという情報が伝わりません。この障害を回避するには次のような変換ルールを適用した問い合わせに書き換えてください。
      変換前 変換後
      “select * into [移行先の表名] from [移行元の表名];” “select 列名カンマ区切りリスト into [移行先の表名] from [移行元の表名];”
      [VARCHAR2/CHAR型の列名] nvl([VARCHAR2/CHAR型の列名],’\N’)
      [NUMBER/DATA型の列名] nvl(to_char([NUMBER/DATA型の列名]),’\N’)
    6. ポイントとしては LOAD DATA INFILE 文で ESCAPED BY ‘\\’ を指定している場合の NULL 表現は“\N”となるので、これに適合するよう NVL 関数を用いて IS NULL の時文字列”\N”が出力されるよう表現しています。
    7. MySQLデータベースのキャラクタ・セットが UTF-8 であるという想定の下での検証となります。その他のキャラクタセットでも有効かどうかは未確認です。
  • 3番、5番、6番の条件に制限されない方法として強化版!MySQLへのデータ移行術(1/2)では、無料のフリーソフトと組み合わせる事例を紹介しています。

 

by 開発1号