[MySQL] 데이터 베이스 / 특정 테이블 백업하는 방법 5가지와 트랜잭션과의 차이점 짚고가기!

오늘은 SQL 공부를 하다가 가장 중요한 백업 방법에 대해 MYSQL을 활용해서 하는 방법을 찾아보고, 작성해보려 한다.

SQL은 막연하게 어렵다는 인식이 들었는데 점차 그 벽을 부수고! 단점인 만큼 더 열심히 해야겠다는 생각이 들고있다.

그럼 본격적으로 백업에 대해 다뤄보도록 하겠다.

 

 

1. 백업이 필요한 이유

데이터베이스 백업은 데이터 손실을 방지하고 재해 대비, 비즈니스 연속성 보장, 데이터 복구, 테스트 및 개발 등 다양한 목적을 위해 필수적이다.

데이터베이스 운영 및 관리에서 가장 중요한 부분 중 하나로 여겨진다.

 

1. 데이터 손실 방지 : 데이터베이스는 비즈니스와 조직에 중요한 정보를 저장한다. 하드웨어 고장, 우리의 실수, 악성 소프트웨어 공격 등으로 인해 데이터가 손실될 수 있다.
=> 백업은 이러한 상황에서 데이터의 복구를 가능하게 함!

2. 재해 대비 : 자연 재해(화재, 홍수 등)나 인적 사고(데이터베이스 오류, 해킹 등)로 인해 데이터베이스가 손상될 수 있다.
=> 백업은 이러한 상황에서 원래 상태로 복구하는데 필요한 중요한 도구

3. 비즈니스 연속성 보장 : 데이터베이스의 중단은 비즈니스 활동에 큰 영향을 미칠 수 있다.
=> 백업을 통해 중단 시간을 최소화하고 데이터베이스의 연속성을 보장할 수 있음

4. 실수로 데이터 삭제 방지 : 실수로 데이터를 삭제하는 경우가 종종 있다.
=> 백업은 이러한 상황에서 삭제된 데이터를 복구하는데 도움을 줌

5. 테스트 및 개발 : 백업은 테스트 및 개발 환경에서 데이터를 사용할 때 유용하다. 실제 데이터를 사용하기보다는 백업된 데이터를 사용하여 안전하게 실험하고 개발할 수 있다.

6. 규정 준수 및 보안 : 규정 준수를 위해 데이터 백업은 필수적이다. 많은 산업 규제 기관은 데이터 백업을 포함한 데이터 보존 정책을 요구한다.
=> 백업은 데이터 보안을 강화하는데 도움 됨

 

 

2. 백업 방법

여기서는 MYSQL을 기준으로 5가지 방법을 소개하려 한다.

 

1) mysqldump

MYSQL에서 가장 보편적으로 사용하는 백업방법이다.

이 방법은 윈도우 기준 명령 프롬프트를 열어서 데이터 베이스나 특정 테이블을 sql 파일로 논리적 백업을 진행한다.

 

1. 명령 프롬프트 열기

관리자 권한을 부여할 필요는 없으며, 명령 프롬프트를 열어주고 아래와 같이 입력해 mysql에 들어가주자.

(단, 아래의 명령어는 일반적으로 설치된 경로이며, 설치 경로가 다를경우 다른 경로를 통해 mysql 서버를 찾아줘야 함.)

cd C:\Program Files\MySQL\MySQL Server _._\bin

이 때, _._ 부분은 본인이 다운한 서버 버전을 입력해줘야 한다. (필자 기준 8.0)

 

2. mysql 계정 로그인하기

서버에 접속했으면 원하는 사용자 계정으로 접속해줘야 한다.

현재 경로에서 다음 명령어를 사용해서 사용자 계정으로 로그인 해주자.

mysql -u [사용자명] -p

나는 사용자명을 root로 지정해뒀기에 root를 작성했고, Enter password라는 것이 뜬다면 비밀번호를 작성해주자.

(참고 : -p 부분으로 비밀번호를 입력할 수 있게 설정된 것이다.)

 

3. 백업 명령 실행

마지막으로 백업 명령을 실행하기 전, 백업 위치를 어디로 설정해야할지를 결정해야한다.

현재 경로 그대로 백업을 해두고 싶다면 아래와 같이 진행해주면 끝이다.

mysqldump -u [사용자명] -p [데이터베이스명] > [백업파일명].sql

 

만일 나는 하드디스크 말고 ssd에 따로 백업하고 싶어! 라면 다음과 같이 진행해주자.

mysqldump -u [사용자명] -p [데이터베이스명] > E:\백업파일명.sql

(ssd의 경로는 추가적으로 더 지정해서 사용하면 된다.)

 

3-1. 특정 테이블만 백업하고 싶을 경우

이 경우 위의 명령에서 테이블 명을 추가로 작성해주면 끝이다.

mysqldump -u [사용자명] -p [데이터베이스명] [테이블명] > [백업파일명].sql

 

 

