mariadb을 사용하다 보면 Binlog(바이너리 로그)가 디스크에 백업이 된다. 

디스크의 용량이 크다면 크게 상관없겠지만, 디스크 용량이 적거나 아니면 백업되는 Binlog 사이즈가 큰 경우

결국 mariadb가 원활하기 구동될 수 있도록 디스크 관리를 해줘야 한다.

이때  Binlog(바이너리 로그)가 불필요하게 너무 많이 쌓이게 되면 삭제를 진행해줘야 한다.

이번에는 Binlog(바이너리 로그)에 대해서 조회 방법, 삭제 방법, 보관 기간 설정하는 부분을 알아보도록 하자. 

그럼 먼저 Binlog(바이너리 로그)가 무엇인지 알아보자.

1. Binlog(바이너리 로그)란?

바이너리 로그는 MySQL 3.23.14 Version부터 도입되었으며, Create, Drop과 같은 DDL문과 Insert, Update, Delete와 같은 DML문을 통해서 데이터의 변화가 발생할 경우 해당 이벤트들을 기록하는 로그 파일이다.

DDL / DML문에 대해서는 아래 내용을 참고하도록 하자.

2. Binlog(바이너리 로그) 조회 방법

먼저 mariadb Data가 쌓이는 디렉토리에서 Binlog(바이너리 로그)가 얼마나 쌓였는지 확인해보자. 

• $ ls -alth binlog*

이와 같이 Binlog(바이너리 로그)가 순차적인 번호를 이용하여 많은 용량을 쌓아가는 것을 확인할 수 있다. 

이때 해당 디렉토리에서 Binlog(바이너리 로그)를 바로 삭제하면 문제가 발생할 수 있으니, 해당 디렉토리에서 먼저 확인 후 꼭 mariadb 내부에서 삭제 명령어를 통해서 삭제하도록 하자.

mariadb 내부에서는 아래 명령을 사용하여 Binlog(바이너리 로그)를 조회할 수 있다. 

• mysql> show binary logs;

3. Binlog(바이너리 로그) 삭제 하기

이제 확인한 Binlog(바이너리 로그)에서 오래된 파일을 삭제해보도록 하자.

위에서도 이야기했지만 꼭 mariadb 내부에서 삭제 명령어를 통해서 삭제하도록 하자.

아래 명령어를 통해서 삭제할 수 있다. 

• mysql> purge master logs to 'binlog.xxx';
  • 삭제할 binlog 번호를 입력하여 이 이전 데이터도 한 번에 삭제할 수 있다.  

이처럼 mariadb Data가 쌓이는 디렉토리에서도 삭제된 것을 확인할 수 있다. 

4. Binlog(바이너리 로그) 보관 기간 조회

이렇게 매번 특정 시점마다 Binlog(바이너리 로그)를 삭제하는 방법도 있겠지만, 보관 기간을 설정하여 해당 기간까지만 Binlog(바이너리 로그)가 남도록 설정하는 것도 방법이다. 

그럼 먼저 Binlog(바이너리 로그) 보관 기간을 조회해보도록 하자.

조회하는 명령어는 아래와 같다. 

• mysql> show global variables like 'binlog_expire_logs_seconds';

mariadb Version에 따라서 기존에는 expire_logs_days를 사용하는 부분도 있으나, MySQL 8.x Version부터는 binlog_expire_logs_seconds를 사용한다.

보관 기간은 second(초)를 기반으로 계산된다. 

현재 기본값(Default)으로 설정된 값은 2,592,000 초이다. 

이것으로 일자로 환산해보면 30일이다. 

환산하는 방법은 잘 알고 있겠지만. 

1일 = 24시간 / 24시간 = 1440분 / 1440분 = 86,400초 를 기반으로 86,400초 * 30일 = 2,592,000 초가 된다. 

5.Binlog(바이너리 로그) 보관 기간 설정

이제 Binlog(바이너리 로그) 보관 기간을 설정해보도록 하자. 

보관 기간은 3일로 설정하고 설정하는 명령어는 아래와 같다. 

