Louis.Kim 의 제 3막

요즘 3.2 에서 5.6.32 로 Data Migration 이 가능하도록 개발 중이다.

그 중에 잠시 이슈를 정리하고 간다.

MySQL 5.6.32 를 설치한 곳에서 python 으로 

MySQL 설치된 곳 lib 폴더 내에 해당 libmysqlclient.so.10 파일이 없는 것을 확인 하였다.

검색하다 보니 여기저기 설정하라고 하는데 정작 설치가 되어 있지 않은 것 같아 rpm 으로 설치를 하였다.

아래 내용들은 잘못 되었다...하지만 참고하신 분들이 있을 것 같아서 삭제는 안하고 선을 그었다.

아래는 설정하라는 위치들이다.

하지만 설치가 우선이라..

이제 /etc/profile 에 설정을 해 보자.

이러고 나면 제대로 되는 것을 확인할 수 있다.

하지만.......5.6에 접속이 되지 않는다.

그렇구나.....다시 검색이 필요하다...

의외로 간단하게 풀었다.

/usr/local/mysql/lib 폴더 내의 libmysqlclient.so.18 을 soft link를 걸어서 생성해 주었다.

이후에 session 을 끊은 후 다시 접속하여 확인해 보니 쉽게 통과 했다.

하지만.....다른 에러를 지금 풀고있다.

위의 내용은 쓸데없이 다른 환경설정 건들지 말고 soft link(symbolic link) 로 생성해 주면 된다.

해당 내용은 일단 끝!

추가로 환경설정이 필요해서 추가합니다.

1. cd /etc/ld.so.conf

2. vi로 아무 파일명 생성하여 아래 내용 추가

/usr/local/mysql/lib

ldconfig 를 쳐서 적용

3. vi ~/.bash_profile 

LD_LIBRARY_PATH=/usr/local/mysql/lib

export LD_LIBRARY_PATH

이후 source ~/.bash_profile

한 후 혹시나 하는 마음에 해당 세션 종료 후 다시 접속 후 확인

꾸준히 공부한 것에 다시 정리하면서 보니 이해 했던 부분은 더 쉽게 머리에 남고..

헷갈렸고 몰랐던 부분에 대해서는 정리가 되는 것 같다.

오늘은 Transaction 에 대한 isolation 정리다.

※ isolation level (Transaction 격리 수준)

- 동시에 여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정

- 격리 수준 :  " Read Uncommitted", "Read Committed", "Repeatable Read", "Serializable"

- 격리 수준이 높아질수록 동시성도 떨어지는 것이 일반적이라고 볼 수 있다.

1. Read Uncommitted (Dirty Read)
• 일반적으로 거의 사용하지 않음
• 변경 내용이 Commit이나 Rollback 여부에 상관 없이 다른 트랜잭션에서 보여짐
• 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상
• Dirty Read를 유발하는 Read Uncommitted는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준
• Dirty read, Non-repeatable read, Phantom read 현상 발생
2. Read Committed
• Oracle DBMS에서 기본적으로 사용되고 있는 격리 수준(Shared Lock을 사용)
• 온라인 서비스에서 가장 많이 선택되는 격리 수준으로 데이터를 변경했더라도 Commit이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있음
• 어떤 트랜잭션에서 처리한 작업이 commit 이 안되어 있다면 다른 트랜잭션은  undo 영역에 있는 기존 값을 참고하여 보여 주게 됨
• Read Committed 격리 수준에서도 "Non-Repeatable Read"("Repeatable Read" 가 불가능) 라는 부정합 문제 발생 (Phantom read 현상 발생)

 3. Repeatable Read

