MySQL DB 이관 작업하기

Moonlog·2020년 12월 10일
Databaseexportimportmysqlmysqldumpsql_mode
0

MySQL

목록 보기
1/1
post-thumbnail

원격 mysqldump 생성하기

mysqldump --column-statistics=0 [데이터베이스명] > [덤프파일명.sql] -h[서버주소] -u[아이디] -p[비밀번호]

로컬 mysqldump 생성하기

mysqldump --column-statistics=0 [데이터베이스명] > [덤프파일명.sql] -u[아이디] -p[비밀번호]

덤프 파일 import 하기

mysql [데이터베이스명] < [덤프파일명.sql] -h[서버주소] -u[아이디] -p[비밀번호]

데이터베이스명에 --all-databases를 넣어주면 모든 데이터베이스를 가져온다.
덤프 파일명엔 덤프 파일이 생길 로컬 path를 써넣어줄 수 있다.
ex) home/test.sql

--column-statistics=0 옵션을 넣어주지 않았을 때, 나같은 경우는
Unkown table ‘COLUMN_STATISTICS’ in information_schema 라는 warning 과 함께 덤프 파일에 테이블 한 개 밖에 생성이 안 됐다.

이 것은 mysql 8 부터 기본적으로 활성화되는 옵션 때문이라고 하는데 위의 옵션을 넣어주니 전체 테이블이 모두 잘 export 되었다.

이 외의 문제들

자 이제 잘 export도 했고, import도 했고, pom.xml에 DB버전을 맞춰주고, applicationContext.xml도 잘 수정했으니 실행 되겠지? 했지만 DB정보를 읽어오지 못하는 사건이 발생했다...

SELECT list is not in GROUP BY clause and contains nonaggregated column '컬럼명' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

이런 에러를 내며 작동하지 않았다...

찾아보니 에러 마지막 줄 sql_mode=only_full_group_by 때문이었다.

only_full_group_by 란?

GROUP BY 구문이 결과 컬럼 리스트에 존재 하지 않는 컬럼을 참조하기 위한 쿼리를 허용하지 않는다.

간단히 예를 들면.

아래와 같이 아이디 별로 영어 점수 총 합계를 조회하는 쿼리가 있다.

1  SELECT id as '아이디',
2         phone_num as '전화번호',
3         SUM(eng_point) as '영어점수합계'
4  FROM  sampletable
5  GROUP BY id

phone_num (전화번호) 은 유니크한 값으로 다른 레코드와 중복될 수 없는 값이란 걸 개발자는 알겠지만,

DB입장에선 phone_num의 경우 어떤 값을 표시해야 할지 알 수 없다.
(이를 집계되지 않은 칼럼 nonaggregated column 이라 칭함)

  • 동일한 쿼리를 ORACLE 에서 실행시 적합하지 않은 group by 사용으로 exception 이 발생한다.

  • mysql 에서 실행시 only_full_group_by 옵션의 활성화/비활성화 상태에 따라 exception이 발생/발생하지 않는다.


[ 해결 방법 ]

1. 쿼리를 수정한다.

1) 쿼리 자체를 수정.

1-1) group by 절에 nonaggregated column 추가

1  SELECT  id as '아이디',
2          phone_num as '전화번호',
3          SUM(eng_point) as '영어점수합계'
4  FROM  sampletable
5  GROUP BY id, phone_num

1-2) 서브쿼리 사용(경우에 따라 left outer join 사용)

  1  SELECT  id as '아이디',
  2        phone_num as '전화번호',
  3        ( SELECT SUM(eng_point) 
  4          FROM  sampletable
  5          WHERE st.id = id 
  6          GROUP BY id) as '영어점수합계'            
  7  FROM  sampletable st

2) select 절의 집계되지 않은 칼럼에 ANY_VALUE(칼럼) 함수 추가

1  SELECT  id as '아이디',
2          ANY_VALUE(phone_num) as '전화번호',
3          SUM(eng_point) as '영어점수합계'
4  FROM  sampletable
5  GROUP BY id

2. mysql 설정파일을 수정한다.

2-1) 현재 옵션 상태를 확인한다.

sql_mode 조회.

select @@sql_mode;

sql_mode 조회 결과.

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

ONLY_FULL_GROUP_BY 이 포함되어 있다면 해당 설정이 활성화 되어 있음을 의미.

2-2) my.cnf 설정파일을 열어 해당 부분을 찾아 제거

2-3) mysql 재기동.


나는 /etc/mysql 에 있는 my.cnf 파일에 

[mysqld] 
sql_mode="IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"

위 코드를 추가하여 해결했다.


sql_mode 란?

MySQL이 어떤 SQL syntax를 지원하고 있는지 그리고 데이터의 유효성(Validation)을 검사하는 방법은 무엇이 있는지를 가리키는 것이다. 이것은 MySQL을 서로 다른 환경에서 사용하는 것을 손쉽게 만들어 주며 다른 데이터 베이스 서버와 함께 MySQL을 사용하는 것이 가능하도록 만들어 준다.
디폴트 SQL 모드 설정은 mysqld 를 --sql-mode="modes"를 사용하면 서버 런 타임 시점에 SQL 모드 값을 변경 시킬 수가 있다.
GLOBAL 변수 설정은 SUPER 권한이 필요하며 그 시간 이후에 연결되는 모든 클라이언트의 동작에 영향을 주게 된다. 모든 클라이언트는 자신의 세션 sql_mode 값을 언제든지 변경할 수가 있다.