• mysql> set global binlog_expire_logs_seconds=259200
  • 86,400초 * 3일 = 259,200 초

6. 참고 문서

• https://dev.mysql.com/doc/internals/en/binary-log-overview.html

🔍 MariaDB 10.6 OLTP 환경 - 시퀀스 채번 지연 문제 분석 정리

1️⃣ SHOW ENGINE INNODB STATUS\G 분석 방법

1. TRANSACTIONS 섹션
   • LOCK WAIT가 있으면 해당 트랜잭션이 다른 트랜잭션을 기다리고 있음.
   • ACTIVE N sec → 실행 시간이 길면 지연 가능성이 높음.
   • undo log entries 값이 크면 롤백 시 영향을 미칠 수 있음.
2. WAITING FOR LOCKS 섹션
   • lock_mode X waiting → 해당 행이 잠겨 있고, 다른 트랜잭션이 대기 중.
   • table test.sequence_table → 어느 테이블에서 잠금이 발생하는지 확인.
3. LATEST DETECTED DEADLOCK 섹션
   • 데드락 발생 시 충돌한 트랜잭션이 표시됨.
   • TRANSACTION ID를 확인하여 원인 분석 가능.

2️⃣ SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%'; 해석

✅ 현재 조회된 값 분석

🚨 문제점

• 트랜잭션이 평균 15초 이상 대기하고 있음.
• 최대 20분 동안 대기한 트랜잭션이 있음.
• 116번의 충돌 발생 → 경쟁이 심한 테이블일 가능성 높음.

🔧 해결책

1. SHOW ENGINE INNODB STATUS\G 실행하여 대기하는 트랜잭션 확인.
2. innodb_lock_wait_timeout을 120초에서 30초 이하로 줄여서 테스트.
3. INSERT ... ON DUPLICATE KEY UPDATE 방식 최적화 (필요 시 REPLACE INTO 또는 UUID 사용 고려).

3️⃣ innodb_lock_wait_timeout을 줄이면?

• 현재 값: 120초 → 대기 시간이 길어 트랜잭션이 지연됨.
• 예를 들어 30초로 설정하면:
  • 30초 동안 잠금을 얻지 못한 트랜잭션은 자동 롤백됨.
  • 롤백된 트랜잭션이 다시 시도할 수 있도록 애플리케이션 로직 필요.

🚨 세션을 새로 맺는 것이 아니라, 대기 시간이 초과된 트랜잭션이 롤백됨.

4️⃣ MariaDB는 AUTO COMMIT인가?

✅ 기본적으로 AUTO COMMIT이 활성화되어 있음 (autocommit = 1)

• 명시적으로 BEGIN; 또는 START TRANSACTION;을 사용하지 않으면 자동 커밋됨.
• 하지만, 다음과 같은 경우 트랜잭션이 롤백될 수 있음:
  1. LOCK WAIT TIMEOUT 초과 → 강제 롤백됨.
  2. 데드락 감지 → 자동 롤백됨.
  3. 애플리케이션에서 명시적으로 ROLLBACK; 실행.

🚨 INSERT 후에도 롤백될 수 있는 이유?

• INSERT ... ON DUPLICATE KEY UPDATE가 실행되었지만, 다른 트랜잭션과 충돌하여 대기.
• innodb_lock_wait_timeout 초과 또는 데드락 발생 시 자동 롤백됨.
• SHOW ENGINE INNODB STATUS\G에서 "ROLLING BACK" 메시지 확인 가능.

✅ 추천 해결 방법

1. 대기 중인 트랜잭션 분석
   • SHOW ENGINE INNODB STATUS\G 실행 후, LOCK WAIT 상태 확인.
   • SHOW PROCESSLIST; 실행하여 대기 중인 쿼리 확인.
2. innodb_lock_wait_timeout 조정
   • 현재 120초 → 30초로 조정 후 테스트.
3. INSERT 방식 개선
   • INSERT ... ON DUPLICATE KEY UPDATE 대신 REPLACE INTO 또는 UUID 방식 검토.
