꽤 힘들었던 MSSQL에서 MYSQL로 데이터베이스 이전!

MSSQL 에서 제로보드xe의 mysql로
데이터베이스 작업을 이전했던 내용의
포스팅입니다.
많은 도움되시길 바랍니다

이번 저희 과 홈페이지 작성 중에 힘들었던 점은 제로보드 설치나 운영이 아니라, DB이전이 가장 힘들었던 점이였습니다.
MS SQL 만 공부했던 저로써는 MYSQL 이 비슷하긴 하지만, 여러모로 생소했기에, 그리고 제로보드xe 또한 처음 만난 툴이였기에 제로보드 xe의 DB구조조차 몰라서 파악을 하며 작업을 해야 했기에 조금은 힘들었던 작업이였습니다. 3일을 밤새게 만들었던 작업이였네요. 그 작업내용을 찬찬히 돌려보겠습니다 ^^ 혹시나 저와 같은 상황에 있으신 분들은 도움 되시길 바랍니다.


이번 과 홈페이지 이전 작업은 아주 오래전에 만들어 놓은 과 홈페이지를 제로보드 xe라는 툴을 사용하여 다른 서버로 이전하는 작업이였습니다. 물론 디자인 적인 면도 싹 바꾸어 버렸구요.

 

조금은 바뀐게 느껴지시나요?^^ 물론 디자인 적인 것도 중요하지만 제게 가장 중요한 작업은 데이터 이전이였기에, DB에 대한 것에 대한 작업을 뒤돌아보는 시간을 갖겠습니다. 이전 과 홈페이지는 MSSQL 을 이용하여 아예 코딩으로만 이루어졌기에, 통일성이나 데이터베이스가 깔끔하지 못한 점이 있었습니다. 데이터베이스 튜닝( 데이터베이스를 잘 정리하여 잘 짜는 작업 ) 만으로도 하드웨어를 바꾸지 않고 10배에 가까운 성능을 보일 수 있는데, 이전 데이터베이스는 오랜 시간전에 만들어졌을 뿐더러 시간이 지남에 따라 추가되는 데이터베이스 등이 막무가내로 생성이 되서 조금은 정리가 되지 않은 부분이 있었습니다.

로 옮기는 작업은 회원 정보 을 이전 하는 작업이였습니다.

* 참고로 옮기는 작업은 MSsql에서 Excel 파일로 export ->
  MSsql(제로보드xe DB)에서 export한 DB구조와 내용을 임의의 테이블에 복사 ->
  임의의 테이블에서 기존 xe.Document의 컬럼에 맞게끔 쿼리를 날린 후->
  (물론 여기서 xe.Documnet의 기본적으로 null 공백 허용이나 autoincrease는 제 상황에 맞게끔 테이블 수정) ->
  임의의 테이블에 수정된 테이블(제로보드 구조에 맞게끔 수정된) 을 Update쿼리로 xe.document에 넣는

위와 같은 순서로 작업을 했습니다^^ 쿼리는 제일 아래에 공개하겠습니다.


이전 과 홈페이지 DB에는 id와 password(물론 암호화되지 않은) 이름 이 전부였습니다 (컬럼이)
하지만 제로보드 xe의 구조에는 member_srl , user_id, user_name, nick_name, password ... 이외에도 많은 컬럼이 있었습니다.
먼저 했던 작업은 원래 있는 id, password, 이름 컬럼을 저 곳에 잘 맞추어 넣느냐 였습니다. 제로보드에서 공백을 모두 허용한 다음, update구문을 사용하여 기존에 있던 세 컬럼을 제로보드 xe에 넣었습니다. (업데이트를 사용한 이유는, admin 계정이 insert를 하면 날아가기 때문에) 업데이트 하고 나니 문제점이 있더군요. * 이때 member_srl 은 자동증가로 (auto increase)로 처리했습니다.

