안녕하세요, 대담이입니다!

오랜만에 MySQL 인터널에 대해 정리해보겠습니다. MySQL의 메모리 구조 중, Change buffer입니다. 먼저, 이에 대한 더 자세한 내용은 아래 문서를 통해 자세히 확인하실 수 있습니다.

체인지 버퍼 개요

체인지 버퍼는 세컨더리 인덱스 페이지들의 변화를 캐시하는 메모리 영역인데요 이는 해당 페이지들이 버퍼풀에 있지 않을 때 사용되고, 이는 만약 쿼리에서 DML을 수행한 경우, 세컨더리 인덱스의 변경을 저장하여 이를 향후 정기적으로 머지하고, 페이지가 버퍼풀로 읽기 작업에 의해 로드될 때 머지됩니다.

즉, 세컨더리 인덱스와 관련된 데이터페이지가 버퍼풀에 없을 경우에는 변화는 체인지 버퍼에만 저장되고, 만약 해당 인덱스 페이지가 체인지 버퍼에 있을 때, 페이지에 있는 데이터가 체인지버퍼로부터 버퍼 풀로 적용(머지) 됩니다. 

그리고 퍼지 작업을 통하여 세컨더리인덱스의 변화가 디스크로 적혀집니다. 이는 보통 idle, slow shultdown 등의 작업에 일어납니다. 이로 인하여 디스크 IO를 효율적으로 가져갈 수 있는 것이죠.

이 체인지 버퍼는 유니크 키에서는 사용되지 않으며, 몇가지 변수를 통하여 설정 가능합니다.

관련 변수값 설정

innodb_change_buffer 변수의 값은 아래와 같습니다.

• all : 기본값으로, 버퍼 insert, delete-marking, purge 작업을 모두 수행합니다.
• none : 체인지 버퍼작업을 진행하지 않습니다.
• inserts : 인서트 작업을 버퍼합니다.
• deletes : delete-marking 작업을 버퍼합니다.
• changes : insert, delete-marking 작업을 버퍼합니다.
• purges : 백그라운드에서 진행되는 물리 삭제 작업을 버퍼합니다.

이에 대한 버퍼값을 innodb_change_buffer_max_size 변수를 통하여 최댓값을 정할 수 있으며, 기본값을 25퍼센트이고 최대 50%까지 설정하실 수 있습니다. 이는 높은 DML 오퍼레이션 작업에서 해당 값을 높이기를 고려하실 수 있습니다. 이와 반대로 정적으로 데이터를 사용하거나, 체인지 버퍼가 메모리 영역을 너무많이 사용하는 경우 줄일 수있습니다.

체인지 버퍼 모니터링

체인지 버퍼는 SHOW ENGINE INNODB STATUS 명령의 출력밧을 통하여 모니터링할 수 있으며, INSERT BUFFER AND ADAPTIVE HASH INDEX 섹션을 통해 확인하실 수 있습니다. 또한 아래 명령을 통해서도 관련 지표를 모니터링하실 수 있습니다.

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G

-출처  Change buffer : https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html

이상 MySQL의 Change buffer의 아키텍쳐를 안내드렸으며, 퍼포먼스 이슈시 만약 워크로드 및 지표에서 해당 메모리 지표의 변경이 필요한 경우, 이 내용을 통하여 변경 및 fine tuning을 고려하시길 추천드려요

이상, 대담이였습니다 :) 

안녕하세요! 대담이입니다. 벌써 10월의 마지막이 다가오고 다음주부터는 11월의 시작이 다가오네요 :) 

월동준비는 다들 잘 되고 계신지요?

오늘은 MySQL 의 엔진의 스토리지 엔진이라는 특성에 대해 안내드리고, 이중 가장 일반적인 목적에서 많이 사용되고, 높은 퍼포먼스를 보이는 InnoDB 스토리지 엔진에 대해 안내드리겠습니다 :) 

오늘 다룰 내용은 모두 MySQL 공식 레퍼런스 문서 (https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html ) 를 참고했음을 미리 안내드리며, 시작해보겠습니다!

스토리지 엔진이란?

앞서 안내드렸던 MySQL 아키텍쳐에서 보듯이, MySQL은 여러가지의 스토리지 엔진을 채택하여 테이블을 생성할 수 있습니다. 이를 통해 사용자는 목적에 맞는 특화된 스토리지 엔진을 사용할 수도 있는 구조입니다. 아래에 간단한 예시를 들어드리겠습니다.

• 사용자가 캐시용도의 테이블을 운영하고 싶을 때(데이터가 날아가도 문제 없으나 빨라야함) : MEMORY
• 로깅 형태의 데이터 저장을 원하는 경우 (트랜잭션은 필요하지 않고 입력속도가 빨라야 함 ) : MyISAM
• RDBMS의 기본인 트랜잭션을 지원해야하고, 장애에 내구성이 강해야하는 일반적인 경우 : InnoDB
• 클러스터 구성을 통하여 쓰기 분산등의 이점을 얻고 싶은 경우 : NDBCluster

정도로 앞선 네개 엔진의 특성을 설명 드릴 수 있고, 추가로 사용자가 원하는 스토리지 엔진을 찾아 설치할 수도있습니다. 

이중, 가장 일반적으로, 그리고 재해에 강한 RDBMS의 특성을 가장 잘 반영하는 스토리지 엔진은 InnoDB라고 볼 수 있겠네요. 오늘은 이에 대해 정리를 해드리겠습니다!

먼저 InnoDB의 특장점부터 설명드리자면 아래와 같습니다 😁

• 트랜잭션을 지원합니다(ACID 지원) -> 어찌보면 다른 DBMS에서는 당연히 지원해야하는 것일 수 있겠지만, MySQL의 다른 스토리지 엔진은 성능 등 다른 이유로 트랜잭션을 지원하지 않는 경우가 있습니다. 그러므로 RDBMS의 가장 기본적 특성인 트랜잭션을 지원한다는 것은 아주 매력적인 부분입니다.
• Row-level 잠금을 지원합니다 -> 잠금을 위하여, Row 단위의 잠금을 사용하므로 DML의 동시성을 늘릴 수 있습니다. 
• Primary Key 위주의 정렬로 Primary Key 기준의 쿼리 수행시 가장 최적화된 쿼리를 수행 가능합니다. 이에 대해서는 차후 클러스터드 인덱스 항목을 통하여 더 자세히 구조에 대해 다뤄보겠습니다. 
• 데이터 정합성을 보장하기 위하여 외래키 기능을 지원합니다.
• 기본적으로 읽기시, 잠금 없는 일관된 읽기를 지원합니다. MVCC, 혹은 버전 컨트롤이라 불리는 이 내용에 대해서는 차후 더 자세히 다루도록 하겠습니다.

오늘은 여기서, InnoDB의 메모리 구조에 대해 안내드리려고 합니다.

InnoDB Buffer pool

먼저 InnoDB의 메모리 구조를 안내드리기 위해선, 일반적으로 MySQL 메모리의 가장 큰 부분을 차지하는 버퍼풀을 빼놓고 얘기할 수 없습니다. 일반적인 목적으로 사용하는 MySQL의 경우, 만약 다른 어플리케이션이 수행되지 않는 DB 전용 서버의 경우, 버퍼 풀의 사이즈를 전체 메모리 용량의 3/4 까지도 잡아서 사용합니다.