4. 트랜잭션 길이 최소화
   • BEGIN ... COMMIT 사용 시, 가능한 한 빨리 커밋하도록 개선.
5. 경합이 심한 테이블 분리 또는 샤딩 고려
   • 특정 테이블이 경합이 심하면, 분리하여 부하를 줄이는 것도 방법.

날짜, 시간 표현

#기본시간 형식 "2021-03-24 09:26:25"
SELECT NOW();
 
#날짜만 "2021-03-24"
SELECT DATE(NOW());
 
#시간만 "09:26:25"
SELECT TIME(NOW());
 
#년도만 "2021"
SELECT YEAR(NOW());
 
#월만 "3"
SELECT MONTH(NOW());
 
#날짜만 포맷지정 "2021/03/24"
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d');
 
#시간만 포맷지정 "09 26 25"
SELECT DATE_FORMAT(NOW(), '%H %i %s');

조건문

#최근 하루
SELECT * FROM table_a WHERE create_dt BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY ) AND NOW();
 
#최근 일주일
SELECT * FROM table_a WHERE create_dt BETWEEN DATE_ADD(NOW(), INTERVAL -1 WEEK ) AND NOW();
 
#최근 한달
SELECT * FROM table_a WHERE create_dt BETWEEN DATE_ADD(NOW(), INTERVAL -1 MONTH ) AND NOW();
 
#특정 날짜 지정 방법1(2021년 3월 24일)
SELECT * FROM table_a WHERE create_dt >= '2021-03-24 00:00:00' AND create_dt <= '2021-03-25 00:00:00';
 
#특정 날짜 지정 방법2(2021년 3월 24일)
SELECT * FROM table_a WHERE DATE_FORMAT(create_dt, '%Y-%m-%d') = '2021-03-24';
 
#날짜 조건
SELECT * FROM table_a WHERE DATE(create_dt) BETWEEN '2020-01-01' AND '2020-12-31';
SELECT * FROM table_a WHERE DATE(create_dt) >= '2020-01-01' AND DATE(a.create_dt) <= '2020-12-31';
 
#월별 조건
SELECT * FROM table_a WHERE DATE_FORMAT(create_dt, '%Y-%m') BETWEEN '2021-01' AND '2021-03';

InnoDB Buffer Pool과 innodb_buffer_pool_dirty 이해하기

InnoDB는 MySQL과 MariaDB의 저장 엔진 중 하나로, 데이터베이스의 데이터와 인덱스를 메모리에 캐싱하여 디스크 I/O를 줄이고 성능을 향상시키기 위해 Buffer Pool을 사용합니다. 이번 포스팅에서는 InnoDB Buffer Pool과 관련된 중요한 개념인 innodb_buffer_pool_dirty에 대해 자세히 설명하겠습니다.

InnoDB Buffer Pool

InnoDB Buffer Pool은 InnoDB에서 가장 중요한 메모리 구조 중 하나입니다. 이는 데이터베이스에서 자주 사용되는 데이터를 메모리에 저장하여 디스크 I/O를 줄이고, 데이터베이스의 성능을 향상시키는 역할을 합니다.

Dirty Pages

Dirty Pages란 버퍼 풀에 있는 데이터 페이지 중 디스크에 아직 기록되지 않은 페이지를 말합니다. 이러한 페이지들은 메모리 내에서는 수정되었지만 아직 영구 저장소인 디스크에 쓰여지지 않은 상태입니다.

innodb_buffer_pool_dirty

innodb_buffer_pool_dirty는 이러한 더티 페이지의 양을 나타냅니다. 이 값이 높을 경우, 많은 페이지가 디스크에 기록되지 않았음을 의미하며 이는 잠재적으로 성능 문제를 야기할 수 있습니다:

1. 데이터 일관성 문제: 시스템이 충돌하거나 정전이 발생하면, 더티 페이지가 디스크에 기록되지 않았기 때문에 데이터 손실이 발생할 수 있습니다.
2. 디스크 I/O 성능 저하: 더티 페이지를 디스크에 기록하는 과정에서 디스크 I/O가 집중적으로 발생하여 성능이 저하될 수 있습니다.

