2장. 설치와 설정

[jjy0918]

2.2 서버 연결

• mysql -uroot -p -host=localhost --socket=/tmp/mysql.sock
  • MySQL의 소켓 파일을 이용해 접속하는 것.
  • host값에 localhost를 이용하면, MySQL은 항상 소켓 모드로 접속한다.
• mysql -uroot -p -host=127.0.0.1 -port=3306
  • TCP/IP를 이용해 접속하는 것.
  • 로컬이 아닌 다른 서버에 있는 MySQL 접속 시 반드시 이 방법을 사용해야 한다.
• mysql -uroot -p
  • 기본 값인 주소(localhost)와 소켓 방식을 이용하여 접속한다.
  • 소켓 파일의 위치는 MySQL 서버의 설정 파일에서 읽어서 사용한다.

[jjy0918]

2.3 서버 업그레이드

• MySQL 서버 업그레이드 하는 방법은 두 가지가 있다.
  1. 인플레이스 업그레이드(In-Place Upgrade)
     • MySQL 서버의 데이터 파일을 그대로 두고 업그레이드
     • 제약 사항은 있지만, 업그레이드 시간을 크게 단축할 수 있다.
  2. 논리적 업그레이드(Logical Upgrade)
     • mysqldump 도구 등을 이용하여 덤프한 후 새로 업그레이드 된 버전의 서버에 덤프된 파일을 적재하는 방법
     • 제약 사항은 거의 없지만, 업그레이드 시간이 오래 걸린다.

2.3.1 인플레이스 업그레이드 제약 사항

• 마이너 버전 업그레이드(8.0.16 -> 8.0.21)은 MySQL 서버 프로그램만 재설치하면 된다.
• 메이버 버전 업그레이드(5.5 -> 8.0)는 크고 작은 데이터 파일의 변경이 필요하기 때문에 반드시 직전 버전에서만 업그레이드가 허용된다.
  • 5.5 -> 5.6: 가능
  • 5.5 -> 5.7: 불가능
• 인플레이스 업그레이드에서 한 가지 더 주의하 점은 메이저 버전 업그레이드가 특정 마이너 버전에서만 가능한 경우가 존재한다는 것이다.
  • 일반적으로 GA 버전 이후의 마이너 버전에서만 가능하다.

2.3.2 MySQL 8.0 업그레이드 시 고려 사항

• 사용자 인증 방식 변경
  • Native Authentication -> Caching SHA-2 Authentication
• MySQL 8.0과의 호환성 체크
• 외래키 이름의 길이
  • MySQL 8.0에서는 외래키의 이름이 64글자로 제한된다.
• 인덱스 힌트
  • 인덱스 힌트에 대한 성능 테스트 필요
• GROUP BY에 사용된 정렬 옵션
  • GROUP BY 뒤에 ASC, DESC 등 정렬 옵션을 사용하고 있다면 제거 필요
• 파티션을 위한 공용 테이블스페이스

2.3.3 MySQL 8.0 업그레이드

• MySQL 5.7 -> MySQL 8.0 업그레이드는 크게 두 단계로 나뉜다.
  1. 데이터 딕셔너리 업그레이드
     • 데이터 딕셔너리 업그레이드 작업은 MySQL 서버(mysqld) 프로그램이 실행한다.
  2. 서버 업그레이드
     • 서버 업그레이드 작업은 mysql_upgrade 프로그램이 실행한다.

업그레이 절차(8.0.15 이하)

1. MySQL 셧다운
2. MySQL 5.7 프로그램 삭제
3. MySQL 8.0 프로그램 설치
4. MySQL 8.0 서버(mysqld) 시작 => 데이터 딕셔너리 업그레이드
5. mysql_upgrade 프로그램 실행 => 서버 업그레이드

업그레이 절차(8.0.16 이상)

1. MySQL 셧다운
2. MySQL 5.7 프로그램 삭제
3. MySQL 8.0 프로그램 설치
4. MySQL 8.0 서버(mysqld) 시작 => 데이터 딕셔너리 업그레이드 & 서버 업그레이드

• MySQL 8.0.16 부터는 mysql_upgrade 유틸리티가 없어지고, 서버 시작 시 모든 업그레이드 작업이 실행된다.

[jjy0918]

2.4 서버 설정

• MySQL 서버는 단 하나의 설정 파일을 사용한다.
  • 리눅스를 포함한 유닉스 계열에서는 my.cnf라는 이름을 사용한다.
  • 윈도우 계열에서는 my.ini라는 이름을 사용한다.
• MySQL은 서버 시작할 때에만 설정 파일을 참조한다.
• 설정 파일은 고정되어 있는 것이 아니라, 지정된 여러 개의 디렉토리를 순차적으로 탐색한 후 처음 발견된 설정 파일을 사용한다.
  • 어떤 파일을 사용하는지 궁금한 경우 mysql --help을 입력하면 알 수 있다.

shell> mysql --help
...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf
...

2.4.1 설정 파일의 구성

• 설정 파일(my.cnf, my.ini)에는 여러 개의 설정 그룹을 담을 수 있다.
  • 일반적으로 실행 프로그램 이름을 그룹명으로 사용한다.

[mysqld]
socket = /usr/local/mysql/temp/mysql.sock
port = 3306

[mysql]
default-character-set = utf8mb4
socket = /usr/local/mysql/tmp/mysql.sock
port = 3304

[mysqldump]
ddefault-character-set = utf8mb4
socket = /usr/local/mysql/tmp/mysql.sock
port = 3305

2.4.2 MySQL 시스템 변수의 특징

• MySQL의 시스템 변수란, MySQL 서버가 기동하면서 설정 파일의 내용일 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위한 값을 의미한다.
• MySQL은 시스템 변수를 별도로 저장해둔다.
• MySQL 서버 접속 후 SHOW VARIABLES; 또는 SHOW GLOBAL VARIABLES; 명령어를 통해 확인할 수 있다.

