pajama

환경

MySQL : 5.0.89, AIX 6.1

Informix : 11.50.FC9, AIX 6.1

MySQL의 BLOB데이터를 Informix로 마이그레이션 해보았습니다.

샘플 테이블 및 데이터 생성 (MySQL)

echo "create table blotest(a int, b blob)" | mysql -u root -p1nfra test

echo "insert into blobtest values (2,load_file('/home/mysql/logo.gif'))" | mysql -u user -ppasswd test

처음에는 mysqldump의 --hex-blob, --tab 옵션으로 데이터를 추출하였으나 구분자가 있는 형태로 추출했을 때 hex형태로 추출되지 않아

아래와 같이 OUTFILE문으로 추출했습니다.

echo "SELECT a,hex(b) INTO OUTFILE '/home/mysql/blobtest.txt'  FIELDS TERMINATED BY '|'      LINES TERMINATED BY '|\n'     FROM blobtest;" | mysql -u user -ppasswd test

위와 같이 MySQL에서 OUTFILE로 데이터를 추출하면 NULL값은 \N으로 추출되기 때문에 이에대한 처리가 필요합니다.

그리고 인포믹스의 구분자 파일에는 레코드 끝에 구분자가 있으므로 LINES TERMINTAED BY를 '|\n' 으로 했습니다.

MySQL에서는 hex값의 알파벳이 대문자로 추출되는 반면 인포믹스는 소문자로 추출되는데, 로드할 때 문제는 없었습니다.

인포믹스에 BLOB 데이터를 입력할 테이블 생성 (Informix)

echo "create table blobtest (a byte)" | dbaccess stores_demo

echo "create table blobtest1 (a blob)" | dbaccess stores_demo

인포믹스에 BLOB 타입에 입력할때는 구분자 파일과 BLOB파일이 별도로 존재하므로 우선 BYTE 타입에 먼저 입력후 BLOB으로 변환하여

다시 입력하는 순서로 진행했습니다.

echo "load from /home/mysql/blobtest.txt insert into blobtest" | dbaccess stores_Demo

echo "insert into blobtest1 select b::blob from blobtest" | dbaccess stores_demo

GUI 툴로 확인한 결과 이미지가 잘 입력되었음을 확인할 수 있었습니다.

How to migrate large blob table from mysql to postgresql?

http://dba.stackexchange.com/questions/4211/how-to-migrate-large-blob-table-from-mysql-to-postgresql

Migrate a database from MySQL to IBM Informix Innovator-C Edition, Part 1: Comparing MySQL to IBM Informix Innovator-C Edition

http://www.ibm.com/developerworks/data/library/techarticle/dm-1102mysqltoinnovatorc/

Migrate a database from MySQL to IBM Informix Innovator-C Edition, Part 2: Step-by-step walk-through of the migration process

http://www.ibm.com/developerworks/data/tutorials/dm-1102mysqltoinnovatorc2/index.html

1. 인스턴스의 일반적인 정보 (현재 사용자, 버전, 캐릭터셋)

mysql> status;

2. 해당 테이블에 대한 DDL

mysql> show create table table_name;

3. 특정 데이터베이스의 테이블 상태 (사이즈, 생성일자, 업데이트일자, Collation등)

mysql> show table status from database_name;

4. 사용자에게 부여된 권한

mysql> show grants for [for user];

5. 실행중인 스레드

mysql> show [full] processlist;

http://comnic.tistory.com/entry/MySQL-%EC%9C%A0%EC%9A%A9%ED%95%9C-%EB%AA%85%EB%A0%B9%EC%96%B4-%EB%AA%A8%EC%9D%8C-1-SHOW

Exporting query results to a remote machine with MySql (an alternative to SELECT INTO OUTFILE)

MySql’s SELECT INTO OUTFILE syntax provides a nice way to export the results of a query into a tab delimited (by default) text file. Unfortunately, the file has to be created on the MySql host (by a user with file permissions), rendering it unsuitable for dumping the data to a remote machine. If you wish to dump the query results to a different machine then the mysql docssuggest redirecting the output from the mysql command line client:

리모트 호스트에서는 SELECT INTO OUTFILE 문을 사용할 수 없으므로 표준출력(stdout)으로 데이터를 추출해야 한다는 이야기.

The SELECT … INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT … INTO OUTFILE. In that case, you should instead use a command such as mysql -e “SELECT …” > file_name to generate the file on the client host.

This works great but the default output you’ll get from redirecting STDOUT won’t be the same as using SELECT INTOOUTFILE. The two main (only?) differences are, in the redirected output, the addition of the headers and that NULL values are output as NULL instead of the special \N (backslash-N) sequence. We can fix both of these things by specifying an optional switch (skip-column-names) to the mysql client and employing some sed magic.

mysql -h"my-host" -u"my-user" -p"my-password" -e"select * from my-database.my-table" --skip-column-names |\
  sed -e 's/[[:<:]]NULL[[:>:]]/\\N/g' >\
  my-export-directory/my-table.csv`

That sed magic is replacing instances of NULL, surrounded by some whitespace, with the special \N escape sequence.

Oh, and it’s probably worth mentioning that this is only really important if you want to load this data back into mysql (using LOAD DATA INFILE for example). If you were to load the default output, from redirecting the mysql client, back into mysql then you’d end up with an unwanted header row and NULL strings (or 0 for numeric columns) where you expected actual NULL values.

It took us a while to figure this out so I thought it might be of some use to others.

http://chrisroos.co.uk/blog/2008-04-10-exporting-query-results-to-a-remote-machine-with-mysql-an-alternative-to-select-into-outfile

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

If you want to export your mysql databases into a csv file here is simple tip for you.

http://www.debianadmin.com/export-mysql-database-into-a-csv-file.html

Mysql에서 한글 정렬이 문제가 되는 것은 charset 때문이다.
이럴땐 my.cnf파일에 한줄을 추가 하는것으로 한글 정렬 문제를 해결 할 수 있다.