MySQL 데이터베이스를 5가지 방법으로 백업하는 방법은?

데이터베이스에 저장되는 데이터가 점점 많아짐에 따라 데이터 백업은 점점 더 중요한 문제가 되고 있습니다. 데이터베이스 백업은 기업이나 개인이 데이터 손실로 인해 막대한 손실을 입지 않도록 보장하는 매우 핵심적인 작업입니다. MySQL은 널리 사용되는 오픈소스 관계형 데이터베이스 관리 시스템으로, 기업에서 널리 활용되고 있습니다. 본 문서는 MySQL 데이터베이스를 백업하는 방법에 대해 설명할 것입니다.

MySQL 데이터베이스 백업 유형

데이터베이스의 크기에 따라 네 가지 유형의 백업이 있으며, 이는 다양한 상황에서 사용됩니다:

전체 백업

이는 대부분의 사람들이 사용하는 일반적인 방법입니다. 사용자 테이블, 시스템 테이블, 인덱스, 뷰 및 저장 프로시저와 같은 모든 데이터베이스 객체를 포함한 전체 데이터베이스를 백업할 수 있습니다. 하지만 더 많은 시간과 공간이 소요되므로 일반적으로 일주일에 한 번 정도 전체 백업을 수행하는 것이 권장됩니다.

트랜잭션 로그 백업

트랜잭션 로그는 데이터베이스에 대한 변경 사항을 기록하는 별도의 파일입니다. 백업은 마지막 백업 이후 데이터베이스에 이루어진 변경 사항만 복사하면 되므로 매우 짧은 시간이 소요됩니다. 데이터베이스의 안정성을 위해 시간 단위 또는 그보다 더 자주 트랜잭션 로그를 백업하는 것이 권장됩니다.

차등 백업

이는 데이터베이스의 일부만 백업하는 또 다른 방법이며, 트랜잭션 로그를 사용하지 않고 대신 전체 데이터베이스의 새 이미지를 사용합니다. 마지막 전체 백업 이후 변경된 데이터베이스만 포함하기 때문에 원래의 전체 백업보다 작습니다. 저장 및 복구가 빠르다는 장점이 있습니다. 매일 차등 백업을 수행하는 것이 좋습니다.

파일 백업

데이터베이스는 하드 디스크에 여러 파일로 구성될 수 있습니다. 데이터베이스가 매우 커서 하루 밤 사이에 백업할 수 없는 경우, 파일 백업을 사용하여 매일 밤 데이터베이스의 일부분을 백업할 수 있습니다. 그러나 일반적으로 데이터베이스가 너무 커서 여러 파일 저장소를 사용해야 할 정도는 아니기 때문에 이러한 유형의 백업은 그리 흔하지 않습니다.

데이터베이스의 상태에 따라 세 가지 유형으로 나눌 수 있습니다:

1. 복사본 백업

콜드 백업은 데이터베이스가 오프라인 상태이고 사용자가 접근할 수 없는 동안 데이터에 대해 수행되는 백업입니다. 백업이 데이터베이스 오프라인 상태에서 이루어지기 때문에 콜드 백업은 오프라인 백업이라고 불리기도 합니다. 콜드 백업은 자원 소모가 상대적으로 적으며, 새로운 데이터를 추가할 수 없기 때문에(데이터베이스가 오프라인임) 백업 프로세스가 한 번에 모든 데이터를 백업할 수 있습니다. 물론 콜드 백업에도 백업 작업이 진행 중일 때는 데이터베이스에 접근할 수 없다는 제한이 있습니다.

2. 핫 백업

핫 백업은 데이터베이스가 온라인 상태이고 사용자가 접근할 수 있는 동안 데이터에 대해 수행되는 백업입니다. 핫 백업은 다운타임을 요구하지 않고 운영 시스템의 방해가 거의 없기 때문에 매우 편리한 솔루션일 수 있지만, 몇 가지 단점도 있습니다. 백업이 진행되는 동안 백업 중인 데이터가 어떤 식으로든 변경된다면, 백업된 데이터가 데이터베이스 내 최종 버전과 동일하지 않을 수도 있습니다.

3. 논리적 백업

논리적 백업은 소프트웨어를 사용하여 데이터베이스에서 데이터를 추출하고 결과를 파일에 씁니다.

MySQL 데이터베이스 백업 방법은 무엇인가요?

백업 복구 방법에 따라 MySQL에는 논리적 백업과 물리적 백업이 있습니다. 논리적 백업은 SQL 문을 백업한 다음 복구 시 이를 실행하여 데이터베이스 데이터를 재생하는 방식입니다. 물리적 백업은 디스크에 저장된 것과 동일한 형식으로 데이터베이스 파일을 복사하는 방식입니다.

이 두 가지 유형의 백업에는 각각 장단점이 있습니다. 일반적으로 물리적 백업은 복구 속도가 빠르지만 더 많은 공간을 차지하는 반면, 논리적 백업은 속도는 느리지만 공간을 덜 차지합니다.