여기에는 사용자가 접근한 테이블과 인덱스 데이터가 저장됩니다. 그리고 앞선 SELECT 수행절차에서 말씀드렸던(https://daedamee.tistory.com/entry/MySQL-Internal-%EC%82%B4%ED%8E%B4%EB%B3%B4%EA%B8%B0-SELECT-%EC%88%98%ED%96%89%EA%B3%BC%EC%A0%95?category=727646) 메모리에 항상 올리고 시작하는 개념이 이 버퍼풀이라고 생각하시면 더 편하시겠죠? 😃

메모리 영역이 디스크보다 크다면 모든 데이터를 항상 메모리에 들고있을 수있어 굉장히 쾌적하게 이용할 수 있겠지만, 우리모두가 알고있듯 램은 비싸고 디스크는 저렴합니다. 그러므로 제한된 메모리를 효율적으로 사용하기 위해 InnoDB에서는 Least recently used (LRU) 알고리즘을 사용합니다. 자주 접근하는 데이터는 밀리지 않도록 앞으로 당기고, 자주 사용되지 않는 데이터는 메모리에서 제거하는(eviction이라고 부릅니다) 우선순위에 두는 방식으로 운영됩니다.

기본적으로 버퍼풀의 3/8은 old sublist로 분류되며(밀려날 수 있는 페이지들), 나머지는 new list로, 그리고 그 사이를 midpoint로 부릅니다. old list의 페이지를 읽게 된다면 이는 다시 new list로 바뀌게 되며, 사용자의 요청으로 읽히게 되도 이 페이지는 new list로 가게 됩니다. 데이터베이스가 운영될때, 버퍼풀의 페이지는 "나이" 에 의해 움직이진 않습니다. 

그리고 이에 대한 모니터링은 SHOW ENGINE INNODB STATUS; 명령을 통해 가능한데요, 아래 코드에서 간단히 설명드리겠습니다.

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
.....
----------------------
BUFFER POOL AND MEMORY -- 버퍼풀 메모리 영역의 설명입니다. 사용하지 않는 곳이라 아주 깨끗하네요 :) 
----------------------
Total large memory allocated 0 -- 버퍼풀에 얼마나 많은 메모리가 할당되었는지 나타냅니다.
Dictionary memory allocated 366605 -- InnoDB 데이터 딕셔너리가 얼마나 할당되어있는지 나타냅니다.
Buffer pool size   8192 -- 버퍼풀에 할당된 페이지의 사이즈입니다.
Free buffers       7061 -- 버퍼풀에 할단된 페이지 중, free 한 사이즈입니다.
Database pages     1127 -- 버퍼풀 LRU 리스트의 페이지의 사이즈입니다. 
Old database pages 436  -- LRU Old 서브리스트에 있는 페이지의 사이즈입니다.
Modified db pages  0    -- 버퍼풀에서 변경된 페이지의 현재 숫자입니다.
Pending reads      0    -- 버퍼풀에서 읽기를 대기하는 페이지의 숫자입니다.
Pending writes: LRU 0, flush list 0, single page 0 -- 쓰기를 대기하는 페이지로, LRU의 경우 더티페이지를, flush의 경우 체크보인팅을, single의 경우 독립 페이지를 의미합니다.
Pages made young 0, not young 0 -- LRU리스트에서 young으로 만들어지는 페이지와 이게 아닌 페이지입니다.
0.00 youngs/s, 0.00 non-youngs/s
Pages read 985, created 142, written 158 -- 버퍼풀에서 읽히고/만들어지고/쓰인 페이지 숫자입니다.
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1127, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

mysql>

이상, 버퍼풀에 대해서 간단히 구조에 대해 공식 문서 및 간단한 명령을 통해 알아보았습니다! 

MySQL에서 메모리의 용량이 데이터베이스에 어떤 영향을 미치는지, 그리고 자주 액세스하는 데이터가 왜 빠르게 리턴하는지에 대해 알 수 있는 시간이 되었네요!

긴 글 읽어주셔서 감사드리며, 다음 문서를 통해 메모리 구조에 대해 남은 Change buffer, Adaptive Hash Index, Log Buffer에 대해서도 정리하는 시간을 가져보겠습니다.

그럼 오늘도 모두 즐거운 하루 되세요!

안녕하세요! 대담이입니다. 

1주일만에 인사드리네요. 😃 오늘은 인터널을 살펴보기 위해서 Recovery Startup 과정을 살펴보려 합니다!

이를 살펴보면서 MySQL이 어떻게 트랜잭션을 유지할 수 있는지, 그리고 데이터 유실을 방지하기 위해 어떤 과정으로 쿼리가 수행되는지 볼 수 있을것으로 보이네요 :) 그럼 바로 시작해보겠습니다!

먼저, 이전에 설치했던 서버를 이용하여 스타트업이 어떤 에러로그를 발생시키는지 살펴볼까요?

2021-10-23T11:29:02.239156Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.26) starting as process 3493
2021-10-23T11:29:02.290251Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-10-23T11:29:02.814050Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-10-23T11:29:03.175226Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-10-23T11:29:03.175366Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-10-23T11:29:03.178772Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-10-23T11:29:03.178959Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-10-23T11:29:03.242920Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-10-23T11:29:03.243089Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.26'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.

이를 통해 알 수 있는 것은 MySQL 이 어떤 버전인지, 어떤 PID를 가지는지, 그리고 로컬 접속을 위한 소켓의 위치와 원격 접속을 위한 port 위치를 알 수 있네요.

여기서는 MySQL 의 시작과 관련하여 일반적인 수행뿐만이 아닌 Crash Recovery 과정까지 포함할 예정이기 때문에, 수동으로 crash를 발생시킨후 띄우는 에러로그까지 포함하겠습니다! kill -9 명령을 통해 킬 후 다시 mysqld를 기동하였을 때, 아래와 같은 에러로그가 포함되는 것을 확인할 수 있었습니다.

2021-10-23T11:54:03.077366Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.26) starting as process 4455
2021-10-23T11:54:03.089246Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-10-23T11:54:04.402779Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 92021-10-23T11:54:04.613362Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
 10 11 122021-10-23T11:54:04.640950Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
 13 14 15 16 17 18 19 202021-10-23T11:54:04.725160Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-10-23T11:54:04.725389Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-10-23T11:54:04.726445Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-10-23T11:54:04.726699Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
 21 22 23 242021-10-23T11:54:04.775302Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-10-23T11:54:04.775443Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.26'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

먼저 MySQL이 시작되기 위해서는, 설정 파일(/etc/my.cnf - 설치 형식/OS에 따라 달라질 수 있습니다.)에 존재하는 설정에 적절한 파일이 존재하고, 접근이 가능해야합니다. 기본적으로 basedir, datadir, socket 설정 등이 있겠습니다. 설정이 제대로 되어있다면, 데이터 파일을 살펴보게 됩니다. 데이터파일을 살펴보던 도중 데이터파일에 이슈가 있다면 이에 대한 자동 복구를 시작하게 되며 이때 recovery를 수행하게 됩니다.

리커버리는 아래와 같은 절차를 통해 수행되게 됩니다. 여기서는 MySQL에서 가장 많이 사용되는 InnoDB 스토리지 엔진에 대해서만 다루겠습니다.