모니터링 및 튜닝

innodb_buffer_pool_dirty를 모니터링하고 최적의 성능을 유지하기 위해 몇 가지 설정을 조정할 수 있습니다:

1. innodb_max_dirty_pages_pct: 버퍼 풀에서 더티 페이지가 차지할 수 있는 최대 비율을 설정합니다. 기본값은 75%이며, 이 값을 낮추면 더 자주 더티 페이지를 디스크에 기록하게 됩니다.
2. sql

   코드 복사
   SET GLOBAL innodb_max_dirty_pages_pct = 50;
3. innodb_flush_neighbors: 더티 페이지를 플러시할 때 인접한 페이지도 함께 플러시할지 여부를 설정합니다. 이 값을 설정하면 플러시 효율이 향상될 수 있습니다.
4. sql

   코드 복사
   SET GLOBAL innodb_flush_neighbors = 0;
5. innodb_flush_log_at_trx_commit: 트랜잭션이 커밋될 때 로그를 플러시하는 빈도를 제어합니다. 기본값은 1로 설정되어 있으며, 이 경우 모든 트랜잭션 커밋 시 로그가 디스크에 기록됩니다. 2로 설정하면 더티 페이지를 플러시하는 빈도를 줄일 수 있습니다.
6. sql

   코드 복사
   SET GLOBAL innodb_flush_log_at_trx_commit = 2;

이러한 설정을 통해 innodb_buffer_pool_dirty를 관리하고 MariaDB의 성능을 최적화할 수 있습니다. 성능과 데이터 일관성 사이에서 균형을 맞추는 것이 중요합니다.

✅ MariaDB, MySQL root 패스워드 초기화 진행

먼저 구동 중인 데몬을 정지 시킵니다.

1. MariaDB 안전 모드 실행: mariadbd-safe

MariaDB일 경우

2. MariaDB 안전 모드 실행: MySQL_safe

Mariadb 일 경우