1. mysqldump 사용하기

mysqldump는 MySQL에 포함된 백업 도구로, bin 디렉터리(/usr/local/mysql/bin/mysqldump)에 위치하며, innoDB 기반의 핫 백업을 지원합니다. 다만 논리적 백업 방식이기 때문에 속도가 빠르지 않으며, 백업 데이터가 상대적으로 적은 상황에 적합합니다. mysqldump 전체 백업과 바이너리 로그를 함께 사용하면 특정 시점 복구(PITR)가 가능합니다.

MyISAM 저장 엔진의 테이블의 경우 핫 백업만 사용할 수 있습니다. 이때 데이터 쓰기를 방지해야 하므로 먼저 읽기 잠금을 추가해야 합니다. 이 시점에서 데이터베이스에 직접 접근하여 수동으로 읽기 잠금을 걸 수도 있지만 번거롭습니다. mysqldump 도구에는 바로 잠금을 설정할 수 있는 옵션인 --lock-all-tables가 제공되는데, 예를 들어 다음과 같이 사용할 수 있습니다. mysqldump --databases test --lock-all- tables --flush-logs > /tmp/backup_test_`date+%F-%H-%M`.sql

하나의 테이블만 백업하는 경우, 라이브러리 이름 test 뒤에 테이블 이름을 추가하면 됩니다.

InnoDB 저장 엔진 테이블의 경우, 데이터베이스를 잠그지 않고도 핫 백업을 수행할 수 있습니다. 핫 백업을 수행하기 위한 옵션을 추가하십시오: --single-transaction, 예를 들어: mysqldump --databases test --single-transaction --flush-logs --master-data=2> /tmp/backup_test_`date+%F-%H-%M`.sql.

P.S. 복원할 때 이진 로그를 닫는 것을 잊지 마세요:

mysql> set sql_log_bin=0;

이는 논리적 백업 방식을 기반으로 하기 때문에 SQL을 실행하여 데이터를 삽입하면 바이너리 로그에도 기록됩니다. 복구를 위해서이기 때문에 삽입된 바이너리 로그를 꺼서 복구 시간을 단축할 수 있습니다.

MySQL 데이터베이스 백업 및 복구(Mysqldump 사용/미사용)에 대해 자세히 알아보세요.

2. LVM 스냅샷 기반 백업

물리 백업 측면에서는 파일 시스템(LVM 스냅샷)을 기반으로 한 물리 백업이 있으며, tar와 같은 명령어를 사용하여 전체 데이터베이스 디렉터리를 패키징 및 백업할 수도 있지만, 이 방법은 콜드 백업에만 사용할 수 있습니다. 저장 엔진에 따라 백업되는 내용이 달라집니다. MyISAM은 자동으로 테이블 단위까지 백업되지만, InnoDB는 독립적인 테이블 공간을 활성화하지 않으면 전체 데이터베이스만 백업할 수 있습니다.

LVM의 스냅샷 기능을 백업에 활용하는 방법에 대한 소개는 다음과 같습니다.

1) 안전을 위해 먼저 데이터베이스에 읽기 잠금을 설정하십시오

mysql>FLUSH TABLES WITH READ LOCK

2) 시간 복구를 위한 이진 로그 새로 고침

mysql>FLUSH LOGS

3) 그런 다음 스냅샷 볼륨을 만듭니다

lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata

4) 읽기 잠금을 해제하기 위해 데이터베이스에 접속합니다

UNLOCK TABLES

5) 백업을 위해 스냅샷 볼륨 탑재

mount –r /dev/myvg/data-snap /mnt/snap

6) 마지막으로 /mnt/snap 아래의 파일들을 패키징하고 백업합니다

복원을 위해:

1) mysqld 닫기

2) 그런 다음 바이너리 로그를 백업하십시오

3) 원래 백업 파일을 복원하십시오

4) 그런 다음 바이너리 로그를 통해 오류가 발생한 시점으로 복원합니다(바이너리 로그를 통해 시점 복원 시 바이너리 로그를 일시적으로 비활성화하는 것을 잊지 마세요)

2010년부터 2012년 사이에 MySQL&nbsp데이터베이스가 아마존의 클라우드 환경에 도입되었습니다. 이들은 매우 편리하고 빠른 LVM 스냅샷을 제공했습니다. LVM 스냅샷을 사용하면 아마존의 10G 네트워크 환경에서 복구 또한 매우 빠르게 이루어집니다.

3. Tar 백업

데이터베이스에서 첫 번째 데이터 백업을 준비하고, 모든 테이블을 일시적으로 잠그고 윈도우 1을 엽니다

mysql> flush tables with read lock;

쿼리 OK, 0개 행 영향(0.00초)

mysql> show master status;

+++++
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |  
| mysql-bin.000003 | 194554 | | |  
1 row in set (0.00 sec)