1.  테이블스페이스 찾기 : InnoDB가 리두 로그 적용이 필요한 테이블스페이스를 찾아내기 위해 사용되는 절차입니다.
2. 리두로그 적용 : 초기화동안 리두로그 적용이 진행됩니다. 다른 커넥션들을 허용하기 전에, 모든 변경분이 버퍼풀로부터 테이블스페이스로 적용이 되어있다면(= 더티 페이지가 없다면) 해당 절차는 생략됩니다.
3. 완료가 되지 않은 트랜잭션 롤백 : 완료가 되지않은 트랜잭션이란, 예상치 못한 크래시 이전에 활성화된 트랜잭션이나 Fast shutdown시 발견되는 트랜잭션을 의미합니다. 해당 작업들을 롤백하는 작업이 리두로그 적용 이후 적용됩니다.
4. 체인지 버퍼 병합 : 변경들을 체인지 버퍼로부터 인덱스의 리프 페이지에 적용합니다(시스템테이블스페이스의 일부).
5. 퍼지 : 삭제 플래그가 있는 레코드들을 삭제합니다.

해당 절차는 (https://dev.mysql.com/doc/refman/5.7/en/innodb-recovery.html) 페이지에서 확인하였으며, 위와 같은 절차로 수행됩니다. 여기서 트랜잭션을 종료 이후에도 유지하기 위한 InnoDB의 요소로 세가지 정도를 확인할 수 있겠네요.

• 리두로그
• 더티 페이지
• 체인지 버퍼

해당 요소에 대한 안내는 차후 엔진의 구조를 설명드릴때 다시한번 자세히 다루도록 하겠습니다! RDBMS를 쓰는 가장 큰 이유! 트랜잭션을 가장 극한의 상황인 예상치못한 크래시로부터 어떻게 유지할 수 있는지에 대해, 그중 리커버리 과정에 대해 알아보았습니다.

다음 시간에는 해당 부분을 다루다 보니 또 중요하게 생각되는 요소인, SELECT 절차와 요 페이지에서 소개된 구조요소에 대해 한번 다시 정리해드리도록 하겠습니다.

날씨가 점점 쌀쌀해지는데, 감기 조심하시길 바라며, 지금까지 대담이였습니다~ 😊

안녕하세요, 대담이 입니다!

저번 블로그에서 설명드린 MySQL 설치에 관한 내용을 이용해 뭐를 더 설명드릴까 하다가.. SELECT 수행 과정에 대해 설명드리는 것도 좋을 거 같아 이 부분에 대해 설명드리겠습니다.

저는 현재 Database Engineer로써 근무하고 있습니다.

데이터베이스의 특성 중 하나는 추상화(abstraction) 인데요, 이는 간단히 얘기해서 데이터베이스가 어떤 방식으로 데이터를 돌려주는지는 몰라도, 쉽게 이를 사용할 수 있다!입니다. 

그래도 운행하는 차에 문제가 생기면 자동차 엔지니어에게 수리를 요청하면 그 자동차 엔지니어는 차가 왜 운행할 수 없는 상태인지 진단할 수 있듯이, 데이터베이스 엔지니어라면 데이터베이스가 느린 원인을 진단을 통해 알아낼 수 있어야겠죠? 😃 

이를 위해 가장 많이 살펴보는 요소중 하나가 쿼리가 의도된 대로 수행되는지입니다.

이를 제대로 알아내기 위해서는 SELECT 쿼리의 수행 방식부터 제대로 파악하는 것이 중요합니다! 그럼 이전에 설치된 테이블을 이용하여 간단한 예제를 만들어 SELECT 쿼리의 수행과정에 대해 안내드려보도록 하겠습니다!

mysql> CREATE DATABASE test; 
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> create table test_tab(id int , val int, date_val datetime, char_val varchar(40), primary key (id)); # 테스트 테이블을 생성해줍니다.
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_tab(val, date_val, char_val) values(RAND()*10000000, DATE_ADD(NOW(), INTERVAL RAND()*100000900 second), UUID());
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_tab(val, date_val, char_val) SELECT RAND()*10000000, DATE_ADD(NOW(), INTERVAL RAND()*100000900 second), UUID() FROM test_tab;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
## ..반복합니다.
mysql> select COUNT(*) FROM test_tab; # 약 100만 건의 테스트 데이터를 입력 완료하였습니다.
+----------+
| COUNT(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.10 sec)

위의 구문을 이용해 랜덤한 데이터를 쉽게 만들어내실 수 있습니다. 이를 이용해 한 가지 쿼리를 해볼 텐데요, 

기본적인 쿼리의 모양을 한번 만들어 보겠습니다.

SELECT YEAR(date_val) as year, COUNT(*) as cnt
FROM test_tab
WHERE id BETWEEN 1 AND 10000
GROUP BY YEAR(date_val)
ORDER BY cnt DESC;
+------+------+
| year | cnt  |
+------+------+
| 2023 | 1866 |
| 2022 | 1854 |
| 2024 | 1783 |
| 2021 |  414 |
+------+------+
4 rows in set (0.00 sec)

위의 쿼리는 제가 테스트로 짜 본 쿼리입니다. 먼저 의미는 아신다는 가정하에 적고 있지만, id가 1부터 10000까지 가지는 테이블의 데이터 중, date_val의  row 카운트를 확인하는 쿼리입니다.

해당 쿼리를 나누어 어디부터 데이터베이스 엔진이 해석하는지 살펴볼까요?

1. FROM 절 : 먼저 어느 테이블을 가져와서 어느 테이블에 접근해야 할지 알아야 하기 때문에 FROM 절부터 확인합니다.

2. WHERE절 : 어느 테이블에 접근해야 하는지 알았으면, 어느 데이터를 가져와야 할지 알아야겠죠? 이를 위해 WHERE절에서 어떤 데이터를 가져오는지 체크하는 절차가 두 번째로 확인됩니다.

3. GROUP BY 절 : 데이터를 가져왔으면, 이번 쿼리에서는 집계가 포함되어있으니 집계해야겠죠? 이를 GROUP BY 절에 맞추어 진행합니다!

4. SELECT 절 : SELECT 절에서 집계한 데이터를 어떻게 표현할지(alias, AS로 표현한 별칭입니다.), 그리고 이 카운트는 얼마나 되는지 체크합니다.

5. ORDER BY 절 : 집계가 완료된 데이터를 제가 요청한 순서에 맞추어 정렬합니다.

이 순서를 알고 나면 뒤에 설명드릴 MySQL 엔진에서의 셀렉트 수행절차를 인터널로 살펴보시는데도 더 도움이 됩니다. 

위와 같은 MySQL 아키텍처를 통하여 SELECT 쿼리 처리 수행 절차에 대해 다시 설명드리겠습니다. 각 아키텍처의 역할은 차후 설명드리고, 또 운영방식에 대한 설명도 차후 다시 드릴 수 있도록 하겠습니다 😃

 1. 먼저, 쿼리가 들어오면, 이것이 Engine이 알아먹을 수 있는지 체크하는 Parser부터 들르게 됩니다! 여기서는 이 쿼리를 수행한 주체가 요청한 쿼리(여기선 SELECT)를 수행할 권한을 가졌는지, 그리고 문법에 맞게 작성된 쿼리인지 확인합니다.

2. 쿼리를 해석했다면, 아까 첫 번째로 수행되는 구문이 FROM이었죠? 일단 메모리 영역(Cache & buffers)에 접근하여, 해당 데이터가 존재하는지 확인합니다! 데이터가 있다면 바로 반환하지만(잠금에 따라 달라지지만, 이는 또 차후에 다루어보겠습니다!) 없을 때는 스토리지에 들러 해당 데이터를 캐시에 올리는 작업까지 진행합니다.

3. 캐시에 올라왔다면, 이를 쿼리를 요청한 클라이언트에 반환합니다. 반환 시에는 집계 등은 완성이 되지 않은 테이블에 저장된 그대로의 형태로 반환됩니다.

4. 클라이언트에서 해당 데이터를 받아, 집계 및 정렬을 수행 후, 반환합니다.

복잡하다면 복잡하고, 쉽다면 쉬운 SELECT 수행절차였습니다. SELECT 구문의 간단한 수행 뒤에는 이런 복잡한 일련의 과정이 Database 내부적으로 일어나고 있었다는 점, 알고 계셨나요? 파면 팔수록 재미있는 데이터베이스 엔진의 내부활동, 앞으로도 기대해주시면 감사하겠습니다!

그럼 오늘도 행복한 하루 보내시길 바라며, 지금까지 대담이었습니다!

안녕하세요, 대담이입니다.

다시 블로그를 시작하려고 오랜만에 글을 써내려가기 시작합니다 :) 

