今回は「データベースのデータ移行」について記載します。
1)データベース作成
2)ユーザの作成および権限付与
3)OracleのデータをCSVファイルへ出力
4)テーブル作成
5)CSVファイルをMySQLへロード
1)データベース作成
データベースの作成は mysqladmin のコマンドにもありますが mysql コマンドで実行します。
以下の例では文字コードセットを指定して作成していますが、指定しない場合は、my.cnf のシステム変数の設定値で決まります。
今回は my.cnf に utf8 をデフォルトして指定しているのでコマンド実行時に指定しなくても同じことですが、明示的に指定したスクリプトを残すほうがわかりやすいですね。
[root@ip-10-130-6-146 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database helpdesk default character set utf8;
Query OK, 1 row affected (0.00 sec)
これで helpdesk という枠ができました。
create database は、create schema も同義。
Oracleのcreate database文は、実行可能なOracleデータベースのインストールのような位置づけです。
データベースのメタ情報を管理するシステム表領域やユーザ用の表領域、一時表領域やパッケージなどが作成されます。
そのデータベースの中にユーザを作成するとユーザ用のスキーマという枠が同時に作成されます。
このスキーマとMySQLのcreate database コマンドがほぼ同義ですね。
2)ユーザの作成および権限付与
データベースの枠ができたので次にその枠を使うユーザを作成して権限を付与します。
権限はとりあえず必要最低限で以下のようにしました。
ユーザの作成と権限付与は grant で同時に行えます。
mysql> grant select,insert,update,delete,create,drop,alter on helpdesk.*
-> to helpdeskadmin@localhost identified by 'helpdeskadmin';
Query OK, 0 rows affected (0.00 sec)
#Oracle風にするならデータベース名（スキーマ名）とユーザ名を合わせる感じですので helpdeskadmin は helpdesk ですね。
メモ：すべての権限を付与するコマンド
mysql> grant all PRIVILEGES ON helpdesk.* to helpdeskadmin@localhost;
メモ：権限変更が反映されるタイミング
権限（権限テーブル）はメモリにキャッシュされるので変更したら以下コマンドの実行してリロードさせましょうという記事も見かけますが grant や revoke で変更した権限情報は、変更直後にサーバーが自動的にリロードするようです。手動リロードが必要なのは権限テーブルを直接DMLで書き換えた場合だけですね。
mysql> flush privileges;
◆権限の確認
mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> desc SCHEMA_PRIVILEGES;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| GRANTEE | varchar(81) | NO | | | |
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| PRIVILEGE_TYPE | varchar(64) | NO | | | |
| IS_GRANTABLE | varchar(3) | NO | | | |
+----------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from SCHEMA_PRIVILEGES where TABLE_SCHEMA = 'helpdesk';
+-----------------------------+---------------+--------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------------------------+---------------+--------------+----------------+--------------+
| 'helpdeskadmin'@'localhost' | NULL | helpdesk | SELECT | NO |
| 'helpdeskadmin'@'localhost' | NULL | helpdesk | INSERT | NO |
| 'helpdeskadmin'@'localhost' | NULL | helpdesk | UPDATE | NO |
| 'helpdeskadmin'@'localhost' | NULL | helpdesk | DELETE | NO |
| 'helpdeskadmin'@'localhost' | NULL | helpdesk | CREATE | NO |
| 'helpdeskadmin'@'localhost' | NULL | helpdesk | DROP | NO |
| 'helpdeskadmin'@'localhost' | NULL | helpdesk | ALTER | NO |
+-----------------------------+---------------+--------------+----------------+--------------+
7 rows in set (0.00 sec)
3)OracleのデータをCSVファイルへ出力
OracleのユーティリティSQL*Plusを使ってデータベースからCSVファイルを簡単に生成します。
※弊社提供のOracle専用ユーティリティ101will Free Editionをぜひご利用ください！
101will Free Editionでは以下のようなSQL文とSQLを実行する親シェルを自動生成することができます。
・Oracleのhelpdeskスキーマ内にあるuser_mテーブルのデータをcsvファイルに抜き出すプログラム一式
すべての移行対象テーブルのCSVファイルを生成します。
4)テーブル作成
◆OracleとMySQLのデータ型マッピング
Oracleのデータ型をMySQLのデータ型に合わせてCREATE TABLE文をカスタマイズします。
メモ：データ型マッピングの詳細は、Oracle社のマニュアル:表2-4 Oracle SQL Developerで使用されるデフォルトのデータ型マッピングを参照
上記のマニュアルを参照しながら以下のようにDDLを変換します。
基本的な型は問題なく変換できます。
OracleのVARCHAR2は最大長4000バイトですがMySQL（ver5.0.34）のVARCHARは65,532バイトなのでスカラー型でもそこそこ大きなテキストを扱えるのでちょっといいなと思いました。
・MySQLのDDL例）
CREATE TABLE NEWS_M (
ID INT NOT NULL COMMENT "ニュースID",
REG_DATE DATETIME COMMENT "登録日",
NEWS VARCHAR(4000) COMMENT "ニュース内容",
PRIMARY KEY (ID)
)
COMMENT = "ニュースマスタ"
ENGINE = INNODB;
・OracleのDDL例）
CREATE TABLE "NEWS_M" (
NEWS_ID NUMBER(8) NOT NULL,
REG_DATE DATE,
NEWS_DETAIL VARCHAR2(4000),
STATUS VARCHAR2(10),
CONSTRAINT "NEWS_M_PK" PRIMARY KEY (NEWS_ID)
);
COMMENT ON TABLE "NEWS_M" IS 'ニュースマスタ';
COMMENT ON COLUMN "NEWS_M"."NEWS_ID" IS 'ニュースID';
COMMENT ON COLUMN "NEWS_M"."REG_DATE" IS '登録日';
COMMENT ON COLUMN "NEWS_M"."NEWS_DETAIL" IS 'ニュース内容';
COMMENT ON COLUMN "NEWS_M"."STATUS" IS 'ステータス';
◆テーブル作成スクリプトの作成
※変換したDDLスクリプトを cre_tables.sql に保存して実行しましょう。
# mysql -u helpdeskadmin -pPASSWORDTEXT helpdesk
mysql> source cre_tables.sql
◆作成したテーブルの確認
mysql> use information_schema
mysql> select TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_COMMENT
-> from tables where TABLE_SCHEMA='helpdesk';
+--------------+------------+--------+---------------------------------------------
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_COMMENT
+--------------+------------+--------+---------------------------------------------
| APSERVER_M | BASE TABLE | InnoDB | TPBコンポーネント; InnoDB free: 3072 kB
| COMPONENT_M | BASE TABLE | InnoDB | チーム内分類マスタ; InnoDB free: 3072 kB
| CUSTOMER_M | BASE TABLE | InnoDB | 顧客マスタ; InnoDB free: 3072 kB
| DB_M | BASE TABLE | InnoDB | ファンクション; InnoDB free: 3072 kB
| DOCTYPE_M | BASE TABLE | InnoDB | ドキュメントタイプ; InnoDB free: 3072 kB
| DOC_M | BASE TABLE | InnoDB | 技術資料; InnoDB free: 3072 kB
| FAQ_M | BASE TABLE | InnoDB | FAQテーブル; InnoDB free: 3072 kB
| IPTBLS | BASE TABLE | InnoDB | IPTBLS; InnoDB free: 3072 kB
| NEWS_M | BASE TABLE | InnoDB | ニュースマスタ; InnoDB free: 3072 kB
| OS_M | BASE TABLE | InnoDB | 新課題分類; InnoDB free: 3072 kB
| PRODUCT_M | BASE TABLE | InnoDB | チームマスタ; InnoDB free: 3072 kB
| ROLE_M | BASE TABLE | InnoDB | ロールマスタ; InnoDB free: 3072 kB
| SUP_LEVEL_M | BASE TABLE | InnoDB | サポートレベルマスタ; InnoDB free: 3072 kB
| SUP_TYPE_M | BASE TABLE | InnoDB | サポートタイプマスタ; InnoDB free: 3072 kB
| TAR_HISTORY | BASE TABLE | InnoDB | 問合せ履歴; InnoDB free: 3072 kB
| TAR_M | BASE TABLE | InnoDB | TAR_M; InnoDB free: 3072 kB
| TAR_STATUS_M | BASE TABLE | InnoDB | ステータスマスタ; InnoDB free: 3072 kB
| USER_M | BASE TABLE | InnoDB | ユーザーマスタ; InnoDB free: 3072 kB
+--------------+------------+--------+----------------------------------------------
18 rows in set (0.01 sec)
5)CSVファイルをMySQLへロード
OracleのSQL*LoaderのようなMySQLのユーティリティは mysqlimport です。
Oracleのsqlldrコマンドを実行するようにmysqlimportを実行します。
コマンド構文）
# mysqlimport -d -i --fields-terminated-by=, --fields-optionally-enclosed-by=\" -u root -pPASSWORDTEXT helpdesk /home/tnikaido/will/csvunloader/csv/USER_M.csv
上記コマンドの指定内容は以下の通りです。
ファイル形式：フィールドの区切り文字は「,」でデータに「,」を含む場合は「"」で囲う。
実行ユーザ：root
パスワード：PASSWORDTEXT
ロード先データベース：helpdesk
ロード先テーブル：USER_M
ロードするCSVファイル：/home/tnikaido/will/csvunloader/csv/USER_M.csv
※CSVファイル名は「テーブル名.csv」でなければいけないという規約があります。
※フルパスで指定しないとデフォルトのディレクトリは datadir（/etc/my.cnf）
/var/lib/mysql/helpdesk/をみます。
実行オプション：-d 既存データを削除後にロード
-i キーの重複エラーを無視して後続データのロードを行う
実行例）
[tnikaido@ip-10-130-6-146 csv]$ mysqlimport -d -i --fields-terminated-by=, --fields-optionally-enclosed-by=\" -u root -p helpdesk /home/tnikaido/will/csvunloader/csv/USER_M.csv
Enter password:
helpdesk.USER_M: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
[tnikaido@ip-10-130-6-146 csv]$
mysql> select USER_ID, NAME from USER_M;
+----------+--------------+
| USER_ID | NAME |
+----------+--------------+
| helpdesk | 二階堂隆 |
| test | テスト |
+----------+--------------+
2 rows in set (0.00 sec)
mysql>
生成したCSVファイルを任意のフォルダに作成して awk コマンドでmysqlimport実行コマンドを生成すると便利です。
% ls | grep csv| awk '{print "mysqlimport -d -i --fields-terminated-by=, --fields-optionally-enclosed-by=\\\" -u root -pPASSWORDtext helpdesk /home/tnikaido/will/csvunloader/csv/" $1}'
次回はアプリケーションの移行（ほとんどSQLの書き換え）についてエントリーします。
（おわり）
0 件のコメント:
コメントを投稿