개발단 db 마이그레이션

Introduction

예전에 기존에 사용하던 클라우드의 데이터베이스를 다른 인스턴스로 마이그레이션 할 일이 있었다..
너무 비쌈 ㅡㅡ 
그래서 코드로 반복문과 setinterval을 사용하여 직접 데이터를 옮겼었는데 포스팅해봄.

---

 

 

시연영상

 

 

 

 

 

환경셋팅

데이터베이스 마이그레이션을 위해 샘플 mysql인스턴스를 생성하겠다.

하나는 bf-db, 다른 하나는 af-db라고 이름지었다.

bf-db라는 인스턴스의 데이터베이스의 데이터들을 af-db로 옮길것이다.

 

 

 

각 EC2인스턴스에 mysql설치

 

# mysql설치하기
sudo apt update
sudo apt install mysql-server

# mysql에 루트유저로 접속
mysql -u root -p
비밀번호 입력

# 데이터베이스를 만들고 유저 생성
SHOW GRANTS FOR 'muzzi'@'%';
GRANT CREATE ON *.* TO 'muzzi'@'%';
FLUSH PRIVILEGES;

• muzzi라는 유저만들고 유저권한주샘
• 참고로 mysql외부접속도 열어줘야함

 

 

mysql 워크벤치 접속

워크 벤치 접속해서 각각 mydatabase라는 데이터베이스 커넥션을 연결해줬다.

 

각 db접속 정보 정리

 

BF_HOST="123.123.123.123"
BF_USER="muzzi"
BF_PASSWORD="babo"
BF_DATABASE="mydatabase"
BF_PORT="3306"

AF_HOST="456.456.456.456"
AF_USER="muzzi"
AF_PASSWORD="babo"
AF_DATABASE="mydatabase"
AF_PORT="3306"

코드단에서 이렇게 환경변수 지정해주고 연결할 준비를 마쳤음.

 

 

코드단

 

GitHub - nurdworker/dev-migration-mysql

깃허브 코드이다.

1. 데이터 베이스 연결

• 환경변수쓸꺼고 mysql2모듈 쓸거임

const dotenv = require("dotenv");
const mysql = require("mysql2");

dotenv.config();

• 풀만들기

const bf_pool = mysql.createPool({
  connectionLimit: 10, // 최대 연결 수
  host: process.env.BF_HOST,
  user: process.env.BF_USER,
  password: process.env.BF_PASSWORD,
  database: process.env.BF_DATABASE,
  port: process.env.BF_PORT,
});

const af_pool = mysql.createPool({
  connectionLimit: 10, // 최대 연결 수
  host: process.env.AF_HOST,
  user: process.env.AF_USER,
  password: process.env.AF_PASSWORD,
  database: process.env.AF_DATABASE,
  port: process.env.AF_PORT,
});

아까 저장해둔 환경변수로 풀 만드는 코드.

풀 안만들어도 되겠지만 연결 한두번하다가 커넥션리밋 넘을거라 써줌

• 데이터베이스 연결확인

const checkDatabaseConnection = (pool) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err);
      } else {
        connection.release();
        resolve();
      }
    });
  });
};

const checkAllDatabaseConnections = async () => {
  try {
    await Promise.all([
      checkDatabaseConnection(bf_pool),
      checkDatabaseConnection(af_pool),
    ]);
    console.log(
      "\\x1b[48;2;128;0;128m Start \\x1b[0m 모든 데이터베이스가 마이그레이션할 준비가 되었습니다!"
    );
    return true;
  } catch (error) {
    // console.error("데이터 베이스가 연결되지 않았습니다.", error);
    console.error("데이터 베이스가 연결되지 않았습니다.");
    return false;
  }
};

checkAllDatabaseConnections();

데이터 베이스 연결됐는지 확인하고 연결안됐으면 멈추게 프로미스 반환하는 비동기함수로 썼음.

 

2. 샘플데이터 넣기

const dotenv = require("dotenv");
const mysql = require("mysql2");

dotenv.config();