문제점은, DB에는 기존 비밀번호가 111111 인 경우 -> 111111 로 저장이 되지만, 새로 만든 제로보드 툴에서 로그인을 했을 경우는 '비밀번호가 맞지 않는다'라는 에러 메세지가 뜨더군요. 흠... 왜 그럴까?

정상적으로 회원가입을 한 경우에는 MD5라는 암호화를 통해서 내가 적은 비밀번호가 pass라면 DB엔 MD5라는 암호화를 거쳐 aa12kdske3 (예를 들어) 로 저장이 되어있었습니다. 그러니 당연히 그저 DB를 옮긴 111111은 MD5를 통해 암호화의 반대인 복호화된 임의의 문자 adfae123415sdafae로 로그인 처리를 하기에 111111과 adfae123415sdafae는 맞지 않다는 에러 메세지가 뜬 것이죠.

그렇다면 일일이 다 회원가입을 해야 하는 것일까!? 하는 또 고민에 봉착한 후 어떻게든 암호화를 하지 않는 방식으로 바꾸어 보려고 제로보드 php 파일을 일일이 뒤졌습니다. member 관련 파일을 notopad++ 로 모두 연 뒤에 "단어찾기"를 통해 모든 문서에 있는 md5라는 단어를 찾았습니다. 그리고 모두 삭제 ! 그러고 난뒤에는 로그인 처리를 할 때에도, 회원가입 페이지를 통해 db에 저장을 할 때에도 더이상 MD5 암호화를 하지 않더군요 ! 저희 과 홈페이지는 모든 신입생이 들어올 때마다 관리자가 회원을 추가 해주기에, 보안에도 상관이 없다는 결론에서 암호화를 풀어버렸습니다.

그 뒤엔 기존에 있던 만개에 가까운 각 게시판의 게시물을 옮기는 작업에 착수했습니다.

기존DB에는 게시물 id, 작성자, title, contents, count 같은 기본적인 정보가 담긴 column명이 있었고, 제로보드 xe DB에는

