READ UNCOMMIT

- 다른 트랜젝션에서 아직 커밋 되지 않은 데이터를 읽어 올수 있는 level

- commit 되지 않아도 읽어 올 수 있기에 읽어 오고 난 뒤에 rollback 되서 읽어 온 값이 아무런 의미 없는 값이 될 수도 있다.

- 전체적으로 읽어온 데이터의 신뢰성이 낮다(낮다라는 의미는 신뢰 할수 없음이고 신뢰 할수 없다면 신뢰도가 0과 마찬가지다)

- 한 트랜젝션 내에서 동일한 select query의 결과가 다를 수 있다(select 하는 중에 해당 데이터가 변경 될수도 있다)

이전에 없던 row가 생겼거나 없어 졌을 수도 있다.

 

READ COMMITED

- 다른 트랜젝션에서 커밋된 데이터만 읽어 올수 있는 level

- 같은 트랜젝션에서 동일한 select query를 실행 한다고 하더라도 그 사이 다른 트랜젝션에서 commit이 발생 할 수 있으므로 동일 트랜젝션 같은 select query라도 결과가 다를 수 있다.

- 이전에 없던 row가 생겼거나 없어 졌을 수도 있다.

- select query 시 테이블 lock을 잡지 않고 매 select query 마다 snapshot을 구축하여 데이터를 읽어 온다

 

REPEATABLE READ

- 다른 트랜젝션에서 커밋된 데이터만 읽어 올수 있음

- 한 트랜젝션 내에서 동일한  select query의 결과가 같음

- 이전의 select query에 없던 row가 생기지 않는다

- select query 시에 테이블 lock을 잡지 않고 트랜젝션 시작시 구축한 snapshot에서 데이터를 읽어 온다
- 테이블 lock을 잡지 않았기에 select 결과는 같더라도 다른 트랜젝션에서 update, insert 한 것에 대해서는 영향을 받는다.

 

SERIALIZABLE

- 데이터 consistancy 100%

- 매 쿼리 마다 table, row lock 다 잡음

 

https://cecil1018.wordpress.com/2016/06/09/mysql-isolation-level/ 

 

MySQL Isolation level

MySQL은 innodb는  트랜잭션을 지원하며, 다양한 비즈니스 로직을 지원하기 위해 많이 사용되는 4가지 격리(isolation) 레벨을 지원한다. READ UNCOMMITTED 가장 낮은 수준의 격리 수준, 아래의 3가지 현상이 발생 dirty read: 트랜잭션을 수행하는 중 , 다른 트랜잭션에서 커밋하지 않은 수정 내용을 볼 수 있음 non-…

cecil1018.wordpress.com

https://jupiny.com/2018/11/30/mysql-transaction-isolation-levels/

Posted by kukuta
TAG MySQL

댓글을 달아 주세요

아래 쉘 스크립트를 crontab등에 등록 해두고 매일매일 실행 시키면 4일 까지의 덤프 데이터가 남는다.

 

#!/bin/sh

BACKUP_DIR=/home/SOMEWHERE/db_backup
TODAY=`date +%Y%m%d`
RETENTION_PERIOD=4  # 백업 덤프 파일 보관 기간 4일

 

DAY_BEFORE_RETENTION_PERIOD=`date --date "${RETENTION_PERIOD} day ago" +%Y%m%d`

#echo "DAY_BEFORE_RETENTION_PERIOD:"${DAY_BEFORE_RETENTION_PERIOD}

 

#유저가 작성한 DB 목록 추출

mysql -u root -h 127.0.0.1 -P 3306 --password='PASSWORD' --skip-column-names -e "select schema_name from information_schema.schemata where schema_name not in ('information_schema','mysql','performance_schema')" > ${BACKUP_DIR}/backup_schema_list.${TODAY}

 

for db_schema in `cat ${BACKUP_DIR}/backup_schema_list.$TODAY | awk '{print $1}'`
do
        backup_schema_list=${backup_schema_list}" "${db_schema}
done

 

# 백업 파일 덤프
mysqldump -u root --password='PASSWORD' --master-data=2 --routines --events --single-transaction --databases ${backup_schema_list} > ${BACKUP_DIR}/tk_db_backup.${TODAY}.sql

rm -f ${BACKUP_DIR}/tk_db_backup.${DAY_BEFORE_RETENTION_PERIOD}.sql

 

'도구의발견' 카테고리의 다른 글

[Unity] Texture2D를 Sprite로 변경  (0) 2017.02.28
Ubuntu Apache(14.04) 설치  (0) 2015.04.29
MySQL 백업 스크립트  (1) 2015.03.06
MySQL master-slave 리플리케이션  (0) 2015.03.06
MySQL Event 등록  (2) 2014.09.24
svn externals 설정  (0) 2014.07.18
Posted by kukuta
TAG MySQL