// MySQL Pool 생성
const bf_pool = mysql.createPool({
  connectionLimit: 10,
  host: process.env.BF_HOST,
  user: process.env.BF_USER,
  password: process.env.BF_PASSWORD,
  database: process.env.BF_DATABASE,
  port: process.env.BF_PORT,
});

// 랜덤한 숫자를 생성하는 함수
const getRandomNumber = (min, max) => {
  return Math.floor(Math.random() * (max - min + 1)) + min;
};

// 샘플 데이터 생성 함수
const generateSampleData = () => {
  const sampleData = [];
  for (let i = 1; i <= 60; i++) {
    const id = `user${i}`;
    const detail = `Sample detail ${i}`;
    sampleData.push({ id, detail });
  }
  return sampleData;
};

// MySQL에 샘플 데이터 삽입 함수
const insertSampleDataToDatabase = async (sampleData) => {
  const connection = await bf_pool.promise().getConnection();
  try {
    await connection.query(
      "CREATE TABLE IF NOT EXISTS users (user_idx INT AUTO_INCREMENT PRIMARY KEY, id VARCHAR(255) UNIQUE, detail TEXT)"
    );

    // 삽입할 데이터를 배열로 변환
    const values = sampleData.map(({ id, detail }) => [id, detail]);

    // INSERT 쿼리 실행
    await connection.query("INSERT INTO users (id, detail) VALUES ?", [values]);
    console.log("Sample data inserted successfully.");
  } catch (error) {
    console.error("Error inserting sample data:", error);
  } finally {
    connection.release();
  }
};

// 샘플 데이터 생성
const sampleData = generateSampleData();

// 샘플 데이터를 MySQL에 삽입
insertSampleDataToDatabase(sampleData);

// 샘플 데이터 생성 함수
const generateSamplePostData = () => {
  const samplePostData = [];
  for (let i = 1; i <= 110; i++) {
    const title = `Post ${i} Title`;
    const content = `Post ${i} Content`;
    samplePostData.push({ title, content });
  }
  return samplePostData;
};

// MySQL에 샘플 데이터 삽입 함수
const insertSamplePostDataToDatabase = async (samplePostData) => {
  const connection = await bf_pool.promise().getConnection();
  try {
    await connection.query(
      "CREATE TABLE IF NOT EXISTS posts (post_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), content TEXT)"
    );

    // 삽입할 데이터를 배열로 변환
    const values = samplePostData.map(({ title, content }) => [title, content]);

    // INSERT 쿼리 실행
    await connection.query("INSERT INTO posts (title, content) VALUES ?", [
      values,
    ]);
    console.log("Sample post data inserted successfully.");
  } catch (error) {
    console.error("Error inserting sample post data:", error);
  } finally {
    connection.release();
  }
};

// 샘플 데이터 생성
const samplePostData = generateSamplePostData();

// 샘플 데이터를 MySQL에 삽입
insertSamplePostDataToDatabase(samplePostData);

bf디비에 일단 데이터가 있어야하니까 코드로 랜덤하게 데이터 넣는 함수 썼음.

테이블은 우리서비스 메인인 users랑 posts테이블 두개로 했음.

 

 

3. bf디비에 테이블 설정 그대로 af테이블에 만들기

// 테이블 그대로 만들기

const getTableDefinitions = async (pool) => {
  return new Promise((resolve, reject) => {
    pool.query("SHOW TABLES", (error, results) => {
      if (error) {
        reject(error);
      } else {
        const tables = results.map((row) => Object.values(row)[0]);
        const tableDefinitions = [];
        const promises = tables.map((table) => {
          return new Promise((resolve, reject) => {
            pool.query(`SHOW CREATE TABLE ${table}`, (error, results) => {
              if (error) {
                reject(error);
              } else {
                const createTableStatement = results[0]["Create Table"];
                tableDefinitions.push(createTableStatement);
                resolve();
              }
            });
          });
        });
        Promise.all(promises)
          .then(() => resolve(tableDefinitions))
          .catch(reject);
      }
    });
  });
};

const getTableCreationQueries = async () => {
  try {
    const tableDefinitions = await getTableDefinitions(bf_pool);
    return tableDefinitions;
  } catch (error) {
    // console.error("테이블 생성 쿼리가 실패했습니다.", error);
    console.error("테이블 생성 쿼리가 실패했습니다.");
    return [];
  }
};