• MySQL InnoDB의 기본적으로 사용되는 격리 수준(모든 데이터에 shared lock 이 걸리므로 다른 사용자는 그 영역에 해당되는 데이터에 대한 수정이 불가능)
• Binary Log를 가진 MySQL 의 장비에서는 최소 Repeatable read 격리 수준 이상을 사용해야 함
• Non-Repeatable Read 부정합이 발생하지 않음
• InnoDB Storage Engine은 트랜잭션이 Rollback 될 가능성에 대비해 변경되기 전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경(MVCC-Multi Version Concurrency Control)
• MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 Transaction 내에서는 동일한 결과를 보여줄 수 있도록 보장(Read committed 도 commit 되기 전 데이터를 보여줌)
• Repeatable Read 와 Read Committed 의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하는지에 있음. -> 언두영역에서 특정 트랜잭션 번호의 구간 내에서 백업된 데이터를 보여 줌, 하지만 트랜직션을 종료하지 않으면 무한정으로 언두 영역이 커질 수 있으며 이로 인해 MySQL 성능이 떨어질 수 있음
• select .. for update 쿼리의 경우 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안보였다가 할 수 있다. 이것을 Phantom Read(Phantom row)라고 표현 -> Undo 영역을 lock 할 수 없기 때문에 변경 전 데이터가 아닌 현재 변경된 레코드를 표현 

 4. Serializable

• 동시성이 중요한 DB에서는 거의 사용하지 않음
• 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 됨
• 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없음
• 일반적 DBMS에서 일어나는 "Phantom Read" 라는 문제가 발생하지 않지만 InnoDB 스토리지 엔진에서는 Repeatable Read 격리 수준에서도 이미 Phantom Read가 발생하지 않으므로 굳이 Serializable를 사용할 필요성 없음

 5. Repeatable Read 격리 수준과 Read Committed 격리 수준의 성능 비교

• Repeatable Read가 Transaction 을 열어 해당 Transaction 에서 모든 테이블의 데이터를 Select 를 한 후 그대로 놔두면 InnoDB의 Undo 영역이 계속 커져서 시스템 테이블스페이스 I/O가 유발되는 경우가 대표적인 예..(성능을 떨어트리는 이유)
• 해당 문제를 일으키지 않으면 성능상 차이가 거의 없음. Binary log가 활성화된 MySQL 서버에서는 Read Committed 격리 수준을 사용할 수 없음

사실 이해하기 위해서는 Transaction 이 일어나는 현상을 이미지로 설명한 것을 읽으면 빠를 듯 싶다.

또한 MySQL(Repeatable Read) 과 Oracle(Read Committed) 은 격리수준이 다르다.

현재 MySQL 3.28 로 구성된 Slave를 MySQL 5.6.31 로 업그레이드 작업을 진행 중이다.

Master의 경우 작업 당일날 진행할 예정이고,

Slave의 경우 백업 데이터가 일 단위로 구성되고 있기에 이 부분에 대해 먼저 다른 장비로 Migration 을 진행 하기로 했다.

매일 Dump를 이용해서 작업 하기에는 지루한 작업이 될 것이다.

그래서 매일 백업 데이터를 dump 받아 scp 로 전송 후 다른 장비에서 해당 dump를 이용한 load 및 데이터 건수 비교하는 스크립트를 개발 중이다.

그 중 scp 로 전송하는 방법이 잠시 막혀 공유하고자 한다.

scp 를 이용하는 방법으로 검색하면 twisted ? paramiko_scp ? pexpect ? 등을 설치해서 간편하게 사용하는 방법이 있지만..

가급적이면 설치 등은 피하는 방법을 사용하다 보니

어쩔 수 없이 os.system 이라는 명령문을 사용할 수 밖에 없었다.

os.system은 os에서 사용하는 shell 명령문을 사용 가능하도록 하는 문장인데...

scp 를 하게 되면 먹통이 되는 현상을 확인할 수 있었다.

또한 간단하게 테스트하기 위해서 python에서 직접 os.system 사용하여 scp 를 날리면 비밀번호를 묻는 것을 확인할 수 있었다.

(비밀 번호 묻는게 당연한 이야기 이겠지만....)

여러가지 확인중 ssh 를 이용하여 비밀번호 묻지 않고 바로 전송 하는 방법으로 진행 했더니 정상적으로 전송이 되는 것을 확인할 수 있었다.

아래는 간단하게 ssh 암호 묻지 않는 방법에 대해서 공유해 본다.

(물론 암호 관련하여서는 가급적이면 하지 않는 방법이 최선이며, 꼭 해야 된다면 내부망에서만 가능하도록 하자)

- SSH 암호 없이 접속하는 방법

접속시도하는 (source OS) 에서 

1. ssh key 생성 

 $ ssh-keygen -t rsa

 - /root/.ssh/ 아래에 암호 관련된 파일이 생성