댓글을 달아 주세요

  1. 싸리끼 2016.09.26 10:30  댓글주소  수정/삭제  댓글쓰기

    유용한 글 너무 감사드립니다.~

1. replication용 DB 계정 생성( master/slave 둘다 )

 $ create user repl@'192.168.186.%' identified by 'repl';

 $ grant replication slave, replication client on *.* to repl@'192.168.186.%' identified by 'repl';

 

2. master 설정

1) master my.cnf 수정

[mysqld]

server-id    = 1 # master DB id for replication

....

########################################

# Replication related settings

########################################

replicate-ignore-db                            = perf_mon

replicate-ignore-db                            = sys

replicate-ignore-db                            = moniter

replicate-ignore-db                            = mysql

2) 먼저 master 의 binlog position 을 확인하고 기록해 둔다.

mysql> show master status \G

*************************** 1. row ***************************

File: mysql_bin.000002

Position: 120

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

3) master shutdown

$ service mysqld stop 또는 /etc/init.d/mysql stop

 

3. slave 설정

1) slave my.cnf 수정

[mysqld]

server-id                               = 2 # slave DB id  for replication

...

########################################

# Replication related settings

########################################

replicate-ignore-db                            = perf_mon

replicate-ignore-db                            = sys

replicate-ignore-db                            = moniter

replicate-ignore-db                            = mysql

#replicate-do-db                               = db_xxx

#

relay_log_purge                                 = OFF

relay-log                                       = /var/log/mysql/relay

#log_slave_update

read-only                                       = 1

report-host                                     = x.x.x.x

report-port                                     = 3306

2) db에 접속해서 slave 초기화

 

mysql> stop slave;

mysql> reset slave all;

3) 복제 설정

※ 기존 운영 중인 MySQL이라면 master에서 데이터를 dump 받아 slave에 넣는 작업이 필요 하다.

mysql> change master to master_host='xxx.xxx.xxx.xxx',

> master_user='repl',

> master_password='repl',

> master_log_file='mysql_bin.000002’,

> master_log_pos=120;

※ master_log_file 과 master_log_pos 는 master status의 binlog position 과 같다.

4) 복제 설정 확인

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: xxx.xxx.xxx.xxx

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 755

Relay_Log_File: testvm2-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000025

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 755

Relay_Log_Space: 107

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

1 row in set (0.00 sec)

5) 복제 시작

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

6) 복제 정상 여부 확인

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.186.xxx

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000025

Read_Master_Log_Pos: 755

Relay_Log_File: testvm2-relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000025

Slave_IO_Running: Yes        <--------------------------   YES 여야 함

Slave_SQL_Running: Yes        <--------------------------   YES 여야 함

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 755

Relay_Log_Space: 411

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)


 

'도구의발견' 카테고리의 다른 글

Ubuntu Apache(14.04) 설치  (0) 2015.04.29
MySQL 백업 스크립트  (1) 2015.03.06
MySQL master-slave 리플리케이션  (0) 2015.03.06
MySQL Event 등록  (2) 2014.09.24
svn externals 설정  (0) 2014.07.18
Unity3D + Facebook 연동 개발 가이드(안드로이드 버젼)  (10) 2014.02.25
Posted by kukuta

댓글을 달아 주세요

심심 풀이로 쿼리를 '발'로 짜다 보니 이중 삼중의 조인쿼리와 서브쿼리가 한데 어우러진 아주 복잡 미묘한 쿼리를 만들게 되었습니다.

암튼 중요한 것은 바롤 짠 쿼리가 아니라 이 쿼리가 과연 정상적인 성능을 낼 수 있을까 하는 의문이 들더군요.

MySQL을 대상으로 만들긴 했지만, MySQL에서 사실 쿼리가 어떤 순서로 실행 되는지도 모르고(쿼리 계획 이라고 하는 것 같던데..) 내부 구조에도 아는 바가 없어 고민하며 프로파일링 툴을 찾다가 몇 가지 재미있는 링크들을 찾아 간만에 포스팅을 하나 올립니다.


일단 explain 이라는 쿼리 플랜을 보여주는 명령어가 있더군요. SQL 표준이 아닌지라, MySQL에서는 select 절만 지원한다고 하는데, 오라클이나 좀 빵빵한 DB들은 explain update, delete까지 지원하지 않을까 싶습니다.

 

쿼리 플랜이란 쿼리가 실행 되는 순서 정도라고 생각하시면 될 듯합니다.

 