2) MySQL Workbench

이 방법은 따로 명령어를 사용하는 방법이 아니라서 제일 간단한 방법이다.

 

1. MySQL Workbench 실행하기

2. Server 탭에서 백업을 원하는 데이터 베이스 선택하기

3. Server 메뉴에서 데이터 베이스 백업 선택하기

4. 백업 설정을 구성한 후 백업 시작 클릭하기

 

 

3) MySQL 서버 데이터 디렉토리

MySQL 서버의 데이터 폴더를 직접 백업할 수 있다.

이 경우는 데이터 폴더를 복사해서 백업하는 방법이라 전체 데이터 베이스를 백업하는 방법이다.

 

1. MySQL 서버 중지하기

2. 데이터 폴더를 백업할 위치로 복사하기

3. MySQL 서버 재시작

 

 

4) 스냅샷

클라우드나 가상화를 사용할 경우 스냅샷으로 데이터베이스를 백업할 수 있다.

이 경우 파일 시스템 수준에서 백업을 진행하기에 MySQL 서버를 중지하지 않고 가능하다는 장점이 있다.

 

1. 클라우드 / 가상화의 관리 대시보드에서 스냅샷 생성하기

 

 

5) 백업 스크립트

자동화된 백업을 위해 스크립트를 작성할 수 있고, 이것을 활용해서 주기적으로 백업을 수행할 수 있다.

아래의 예시는 배치파일을 활용해서 윈도우에서 백업 스크립트를 활용하는 방법이다.

@echo off
setlocal

set MYSQL_PATH="C:\Program Files\MySQL\MySQL Server 8.0\bin"
set BACKUP_DIR="C:\backup"

%MYSQL_PATH%\mysqldump -u [사용자명] -p [데이터베이스명] > %BACKUP_DIR%\backup_%date:~0,4%%date:~5,2%%date:~8,2%.sql

 

 

백업에 대해 알아보다보니 갑자기 트렌잭션은 그럼 뭐지? 하는 의문이 들었다.

나는 지난 sqld 공부를 하다가 트랜잭션과 커밋에 대해 알게 되었고, 이를 활용하는게 내부적인 백업이나 스냅샷의 개념이라는 것을 느꼈다.

그래서 트랜잭션과 백업의 차이점을 추가로 알아보려 한다.

 

트랜잭션?

트랜잭션이란 데이터베이스에서 일련의 작업을 원자적으로 처리하기 위해 사용된다.

트랜잭션을 활용해서 데이터를 백업하는 것이 아니라, 데이터 변경을 관리하고 롤백 가능한 상태를 유지하는 것에 사용한다.

즉, 트랜잭션은 데이터베이스의 일관성을 유지하기 위해 설계되었고, 데이터 변경을 롤백하는 기능을 지원한다.

결론은 트랜잭션을 활용하면 어떤 기능을 수행하기 이전으로 돌아가기 기능을 제공하는 것으로 트랜잭션 내에서 데이터를 백업하는 것은 데이터의 일관성을 파괴할 수 있게된다.

 

 

트랜잭션 수행방법

트랜잭션을 수행하는 방법은 간단하다.

-- 트랜잭션 시작
START TRANSACTION;

-- 트랜잭션 내에서 쿼리 실행
INSERT INTO employees (name, age, department) VALUES ('John', 30, 'HR');
INSERT INTO employees (name, age, department) VALUES ('Alice', 35, 'Engineering');

-- 커밋: 트랜잭션을 완료하고 변경사항을 영구적으로 데이터베이스에 적용
COMMIT;

 

 

+) 파이썬과 연계해서 트랜잭션 수행하기

import mysql.connector

# MySQL 데이터베이스 연결 설정
conn = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="dbname"
)

# 커서 생성
cursor = conn.cursor()

try:
    # 트랜잭션 시작
    conn.start_transaction()

    # 트랜잭션 내에서 쿼리 실행
    cursor.execute("INSERT INTO employees (name, age, department) VALUES (%s, %s, %s)", ("John", 30, "HR"))
    cursor.execute("INSERT INTO employees (name, age, department) VALUES (%s, %s, %s)", ("Alice", 35, "Engineering"))

    # 커밋: 트랜잭션을 완료하고 변경사항을 영구적으로 데이터베이스에 적용
    conn.commit()
    print("트랜잭션 성공적으로 커밋됨")

except mysql.connector.Error as err:
    # 롤백: 트랜잭션 중에 에러가 발생한 경우 변경사항을 취소하고 이전 상태로 롤백
    conn.rollback()
    print("트랜잭션 롤백됨:", err)

finally:
    # 커넥션 및 커서 종료
    cursor.close()
    conn.close()

 

 

오늘은 이렇게 다양한 백업 방법과 트랜잭션을 수행하는 것에 대해서 정리해보는 시간을 가졌다.

다음에는 복구하는 방법에 대해 알아보고 정리해보는 시간을 가져야겠다.