먼저, 다시 시작하는 마음으로 Linux에 MySQL을 설치하는 방법을 다시 소개해드리려고 합니다.

최신 버전이 아닌 다른 버전으로 설치하고 싶을 때가 생길 수 있는데요, 이럴 때 사용하실 수 있는 방법을 소개드리고자 글을 써내려가게 되었습니다!

MySQL을 처음 설치하시거나, Linux에 구성을 하고 싶으신 분들께 추천드립니다. (최근엔 AWS의 RDS 등 아주 쉽게 데이터베이스 서비스를 운영할 방법이 많지만, 내가 소유한 머신에 설치하고 싶을 때도 있으니까요 😃 )

참고 URL: https://dev.mysql.com/doc/refman/8.0/en/linux-installation-rpm.html

첫번째로, 설치하고 싶으신 버전을 선택합니다. 저는 8.0.20 버전을 설치해보도록 하겠습니다.

 - 설치 환경 : Amazon Linux 2

1. yum 레포지터리 다운로드 : 제가 사용하려는 Amazon Linux 2은 REHL 7 과 호환성이 있으므로, REHL용 MySQL 8.0.20을 찾아 헤메야합니다! 

https://aws.amazon.com/ko/premiumsupport/knowledge-center/ec2-enable-epel/

먼저, MySQL Community Download 페이지에 진입합니다.

https://dev.mysql.com/downloads/mysql/

여기서, 저희가 찾는 버전은 2021-10월 기준 최신 버전인 8.0.26이 아닌 8.0.20이므로, 상단 메뉴 중 Archives를 클릭합니다. 그럼 아래 페이지로 진입하게 됩니다 :) 

https://downloads.mysql.com/archives/community/

여기서 Product Version : 8.0.20 

Operating System : Red Hat Enterprise Linux / Oracle Linux를 선택하시면, OS Version이 새로 표시됩니다.

OS Version : Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86, 64-bit) 를 선택합니다. 제일 마지막 부분은 혹시나 ARM 프로세서를 사용하신다면 변경이 필요합니다.

제일 위, RPM Bundle의 주소를 복사합니다! 앞선 과정이 귀찮으신 분들은 제가 공유해드린 링크를 복사하셔도 괜찮습니다 😁

 https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.20-1.el7.x86_64.rpm-bundle.tar

위 링크를 이용하시어 wget 을 이용하여 다운로드 및 tar를 이용하여 묶음 해제까지 수행합니다.

# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.20-1.el7.x86_64.rpm-bundle.tar

# tar xvf mysql-8.0.20-1.el7.x86_64.rpm-bundle.tar

그럼 아래와 같은 파일을 확인하실 수 있습니다.

[root@ip-172-31-7-88 ~]# ll
total 1630032
-rw-r--r-- 1 root root  834560000 Apr 28  2020 mysql-8.0.20-1.el7.x86_64.rpm-bundle.tar
-rw-r--r-- 1 7155 31415  48822048 Mar 27  2020 mysql-community-client-8.0.20-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415    623508 Mar 27  2020 mysql-community-common-8.0.20-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   8129988 Mar 27  2020 mysql-community-devel-8.0.20-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415  23599996 Mar 27  2020 mysql-community-embedded-compat-8.0.20-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   4667884 Mar 27  2020 mysql-community-libs-8.0.20-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   1277128 Mar 27  2020 mysql-community-libs-compat-8.0.20-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 512057468 Mar 27  2020 mysql-community-server-8.0.20-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 235369940 Mar 27  2020 mysql-community-test-8.0.20-1.el7.x86_64.rpm

이 파일들을 설치해야하는데 모두 일일이 설치하려면, 호환성 검사로 인해 순서도 지켜야하고, 여간 귀찮은게 아닙니다(다 제가 겪어보았습니다.. 저도 알고싶지 않았어요 😭 )

이를 해결하기 위해! 저희는 와일드카드 *을 활용하여 전부 설치를 진행합니다! 

 sudo yum install mysql-community-*.rpm
... 설치 관련 문구 출력중..
==================================================================================================================================================================================================================================================
 Package                                                      Arch                                Version                                                 Repository                                                                         Size
==================================================================================================================================================================================================================================================
Installing:
 mysql-community-client                                       x86_64                              8.0.20-1.el7                                            /mysql-community-client-8.0.20-1.el7.x86_64                                       225 M
 mysql-community-common                                       x86_64                              8.0.20-1.el7                                            /mysql-community-common-8.0.20-1.el7.x86_64                                       8.6 M
 mysql-community-devel                                        x86_64                              8.0.20-1.el7                                            /mysql-community-devel-8.0.20-1.el7.x86_64                                         56 M
 mysql-community-embedded-compat                              x86_64                              8.0.20-1.el7                                            /mysql-community-embedded-compat-8.0.20-1.el7.x86_64                               90 M
 mysql-community-libs                                         x86_64                              8.0.20-1.el7                                            /mysql-community-libs-8.0.20-1.el7.x86_64                                          22 M
     replacing  mariadb-libs.x86_64 1:5.5.68-1.amzn2
 mysql-community-libs-compat                                  x86_64                              8.0.20-1.el7                                            /mysql-community-libs-compat-8.0.20-1.el7.x86_64                                  6.1 M
     replacing  mariadb-libs.x86_64 1:5.5.68-1.amzn2
 mysql-community-server                                       x86_64                              8.0.20-1.el7                                            /mysql-community-server-8.0.20-1.el7.x86_64                                       2.2 G
 mysql-community-test                                         x86_64                              8.0.20-1.el7                                            /mysql-community-test-8.0.20-1.el7.x86_64                                         635 M