이 창은 종료할 수 없으며, tar 패키지가 완료될 때까지 유지되어야 합니다.

--Re-open a shell window, go to the data file directory tar package
open window 2
[root@myfstv_21_11 data]# tar -zcvf mysqla1.tar.gz mysqla
tar -zcvf mysqla1.tar.gz mysqla Compressed
Switch to window 1 and execute the unlock command

mysql> 테이블 잠금 해제;

Query OK, 0 rows affected (0.00 sec)
mysql>

tar 패키지를 다른 MySQL 라이브러리 서버로 복사하고, 데이터 디렉터리를 덮어쓴 후 MySQL 데이터베이스 서비스를 다시 시작하십시오.

4. Percona에서 제공하는 xTraBackup

innoDB의 물리적 핫백업을 지원하며, 전체 백업 및 증분 백업을 지원하고 속도가 매우 빠릅니다. innoDB 저장소로 인해 발생하는 다양한 데이터베이스 간 데이터 마이그레이션을 지원하며, 복제 모드에서 슬레이브 백업과 복구도 지원합니다. xTraBackup이 더 많은 기능 확장을 지원할 수 있도록 하려면 독립적인 테이블 스페이스를 설정하고 innoDB_file_per_table 기능을 활성화할 수 있습니다. 그러면 개별 테이블 백업을 지원할 수 있습니다.

xTraBackup 백업 원리:

xTraBackup은 innoDB의 충돌 복구 기능을 기반으로 합니다. 이는 innoDB의 데이터 파일을 복사하게 되는데, 테이블이 잠기지 않기 때문에 복사된 데이터는 일관되지 않을 수 있습니다. 복구 시에는 충돌 복구 기능을 사용하여 데이터를 일관되게 만듭니다.

InnoDB는 redo 로그(트랜잭션 로그라고도 함)를 유지하며, 이 로그에는 InnoDB 데이터에 대한 모든 변경 사항이 포함됩니다. InnoDB가 시작될 때, 데이터 파일과 트랜잭션 로그를 먼저 확인하고 다음과 같은 단계를 수행합니다:

xTraBackup이 백업을 수행할 때, 테이블을 잠그지 않고 InnoDB 데이터를 페이지 단위로 복사합니다. 동시에 xTraBackup은 또 다른 스레드를 사용하여 트랜잭션 로그를 모니터링합니다. 로그가 변경되면 해당 변경된 로그 페이지를 복사해 둡니다. 트랜잭션 로그 파일의 크기는 제한되어 있기 때문에 로그가 가득 차면 처음부터 다시 쓰기 시작하며, 이로 인해 새로운 데이터가 오래된 데이터를 덮어쓸 수 있습니다.

준비 과정에서 xTraBackup은 복사된 트랜잭션 로그를 사용하여 백업된 innoDB 데이터 파일에 대해 충돌 복구를 수행합니다.

5. Vinchin Backup & Recovery로 MySQL 데이터베이스 백업하기

Vinchin Backup & Recovery 는 가상 머신과 물리 서버 모두에서 데이터베이스를 보호할 수 있는 강력한 기능을 제공합니다. VM 수준 백업와 원활한 협업을 통해 가상 환경 사용자들에게 핵심 비즈니스 데이터 및 정보 시스템에 이중 보장을 제공합니다. Vinchin Backup & Recovery는 강력한 데이터베이스 백업 및 복원 기능을 통해 물리 및 가상 머신에 설치된 Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro, MariaDB의 보호를 지원합니다. 또한 전체 백업, 차등 백업, 증분 백업 및 트랜잭션 로그 백업 전략을 제공하여 귀사의 요구에 맞는 자체 백업 계획을 세울 수 있습니다.

Vinchin Backup & Recovery는 데이터베이스의 정상적인 운영에 영향을 주지 않으면서 MySQL, SQL Server 및 Oracle의 효율적인 핫 백업을 지원합니다.

Vinchin Backup & Recovery의 작업은 매우 간단하며, 단 몇 단계만으로 이루어집니다. 

1. 백업 대상 선택

2. 백업 대상 선택

3. 백업 전략 구성

4.작업 검토하고 제출

이 강력한 시스템을 60일 동안 완전 기능이 포함된 무료 체험판으로 사용해 보실 수 있습니다. 설치 패키지를 받으려면 버튼을 클릭하세요. Vinchin 백업 및 복구로 MySQL을 백업하는 방법에 대해 자세히 알아보려면 여기를 클릭하세요.

요약

MySQL 데이터베이스는 많은 산업 분야에서 널리 사용되고 있습니다. 이를 보다 안전하게 사용하기 위해 이 글에서는 MySQL 백업 데이터 방법 5가지를 소개합니다. 실제 상황에 따라 더 적합한 방법을 선택하시기 바랍니다. 무엇을 망설이십니까? 서둘러 MySQL 데이터베이스 백업을 진행하십시오!