2. ssh 복사

 - /root/.ssh/id_rsa.pub 파일을 cat 으로 열어 내용을 복사

3. Target OS에서 authorized_keys 생성

 - Target (접속하는 서버) 의 /root/.ssh/authorized_keys 파일을 만들어 내용 복사 진행

4. 테스트 진행

 - source OS에서 target OS 으로 접속 진행 테스트

 - port는 1004 라고 가정

 $ ssh -p 1004 192.168.0.2  

암호없이 접속 되면 성공

어쩌면 이것도 ETL 의 개념???과 비슷하지 않을까 싶다. ㅎㅎㅎㅎㅎㅎㅎㅎ

ETL 이란 ? [출처] 위키백과 https://ko.wikipedia.org/wiki/%EC%B6%94%EC%B6%9C,_%EB%B3%80%ED%99%98,_%EC%A0%81%EC%9E%AC

◎ 운영체제(OS)에서 제공되는 기본적인 기능들을 제공.

완전 퍼온 글이다. 도움이 될 듯 싶어서 공유한다.

BINLOG FORMAT은 세가지가 있다. 

1) STATEMENT
2) MIXED
3) ROW 

이 중 MIXED는 1),3) 의 혼합 형태로 binary 로그를 남기게 되는데 
몇몇 일관성을 보장하지 못하는 케이스를 제외하고는 1) STATEMENT 형태로 남게 된다. 

그런데 ISOLATION LEVEL 도 binary 로그 포맷에 영향을 주게 되는데 
ISOLATION LEVEL 이 READ-COMMITTED의 경우 binlog_format을 MIXED 이상으로 설정을 해야 한다. 


ISOLATION LEVEL 

1) READ-COMMITTED 

The default isolation level for most database systems ( nut not MySQL ) is READ COMMITTED. It satisfies the simple
definition of isolation used earlier: a transaction will see only those changes made  by transactions that were already committed when it began, and its changes won't be visible to others until it has committed. This level still allows what's 
known as a nonrepeatable read. This means you can run the same statement twice and see different data.

2) REPEATABLE READ

It guarantees that any rows a transaction reads will "look the same" in subsequent reads within the same transaction, but 
in theory it still allows another tricky problem: phantom reads. Simply put, a phantom read can happen when you select some range of rows, another transaction inserts a new row into the range, and then you select the same range again: 
you will then see the new "phantom" row. InnoDB and XtraDB solve the phantom read problem with multiversion comcurrency control, which we explain later in this chapter. REPEATABLE READ is MySQL's default transaction isolation level.


Isolation level을 read committed로 하고 binlog format을 MIXED로 한 후 대부분의 binary 로그가 statement 형태로 남을 것으로 생각했다. 그런데 전부다 row format 으로 남았고, 이것이 정상이라고 한다. isolation level이 repeatable read 에서는 binlog format MIXED 일 때 대부분의 경우 statement 형태로 남고 일관성이 보장이 안되는 특정 경우에만 row 형태로 남았다. 

이건 isolation level의 개념만 다시 확인해 보면 당연한 결과이다. 

테스트를 해보자. 

1. transaction_isolation = repeatable-read

두 개의 세션에서 트랜잭션을 열고 아래 번호 순서대로 DML이 실행한다. 그리고 
 

[root@localhost] (test) 12:12> rollback;
Query OK, 0 rows affected (0.00 sec)

####### Session A

[root@localhost] (test) 12:13> start transaction ;
Query OK, 0 rows affected (0.00 sec)

[root@localhost] (test) 12:14>
[root@localhost] (test) 12:14> update trans_test2 set b=999 where a=1;   ----------- 1)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 12:14> update trans_test2 set b=222 where a=10;  ----------- 3) 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 12:14> commit;
Query OK, 0 rows affected (0.00 sec)

####### Session B

[root@localhost] (test) 12:13> start transaction ;
Query OK, 0 rows affected (0.00 sec)

[root@localhost] (test) 12:14>
[root@localhost] (test) 12:14> update trans_test2 set b=999 where a=20;  ------------ 2)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 12:14> update trans_test2 set b=222 where a=25;  ------------ 4)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 12:14> commit;
Query OK, 0 rows affected (0.00 sec)

