MySQL 백업하기 -1
데이터베이스 백업설정
Ncloud 에서 Ocloud로 데이터베이스 이관이 성공적으로 끝났다.
이번 포스팅에서는 실시간 자동 백업을 설정하기 전에,
백업의 종류에 대해 간략하게 알아가고 백업을 진행을 하도록 하겠다.
Full-Backup
전체 데이터를 백업하는 방식이다.
보통 일단위, 주단위로 백업한다.
일단위로 받는것이 보통이다. 일주일 단위로 받으면 백업받는데 너무 오래걸리고,
서비스도 느려지기때문에 가능하면 일단위로 받는것이 좋다.
mysqldump를 사용해서 백업하면 된다.
Incremental-Backup
증분량 백업이라고도 한다. Full-Backup을 하고 다음 Full-Backup전에 쌓인 데이터(create, insert등)를
증분량 이라고 하며 이 증분량에 대한 백업을 Incremental-Backup이라고 한다.
mysqlbinlog를 사용해서 백업하면 된다.
full-backup 실습
cd /var/lib/mysql 명령어로 mysql 폴더로 이동한다.
비밀번호를 틀려서 명령어를 한번 더 실행했다.
mysqldump -u root -p -R -v testdb > backup_testdb.sql
해당 명령어를 사용해 backup_testdb.sql 파일을 만든다. -R옵션은 데이터뿐만 아니라
프로시저와 함수를 함께 백업하는 옵션으로 앞으로 자주 사용될 것이다. (routines)
-v 옵션은 해당 명령어 실행 시 모든 메시지를 출력한다. (verbose)
> 는 export 반대로 <는 import이다.
백업파일이 잘 생성되었다.
실제사례 실습
다음과 같은 상황이 발생하였다고 가정하여 실습을 진행하겠다.
- Test 테이블 생성 및 5개 데이터 추가(Insert)
- Test 테이블에서 3개 데이터 삭제
- 전체 데이터 백업 (Full-Backup)
- Test 테이블에 데이터 변경작업
- 실수로 Test 테이블의 모든 데이터 삭제
- 실수 이전(4) 까지 데이터 복구
1. Test 테이블 생성 및 5개 데이터 추가
사용할 데이터베이스를 선택하고 (use testdb)
create table 쿼리문을 사용하여 Test테이블을 추가한다.
insert into 쿼리문을 사용하여 데이터를 추가한다.
select * from Test 쿼리문을 사용해 데이터를 확인한다.
2. Test 테이블에서 3개 데이터 삭제
delete from Test where id in (3,4,5); 쿼리문으로 id가 3,4,5인 데이터를 삭제하였다.
3. 전체 데이터 백업 (Full-Backup)
위에서 사용한 mysqldump파일을 생성하는 명령어를 사용해서 fullbackup 파일을 생성한다.
fullbackup 파일이 잘 생성 되었다.
4. Test 테이블에 데이터 변경작업
insert into 쿼리문을 사용해 새로운 데이터를 추가해보자.
새로운 데이터가 추가 되었고 ccc를 대문자로 바꿔보자.
update Test set ttt = 'CCC' where id = 7 쿼리문으로 ccc를 대문자로 변경하였다.
5. 실수로 Test 테이블의 모든 데이터 삭제
상상만 해도 끔찍한 데이터 삭제를 실수로 진행해보자.
delete from Test 쿼리문을 사용하자.
에러가 발생하면서 삭제가 안된다. 에러내용은 where조건을 걸지않고 삭제나 업데이트를 할수없다는 것인데,
mysql workbench에서 친절하게도 실수를 방지하고자 삭제,업데이트 조건을 걸어두었다.
해당 조건을 workbench 환경설정에서 변경해주면 되지만, 혹여나 정말 실수 할수도있으므로
where조건을 걸어서 삭제해보자.
id는 모두 0보다 크니 id > 0 조건으로 실수로 데이터를 삭제해보자
성공적으로 삭제가 진행되었다.
복구 계획
3번 까지는 full-backup으로 데이터를 한번 백업했었다.
문제는 4번 이후로 변경한 것들의 데이터의 복구가 필요한데 이 구간을 binlog로 찾아서 복구해보자.
밑줄친 binlog파일을 가지고 해당 구간을 찾아보자.
복구시점 찾아보기
mysqlbinlog --no-defaults -v binlog.000005 > t.log
해당 명령어로 binlog전체를 가져온 뒤, vi t.log 로 찾아보자.
vi 명령어 모드에서 (esc키를 누른 상태) /13:28 을 입력하여 대략적으로 저 구간을 찾아보면
insert했던 시점을 찾을 수 있다.
at 1479가 binlog 아이디값이다. 동그라미 표시한 end_log_pos 1540 부분까지의 로그를 가지고있다는 것이다.
따라서 insert 시점은 binlog id로 1479, 시간으로는 13:19:49이다. 복구를 위해 기록해 두자.
Delete 구간 찾기
Delete 했던 시점을 찾기위해 밑으로 내려보다 보면
해당 구간을 찾을 수 있다.
동그라미 표시한 Query 부분이 테이블을 매핑하는 부분이다. 따라서 바로 앞의 id인 2018 이전으로 복구 시점을 잡으면 된다.
1939가 복구시점 binlog id이며 시간은 13:28:52이다. 복구를 위해 기록해 두자.
Time을 사용해서 복구
binlog 파일을 통해서 복구 시점을 찾을 수 있었다.
binlog id 값과 시간을 구할 수 있었는데, 각각의 값으로 복구를 할 수 있다.
우선 구했던 시간을 통해서 복구를 해보자.
Time을 사용해서 복구 시작
mysqlbinlog --no-defaults --start-datetime="2021-03-20 13:19:49" --stop-datetime="2021-03-20 13:28:52" -d testdb binlog.000005 > t2.log
날짜와 시간을 입력하고 t2.log로 해당 구간을 복구한다.
-d옵션은 특정 데이터베이스를 선택할 떄 사용한다. 현재 testdb를 사용하고있으니 testdb를 입력한다.
insert 확인
vi t2.log 명령어로 잘 생성되었는지 확인해본다. insert했던 부분이 잘 되었다.
update 확인
update했던 부분또한 잘 복구된것을 볼 수 있다.
Id값을 사용해서 복구
이번엔 id값을 사용해 복구 해보자. 위에서 사용했던 명령어에서 datetime을 position으로만 변경해주고 t3.log로 생성하면 된다.
mysqlbinlog --no-defaults --start-position="1479" --stop-position="1939" -d testdb binlog.000005 > t3.log
Id값으로 복구 시작 - insert 확인
vi t3.log 명령어로 잘 생성되었는지 확인해본다. insert부분이 잘 백업 되었다.
update 확인
마찬가지로 update도 잘 복구 되었다.
데이터베이스 복구
full-backup, incremental-backup 모두 했으니 이제 데이터베이스에 적용해보자. 순서는 아래와 같다.
- Full-Backup으로 3번까지의 데이터 복구
- Incremental-Backup으로 3번이후의 증분량에 대한 복구
데이터 복구
mysql -u root -p testdb < backup_testdb.sql 명령어로 1번 데이터를 복구한다.
데이터가 잘 복구 된 것을 볼 수 있다.
증분량 복구
mysql -u root -p testdb < t2.log 명령어로 2번 증분량을 복구한다.
증분량에 대한 데이터도 잘 복구 된 것을 볼 수 있다.
마치며
공부를 목적으로 적는 글이며 잘못된 정보가 있을 수 있습니다.
꽤 긴 포스팅이 되었다. 블로그 글을 쓸때 마다 꽤 많은 시간이 소요되지만,
블로그를 시작하길 잘 했다고 생각한다.
헤매고 이해가 잘 안되던 부분도 반복해서 해보고, 검색하면서 찾아보고 해보니
내 것이 된것같다. 나만 보는게 아니라 남이 본다고 생각하니 더 엄격해지는 느낌도 든다.
다음 포스팅에서는 실시간 백업을 진행해보도록 하겠다.