MTU lab

手軽で速い！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 のセッションで有効なキャラクタセットと互換性のあるものを表２から選んで下さい。
    NOBYTEORDERMARK true

    表１．環境変数の設定値

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

      表２．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 です。
監査接続ＩＤは 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. １つ目のSQLは件数を数えるためのもので、２つ目のSQLは実際のデータを３件分出力させるものです。実施例を下記に示します。 
      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>

まとめ

 

by 開発１号