해당 정보를 보시면 특정 테이블에 몇 번 접근 했는지, index를 이용했는지 그냥 쌩으로 리스트를 쭉 훑었는지 등등 튜팅에 도움이 될만한 정보를 많이 보여주더군요.

 * MySQL - Explain 정보보는법
(, MySQL에서는 select 밖에 지원이 안됩니다..ㅠㅠ)

 

그리고 MySQL 5.0 버전 부터는 프로파일러가 붙어서 따라 나오더군요. 사용법은 http://niflheim.tistory.com/56 에 보시면 한글로 아주 친철하게 설명이 되어 있습니다만…쿼리 브라우져로 붙어서 하면 안 나오더군요, 꼭 콘솔로 하셔야만 합니다.

쿼리의 성능
, 실제 쿼리가 실행 되는데 걸리는 시간, 테이블 접근에 걸리는 시간 등의 시간정보들을 얻을 수 있습니다.

 

Posted by kukuta

댓글을 달아 주세요

MySQL을 사용하던 서버가 Commands out of sync 라는 에러를 내며서 정상적으로 동작하지 않는다. 무슨 일인가 싶어 구글에게 물어 봤다. 그러니 나오는 대답..
If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

만일 Command out of sync; you can't run this command now 라는 메시지를 클라이언트 코드에서 보게 된다면, 클라이언트 함수의 순서를 잘 못 호출하고 있는 것이다!!

예를 들자면, mysql_use_result() 함수를 호출하고 mysql_free_result() 호출 하기 이전에 새로운 쿼리를 실행 하면 이런 현상이 발생한다. 또 다른 경우로 리턴 데이터가 있는 두 개의 쿼리를 mysql_use_result()이나 mysql_store_result() 없이 연속으로 호출 하면 같은 현상이 발생한다.

해석이 제대로 된건지는 잘 모르겠다만...들어 보니 thread safe가 제대로 맞지 않아서 이렇게 되었다고 한 것 같은데...

언제 코드를 또 다 살펴 보나...-_-;;;
Posted by kukuta

댓글을 달아 주세요

DB A에 있는 테이블들을 DB B로, rename을 사용하여 옮기는 코드가 오류를 내고 있다는 연락을 받았다. 다른 모든 테이블들은 아무런 군소리 없이 옮겨 지는데 딱 한 테이블만 121 에러 코드를 내면서 옮겨 지지 않았다.

무슨 소린가 해서 구글 박사님게 물어 보니 외부 참조 키가 중복이 되면 121 에러 코드가 리턴 된단다. 그래서 테이블 스키마를 살펴 보니...아무런 constraint가 없었다. 이 테이블은 그냥 컬럼들의 나열일 뿐이었다.

/**
1) MySQL rename menual page : http://dev.mysql.com/doc/refman/4.1/en/rename-table.html
2) 121 error : http://forums.mysql.com/read.php?22,33999,76181#msg-76181

1)은 rename을 하기 위해서는 lock이 걸려 있지 않아야 한다는 것을 길게 이야기 하고 있고, 2)는 외부키 참조 중복과 더 불어, DB파일 자체가 잘 못 될 수도 있음을 이야기 한다.
*/

한창 이 문서 저 문서를 뒤지고 살펴 보던 중 /usr/local/var 밑에 MySQL의 에러 로그가 남는다는 사실을 알았다.(뭐...설정마다 다를 수 있지만..)

그래서 로그를 살펴 보니..이 일이 이번에 발생한 것이 아니고 아주 오래전...2005년 12월 부터 발생하고 있었던 사실을 알아 냈다.

결론은 환경이나 코드상의 버그가 아니라, MySQL자체의 버그이거나, 파일이 깨진 것으로 판단. table을 drop하고 재생성했다. 결과는 당연히 정상 동작하고 있다. 하지만 이거 하나 하려고, 고생했던 수 많은 시간들을 생각하면 눈물이 난다..ㅠㅠ

---------------------------------------------------------------------------------------------------

위의 내용은 사실이 아니었습니다. 속지 마시기를...-_-;;
제가 올린 글을 제가 속지 말라고 하다니 웃기긴 하군요..아무래도 이 문제는 DB의 타입에 문제가 있었던듯 합니다.

InnoDB에서 ISAM으로 변경을 하니 에러가 나지 않는군요.

이게 정확한 해결 방법인지는 저도 잘 모르겠습니다만..아뭏튼 이렇게 하고 난 후에는 에러가 발생하지 않고 있습니다. 혹시 정확한 원인을 아시는 분은 가르쳐 주시면 감사하겠습니다.(-_-)(_ _)

Posted by kukuta

댓글을 달아 주세요