Installing for dependencies:
 keyutils-libs-devel                                          x86_64                              1.5.8-3.amzn2.0.2                                       amzn2-core                                                                         37 k
 krb5-devel                                                   x86_64                              1.15.1-37.amzn2.2.2                                     amzn2-core                                                                        272 k
 libcom_err-devel                                             x86_64                              1.42.9-19.amzn2                                         amzn2-core                                                                         32 k
 libkadm5                                                     x86_64                              1.15.1-37.amzn2.2.2                                     amzn2-core                                                                        179 k
 libselinux-devel                                             x86_64                              2.5-12.amzn2.0.2                                        amzn2-core                                                                        187 k
 libsepol-devel                                               x86_64                              2.5-8.1.amzn2.0.2                                       amzn2-core                                                                         77 k
 libverto-devel                                               x86_64                              0.2.5-4.amzn2.0.2                                       amzn2-core                                                                         12 k
 ncurses-compat-libs                                          x86_64                              6.0-8.20170212.amzn2.1.3                                amzn2-core                                                                        308 k
 openssl-devel                                                x86_64                              1:1.0.2k-19.amzn2.0.8                                   amzn2-core                                                                        1.5 M
 pcre-devel                                                   x86_64                              8.32-17.amzn2.0.2                                       amzn2-core                                                                        480 k
 perl-Data-Dumper                                             x86_64                              2.145-3.amzn2.0.2                                       amzn2-core                                                                         48 k
 perl-JSON                                                    noarch                              2.59-2.amzn2                                            amzn2-core                                                                         96 k
 zlib-devel                                                   x86_64                              1.2.7-18.amzn2                                          amzn2-core                                                                         50 k
Updating for dependencies:
 openssl                                                      x86_64                              1:1.0.2k-19.amzn2.0.8                                   amzn2-core                                                                        495 k
 openssl-libs                                                 x86_64                              1:1.0.2k-19.amzn2.0.8                                   amzn2-core                                                                        1.2 M

Transaction Summary
==================================================================================================================================================================================================================================================
Install  8 Packages (+13 Dependent packages)
Upgrade             (  2 Dependent packages)

Total size: 3.3 G
Total download size: 4.9 M
Is this ok [y/d/N]: y # 해당 문구가 나오면 y를 입력하여 전부 설치를 진행합니다.
... 설치 진행중...
Installed:
  mysql-community-client.x86_64 0:8.0.20-1.el7       mysql-community-common.x86_64 0:8.0.20-1.el7  mysql-community-devel.x86_64 0:8.0.20-1.el7  mysql-community-embedded-compat.x86_64 0:8.0.20-1.el7  mysql-community-libs.x86_64 0:8.0.20-1.el7 
  mysql-community-libs-compat.x86_64 0:8.0.20-1.el7  mysql-community-server.x86_64 0:8.0.20-1.el7  mysql-community-test.x86_64 0:8.0.20-1.el7  

Dependency Installed:
  keyutils-libs-devel.x86_64 0:1.5.8-3.amzn2.0.2   krb5-devel.x86_64 0:1.15.1-37.amzn2.2.2     libcom_err-devel.x86_64 0:1.42.9-19.amzn2               libkadm5.x86_64 0:1.15.1-37.amzn2.2.2        libselinux-devel.x86_64 0:2.5-12.amzn2.0.2  
  libsepol-devel.x86_64 0:2.5-8.1.amzn2.0.2        libverto-devel.x86_64 0:0.2.5-4.amzn2.0.2   ncurses-compat-libs.x86_64 0:6.0-8.20170212.amzn2.1.3   openssl-devel.x86_64 1:1.0.2k-19.amzn2.0.8   pcre-devel.x86_64 0:8.32-17.amzn2.0.2       
  perl-Data-Dumper.x86_64 0:2.145-3.amzn2.0.2      perl-JSON.noarch 0:2.59-2.amzn2             zlib-devel.x86_64 0:1.2.7-18.amzn2                     

Dependency Updated:
  openssl.x86_64 1:1.0.2k-19.amzn2.0.8                                                                                  openssl-libs.x86_64 1:1.0.2k-19.amzn2.0.8                                                                                 

Replaced:
  mariadb-libs.x86_64 1:5.5.68-1.amzn2                                                                                                                                                                                                            

Complete!

짝짝짝! 이로 인하여 여러분은 원하는 마이너 버전을 RPM 패키지를 이용하여 쉽게 설치하셨습니다 😁

설치를 했으면 접속해봐야겠죠? 아쉽게도 현재는 접근을 할 수 없습니다..

왜냐하면 MySQL 프로세스가 현재 리눅스에 활성화되지 않은 상태이기 때문이에요. 걱정마세요! 이부분도 쉽게 한가지 명령어로 프로세스를 띄우실 수 있습니다.

# systemctl start mysqld # 명령이 끝나고 나면 MySQL이 기동됩니다!

# ps 명령어를 통해 mysql 기동여부를 확인하실 수 있어요.
# ps -ef |grep mysql
mysql      362     1  0 13:53 ?        00:00:03 /usr/sbin/mysqld
root       456  3471  0 14:09 pts/0    00:00:00 grep --color=auto mysql

#  sudo grep 'temporary password' /var/log/mysqld.log # 이 명령어를 통해 임시 비밀번호를 체크합니다.
2021-10-08T13:53:44.219783Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 4.mBNncFk!Ya

# mysql -uroot -p # MySQL에 접근하기 위하여 기본 유저 이름(root) 와 비밀번호를 입력합니다! 비밀번호는 아까 설정한 임시 비밀번호겠지요? ^^
Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.20

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> # 짝짝짝! 위 메시지를 보셨죠? 8.0.20 으로 정상 설치된데다가, 접근까지 성공하셨습니다! 아래 명령어를 이용하여 사용하고싶으신 비밀번호로 변경합니다 :) 
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
Query OK, 0 rows affected (0.01 sec)

여기까지 진행하셨다면, 여러분은 MySQL을 진행하기 위한 패키지 설치를 마친 셈입니다! 축하드립니다 :) 

지금까지 원하는 특정 버전이 있을 때의 패키지 설치 방법에 대해 알아보았습니다.

다음 포스팅에서는 설치된 MySQL을 이용하여 MySQL 8.0 의 새로운 기능이나, 간단한 테이블 데이터 만들기 등을 안내드릴 수 있도록 하겠습니다! 

그럼 다음 포스팅에서 다시 뵙겠습니다, 지금까지 대담이였습니다!

여기서는 MySQL 5.7 의 새로운 기능에 대해서 간단히 나열합니다.

해당 내용 테스트를 병행하기 위해 이 문서는 수시로 업데이트가 발생할 수 있으며, 관련하여 정리해 테스트 진행 예정입니다.

해당 내용은 (https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html ) 에서 가져옵니다.

What Is New in MySQL 5.7

Features Added in MySQL 5.7

보안 향상

 - 자동 패스워드 만료 기능 제공

 - 계정 잠금 및 잠금해제 기능 제공

 - 보안 연결을 위해 OpenSSL을 자동으로 생성하고 키파일을 생성한다.

 - 만약 SSL이 명확히 명세되어있지 않다면 시작할때 자동으로 SSL을 데이터디렉토리에서 찾는다. 그리고 mysql_ssl_rsa_setup 유틸리티를 통해 수동으로 SSL과 RSA 키와 인증서를 넣을 수 있다.

 - MySQL을 mysqld --initialize로 배포시 더욱 안전하다.