####### binary log를 확인해 보면 먼저 commit 된 transaction 순서대로 기록된다. ( DML 실행 순서 아님 )

#131001 12:14:08 server id 12  end_log_pos 10509        Query   thread_id=2     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1380597248/*!*/;
update trans_test2 set b=999 where a=1f<9c>ⓒK
/*!*/;
# at 10509
#131001 12:14:21 server id 12  end_log_pos 10622        Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1380597261/*!*/;
update trans_test2 set b=222 where a=10oI^PØ
/*!*/;
# at 10622
#131001 12:14:47 server id 12  end_log_pos 10653        Xid = 127
COMMIT/*!*/;
# at 10653
#131001 12:14:13 server id 12  end_log_pos 10732        Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1380597253/*!*/;
BEGINU<90>%²
/*!*/;
# at 10732
#131001 12:14:13 server id 12  end_log_pos 10845        Query   thread_id=3     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1380597253/*!*/;
update trans_test2 set b=999 where a=20AE§￥
/*!*/;
# at 10845
#131001 12:14:44 server id 12  end_log_pos 10958        Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1380597284/*!*/;
update trans_test2 set b=222 where a=25^]GRA
/*!*/;
# at 10958
#131001 12:14:50 server id 12  end_log_pos 10989        Xid = 129
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

2. transaction_isolation = read-committed

=== session A

[root@localhost] (test) 13:27> start transaction;
Query OK, 0 rows affected (0.00 sec)

[root@localhost] (test) 13:32>
[root@localhost] (test) 13:32> update trans_test2 set b=444 where a=1;    ---------- 1)
Query OK, 1 row affected (0.16 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 13:32> select * from trans_test2 where a=20;      ---------- 2) 
+----+------+
| a  | b    |
+----+------+
| 20 |  999 |
+----+------+
1 row in set (0.01 sec)

[root@localhost] (test) 13:33> select * from trans_test2 where a=20;      ----------- 5) 3) DML 결과가 보인다.  
+----+------+
| a  | b    |
+----+------+
| 20 |  777 |
+----+------+
1 row in set (0.00 sec)

[root@localhost] (test) 13:33> update trans_test2 set b=787 where a=20 and b=777;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 13:33>
[root@localhost] (test) 13:33> commit;
Query OK, 0 rows affected (0.00 sec)

[root@localhost] (test) 13:33> select * from trans_test2 where a=20;
+----+------+
| a  | b    |
+----+------+
| 20 |  787 |
+----+------+
1 row in set (0.00 sec)

=== session B

[root@localhost] ((none)) 13:28> use test
Database changed
[root@localhost] (test) 13:28> start transaction;
Query OK, 0 rows affected (0.00 sec)

[root@localhost] (test) 13:33>
[root@localhost] (test) 13:33> update trans_test2 set b=777 where a=20;    ----------- 3) 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 13:33>
[root@localhost] (test) 13:33> commit;   -------- 4) 
Query OK, 0 rows affected (0.00 sec)

위 테스트 처럼 isolation level READ-COMMITTED의 경우  현재의 트랜잭션이 종료 되지 않은 상황에서 다른 트랜잭션이 commit 이 되면 그 트랜잭션 통해 변경된 값이 보이게 된다. 그런데 이런 경우를 statement 방식으로 트랜잭션 단위로 묶어서 순서대로 로깅해서 그 순서대로 쿼리를 실행하게 될 경우 실제 상황을 그대로 재현할 수 없게 되고 다른 결과가 나올 수 있게 된다. 그렇기 때문에 isolation level READ-COMMITTED , binlog_format MIXED 에서 는 binary log가 row 형태로 남게 된다. 

utf-8 과 utf8mb4 에 대해서 알아보자.

해당 캐릭터셋은 현재 회사 DB가 euckr 인데...이것을 utf8로 변경하고자 확인하던 도중 많은 도움이 된 블로그가 있어서 정리하면서 같이 소개해 본다.

아래 블로그의 모든 내용을 조금씩 정리하면서 작성하였습니다.

가져온 블로그 : https://blog.lael.be/post/917