이 외의 설정들.

IGNORE_SPACE : 함수 이름과 '(' 문자 사이에 스페이스를 허용한다. 이것은 모든 함수 이름을 사용 지정된 (reversed) 단어로 취급하도록 한다. 그 결과, 만약에 어떤 데이터 베이스, 테이블, 또는 사용 지정된 컬럼 이름에 여러분이 접근 하고자 한다면, 반드시 그것을 인용 부호화 해야 한다. 예를들면, USER() 라는 함수는 이미 존재하기 때문에, mysql 데이터 베이스에 있는 user 테이블과 그 테이블 안에 있는 User 컬럼은 사용이 지정되며, 따라서 그것들은 인용 부호로 둘러 쌓여야 한다. Ex) SELECT "User" FROM mysql."user";
IGNORE_SPACE SQL 모드는 빌트인 (built-in) 함수에 적용 되며, 스토어드 루틴에는 적용되지 않는다. 루틴 이름 다음에 스페이스를 가지는 것은 항상 가능한데, IGNORE_SPACE 가 활성화 되어 있는지 와는 상관이 없다.
ERROR_FOR_DIVISION_BY_ZERO : INSERT 또는 UPDATE 를 실행하는 동안 0 (또는 MOX(X,0))으로 나눗셈을 하는 것을 만나게 되면 스트릭트 모드에서는 에러가 나오게 된다 (다른 모드에서는 경고가 나옴). 만약에 이 모드가 활성화 되지 않았다면, MySQL은 0으로 나누는 것에 대해 NULL값을 대신 리턴한다.
INSERT IGNORE 또는 UPDATE IGNORE에서 사용되면, MySQL은 0으로 나누는 것에 대해 경고문을 만들어 내지만, 동작의 결과는 NULL이 된다.
NO_ZERO_DATE : 스트릭트 모드에서, '0000-00-00'을 유효한 날짜 표시 방법으로 사용하지 않도록 한다. 하지만 여전히 IGNORE 옵션을 사용해서 제로 날짜를 입력할 수 있게 된다. 스트릭트 모드가 아닐 때에는, 이러한 날짜는 사용할 수는 있지만 경고가 발생한다.
NO_ZERO_IN_DATE : 스트릭트 모드에서, 월 또는 날짜 부분이 0인 날짜는 받아들이지 않는다. 만약에 IGNORE 옵션이 사용된다면, MySQL은 '0000-00-00' 날짜를 이러한 날짜에 삽입한다. 스트릭트 모드가 아닌 경우에는, 이러한 날자는 경고를 만들기는 하지만 사용 가능하다.
NO_ENGINE_SUBSTITUTION : CREATE TABLE과 같은 명령문이 비활성화 되었거나 또는 컴파일 되지 않은 스토리지 엔진을 지정할 때 디폴트 스토리지 엔진으로 자동 대체 되는 것을 막는다.

스트릭트 모드란?

스트릭트 모드는 MySQL이 유효하지 않거나 누락된 (missing) 데이터를 처리하는 방법을 제어한다. 하나의 값은 여러가지 이유로 유효하지 않게 된다. 예를 들면, 컬럼에 대해 틀린 데이터 타입을 가질 수가 있거나, 또는 범위를 벗어날 수도 있다. 삽입될 새로운 열이 자신의 정의문 안에서 명확한 DEFAULT 구문이 없는 비-NULL 컬럼 값을 가지고 있지 않을 때 데이터는 누락 된다.

트랙젝션 테이블에 대해서는, STRICT_ALL_TABLES 또는 STRICT_TRANS_TABLES 중 하나의 모드가 활성화 될 때 명령문 안에 데이터가 유효하지 않거나 누락이 되면 에러가 발생하게 된다. 명령문은 중단되고 롤백 (roll-back) 된다.

논-트랜젝션 (non-transactional) 테이블에 대해서는, 만약에 삽입되어야 하거나 업데이트 되어야 하는 처음 열에 유효하지 않은 값이 두 번째 또는 그 이후의 열에 나타나게 되면, 그 결과는 어떤 스트릭트 옵션이 활성화 되어 있는지에 따라 다르게 나온다.

* STRICT_ALL_TABLES : MySQL은 에러를 리턴하고 나머지 열들을 무시한다. 하지만, 이와 같은 경우에는 가장 앞서 있는 열들은 여전히 삽입 또는 업데이트가 된다. 이것은 부분적인 업데이트를 한다는 것을 의미한다. 이런 상황을 피하기 위해선 단일 열 명령문을 사용하는 것이 최선인데, 그 이유은 이러한 명령문은 테이블 변경 없이 중단될 수 있기 때문이다.
* STRICT_TRANS_TABLES : MySQL은 유효하지 않은 값을 컬럼에 대해 가장 근접한 유효 값으로 변환 시킨 다음에 이 값을 삽입한다. 데이터 값이 누락될 경우에는, MySQL은 컬럼 데이터 타입에 대한 암시적이 (implicit) 디폴트 값을 삽입하게 된다. 둘 중의 어느 경우에도, MySQL은 에러 메시지가 아닌 경고 메시지를 나타내며 명령문을 계속 처리하게 된다.

* 참고

profile
Moonlog
Start 20.12.10 ~ ing

1개의 댓글

comment-user-thumbnail
김미영
2020년 12월 10일

퍼가요~🖤

답글 달기