- 설치가 하나의root 계정만 생성되며, 'root'@'localhost'이다. 자동으로 이 계정의 랜덤 패스워드가 만료로 생성된다. MySQL 관리자는 반드시 이 랜덤 패스워드로 접근하고 새로운 비밀번호를 할당해야 한다.

- 익명유저 계정을 생성하지 않는다.

- test 데이터베이스를 생성하지 않는다.

SQL모드의 변경 : Strict SQL 모드 중 트랜잭션 기능이 기본적으로 활성화된다.

Why We’re Excited About MySQL 8.0 (2018-01-10 진행) 리뷰

오늘 진행되었던 웨비나 내용을 타이틀을 대강 정리해 보았습니다.

여기서 제가 보았을 때 특히 눈에 띄는 것은

ROLE기능, Window 펑션 기능 강화(좋은소식), SET PERSIST 명령인데요, 점점 OS단 접근 없이MySQL 클라이언트 접근만으로 MySQL 조작이 가능해질 듯 하네요.

또한 디센딩 인덱스가 유명무실하지 않고 실제 사용이 가능해진다는 것 또한 매우 좋은 소식이라고 생각합니다.

물론 stable 버전 릴리즈 이후 직접 테스트 해봐야겠지만, 많은 부분이 기대되네요 ^^

아래는 관련 타이틀별로 좋지 못한 실력으로 번역해놓아보았습니다.

5.7에 비해 달라진 기능을 확인하시고 한번 체험해 보시는 것도 나쁘지 않겠습니다!

1. information_schema의 퍼포먼스가 많이 빨라짐

2. utf8 캐릭터셋의 속도가 빨라지고 좋아짐(utf8mb4의 경우로 비교).

3. 보안 부분 강화

 - ROLE 기능 제공
 - SUPER 권한에서 Breakdown
 - 비밀번호 히스토리
 - cached-SHA2 인증에서 더욱 빨라짐
 - --skip-grants 명령이 원격 연결에서 거부됨.
 - 리두와 언두로그가 테이블 암호화가 활성화되어있다면 함께 암호화됨.

 4. 영속적인 AUTO INCREMENT\

 5. 자동관리되는 UNDO 테이블스페이스

 - 시스템 테이블 스페이스를 언두를위해 사용하지 않음
 - 자동적으로  디스크의 공간을 큰 트랜잭션으로부터 교정함.

6. 셀프 튜닝(이노디비에서만 적용)

- innodb_dedicated_server값을 세팅하면 자동으로 튜닝함
- 버퍼풀, 로그버퍼사이즈, 플러시메소드

7. JSON을 위한 부분 In-Place 업데이트

- JSON 오브젝트를 모든 필드 재작성 없이 업데이트 가능
- 카운터, 스테이터스, 타임스탬프에 훌륭함.
- 오직 특정요소 업데이트나 제거 지원

8. 보이지 않는 인덱스(invisible index)

- 실제 인덱스를 드랍했을때의 효과를 테스트 가능
- use_invisible_indexes 를 세션에서 생성, 제거할 수 있음.

9. TmpTable 스토리지 엔진

 -내부 임시테이블들을 위한 더욱 효율적인 스토리지 엔진
 -VARCHAR 와 VARNINARY를 위한 더욱 효율적인 스토리지 엔진

10. 백업 잠금

- 비일관적인 백업을 방지
- 백업을 위한 인스턴스 잠금

11. 옵티마이저 히스토그램

 - 상세화된 통계가 컬럼에 나온다. 인덱스들만이 아니다.

12. 퍼포먼스 스키마

 - 응답 시간 히스토그램 제공
 - 현재 상호적으로 사용가능하고 스케일 가능.
 - 퍼포먼스 비교.
 - sys.session 모두 비교시 8.0이 30배 빠름(5.7에 비해)

13. persistent global variables.

 - set persist 명령 제공.(my.cnf 에 직접 써주나: 이건 아니고 다른 파일에 쓰는듯)

14. 스토리지가 SSD라고 가정한느 것이 디폴트

15. 바이너리로그의 디폴트

 - 빈로그 ON이 디폴트
 - log_slave_update도 디폴트로 켜지고
 - expire_logs_date도 30이 디폴트

16. 쿼리 캐시가 사라짐.

 - 이를 넣으려니 더욱 많은 문제
 - ProxySQL이나 다른 것을 외부 쿼리 캐시로 사용.

17. 오직 Native partitioning만 사용가능(?)

18. 리소스 그룹(?)

 - 격리수준과 더 나은 퍼포먼스
 - MySQL 8.0 리소스 그룹 : 100% 더 빠름.

19. 스케일에서 더욱 좋은 퍼포먼스

 - 시스벤치로 보았을때, 유저가 많아져도 계속 성능이 좋음

20. 기능 요청(?)

 - 싱글스레드 퍼포먼스가 더 나아지는 것 (싱글 스레드 퍼포먼스는 MySQL 버전이 올라갈수록 QPS가 떨어짐.)
 - 페럴렐 쿼리 프로세싱

21. NO WAIT와 SKIP LOCKED(향후 테스트)

 - SELECT 쿼리에서 NOWAIT는 row가 잠겨있다면 에러가 나오고,
 - SKIP LOCKED는 결정적으로 잠긴 로우를 넘김.

22. 디센딩 인덱스.

 - 인덱스에서 디센딩 플래그가 더이상 무시되지 ㅇ낳는다.
 - ORDER BY a desc, b asc 등이 가능해짐

23. JSON에서의 관계형 병합

 - JSON_table 함수로 테이블 모양으로 볼 수 있음.

24. 데이터 추출에 JSON 다큐먼트가 더욱 나아짐.

25. 공통 테이블 표현(?)

 - 리커시브와 non-recursive 표현.

26. 윈도우 펑션들.

 - 그룹바이와 비슷하게, 그러나 row간의 관계를 더욱 잘 표현할 수 있음.

27, GIS가 더욱 좋아짐(GPS관련 인덱스인듯?)

28. MySQL 다큐먼트 스토어(레퍼런스 읽어보기 필요할듯)

 - 풀텍스트 인덱싱
 - GeoJSON 지원

슬레이브 비정상 종료, 재구동 이후 복제를 위한 positioning 중, auto_increment 값이 맞지 않는 현상 분석

MySQL 롤백으로 인한 M-S 비일치를 발생시킴

상기의 모델은 마스터-리플간 불일치 발생으로 인한 건으로, 리플리케이션이 늦은 상태가 되나,

원래 테스트하려고 했던 현상은 binlog상태 <> 실제 DB서버에 커밋된 상태(DB서버에 커밋된 상태가 빈로그 상태보다 더 앞선 상태로, 릴레이로그의 커밋된 부분이 커밋 안된 부분으로 나타나는 현상)이나,

해당 현상은 재현하지 못한 관계로 수동으로 상황을 재현하여 테스트.

시나리오 작성

현재 포지션 값 저장

50000건 인서트(슬레이브도 쫓아가도록)

포지션값 1. 의 포지션값으로 변경(50000건 차이 발생했으나, 실제론 데이터 입력된 상태)