const createTableQuery = async () => {
  return await getTableCreationQueries();
};
const makeConditionQuery = async () => {
  const queries = await createTableQuery();
  const modifiedQueries = queries.map((query) => {
    // CREATE TABLE 문에 IF NOT EXISTS 조건 추가
    return query.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
  });
  return modifiedQueries;
};

// af 디비에 테이블 만들기
const createTablesInAfDatabase = async (queries) => {
  const connection = await af_pool.promise().getConnection();
  try {
    for (const query of queries) {
      await connection.query(query);
      const tableName = query.match(/`([^`]*)`/)[1];
      console.log(
        `\\x1b[48;2;255;255;0m Info \\x1b[0m 테이블 ${tableName}가 생성되었습니다.`
      );
    }
    console.log(
      "\\x1b[48;2;0;0;255m In Progress \\x1b[0m 마이그레이션 후 테이블이 정상적으로 생성되었습니다!"
    );
  } catch (error) {
    console.error("이전할 데이터베이스의 테이블 생성이 실패했습니다.");
  } finally {
    connection.release();
  }
};
const makeTablesToAF = async () => {
  await createTablesInAfDatabase(await makeConditionQuery());
};

• 테이블 데피니션 함수로 완전 똑같은 테이블 정보 가져오는 함수를 썼음.
• 테이블 생성 쿼리 생성함수를 만들었음
• 테이블 생성하는 쿼리함수에 if not exist조건이 안붙길래 이 조건 붙여주는 함수를 썼음.
• 테이블 갯수에 따라서 어레이에다가 그만큼 테이블 생성쿼리를 만들어줌.
• 마지막 테이블 생성 함수를 만들었음. 여기서 저 어레이 담긴거 순회하면서 테이블 만들거임

 

4. 데이터 순회하기

const moveTableDataFromBFtoAF = async (bfTableName, afTableName) => {
  let success = false; // 초기값은 실패로 설정

  const bfConnection = await bf_pool.promise().getConnection();
  const afConnection = await af_pool.promise().getConnection();

  try {
    let offset = 0;
    const batchSize = 50;
    let bfData = [];

    // BF 데이터베이스에서 데이터를 검색
    while (true) {
      const [rows] = await bfConnection.query(
        `SELECT * FROM ${bfTableName} LIMIT ${offset}, ${batchSize}`
      );
      if (rows.length === 0) break; // 더 이상 데이터가 없으면 종료
      bfData = rows;

      // AF 데이터베이스에 데이터 삽입
      const values = bfData.map((row) => Object.values(row));
      await afConnection.query(`INSERT INTO ${afTableName} VALUES ?`, [values]);

      // 2초 대기
      await new Promise((resolve) => setTimeout(resolve, 1000));

      // 오프셋 증가
      offset += batchSize;
      console.log(
        `\\x1b[48;2;0;0;255m In Progress \\x1b[0m${bfTableName}테이블의 ${offset}rows까지의 데이터가 이전되었습니다.`
      );
    }

    console.log(`${bfTableName}테이블의 데이터 이동이 완료되었습니다.`);
    success = true; // 성공 시 true로 설정
  } catch (error) {
    // console.error(`데이터 이동 중 오류 발생:`, error);
    console.error(`\\x1b[48;2;255;0;0m Fail \\x1b[0m 데이터 이동 중 오류 발생`);
    console.log(
      `\\x1b[48;2;255;255;0m Info \\x1b[0m 에러 발생으로 인해 ${afTableName} 테이블의 모든 데이터를 삭제합니다.`
    );
    await afConnection.query(`DELETE FROM ${afTableName}`);
  } finally {
    bfConnection.release();
    afConnection.release();
    return success; // 성공 또는 실패 여부 반환
  }
};

• db과부하를 막기위해 테이블들 이름 들어있는 어레이를 따로 빼고 이걸 순회하는 식으로 했음.
• 또한 한번에 너무 많은 데이터가 들어가는걸 막기위해 한번에 50row씩만 조회하고 넣는 코드를 짰음.

 

5. 마이그레이션 하기

const migration = async () => {
  try {
    await checkAllDatabaseConnections();
    await showTableList();
    await makeTablesToAF();
    const tableLists = await getTableList(bf_pool);

    for (const table of tableLists) {
      const success = await moveTableDataFromBFtoAF(table, table);
      if (!success) {
        console.log(
          `\\x1b[48;2;255;0;0m Fail \\x1b[0m 테이블 ${table}의 데이터 마이그레이션 실패`
        );
        await clearAllTablesData(); // 모든 테이블의 데이터를 삭제
        return;
      }
    }

    console.log(
      "\\x1b[48;2;0;255;0m Success \\x1b[0m 모든 테이블의 데이터 마이그레이션이 완료되었습니다."
    );
  } catch (error) {
    console.error(
      "\\x1b[48;2;255;0;0m Fail \\x1b[0m 마이그레이션 중 오류 발생:",
      error
    );
  }
};

const clearAllTablesData = async () => {
  const tableLists = await getTableList(af_pool);
  for (const table of tableLists) {
    await af_pool.promise().query(`DELETE FROM ${table}`);
    console.log(
      `\\x1b[48;2;255;0;0m Fail \\x1b[0m ${table} 테이블의 데이터가 모두 삭제되었습니다.`
    );
  }
};

migration();

• 최종적인 마이그레이션함수임
• 위에서 만든 함수에서 주요기능들은 전역에서 호출한게 없음. 애초에 이전단계가 완료안되면 걍 에러터지게 설계해버림
• clearAllTablesData함수는 테이블 리스트를 순회하면서 각각 데이터를 넣는데, 예를들어 users테이블은 마이그레이션이 완료됐는데 posts테이블은 중간에 에러터져서 데이터 다 지우면 하나는 됐고 하나는 안되는 귀찮은 일이 생겨서 걍 하나 에러터지면 이전 성공한 테이블도 데이터 다 지워주는 함수 만들었음

 

 

 

 

Trouble

• database라는 데이터를 만드니까 예약어에 포함되서 에러가 팡팡떳다. 만들고나서 안되는게 짜증나는데 만들떄 그냥 만들어주지말지 왜 만들어지게해놓고 db접근에서 오류나게 해놨는지 모르겄음
• mysql모듈을 썼는데 구닥다리라 유저권한 암호화방식 오류가 계속나서 mysql2를 썼음
• bf디비에 있는 테이블들을 조회하고 어레이에 담아서 af디비에 똑같이 만들게 하는 코드를 썻는데 foreign key가 설정된 테이블을 먼저 만들면 안된다. 이건 코드단에서 더 신경써야하는거임

추가적으로 생각해봐야 할 것

• 만약 완전 자동화를 한다면 인프라단의 테라폼, 쉘스크립트등이 필요할듯. 인스턴스만들고 뭐 안에다가 디비 설치하고 이런거 하려면 개발단만으로는 안되니까.
• 이 주제에서는 연습은 mysql to mysql이다. 만약 다른 데이터베이스 같은경우는 모듈을 추가적으로 받아와야하고 쿼리문 문법도 조금씩 써야하니 ORM을 같이써야할듯
• 네트워크는 완전 퍼블릭으로 열어놓은 상태인데 민감한 데이터베이스 마이그레이션이니 현업에서는 프라이빗 네트워크끼리 해야할듯.
• DMS에서 어려웠던 온프레미스 to 클라우드 마이그레이션도 이걸로는 가능하다. 두 인스턴스 사이에 내 로컬컴퓨터가 껴있는 상태에서 한거니까.
• DMS에서 안됐던 AUTO INCREMENT 조건도 저기 테이블 쿼리 자동생성 함수에서 가져와준다.
• 데이터가 조금씩 형식이 바뀌는 경우도 있을것같은데 예를들어 쇼핑몰 도메인 컬럼이 있는데 새로운 쇼핑몰 도메인으로 내용을 바꿔야 한다면 이 데이터들만 수정하는 마이그레이션 해야한다면 코드단에서 조절하면 될거임.