mysql binary log 설정

mysql 바이너리 로그는
쿼리 수행을 로그로 남기는것.

로그 백업으로 사용되어
복구할때도 쓰일 수도 있으며,
Replication 사용시 동기화에도 사용이 됨.

binary 주요 파라미터 정보

파라미터값 (value)	설 명 (description)
log-bin	바이너리 로그 경로
binlog_cache_size	바이너리 로그 캐시 사이즈
max_binlog_size	바이너리 로그 최대 사이즈
expire_logs_days	보관기간
binlog_format	바이너리 로그 포맷(binary logging format - mixed recommended) statement 쿼리문으로 기록되는 형식이고 용량을 적게 차지합니다. 버전 특성을 타지 않습니다.(sysdate()와 now()의 결과가 다른경우 등) 하지만 복구시 일관된 데이터에 대한 보장이 적으며 쿼리기반이기 때문에 복구(동기화)시 느려질 수 있습니다. row 변경된 데이터 기반으로 기록됩니다. 복구 시 일관성을 보장받을 수 있으며 빠른 복구(동기화)가 가능하지만. 용량이 커지는 단점이 있습니다. mixed statement와 row방식의 장점을 취합한 형태로 기본적으로 statement방식을 취하나 필요에 따라 row방식으로 기록합니다. <주의 해야할 점> isolation level이 read-committed일 경우 현재 트랜잭션이 종료되지 않았더라도 다른 트랙잭션에서 동일한 데이터의 commit이 일어나면 현재 트랜잭션에서도 변경된 값이 보이게 됩니다.이런 환경에서 statement방식을 사용하게 되면 트랜잭션 단위로 순서대로 로깅하기 때문에 복구나 슬레이브동기화시 원하는 바와 달리 다른 결과가 나타날 수 있습니다. 그렇기 때문에 read-committed에서는 row방식이나 이런 상황에서 자동으로 row형태로 변경해주는 mixed방식을 사용해야 합니다.

예시)

[mysqld]
log-bin=mysql-bin
binlog_cache_size=10M
max_binlog_size=512M
expire_logs_days=3
binlog_format=mixed

예시와 같이 설정 후 서비스 재기동하게되면 아래처럼 바이너리 로그가 생긴것을 확인할수 있음.

[darksharavim]ls -1 | grep mysql-bin
mysql-bin.000001
mysql-bin.index

바이너리로그 확인

MariaDB [sensmail]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |    850378 |
+------------------+-----------+
1 row in set (0.000 sec)

바이너리 로그 정리

MariaDB [sensmail]> reset master;
Query OK, 0 rows affected (0.012 sec)

MariaDB [sensmail]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.000 sec)

바이너리 로그 복원

MariaDB [testdb]> use testdb;
Database changed
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test             |
+------------------+
1 row in set (0.001 sec)

MariaDB [testdb]> select * from test;
+------+
| a    |
+------+
| 1    |
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
+------+

# testdb 덤프
[darksharavim]mysqldump --single_transaction --databases testdb --flush_logs -h localhost -p  > test_db_dump.sql
Enter password:

# 덤프후 데이터 추가
MariaDB [testdb]> insert into test (a) values (7),(8);
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [testdb]> select * from test;
+------+
| a    |
+------+
| 1    |
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
| 7    |
| 8    |
+------+
9 rows in set (0.001 sec)

바이너리로그를 텍스트로 변환

[darksharavim]mysqlbinlog --database testdb mysql-bin.000002 > testdb_binlog.sql
mysqlbinlog: unknown variable 'default-character-set=utf8'

# mysqlbinlog: unknown variable 'default-character-set=utf8' 에러발생할 경우
# /etc/my.cnf에서 옵션 주석처리

데이터베이스 삭제

# 데이터베이스 삭제
MariaDB [testdb]> drop database testdb;
Query OK, 1 row affected (0.024 sec)

덤프파일복원

[darksharavim]mysql -p < test_db_dump.sql

확인

MariaDB [(none)]> use testdb
Database changed
MariaDB [testdb]> select * from test;
+------+
| a    |
+------+
| 1    |
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
+------+
7 rows in set (0.000 sec)

바이너리 로그 복원

[darksharavim]mysql -p < testdb_binlog.sql

MariaDB [testdb]> select * from test;
+------+
| a    |
+------+
| 1    |
| 1    |
| 2    |
| 3    |
| 4    |
| 7    |
| 8    |
+------+
7 rows in set (0.000 sec)

특정날짜 시간 쿼리로그 확인

# 2015년 12월 15일부터 2015년 12월 16일까지 바이너리 로그 추출하여 저장.
[darksharavim]mysqlbinlog 
--no-defaults 
--start-datetime="2015-12-15 00:00:00" 
--stop-datetime='2015-12-16 00:00:00' 
mysql-bin.000053 > /root/binlog/bin.sql

mysqlbinlog option
--database=[DB name] : 특정 db에 대해서만 출력.
--short-form or -s : 일반쿼리만 출력.
--start-datetime="yy-mm-dd hh:mm:ss"
--stop-datetime="yy-mm-dd hh:mm:ss"
지정한 시간부터 지정한시간까지 출력.