어느 값으로 변경했을 때 insert_id가 변경되는지 확인.

-- 현재 슬레이브 상태와 마스터 상태

-- 50,000건 데이터 입력후, 이전 포지션 값으로 변경 후 테스트 시작.

-- 해당 에러 해결을 위해 현재 문제가 된 값을 찾아 binlog open(의도적으로 수행 전으로 돌렸으므로 131번 오픈)

root@kimgn-splunk-test  /data/mysql_1/data

$ /mysql/bin/mysqlbinlog mysql-bin.000131 > ./test.sql

에러가 난 값은 1200001 이나, 현재 들어간 값까지 문제가 없다는 가정 하에 슬레이브의 PK 맥스값  검색

mysql> select max(id) fROM kimgn_test.kimgn_test;

+---------+

| max(id) |

+---------+

| 1250000 |

+---------+

1250000 에 해당하는 positon 탐색(파일의 위치 등은 시간을 통해 유추하나, 여기서는 131번으로 확인(변경 테스트 포지션은 볼드처리)

# at 5477845

#180103 16:14:58 server id 1 end_log_pos 5477872 Xid = 4247603

COMMIT/*!*/;

# at 5477872

#180103 16:14:58 server id 1 end_log_pos 5477954 Query thread_id=257606 exec_time=0 error_code=0

SET TIMESTAMP=1514963698/*!*/;

BEGIN

/*!*/;

# at 5477954

#180103 16:14:58 server id 1 end_log_pos 5477982 Intvar

SET INSERT_ID=1250000/*!*/;

# at 5477982

#180103 16:14:58 server id 1 end_log_pos 5478017 Rand

SET @@RAND_SEED1=22686107, @@RAND_SEED2=1027190506/*!*/;

# at 5478017

#180103 16:14:58 server id 1 end_log_pos 5478172 Query thread_id=257606 exec_time=0 error_code=0

SET TIMESTAMP=1514963698/*!*/;

INSERT INTO kimgn_test (contents,date_val) VALUES ( CEILING(RAND()*100),now())

/*!*/;

# at 5478172

#180103 16:14:58 server id 1 end_log_pos 5478199 Xid = 4247604

COMMIT/*!*/;

시나리오 : 현재 1250000 번까지 들어가 있으므로 1250000을 인서트 시도 후 에러가 나면 skip 후 정상 인서트 확인.(현재 데이터는 1300000까지 들어가 있음)

케이스 1 :  INSERT_ID를 지정하는 포지션인 5477954 로 지정

케이스 2 : INSERT_ID를 지정하는 포지션 중 end_pos로 지정되어 있는 5477982 로 지정

케이스 3 : BEGIN과 TIMESTAMP를 지정하는 5477872 로 지정

케이스 1 시나리오 결과

의도한 대로 원하는 곳에서 한번 더 STOP 발생

sql 스레드 스킵 후 데이터 확인 결과 원하는 대로 데이터 입력됨을 확인할 수 있었음.

케이스 2 시나리오 결과

의도한 대로 원하는 곳에서 한번 더 STOP 발생

sql 스레드 스킵(케이스 1의 경우 1회였으나, 케이스 2의 경우 2회) 후 데이터 확인 결과 skip 대상이었던 kimgn_test 테이블의 데이터가 1건 오입력됨을 확인할 수 있었음.

원인 분석 : 데이터 확인 결과, 최초 스킵한  1250001번의 데이터가 1250000번 의 데이터와 데이터가 일치함.

               즉, 앞에서 skip을 의도했던 INSERT구문이 수행 완료되었음을 확인할 수 있었음.

               추가적으로 앞에서 SKIP하려 했던 auto_increment값이 에러가 나도, 그 이후 값은 정상적으로 입력되는 것 확인.

--1. 마스터 서버의 데이터 : 1,300,000건

mysql> select * FROM kimgn_test order by id desc LIMIT 10;

+---------+----------+---------------------+

| id      | contents | date_val            |

+---------+----------+---------------------+

| 1300000 | 2        | 2018-01-03 18:02:29 |

| 1299999 | 32       | 2018-01-03 18:02:29 |

| 1299998 | 67       | 2018-01-03 18:02:29 |

| 1299997 | 72       | 2018-01-03 18:02:29 |

| 1299996 | 87       | 2018-01-03 18:02:29 |

| 1299995 | 65       | 2018-01-03 18:02:29 |

| 1299994 | 71       | 2018-01-03 18:02:29 |

| 1299993 | 54       | 2018-01-03 18:02:29 |

| 1299992 | 50       | 2018-01-03 18:02:29 |

| 1299991 | 66       | 2018-01-03 18:02:29 |

+---------+----------+---------------------+

10 rows in set (0.01 sec)

mysql> select * FROM kimgn_test order by id desc LIMIT 49995,10;

+---------+----------+---------------------+

| id      | contents | date_val            |

+---------+----------+---------------------+

| 1250005 | 7        | 2018-01-03 18:02:10 |

| 1250004 | 70       | 2018-01-03 18:02:10 |

| 1250003 | 2        | 2018-01-03 18:02:10 |

| 1250002 | 51       | 2018-01-03 18:02:10 |

| 1250001 | 40       | 2018-01-03 18:02:10 |

| 1250000 | 3        | 2018-01-03 16:14:58 |

| 1249999 | 37       | 2018-01-03 16:14:58 |

| 1249998 | 88       | 2018-01-03 16:14:58 |

| 1249997 | 14       | 2018-01-03 16:14:58 |

| 1249996 | 31       | 2018-01-03 16:14:58 |

+---------+----------+---------------------+

10 rows in set (0.02 sec)

--2. 슬레이브 서버의 데이터: 1,300,000건

mysql> select * FROM kimgn_test order by id desc LIMIT 10;

+---------+----------+---------------------+

| id      | contents | date_val            |

+---------+----------+---------------------+

| 1300000 | 2        | 2018-01-03 18:02:29 |

| 1299999 | 32       | 2018-01-03 18:02:29 |

| 1299998 | 67       | 2018-01-03 18:02:29 |

| 1299997 | 72       | 2018-01-03 18:02:29 |

| 1299996 | 87       | 2018-01-03 18:02:29 |

| 1299995 | 65       | 2018-01-03 18:02:29 |

| 1299994 | 71       | 2018-01-03 18:02:29 |

| 1299993 | 54       | 2018-01-03 18:02:29 |

| 1299992 | 50       | 2018-01-03 18:02:29 |

| 1299991 | 66       | 2018-01-03 18:02:29 |

+---------+----------+---------------------+

10 rows in set (0.00 sec)

mysql> select * FROM kimgn_test order by id desc LIMIT 49995,10;

+---------+----------+---------------------+

| id      | contents | date_val            |

+---------+----------+---------------------+

| 1250005 | 7        | 2018-01-03 18:02:10 |

| 1250004 | 70       | 2018-01-03 18:02:10 |

| 1250003 | 2        | 2018-01-03 18:02:10 |

| 1250002 | 51       | 2018-01-03 18:02:10 |

| 1250001 | 3        | 2018-01-03 16:14:58 |

| 1250000 | 3        | 2018-01-03 16:14:58 |

| 1249999 | 37       | 2018-01-03 16:14:58 |

| 1249998 | 88       | 2018-01-03 16:14:58 |

| 1249997 | 14       | 2018-01-03 16:14:58 |

| 1249996 | 31       | 2018-01-03 16:14:58 |

+---------+----------+---------------------+

10 rows in set (0.01 sec)

케이스 3 시나리오 결과

의도한 대로 원하는 곳에서 한번 더 STOP 발생

sql 스레드 스킵 후 데이터 확인 결과 원하는 대로 데이터 입력됨을 확인할 수 있었음.

추가 테스트 사항, UPDATE시 결정 값이 고정적이지 않을 때 SBR에서 두번 실행되지 않을까?

상기의 케이스에서 , UPDATE하려는 칼럼의 값이 기존 값에서 계산하는 형식이라면, 해당 명령이 두번 수행되어 마스터와 슬레이브 노드의 데이터가 일치하지 않지 않을지 확인

테스트 시나리오

PK 데이터 입력

UPDATE 구문 수행(UPDATE test_temp SET info= info + 1 where id=1)\

PK 데이터 입력

슬레이브 포지션 1 이전의 상태로 복원

set global sql_slave_skip_counter=1; 문장을 이용해 sync 동기화

테스트 결과 

  상기와 같은 결과값이 일정하지 않은 쿼리 또한 반영하여 처리하는 것으로 보임. 해당 쿼리를 포함한 binlog 값을 변경하였음에도 불구하고 계속적으로 test_temp 테이블의 info 칼럼의 값이 일치하는 것을 확인할 수 있었음

결론

begin에 해당하는 at ... 번호를 찍는 것이 가장 좋을 것으로 보임

end_log_pos는 해당 명령을 수행하고 난 후의 포지션을 의미하기 때문에,

실제 포지션은 명령 이전에 존재하는 # at 포지션번호 임.

# at 5477872

#180103 16:14:58 server id 1 end_log_pos 5477954 Query thread_id=257606 exec_time=0 error_code=0

SET TIMESTAMP=1514963698/*!*/;

BEGIN

MySQL 5.5 설치(CentOS)

## 소스코드 다운로드

$ wget https://dev.mysql.com/get/Downloads/MySQL-{메이저버전}/mysql-{버전}.tar.gz

## 5.5.56 예시)

wget https://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.56.tar.gz

## mysql 유저 확인

$ cat /etc/passwd | grep mysql

## mysql 그룹 확인

$ cat /etc/group | grep mysql

## 없다면 그룹과 계정 생성

groupadd mysql

useradd -g mysql mysql

## 설치가 되어있지 않다면 다음의 명령으로 설치 진행

## mysql 데이터 및 설정 필요한 디렉토리 생성

$ mkdir /데이터경로/

## 파일 디렉토리 권한 mysql로 변경

$ chown -R mysql.mysql 권한설정필요디렉토리

## 실행파일 /mysql 폴더로 이동

$ mv mysql-5.5.56.tar.gz /mysql/

## 압축 풀기.

$ tar -zxvf mysql-5.5.56.tar.gz

$ cd mysql-5.5.56

## Tip: 컴파일 전 아래의 패키지 설치 확인

$ yum -y install zlib curl

$ yum -y install gcc g++ cpp gcc-c++

$ yum -y install openssl openssl-devel

$ yum -y install libtermcap-devel ncurses-devel libc-client-devel bzip2-devel

$ yum -y install bison

$ yum -y install cmake

## 컴파일

$ cmake -DCMAKE_INSTALL_PREFIX=/mysql \ -- 인스톨 경로

-DMYSQL_DATADIR=/데이터디렉토리 \ -- 데이터디렉토리 경로

-DDEFAULT_CHARSET=utf8 \ -- 캐릭터셋

-DDEFAULT_COLLATION=utf8_general_ci \ -- 콜레이션

-DWITH_EXTRA_CHARSETS=all \ -- 기타 캐릭터셋

-DENABLED_LOCAL_INFILE=1 \ -- local infile(파일 벌크 업로드) 설정여부

-DWITH_INNOBASE_STORAGE_ENGINE=1 \ -- innobase 스토리지 엔진 추가 여부

-DWITH_SSL=bundled \ -- ssl 통신 추가 여부

## 설치

$ make 

$ make install

## 컴파일 실패시 재수행 방법

$ make clean

$ rm CMakeCache.txt

## 퍼미션 변경

chmod 750 /mysql/support-files/mysql.server

chmod 750 /mysql/bin/mysqld_safe

## my.cnf 심볼릭 링크로 설정

$ rm -f /etc/my.cnf

$ ln -s /원하는위치/my.cnf /etc/my.cnf

## mysql DB 생성

$ /mysql/scripts/mysql_install_db --user=mysql --basedir=/mysql --datadir=/data/ismdata/ --defaults-file=/etc/my.cnf

Installing MySQL system tables...

OK

Filling help tables...

OK

## 구동 및 종료 명령

## 구동

/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

## 종료

/mysql/bin/mysqladmin shutdown -u{dba계정} -p

MySQL 5.7 설치(CentOS)

## 소스파일 다운로드

wget https://dev.mysql.com/get/Downloads/MySQL-{메이저버전}/mysql-boost-{버전}.tar.gz

## mysql 유저 확인

$ cat /etc/passwd | grep mysql

## mysql 그룹 확인

$ cat /etc/group | grep mysql

## 없다면 그룹과 계정 생성

groupadd mysql

useradd -g mysql mysql

## cmake 확인 및 설치.

$ which cmake

/usr/bin/cmake

## 설치가 되어있지 않다면 다음의 명령으로 설치 진행

$ yum install cmake

$ tar xvfz mysql-boost-5.7.13.tar.gz

## gcc 4.4.7 에서는 오류 발생!!

## mysql cc1plus: warning: unrecognized command line option "-Wno-unused-local-typedefs"

## gcc 4.7.2 로 업그레이드 후 진행

## 업그레이드 방법

$ wget http://people.centos.org/tru/devtools-1.1/devtools-1.1.repo -P /etc/yum.repos.d

$ yum install devtoolset-1.1

$ scl enable devtoolset-1.1 bash

$ cmake .. -DCMAKE_INSTALL_PREFIX=/mysql \

-DMYSQL_DATADIR=/데이터디렉토리경로 \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci  \

-DWITH_BOOST=../boost/boost_1_59_0 \ -- 부스트 디렉토리 경로

-DENABLE_DOWNLOADS=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \ 

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1

(빌드 옵션: http://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html)

$ make -j `grep processor /proc/cpuinfo | wc -l`

$ make install

## 컴파일 실패시 재수행 방법

$ make clean

$ rm CMakeCache.txt

## 퍼미션 변경

chmod 750 /mysql/support-files/mysql.server

chmod 750 /mysql/bin/mysqld_safe

## my.cnf 심볼릭 링크로 설정

$ rm -f /etc/my.cnf

$ ln -s /원하는위치/my.cnf /etc/my.cnf

$ /mysql/bin/mysqld --initialize

 ...

 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

 1 [Note] A temporary password is generated for root@localhost: 임시비밀번호

/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

$ /mysql/bin/mysql -uroot --socket=/dblab/var/mysql.sock -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.13

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified by '비밀번호';

Query OK, 0 rows affected (0.00 sec)