2.4.3 글로벌 변수와 세션 변수

• MySQL의 시스템 변수는 적용 범위에 따라 글로벌 변수와 세션 변수로 나뉜다.
• 일반적으로 세션별로 적용되는 경우 글로벌 변수뿐만 아니라 세션 변수에도 동시에 적용된다.
• 글로벌 변수란 MySQL 서버 인스턴스 전체적으로 영향을 미치는 시스템 변수를 의미한다.
• 세션 변수란 MySQL 클라이언트가 MySQL 서버에 접속할 때 기본적으로 부여하는 옵션의 기본값을 제어하는 데 사용된다.
  • 세션 변수의 기본 값은 글로벌로 적용되지만, 각 커넥션 별로 설정 값을 다르게 지정할 수 있다.
• 설정 파일에 명시해 초기화할 수 있는 변수는 대부분 범위가 Both이다.
  • 이 경우에는 커넥션이 생성되는 순간 해당 커넥션의 기본 값으로 사용되는 값이다.
  • 순수 세션 변수의 경우 설정 파일에 초기값을 명시할 수 없다.

2.4.4 정적 변수와 동적 변수

• MySQL 서버가 기동 중인 상태에서 시스템 변수룰 변경 가능할 경우 동적 변수라고 한다.
  • SET 명령어를 이용하여 변경할 수 있는 시스템 변수 값을 의미한다.
  • 기동 중인 시스템 변수가 변경되더라도 설정 파일에 적용되지는 않는다.
  • MySQL 8.0부터는 SET PERSIST 명령어를 이용하면 변경과 동시에 my.cnf가 아닌 별도 mysqd-auto.cnf에 기록된다.
• MySQL 서버가 기동 중인 상태에서 시스템 변수를 변경할 수 없는 경우 정적 변수라고 한다.
  • 설정 파일을 통해서만 변경이 가능한 시스템 변수 값을 의미한다.
  • 서버가 재기동을 해야 적용된다.

2.4.5 SET PERSIST

• SET PERSIST 명령을 이용하여 저장된 mysqld-auto.cnf 파일의 옵션은 서버 시작 시 자동으로 my.cnf와 함께 참조해서 적용된다.
• SET PERSIST 명령의 경우 세션 변수에는 적용되지 않고, 자동으로 GLOBAL 변수로 인식하고 변경한다.
• SET PERSIST_ONLY 옵션을 이용하면, 현재 서버애는 적용하지 않고 mysqld-auto.cnf 파일에만 기록한다.
  • 그렇기 때문에 정적인 변수 값을 변경할 때 사용할 수 있다.
  • 정적인 변수를 PERSIST로 변경할 경우 에러가 발생한다.
• mysqld-auto.cnf 파일은 JSON 형태로 누가 언제 어떠한 시스템 변수를 변경했는지 함께 기록된다.
• mysqld-auto.cnf 파일의 내용을 삭제하기 위하여 RESET PERSIST 명령을 제공한다.
  • RESET PERSIST max_connections;
  • RESET PERSIST IF EXISTS max_connections;
  • RESET PERSIST; => 모든 내용 삭제

[KKambi]

5.x 버전에서 사용하던 GROUP BY

https://chamch-dev.tistory.com/3 https://info-lab.tistory.com/274

SELECT name, address FROM tb_test GROUP BY name;

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'drscan_dev.tb_test.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• Group By 절에 포함되지 않는 Column (non-aggregated column)을 select 할 경우, 컬럼의 어느 부분에 표시해야 할 지 애매하여 에러 발생
• 8 버전부터 Only_Full_Group_By 설정이 기본으로 활성화된다.
• 해당 쿼리를 실행하고 싶다면 이 옵션을 비활성화해야한다.
• 또는 쿼리를 변경한다.

SELECT name, ANY_VALUE(address) AS address FROM tb_test GROUP BY name;

SELECT name, address FROM tb_test
WHERE id in (SELECT max(id) FROM tb_test GROUP BY name)

위와 같이 ANY_VALUE() 함수를 사용하거나, non-aggregated column에 대한 서브 쿼리를 만든다.

시스템 변수의 범위

• Global: 글로벌하게 적용됨. 모든 세션마다 동일함. 일반적으로 MySQL 서버 자체에 대한 설정
• Both: 기본값을 변경할 수 있고, 이후 세션마다 다른 값으로 변경할 수 있음.
• Session: 기본값을 변경할 수 없음. 예를 들어 sql_log_bin 세션 변수는 항상 default value가 ON. 세션마다 다른 값으로 변경할 수 있음.

신기한점

SET PERSIST는 글로벌 시스템 변수 전용이다. (재시작하면 초기화되버리는 세션 변수는 의미가 없기 때문에)

MySQL 데이터 딕셔너리

https://blog.naver.com/PostView.naver?blogId=sory1008&logNo=221980241351&categoryNo=79&parentCategoryNo=0&viewDate=&currentPage=1&postListTopCurrentPage=1&from=postView MySQL 인스턴스 내의 데이터베이스 개체에 대한 정보. MySQL 8.0부터 트랜잭션이 지원되는 InnoDB 테이블로 저장된다. test를 제외한 4개 (information_schema, performance_schema, mysql, sys)

• 5.7까지 사용하던 파일 기반 데이터 딕셔너리는 구현이 복잡해지고 성능 문제
• InnoDB도 별도로 데이터 딕셔너리를 가지면서, 파일과 중복된 정보가 존재하거나 싱크가 맞지 않는 문제가 있었다.
• 포맷이 다양해서 유지보수가 어려웠다. (.FRM 말고도 다양했다고 함)