utf-8 문자 집한은 1~4 byte까지 저장이 가능하게 설계(가변 바이트)

 -> 다국어가 지원이 되기 때문에 전세계 모든 언어를 저장이 가능하다

 -> MySQL에서 utf8 을 3 Byte 가변 자료형으로 설계

하지만 최근에 나온 4 byte 문자열(Emoji 같은 것)을 utf8 에 저장하면 값이 손실되는 현상이 발생

(charset 은 utf8 / collation 는 utf8_general_ci)

4 Byte UTF-8 문자열

 : MySQL에서 기존 설계대로 가변 4 Byte utf-8 문자열을 저장할 수 있는 자료형을 추가

 -> 이것이 utf8mb4 로 지정 (MySQL 5.5.3 에서부터 추가)

추가로 utf8mb4 는 Emoji 문자열 지원

- 스마트폰에서 지원하는 문자들을 지원 (SMP - Supplementary Multilingual Plane)

  (모바일 어플의 경우의 DB는 utf8mb4 로 설정하는 것이 좋을 듯 싶다) 

 아래 이미지 참고 

즉, utf8 (가변 3바이트) utf8mb4 (가변 4바이트) 저장공간의 크기

Collaction

Collaction은 텍스트 데이터를 정렬 (Order by) 할 때 사용

text 계열 자료형에서만 사용할 수 있는 속성

utf8_bin (utf8mb4_bin)

Binary 저장 값 그대로 정렬

Hex 코드 (16진수)로 되어 있기에 해당 값으로 정렬

utf8_uniconde_ci (utf8mb4_unicode_ci)

한국어, 영어, 중국어, 일본어 사용환경에서는 general_ci 와 unicode_ci 의 결과가 동일

하지만 외국어 및 특수문자가 들어가는 경우 다름

매일 하나씩 공유하고 연구하자는게 하루만에 무너졌다....

다시 시작해보자!!!

mysqlbinlog 관련하여 테스트 중 binlog_format 을 row로 변경하여 테스트 하던 도중 아래와 같은 에러가 발생 하였다.

으음.....여기저기 찾아봤는데 원하는 답변을 못 찾았다...

그러다 어느 중국 사이트에서 유사 관련하여 올려 놓은 것을 확인할 수 있었다.

단순 default 로 잡힌 mysqlbinlog 의 version을 확인해 보자

3.2 인것을 확인할 수 있다.

버전차이 때문에 발생할 수 있으니 다음과 같이 엔진에 있는 mysqlbinlog 로 확인해 보자

3.4 인 것을 확인할 수 있다.

그렇다면 다시 binlog가 제대로 되는지 확인해 보자

제대로 import되는 것을 확인할 수 있다.

row 관련한 내용은 좀 더 공부해야겠다...참고로 아래 이미지를 보면 정말 oracle 의 rowid 를 보는 듯한 느낌이 난다...

• Storage Engine별 지원하는 logging 형태에 따라 2가지로 분류
1. SLC (Statement-Logging Capable) - Statement Logging 방식을 지원하는 Storage Engine.
2. RLC (Row-Logging Capable) - Row Logging 방식을 지원하는 Storage Engine

• Binary Log에 작성되는 3가지 종류

1. Safe : Statement 방식으로 작성 시 Replication 이나 복구 시에 문제가 되지 않는 쿼리 형태
2. UnSafe : Statement 방식으로 작성 시 Replication 이나 복구 시에 문제가 되는 쿼리 형태
3. Row/Binary Injection : Row Event로 Row Based 방식으로 실행하기 위해 저장되는 Event형태 (Row format 으로 로깅해야 지만, 변경 내역이 저장되는 것을 의미)

•  MySQL은 Grant 작업은 직접적인 DML로 진행될 수도 있으나 간접적인 DDL 구문으로 실행 가능

1. 직접적으로 실행되는 DML 는 Binlog_format에 따라 작성되는 방법이 다름 (Insert / Update / Delete / Replace / Do / Load Data Infile / Select / Truncate table)
2. 간접적으로 실행되는 DDL 문은 Binlog_format과 상관없이 무조건 Statement방식으로 저장(Grant / Revoke / Set Password / Rename User / Create / Alter Drop)
3. Create table .. Select 경우 Create Table 은 Statement 방식으로 저장되고 Select 부분은 Binlog_format 설정값에 따라 달라짐