위에 보시다 시피 기본적인 내용은 옮기더라도, 기존엔 없던 document_srl(문서번호), module_srl 이 필수적으로 들어가게 됩니다.
때문에 저는 document_srl 을 자동증가, (아마도 450 이전은 페이지 수정모듈이 , 450 이후는 게시판 게시글 모듈로 들어가는 듯 합니다, 자동증가 후 게시판을 글을 쓰면 뭐 기억은 자세히 나지 않지만, 예를 들어 '450' 번 게시글이 있다고 게시가 되질 않는데 미친 듯이 엔터를 쳐서 DB에 업데이트 된 이후의 번호로 옮겨놓은 뒤부터는 잘되는 걸 볼 수 있습니다. 물론 2의 10승까지 게시판의 글이 입력될 수 있게 해놨으니 20000개 정도의 글은 그냥 넘어가는 것으로 대체 했습니다. 또 module_srl의 경우에는 게시판의 번호 입니다.

예전 DB에는 일일이 자유게시판 DB, 토론게시판 DB 각각의 DB들이 다 다른 테이블에 저장이 되어있었지만, 꽤나 신선했던 것은 제로보드에는 dbo.document 라는 테이블안에 모든 게시물이 저장이 되고 이 module_srl을 통해 어떤 게시판의 글인지를 구분하고 있었습니다. 때문에 제로보드 테스트 페이지에서 게시판을 생성하고 test글을 생성한 뒤에 그 테스트 글의 module_srl을 파악하고 ( 예를 들어 토론게시판의 module_srl은 95 ) 그 게시글에 맞는 module_srl 을 모두 업데이트해서 게시판에 맞는 module_srl 을 설정한 뒤 제로보드 xe의 documnet 테이블에 넣는 작업을 했습니다. 후압, 하지만 또또또 여기서 문제 봉착, 자료실과 같이 자료가 포함되어 있는 게시글이나 댓글은 어떻게 하지 라는 고민에 빠졌습니다.

아무리 고민해도 기존 DB에는 자유게시판게시글DB, 자유게시판게시글댓글DB 와 같은 형식으로 각 게시판마다 전부 다른 DB로 구성이 되어있었고, 중요한 것은 제로보드 내의 댓글 테이블은 게시글 테이블과 같이 하나의 댓글 Table을 통해서 모듈과 게시판 번호로 구분되어 있었습니다. 10000개에 달하는 댓글을 일일이 게시글 번호를 대입할 수 없어서 어쩔 수 없이 댓글을 포기!

자료실의 경우에도 기존 DB가 콘텐츠에 자료를 포함하고 있는 식으로 구성되어 있어서 ( 물론 때문에 큰 용량의 자료를 포함하진 못했지만 ) 제로보드의 경우에는 업로드하면 업로드 게시물의 번호와 업로드 전용 테이블에서 파일의 경로를 지정해주는 식으로 2개의 테이블로 연동이 되어 있어서 그냥 일일이 자료를 업로드 했습니다 (자료가 많지 않았기에 다행이지, 자료가 많았다면 이 역시 해결한다고 애를 먹었을 뻔 했네요. )

마지막으로 문제점은 제로보드는 타임스탬프(varchar) 로 날짜를 인식하는데, 이전 DB는 datetime형식으로 저장되어 있었다는 것... 지금 고민 중입니다. 짧은 생각에 DB를 옮기는 과정에서 형식을 export할 때 임의대로 varchar로 바꿔주면 되지 않을까 했는데 - 와 같은 기호들까지 따라오게 되서 백업하지 않은 게시판 DB를 날려먹었다는.... 네이버 등에 찾아보니 타임스탬프나 date형식을 varchar로 바꾸어 주는 쿼리가 있던데 아직까진 살펴보고 있는 중입니다^^


 중간에 join 쿼리도 사용을 했는데 이 쿼리는 out 쿼리와 함께 조금 더 공부해보고 포스팅 하도록 하겠습니다.
만든 홈페이지는 이번주 일요일 12시에 오픈 할 예정이고, 주소는 http://bme.inje.ac.kr (현재는 이전 홈페이지로 접속중)
입니다. 아 맞다 아래는 하면서 사용했던 쿼리들입니다. 마구 저장되어 있어서 두서 없이 그냥 올립니다.

SELECT * FROM xe_member

SELECT USER_NAME, nick_NAME, USER_ID, PASSWORD FROM temp_tbl

SELECT DISTINCT title COUNT(title) AS cnt FROM xe_documents GROUP BY title HAVING cnt > 1

INSERT INTO login(login.user_id)
SELECT DISTINCT temp_tbl.user_id FROM temp_tbl

UPDATE login AS l INNER JOIN temp_tbl AS t ON (l.user_id = t.user_id)
SET l.user_name = t.user_name, l.password = t.password

INSERT INTO xe_member(xe_member.user_id, xe_member.password, xe_member.user_name, xe_member.nick_NAME)
SELECT login.user_id, login.password, login.user_name, login.user_name FROM login WHERE login.user_id NOT IN ('admin');

SELECT * FROM login WHERE user_name = '박소희'

INSERT INTO xe_member(xe_member.user_id, xe_member.password, xe_member.user_name, xe_member.nick_NAME)
SELECT login.user_id, login.password, login.user_name, login.user_name FROM login WHERE login.user_id NOT IN (SELECT xe_member.user_id FROM xe_member);

SELECT * FROM xe_member

INSERT INTO xe_documents ( title, user_name, email_address, nick_name, last_update, regdate, readed_count, content, module_srl)

SELECT title, NAME, email, NAME, tdate, tdate, COUNT, content, module_srl

FROM news$
WHERE NOT EXISTS( SELECT * FROM xe_documents WHERE xe_documents.module_srl = news$.module_srl);

게시판 업데이트 문

INSERT INTO xe_documents( user_name, nick_name, email_address, title, regdate, last_update, readed_count, content, module_srl)

SELECT NAME, NAME, email, title, tdate, tdate, COUNT, content, module_srl
FROM classmate