원격 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
란?--sql-mode="modes"
를 사용하면 서버 런 타임 시점에 SQL 모드 값을 변경 시킬 수가 있다. IGNORE_SPACE
: 함수 이름과 '(' 문자 사이에 스페이스를 허용한다. 이것은 모든 함수 이름을 사용 지정된 (reversed) 단어로 취급하도록 한다. 그 결과, 만약에 어떤 데이터 베이스, 테이블, 또는 사용 지정된 컬럼 이름에 여러분이 접근 하고자 한다면, 반드시 그것을 인용 부호화 해야 한다. 예를들면, USER() 라는 함수는 이미 존재하기 때문에, mysql 데이터 베이스에 있는 user 테이블과 그 테이블 안에 있는 User 컬럼은 사용이 지정되며, 따라서 그것들은 인용 부호로 둘러 쌓여야 한다. Ex) SELECT "User" FROM mysql."user"; ERROR_FOR_DIVISION_BY_ZERO
: INSERT 또는 UPDATE 를 실행하는 동안 0 (또는 MOX(X,0))으로 나눗셈을 하는 것을 만나게 되면 스트릭트 모드에서는 에러가 나오게 된다 (다른 모드에서는 경고가 나옴). 만약에 이 모드가 활성화 되지 않았다면, 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과 같은 명령문이 비활성화 되었거나 또는 컴파일 되지 않은 스토리지 엔진을 지정할 때 디폴트 스토리지 엔진으로 자동 대체 되는 것을 막는다.* 참고
퍼가요~🖤