4. sshd_config 파일 내용 중 PasswordAuthentication no 로 설정된 부분을 yes로 변경한다.
5. sshd 서비스를 재기동한다.
sudo service sshd restart
6. 외부에서 접속할 VM 인스턴스의 계정을 생성한다.
sudo useradd -m -s /bin/bash -G {groups} {new user name}
아래 예시는 users 그룹의 demouser 계정을 생성하는 명령어이다.
sudo useradd -m -s /bin/bash -G users demouser
7. 계정 패스워드 설정
위에서 생성한 계정의 패스워드를 설정한다.
아래 예시는 demouser 계정의 패스워드를 설정하는 명령어이다.
sudo passwd demouser
8. 외부에서 해당 VM 인스턴스의 외부 IP로 접속한다. 윈도우 PC나 리눅스 서버에서 대상 VM 인스턴스에 접속한다.
ssh username@외부_IP
아래 명령어는 VM 인스턴스의 외부 IP로 위에서 생성한 계정으로 접속하는 예시이다. 아래처럼 위에서 생성한 패스워드를 입력하면 VM 인스턴스에 접속할 수 있다.
ssh demouser@35.216.99.220
demouser@35.216.99.220's password:
Linux vm-aicallbot-admin-prd-02 4.19.0-18-cloud-amd64 #1 SMP Debian 4.19.208-1 (2021-09-29) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
오픈된 화면에서 ssh-keygen -t rsa -C username 명령어로 keypair를 생성한다. 계정의 홈 디렉토리 하위의 .ssh 디렉토리에 id_rsa private key와 id_rsa.pub 라는 public key가 생성된다.
$ ssh-keygen -t rsa -C username
아래 명령어로 ssh 인증키를 등록한다.
cat ~/.ssh/id_rsa.pub > ~/.ssh/authorized_keys
또는 구글 콘솔에서 "Compute Engine" 메뉴의 "메타데이터"를 클릭한 후 SSH public key를 등록해준다.
등록할 key는 VM 인스턴스에 생성되어 있는 id_rsa.pub 내용을 등록한다.
이제 VM 인스턴스에 생성되어 있는 private key를 다운로드한다.
GCP 콘솔에서 오픈한 SSH 창의 우측 상단에 "Download file"을 클릭한 후 다운로드 화면이 나오면 private key가 생성되어 있는 경로명과 파일명을 입력한다. 예시) /home/username/.ssh/id_rsa
다운로드한 private key를
윈도우인 경우 C:\Users\<username>\.ssh 하위에 복사한다.
리눅스인 경우에는 /home/<username>/.ssh 하위에 복사한다.
아래의 명령어로 대상 서버에 접속한다.
ssh <username>@35.216.96.152
또는 ssh -i c:\users\<username>\.ssh\id_rsa username@35.216.96.152 를 수행한다.
여기서 -i 옵션은 private key의 위치를 지정한다.
IP Address는 GCP VM 인스턴스의 외부 IP 이다.
> ssh username@351.216.96.152
Linux vm-aicallbot-admin-prd-01 4.19.0-18-cloud-amd64 #1 SMP Debian 4.19.208-1 (2021-09-29) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Fri Mar 11 07:38:49 2022 from 35.235.242.81
username@vm-admin-prd-01:~$
MySQL 의 느린 쿼리 로그 기능을 사용하면 미리 정의된 시간 제한을 초과하는 쿼리를 기록할 수 있습니다. 이는 비효율적이거나 시간 소모적인 쿼리를 찾는 작업을 크게 단순화합니다. 이러한 로그를 Cloud Logging 및 Monitoring과 통합하면 데이터베이스 성능을 주시하여 최적화가 필요한 쿼리를 식별하고 선택적으로 느린 쿼리가 너무 많은 경우 알림을 받을 수 있습니다.
이 가이드는 Compute Engine에서 실행되는 자체 관리형 MySQL 인스턴스에도 사용할 수 있습니다.
목표
Cloud SQL에서 느린 쿼리 로그를 사용 설정하는 방법을 알아보세요.
Cloud Logging에서 느린 쿼리 로그에 액세스하는 방법을 알아보세요.
경고 및 문제 해결을 위한 로그 기반 측정항목을 만드는 방법을 알아보세요.
비용
본 문서에서는다음 Google Cloud 구성요소를 사용합니다. 비용이 발생합니다.
MySQL용 Cloud SQL(본 문서에스는 demo-mysql 인스턴스ID를 사용)
클라우드 로깅
클라우드 모니터링
MySQL용 Cloud SQL에 느린 쿼리 로그 사용 설정
MySQL용 Cloud SQL은 GCP에서 MySQL 데이터베이스를 쉽게 설정, 유지, 관리할 수 있는 완전 관리형 데이터베이스 서비스입니다. Cloud SQL에는 Cloud Logging 통합이 내장 되어 있으므로 로그 뷰어에서 MySQL 로그를 사용할 수 있습니다.
느린 쿼리 로그는 기본적으로 2초 이상 걸리는 SQL 문을 로깅합니다.long_query_time플래그값을 조정하여 이 필터링을 제어할 수 있습니다 .기본적으로 느린 쿼리 로그는 MySQL에서 활성화되어 있지 않습니다.느린 쿼리가 활성화되지 않은 기존 데이터베이스 인스턴스를 사용하는 경우 다음을 사용gcloud sql instances patch하여 플래그를 설정할 수 있습니다.
Connecting to database with SQL user [root]. Enter password:
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:01:00
리소스를 많이 사용하는 SQL 쿼리 목록이 있는 파일을 만듭니다.
cat << EOF >select_query.sql
use employees;
SELECT * FROM salaries WHERE salary > 0 LIMIT 100000;
SELECT departments.dept_name,dept_emp.from_date,employees.* FROM departments LEFT JOIN dept_emp ON (departments.dept_no = dept_emp.dept_no) LEFT JOIN employees ON (dept_emp.emp_no = employees.emp_no) WHERE to_date = '9999-01-01' LIMIT 100000;
SELECT employees.* FROM employees LEFT JOIN dept_emp ON ( dept_emp.emp_no = employees.emp_no ) LEFT JOIN salaries ON ( salaries.emp_no = salaries.emp_no ) WHERE employees.first_name LIKE '%Jo%' AND salaries.from_date > '1993-01-21' AND salaries.to_date < '1998-01-01' limit 1000000;
EOF
Cloud Shell 인스턴스의 공개 IP 주소에 Cloud SQL 인스턴스에 대한 액세스 권한을 부여합니다.
Benchmark
Average number of seconds to run all queries: 103.712 seconds
Minimum number of seconds to run all queries: 103.712 seconds
Maximum number of seconds to run all queries: 103.712 seconds
Number of clients running queries: 10
Average number of queries per client: 50
테스트에는 몇 분이 소요될 수 있습니다.
테스트가 완료되면 Cloud Logging 로그 뷰어 페이지 로 돌아갑니다.(검색창에 logging을 입력한 후 검색된 "로깅" 을 클릭합니다.)
리소스 드롭다운에서 Cloud SQL을 클릭한 후 및 본인이 생성한 Cloud SQL 인스턴스ID를 선택 후 [추가]를 클릭합니다.
로그이름 드롭다운에서 mysql-slow.log 를 선택한 후 [추가] 버튼을 클릭합니다.
SELECT 로 시작하는 부하 테스트의 느린 쿼리를 볼 수 있습니다.
sudo service google-fluentd restart
MySQL 느린 쿼리 로그 켜기
기본적으로 MySQL은 느린 쿼리 를 기록하지 않습니다 . 이를 활성화하려면 루트로 MySQL에 연결하고 루트 암호를 입력하십시오.
mysql -u root -p
mysql>프롬프트 에서 다음 명령을 입력 합니다.
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log = 'ON';
이제 MySQL은 2초 이상 걸리는 모든 쿼리를 기록합니다. 의 값을 조정하여 이 임계값을 변경할 수 있습니다 long_query_time.
이제 느린 쿼리 로그가 google-fluentd예상되는 위치에 기록되고 있는지 확인합니다.
sudo cat /var/log/mysql/mysql-slow.log
예상 출력:
/usr/sbin/mysqld, Version: 5.7.26-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
이 설정은 일시적이며 MySQL을 다시 시작하면 손실됩니다. 이를 유지하려면 MySQL 옵션 파일 (일반적으로 /etc/mysql/mysql.conf.d/mysqld.cnf)을 찾아 루트로 파일을 편집하고 다음 행을 추가하거나 주석 처리를 제거하십시오.
Google Cloud Platform에서 영구 디스크 (HDD or SDD)를 추가하는 방법입니다.
GCP에서 하드 디스크 추가하는 방법은 아래 2단계를 거쳐야 합니다.
Google Cloud Console에서 디스크를 생성하고, VM Instance에 연결
VM instance 에서 디스크를 포맷하고 마운트 한다. (일반 리눅스에 HDD 연결과 동일한 작업)
Google Cloud Conole에서 VM Instance에 디스크 추가
1) Google Cloud Console에서 Compute Engine에서 VM 인스턴스를 선택
2) 디스크를 추가하고자 하는 VM Instance 선택
3) 세부 정보에서 상단의 '수정' 클릭
3) "추가 디스크" 항목에서 '+새 디스크 추가' 클릭
4) 디스크 이름, 유형, 크기, 삭제규칙 등 설정
5) [저장] 버튼 클릭
VM instance 상에서 HDD를 포맷하고 마운트 하는 방법
디스크 포맷
Google Cloud console의 SSH를 이용해서 VM에 접속해서 아래 명령어로 디스크 포맷과 마운트를 수행합니다.
예제에서는 sdb가 새 디스크의 device 이름입니다.
$ sudo lsblk
$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 10G 0 disk
├─sda1 8:1 0 9.9G 0 part /
├─sda14 8:14 0 3M 0 part
└─sda15 8:15 0 124M 0 part /boot/efi
sdb 8:16 0 10G 0 disk
mkfs 도구를 사용하여 디스크를 포맷합니다. 이 명령은 지정된 디스크의 모든 데이터를 삭제하므로 디스크 장치를 올바르게 지정했는지 확인합니다. 필요한 모든 파일 형식을 사용할 수 있지만 파티션 테이블이 없는 단일 ext4 파일 시스템을 권장합니다. 디스크 파티션을 수정하지 않고도 나중에 디스크 크기를 늘릴 수 있습니다.
디스크 성능을 최대화하려면 -E 플래그에서 권장되는 포맷 옵션을 사용합니다. 이 보조 디스크의 루트 볼륨에 대한 공간을 예약할 필요가 없으므로 사용 가능한 모든 디스크 공간을 사용기 위해 -m 0을 지정합니다.
DEVICE_NAME을 포맷하려는 디스크의 장치 이름으로 바꿉니다. 이 예에서는 sdb를 지정하여 파티션 테이블 없이 전체 디스크를 포맷합니다.
VM이 다시 시작될 때 디스크가 자동으로 다시 마운트되도록 디스크를 /etc/fstab 파일에 추가합니다. Linux 운영 체제에서 장치 이름은 재부팅할 때마다 변경될 수 있지만 장치 UUID는 시스템 간에 디스크를 이동할 때에도 항상 동일한 볼륨을 가리킵니다. 이 때문에 장치 이름 대신 장치 UUID를 사용하여 VM 다시 시작 시 자동 탑재를 구성하는 것이 좋습니다.
MOUNT_OPTION: 부팅 시 영역 영구 디스크를 마운트할 수 없는 경우 운영 체제가 수행하는 작업을 지정합니다. 유효한 값은 Linux fstab 설명서의 네 번째 필드를 참조하십시오. 디스크를 사용할 수 없는 경우에도 시스템이 부팅되도록 하려면 nofail 마운트 옵션을 사용합니다.
이 디스크를 분리하거나 이 VM의 부팅 디스크에서 스냅샷을 만드는 경우 /etc/fstab 파일을 편집하고 이 디스크에 대한 항목을 제거합니다. MOUNT_OPTION이 nofail 또는 nobootwait로 설정된 경우에도 /etc/fstab 파일을 VM에 연결된 장치와 동기화 상태로 유지하고 부팅 디스크 스냅샷을 생성하거나 디스크를 분리하기 전에 이러한 항목을 제거합니다.
"승인된 네트워크" 항목에서는 Cloud SQL에 접속할 수 있는 네트워크 대역을 설정합니다. 본 문서에서는 외부에서 접속을 가정하여 0.0.0.0/0 으로 설정했습니다. 공개 IP에 연결하려면 승인된 네트워크 또는 Cloud SQL 프록시를 사용해야 합니다.
유지보수 :
- 유지보수를 위한 업데이트되는 동안 인스턴스가 다시 시작되므로 서비스 중단이 발생할 수 있다. 이를 방지하기 위해서 유지보수 거부기간을 설정한다. 단 최대 90일 까지만 적용되므로 운영 단계에서는 주기적으로 설정해야 한다.
플래그 : MySQL DB 파라미터. 변경을 원하는 파라미터가 있을 경우 플래그 항목에서 설정
플래그 중 lower_case_table_names, default_time_zone, character_set_server 는 반드시 자신의 시스템에 맞도록 설정해야 합니다. character_set_server 는 utf8과 utf8mb4가 있는데 emoji 등 4byte 문자를 사용할 경우에는 utf8mb4를 지정하고 그렇지 않을 경우 공간의 효율을 위해 utf8로 설정합니다.
--> 승인된 네트워크 설정 시 subnet인 10.0.0.0/22 입력값이 오류가 발생. 0.0.0.0/0 을 입력하니 오류는 발생하지 않음.
7. 아래와 같이 GCP 에서 제공하는 Cloud SQL인 mysql 서버가 생성된 것을 확인할 수 있습니다.
Cloud SQL (MySQL) 접속 방법
Cloud SQL DB에 접속하는 방법으로 DB관리 툴, GCP Cloud shell, Data Studio로 연결하는 방법을 설명합니다. DB 접속 도구는 MySQL Workbench, Toad for MySQL, SQuirrel, DBeaver 등이 있습니다. 본 문서에서는 HeidiSQL 툴로 접속하는 방법을 설명합니다.
1. DB 관리 툴(HeidiSQL)로 연결
GCP 콘솔에 로그인한 다음 좌측 상단의 햄버거 메뉴를 클릭한 후 SQL 메뉴를 클릭한다.
위에서 생성한 Cloud SQL 인스턴스에 대한 정보가 아래와 같이 표시됩니다. 공개IP주소는 외부에서 DB로 연결 시 필요한 정보입니다. 추가로, 외부에서 접속하기 위한 네트워크 설정(inbound 설정)이 필요합니다.
네트워크 설정을 위해 인스턴스를 클릭한 후 좌측의 [연결] 메뉴를 클릭합니다. 우측에 나타나는 화면에서 [네트워크 추가]를 클릭합니다.
[네트워크 추가] 클릭 후 이름 항목에 원하는 이름을 입력하고(본 문서에서는 ALL 입력) 네트워크 항목에는 0.0.0.0/0 을 입력하고 [완료]를 클릭합니다. 최종 저장을 위해 하단의 [저장] 버튼을 클릭하여 변경된 사항을 저장합니다.
Google Cloud Console에서 Cloud SQL 인스턴스 페이지로 이동합니다. 좌측 햄버거 메뉴에서 "SQL" 을 클릭하던지 클라우드 콘솔 검색창에 sql을 입력하여 [제품 및 페이지]에서 SQL을 선택합니다.
인스턴스 만들기를 클릭합니다.
MySQL을 클릭합니다.
Compute API를 사용 설정하라는 메시지가 표시되면 API 사용 설정 버튼을 클릭합니다.
인스턴스 ID에 myinstance를 입력합니다.
루트 사용자의 비밀번호를 입력합니다.
다른 필드에는 기본값을 사용합니다.
만들기를 클릭합니다.참고: 이 예시에서는 공개 IP 주소를 포함한 기본 설정을 사용하여 인스턴스가 생성됩니다.
mysql 클라이언트 설치
본 문서에서는 Google VM instance OS인 Debian linux 에 설치합니다.
$ sudo apt-get install default-mysql-client
Cloud SQL 인증 프록시 클라이언트 설치
사용 중인 운영체제 및 운영체제가 사용하는 커널(32비트 또는 64비트)에 따라 다른 Cloud SQL 인증 프록시 바이너리를 다운로드합니다. 대부분의 최신 하드웨어는 64비트 커널을 사용합니다. 머신에서 32비트 커널을 실행 중인지 또는 64비트 커널을 실행 중인지 확실하지 않은 경우 Linux 또는 macOS에서 uname -a 명령어를 사용합니다. Windows의 경우 Windows 문서를 참조하세요.
본 문서에서는 GCP에 생성한 VM 인스턴스에 설치합니다.
sql proxy 서버로 사용할 새로운 VM instance를 생성하고 접속하여 아래 명령어를 수행합니다.
## wget 이 설치되어 있지 않을 경우 wget 설치
$ sudo apt install wget
$ wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
$ chmod +x cloud_sql_proxy
인스턴스 연결 이름 확인
Cloud SQL 인증 프록시에 접속할 인스턴스명을 확인하기 위해 Cloud SQL의 인스턴스 연결이름을 확인합니다.
본 문서에서는 advance-casing-340704:asia-northeast3:demo-mysql 이름을 사용합니다.
Google Cloud Console에서 Cloud SQL 인스턴스 페이지로 이동합니다.
인스턴스 이름을 클릭하여 개요 페이지를 엽니다.
인스턴스에 연결 섹션에서 연결 이름을 복사합니다. 연결 이름은 projectID:region:instanceID 형식입니다.
$ ./cloud_sql_proxy -instances=advance-casing-340704:asia-northeast3:demo-mysql=tcp:3306
2022/02/16 08:31:16 Rlimits for file descriptors set to {Current = 8500, Max = 1048576}
2022/02/16 08:31:17 errors parsing config:
googleapi: Error 403: Request had insufficient authentication scopes.
Details:
[
{
"@type": "type.googleapis.com/google.rpc.ErrorInfo",
"domain": "googleapis.com",
"metadata": {
"method": "google.cloud.sql.v1beta4.SqlConnectService.GetConnectSettings",
"service": "sqladmin.googleapis.com"
},
"reason": "ACCESS_TOKEN_SCOPE_INSUFFICIENT"
}
]
More details:
Reason: insufficientPermissions, Message: Insufficient Permission
위와 같이 Insufficient Permission 이 발생할 경우, VM 인스턴스를 중지한 후
Compute Engine > VM instance details > Identity and API access(api 및 ID 관리) 항목에서 서비스 계정의 액세스 범위를
"모든 Cloud API에 대한 전체 액세스 허용"으로 변경 후 인스턴스를 기동합니다.
또는 Identity and API access(api 및 ID 관리) 항목에서 "각 API에 액세스 설정" 선택 후 Cloud SQL 항목을 "사용 설정됨"으로 선택합니다.
다시 접속을 시도해 보니 아래와 같은 에러가 발생했습니다.
googleapi: Error 403: The client is not authorized to make this request., notAuthorized
두 번째 오류 처리를 위해 아래 명령어를 수행하고 다시 접속시도를 했으나, 동일한 오류가 발생했습니다.
아래 명령어를 수행하면 url 접속 정보가 뜨는데 브라우저로 접속하면 구글 접속계정이 화면에 표시되고 계정을 클릭하면 인증코드가 발행됩니다. 이 인증코드를 아래 화면에 입력하면 인증파일이 생성됩니다.
$ gcloud auth application-default login
You are running on a Google Compute Engine virtual machine.
The service credentials associated with this virtual machine
will automatically be used by Application Default
Credentials, so it is not necessary to use this command.
... ...
Do you want to continue (Y/n)? Y
Go to the following link in your browser:
https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=764086051850-6qr4p6gpi6hn506pt8ejuq83di341hur.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&scope=openid+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fuserinfo.email+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fcloud-platform+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Faccounts.reauth&state=jyPBenHBlKW9ZGmL090wTmjX5w63fI&prompt=consent&access_type=offline&code_challenge=VCXpHUS9G_Y0yEWPDuHZpgFhQsNV3UKBOoOSfuG5Qdc&code_challenge_method=S256
Enter verification code:
프록시를 사용하여 연결할 때는 프록시가 구글 클라우드 플랫폼으로 인증해야 합니다. Cloud SDK 사용자 인증 정보를 사용하거나, 생성한 서비스 계정의 로컬 키 파일 경로를 프록시에 제공할 수 있습니다.
추가로, SQL 클라이언트의 모든 송신 port가 deny 되어 있는 경우, SQL proxy를 사용하려면 3307 port가 오픈되어 있어야 합니다.
참고:Cloud SQL Auth 프록시는 3307을 사용하여 Cloud SQL Auth 프록시 서버에 연결합니다. 포트 3306은 직접 TCP 연결의 MySQL 프로토콜에 대한 기본 포트입니다(Cloud SQL 인증 프록시 사용 안 함)
Listening on 127.0.0.1:3306 for INSTANCE_CONNECTION_NAME
Ready for new connections
만일, 프록시 서버의 IP 또는 프록시 서버의 local IP로 Cloud SQL DB 접속이 가능하게 하려면, 아래처럼 instances에 나열해 주면 됩니다. 마지막의 '&' 문자는 백그라운드로 실행하라는 명령문입니다. foreground로 실행하려면 '&' 문자를 없애고 실행하면 됩니다.
