백업없는 MySQL Database bin log를 이용하여 복구

워드프레스 블로그 테마를 변경하다가 Database를 날렸다. 아직 자동 백업을 구축하기 전이라 수동으로 테이블을 백업해놓고 진행했다. 그래서 마음 놓고 이것 저것만지다가 다시 DB를 복구하려고 하는데… 다른 DB를 백업떠놓고 착각해둔걸 깨달았다.

Mysql bin log를 찾아 쿼리로 변경한 후, 백업해야 했다.

MySql Bin log

mysql/data 폴더에 보면 binlog.00000n 형태로 mysql bin log가 저장되어 있다.

나의 경우 bitnami를 이용해 워드프레스를 설치해서 경로는 /opt/bitnami/mysql/data/ 이다.

아래와 같이 bin log 을 확인할 수 있다.

MySQL Bin log을 Sql로 변환

저장된 bin log를 mysql이 읽을 수 있게 쿼리문으로 변경해줘야한다. binlog.000002를 backup.sql 파일에 저장하는 방법은 아래와 같다.

mysqlbinlog -d [[Database Name]] binlog.000002 > backup.sql

나는 데이터가 한글 형태였고, 21년 4월 23일까지의 bin log가 필요했기에 아래와 같은 옵션을 추가했다.

mysqlbinlog --set-charset='utf8' -d [[Database Name]] binlog.000002 --stop-datetime="2021-04-23 00:00:00" > backup.sql

--set-charset='utf8'은 한글 인코딩을 위해 추가했고,

--stop-datetime은 기간을 주기위해 추가한 옵션이다. --start-date 옵션을 사용하면 시작 날짜도 지정가능하다.

나의 경우에는 bin log가 binlog.000004까지 있어서 나머지 bin log 파일도 backup.sql에 쿼리로 추가했다.

mysqlbinlog --set-charset='utf8' -d [[Database Name]] binlog.000003 --stop-datetime="2021-04-23 00:00:00" >> backup.sql

mysqlbinlog --set-charset='utf8' -d [[Database Name]] binlog.000004 --stop-datetime="2021-04-23 00:00:00" >> backup.sql

MySQL에 복원

원하는 백업 쿼리가 backup.sql에 전부 저장됐다면 import하면 된다.

mysql -f -uroot -p [[Database Name]] < backup.sql 

unknown variable ‘default-character-set=utf8mb4’ Error

unknown variable 'default-character-set=utf8mb4' 에러가 발생한다면 mysql/my.cnf에서 [client] 아래 default-character-set=utf8mb4를 주석처리한다.

[mysqladmin]
user=root

[mysqld]
basedir=/opt/bitnami/mysql
datadir="/opt/bitnami/mysql/data"
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
tmpdir=/opt/bitnami/mysql/tmp
max_allowed_packet=32M
bind-address=127.0.0.1
skip-name-resolve=1
binlog_expire_logs_seconds=604800
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

default-authentication-plugin=mysql_native_password
[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock

#default-character-set=utf8mb4
[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid-file=/opt/bitnami/mysql/tmp/manager.pid
default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin


!include /opt/bitnami/mysql/bitnami/my.cnf

백업을 완료한 후에는 다시 주석을 해제하면 된다.