* mariadb  설치경로 ( ex - mariadb/instance/bin/  안에 

mariadb-safe --defaults-file=/DEV_DB/data/forshop/my.cnf --skip-grant & 실행

서버에서 사용하는 버전에 맞게 패스워드 접속을 시도합니다.

## sock

mysql -u root -p -S /tmp/forshop.sock

mariadb -u root -p
mysql -u root -p

이제 패스워드 변경을 진행합니다.

# mysql 5.6 이하(마리아 DB 5 버전)
mysql> update mysql.user set password = password('패스워드') where user = 'root';
# mysql 5.7 이상 및 MariaDB 10 버전 이상
ALTER USER 'root'@'localohst' IDENTIFIED BY '새로운패스워드';

✅ root 권한이 날라갔을 경우 

mariadb 10.4 버전 이후 부터는 mysql.user 테이블이 없어지고

global_priv 테이블로 변경이 되었다. 

root 의 권한이 날라갔을 경우 다른 instance 의 모든 권한이 있는 계정을 참고 하여

mysql.global_priv 테이블의  priv 컬럼의 값을 복사하여권한이 없는 instance 에 업데이트 쳐주면 정상적으로 권한이 부여된다. 

✅ 안전모드 종료하기

모든 작업이 진행되었으면 안전 모드 종료를 진행합니다.

Mysql FEDERATED Engine 으로 dblink 구현하기

Mysql 과 Mariadb에서 지원하는 FEDERATED 기능은 사실 Oracle에서 써오던 dblink와는 개념이 다릅니다.

Oracle의 dblink가 다른 DB로 어떤 세션을 열어주고, 접속하고나면 권한이 허용된 범위내에서 다양한 작업을 자유롭게 할 수 있는 반면, Mysql 과 Mariadb에서 지원하는 FEDERATED 기능은 각자 다른 DB간의 테이블 동기화의 개념에 더 가깝습니다.

가볍고 간편하게, 별도의 ETL Tool을 쓰지 않고도 물리적으로 다른 DB간에 데이터를 동기화 할 수 있는 좋은 방법 입니다.

아래 그림과 같이 원본 <original> DB 서버의 어떤 table에 데이터를 넣으면 대상이 되는 <target> DB 서버의 특정 테이블에 해당 데이터가 입력되어지는 로직 입니다.

그래서 기능 명칭도 FEDERATED 라고 명명 된것 같습니다.

FEDERATED 는 InnoDB 또는 MyISAM과 같이 Mysql과 MariaDB내 스토리지 엔진의 한 타입중 하나 입니다.

해당 기능명인 FEDERATED 의 의미를 한번 찾아 봤습니다.

<연합하다> 라는 뜻이네요.

말그대로 다른 DB서버의 데이터와 연합을 한다 라는 의미인것 같습니다.

아무튼, 이젠 이 FEDERATED 방식을 이용하는 방법을 안내해 드리겠습니다.

Target DB에 FEDERATED storage engine 인스톨

우선 Target DB, 즉 원본 데이터를 받아서 저장 할 대상 DB에 접속 해서 아래와 같은 명령어를 실행해 봅니다.

저같은 경우는 아래와 같이 결과가 나왔습니다.

여기서 FEDERATED storage engine 인스톨 해줍니다.

인스톨을 하고 나서 다시 아래 명령어를 실행해 봅니다.

그러면 아래와 같이 FEDERATED storage engine 이 설치 된 것을 확인 할 수 있습니다.

Original DB 에 원본테이블 생성

다음은 Original DB에 접속을 하여 Target DB로 보낼 데이터가 저장 될 테이블을 하나 생성 합니다.

생성이 잘 되셨나요?

아직 데이터를 넣지는 말고, 다시 Target DB로 가서 작업을 이어갑니다.

Target DB에 FEDERATED storage engine 테이블 생성

다시 Target DB로 옵니다. 여기서 FEDERATED storage engine 테이블을 생성을 하는데, 이게 가장 중요 합니다.

자, Target DB에서 위와 같이 Original DB에 만든 테이블과 똑같이 생긴 테이블을 생성 해줍니다.

단, 스키마명과 테이블명은 달라도 됩니다. 하지만 컬럼은 같아야 합니다.

그리고 create table 문 마지막 부분에 engine type을 FEDERATED로 지정을 하고, 그 아래 CONNECTION 부분을 잘 써줘야 합니다.

위 예문을 기준으로 설명을 드리면 db_계정과 계정_비밀번호를 넣으시고, XXX.XXX.XXX.XXX 에 original DB의 IP와 그 뒤에 port 번호를 써줍니다.

그리고 "/" 뒤에 original DB내 동기화할 테이블이 있는 스키마명을 넣고, "/"로 구분을 지어준 다음 동기화할 테이블명을 적어 줍니다.

위에 예로든 DDL 두개의 빨간색으로 표시한 부분이 서로 같아야 한다는 의미 입니다.

만약, 이렇게 잘 했는데도 아래와 같이 연결 오류가 뜬다면 방화벽 내지는 접속권한, 비밀번호 오류등을 확인 해 보시기 바랍니다.

FEDERATED 테스트

자, 여기까지 잘 진행이 되셨다면 이젠 동기화가 되는지 테스트를 해볼 차례 입니다.

우선 원본(original DB) 와 대상(Target DB)의 테스트 테이블이 잘 생성 되었고, 데이터가 없는것을 확인 한 후에,

원본(original DB) 에 데이터를 넣어 보겠습니다.

위와 같이 두개의 데이터를 넣었습니다.

먼저 원본에서 확인을 해보겠습니다.

데이터가 잘 들어갔습니다.

그럼 FEDERATED 되어 있는 Target DB에서 데이터를 확인 해보겠습니다.

원본 original DB와 똑같은 데이터를 확인 할 수 있습니다.

이렇게 insert 한 데이터도 동기화가 되고, 삭제를 하거나 업데이트를 해도 동기화가 잘 되는것을 확인 했습니다.

여러분들도 한번씩 확인을 해보시길 바랍니다.

오늘은 여기까지 하겠습니다.

감사합니다~!!

출처: https://stricky.tistory.com/325 [The DataBase that i am good at:티스토리]