MySQL 바이너리 로그는 무엇이고 어떻게 활성화하나요?

💡 요약 정리

mysql binlog는 mysql 데이터베이스의 업데이트와 관련된 내용을 이벤트 형태로 기록합니다.
binlog는 바이너리 형식으로 기록되며, 데이터 업데이트 시 수행 시간 정보도 포함하고 있습니다.
데이터베이스의 복원을 위해서는 binlog가 필요하며, 리플리케이션 구성을 할 때는 binlog를 기반으로 slave 서버가 동기화되기 때문에 반드시 활성화해야 합니다.

mysql binlog는 mysql 데이터베이스의 업데이트와 관련된 내용을 이벤트 형태로 기록합니다.

binlog는 바이너리 형식으로 기록되며, 데이터 업데이트 시 수행 시간 정보도 포함하고 있습니다.

데이터베이스의 복원을 위해서는 binlog가 필요하며, 리플리케이션 구성을 할 때는 binlog를 기반으로 slave 서버가 동기화되기 때문에 반드시 활성화해야 합니다.

1. binlog 활성화

my.cnf 파일에 log-bin 옵션을 추가하여 mysql 데몬을 restart합니다.

binlog는 mysql 데이터 디렉토리에 "호스트명-bin.index" 와 "호스트명-bin.00000x" 로 파일을 생성하여 로그를 기록합니다.

별도의 파일명이나 경로에 저장할 경우는 파일명이나 경로를 지정할 수 있습니다.

별도의 경로에 저장할 경우는 해당 디렉토리가 mysql user 권한으로 쓰기 가능해야 합니다. 권한이 없을 경우는 mysql 데몬이 실행되지 않습니다.

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock

back_log = 50

max_connections = 100
max_connect_errors = 10
table_open_cache = 2048

max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M

~

log-bin                   -> mysql 데이터 디렉토리에 "호스트명-bin.xxxx" 형태로 binlog를 기록
 or
log-bin=mysql-bin         -> mysql 데이터 디렉토리에 "mysql-bin.xxxx" 형태로 binlog를 기록
 or
log-bin=/var/log/mysql-bin  -> /var/log 디렉토리에 "mysql-bin.xxxx" 형태로 binlog를 기록

binlog_format=mixed

log

slow_query_log
long_query_time = 2

~

2. binlog 확인

mysql binlog는 바이너리 형태로 기록되기 때문에 cat이나 vi로는 확인이 불가능합니다.

mysqlbinlog 명령을 통해서 아래와 같이 확인 가능합니다.

[root@localhost data]# mysqlbinlog mysql-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130226 10:22:54 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.28-log created 130226 10:22:54 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 107
#130226 10:24:32 server id 1  end_log_pos 176   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1361841872/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
BEGIN
/*!*/;
# at 176
#130226 10:24:32 server id 1  end_log_pos 295   Query   thread_id=1     exec_time=0     error_code=0
use mysql/*!*/;
SET TIMESTAMP=1361841872/*!*/;
update user set password=password('') where user='root'
/*!*/;
# at 295
#130226 10:24:32 server id 1  end_log_pos 365   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1361841872/*!*/;
COMMIT
/*!*/;
# at 365
#130226 10:24:37 server id 1  end_log_pos 445   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1361841877/*!*/;
flush privileges
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@localhost data]#

3. mysql binlog 옵션

max_binlog_size: 바이너리 로그 파일 크기 설정
binlog_cache_size: non-트랜잭션 테이블을 업데이트 할 때 바이너리 로그 저장 용량
max_binlog_cache_size: 다중-명령문 트랜잭션 캐시의 전체 크기
binlog_cache_use: 명령문을 저장하기 위해 버퍼를 사용한 트랜잭션 숫자 표시
binlog_cache_disk_use: 임시파일을 실제로 사용한 트랜잭션 숫자 표시
expire_logs_days=3: 바이너리 로그 보관 기간 (예: 3일)

4. mysql binlog를 통한 테이블 복구

mysqlbinlog의 --stop-date 옵션을 사용하여 binlog가 생성된 시점부터 특정시점까지의 데이터를 복구할 수 있습니다.

아래와 같은 방법으로 --stop-date 옵션에 명시한 시점의 데이터를 복구할 수 있습니다.

[root@localhost data]# mysqlbinlog  --stop-date="2012-11-23 16:00:00" mysql-bin-000012 > mysql.sql
[root@localhost data]# mysql -u root  -d db_name < mysql.sql