[Cloud Skills Boost] Migrating MySQL data to Cloud SQL using Database Migration Service ④

< Lab >

---

Migrating MySQL User Data When Running Database Migration Service Jobs

데이터베이스 마이그레이션 서비스 작업을 실행할 때 MySQL 사용자 데이터 마이그레이션

 

• Google Cloud Shell 활성화

1. 클라우드 셸을 열고 PROJECT_ID 설정 확인
   
2. 활성 계정 이름 나열
   

   gcloud auth list​

3. 프로젝트 ID 나열
   

   gcloud config list project​

• 데이터베이스 마이그레이션 API가 활성화되어 있는지 확인

1. 콘솔에서 Database Migration API 입력 후 활성화

• 서비스 네트워킹 API가 활성화되어 있는지 확인

1. 콘솔에서 Service Networking API 입력 후 활성화

• 소스 인스턴스에서 기존 MySQL 사용자 식별

1. MySQL 소스 인스턴스에 연결 :
   Compute Engine - VM 인스턴스에서 dms-mysql-trainning-vm 인스턴스를 찾은 후 SSH를 클릭
   MySQL 대화형 콘솔에 연결 및 암호 입력
   
   

   

   mysql -u admin -p
   
   changeme!

2. 기존 MySQL 사용자 식별 :
   기존 MySQL 사용자 식별을 하여 debian-sys-maint , mysql.session 및 mysql.sys 라는 시스템 사용자를 확인
   

   select host, user, authentication_string from mysql.user
   order by user;​

   
   

   

   수정된 쿼리를 실행해 시스템 사용자를 제외
   

   select host, user, authentication_string from mysql.user
   where user not like '%mysql%' and user not like '%debian%'
   order by user;​

   
   

   

   대상 Cloud SQL 인스턴스에서 다시 만들어야하는 사용자 목록 검토
   admin 및 root 를 제외한 모든 사용자는 localhost에만 액세스할 수 있으며
   새 데이터베이스 마이그레이션 서비스 작업을 생성할 때 루트 사용자를 생성

• MySQL 소스 인스턴스에서 DEFINER를 INVOKER로 업데이트
  (Destination 인스턴스에 아직 존재하지 않는 루트 또는
  다른 사용자에 대한 DEFINER 항목이 있는 데이터베이스 개체를 식별하고 업데이트,
  Destination 인스턴스에 사용자를 만들지 않았거나 만들지 않을 경우
  해당 사용자와 연결된 모든 DEFINER 항목을 INVOKER로 업데이트해야 하므로
  루트에 대한 모든 DEFINER 항목은 admin과 같은 다른 사용자를 사용하여 INVOKER로 업데이트)
  +) SQL SECURITY DEFINER : 생성한 user의 권한을 따름 / SQL SECURITY INVOKER : 실행한 user의 권한을 따름

1. DEFINER 항목으로 개체 식별 :
   쿼리를 실행하여 DEFINER 항목을 식별
   결과는 DEFINER 항목에 대한 이벤트, 루틴, 트리거 및 보기를 확인해야 함을 나타냄
   

   select table_schema, table_name from information_schema.columns
   where column_name = 'DEFINER'
   and table_schema != 'mysql';

   
   

   

   시스템 사용자는 데이터베이스 개체에 대한 DEFINER 항목을 식별하기 위한 쿼리에서 제외

   쿼리를 실행하여 이벤트에서 DEFINER를 식별

   select definer, event_schema, event_name from information_schema.events
   where definer not like '%mysql%' and definer not like '%debian%';

   
   

   

   비시스템 사용자와 관련된 이벤트에는 DEFINER의 인스턴스가 없음

   쿼리를 실행하여 루틴에서 DEFINER를 식별
   

   select definer, routine_schema, routine_name from information_schema.routines
   where definer not like '%mysql%' and definer not like '%debian%';

   
   

   

   비시스템 사용자와 연관된 루틴에는 DEFINER 인스턴스가 없음

   쿼리를 실행하여 트리거에서 DEFINER를 식별
   

   select definer, trigger_schema, trigger_name from information_schema.triggers
   where definer not like '%mysql%' and definer not like '%debian%';

   
   

   

   비시스템 사용자와 관련된 트리거에는 DEFINER 인스턴스가 없음

   쿼리를 실행하여 보기에서 DEFINER를 식별
   mhill과 연결된  invokes_storenum_3656 뷰에 대한 세부정보를 검토
   mhill 사용자는 destination instance에서 생성되지 않았으므로
   DEFINER를 INVOKER로 업데이트하여 대상 인스턴스에서 성공적으로 실행되도록 할 것
   admin, bsmith, mhill과 달리 이전에 식별된 사용자 중 한 명인 dwilliams는 뷰의 DEFINER 항목과 연결되지 않았고 
   또한 루트 사용자 에 대한 DEFINER 인스턴스가 없으므로 dwilliams 또는 root 에 대해 조치가 필요하지 않음
   

   select definer, security_type, table_schema, table_name from information_schema.views
   where definer not like '%mysql%' and definer not like '%debian%'
   order by definer;

   
   

   

   mhill과 연결된 invokes_storenum_3656 뷰에 대한 세부정보를 검토

2. DEFINER를 INVOKER로 업데이트 :
   이 사용자는 Destination 인스턴스에서 생성되지 않기 때문에
   이 하위 작업에서 mhill 과 연결된 DEFINER 항목을 INVOKER로 업데이트
   마이그레이션 작업을 실행하기 전에 나중 작업에서 Destination 인스턴스에 이러한 사용자를 생성할 것이기 때문에 
   admin 및 bsmith 에 대한 DEFINER 항목은 그대로 둠
   mhill과 연결된 보기(views)의 세부 정보를 확인
   

   select definer, security_type, table_schema, view_definition from information_schema.views
   where table_name = 'invoices_storenum_3656';

   뷰와 연결된 데이터베이스 선택 후 DEFINER를 INVOKER로 업데이트 후 업데이트되었는지 확인
   

   alter sql security INVOKER view invoices_storenum_3656 as
   (select * from invoices where storeNum = 3656);
   
   select definer, security_type, table_schema, table_name from information_schema.views
   where definer not like '%mysql%' and definer not like '%debian%'
   order by definer;

   
   

   

   admin 및 bsmith에 대한 DEFINER 항목은 그대로 둠

   MySQL 대화형 콘솔와 터미널 세션 종료
   

   exit
   exit​

• 작업을 시작하지 않고 데이터베이스 마이그레이션 서비스 작업 생성 및 저장

1. MySQL 소스 인스턴스에 대한 연결 정보 가져오기 :
   Compute Enigne - VM 인스턴스 - dms-mysql-training-vm 인스턴스를 찾아 내부 IP 주소 값 복사
   
   

   

   +) 내부 IP 주소 : 10.128.0.2
2. MySQL 소스 인스턴스에 대한 새 연결 프로필 생성 :
   콘솔창에 Database Migration - Connection profiles - 프로필 만들기 클릭 - MySQL 선택 후 정보 입력
   연결 프로필 이름 : mysql-vm
   연결 프로필 ID : 자동 생성된 값 유지
   호스트 이름 또는 IP 주소 : 10.128.0.2
   포트 : 3306
   사용자 이름 : admin
   비밀번호 : changeme!
   암호화 유형 : None
   mysql-vm이라는 새 연결 프로필 생성 확인
   
   

   

3. 새 일회성 마이그레이션 작업 만들기 (Get started) :
   콘솔에서 Database Migration - Migration jobs - 마이그레이션 작업 만들기 - 정보 입력
   마이그레이션 작업 이름 : vm-to-cloudsql
   마이그레이션 작업 ID : 자동 생성된 값 유지
   소스 데이터베이스 엔진 : MySQL
   마이그레이션 작업 유형 : One-time
4. 소스 인스턴스 정의 (Define a source) :
   소스 연결 프로필로  mysql-vm 선택 
   
5. 대상 인스턴스 만들기 (Create a destination) :
   Cloud SQL에서 대상 인스턴스를 만드는데 필요한 정보 입력
   대상 인스턴스 ID : mysql-cloudsql
   루트 비밀번호 : supersecret!
   데이터베이스 버전 : Cloud SQL for MySQL 5.7
   존 : Any
   연결 : Private IP and Public IP 선택 후 자동 할당된 IP 범위 사용
   머신 유형 : db-n1-standard-1
   저장 유형 : SSD
   저장 용량 : 10
6. 연결 방법 정의 (Define connectivity method) :
   VPC Peering 선택 - default 선택
   
7. 지속적인 마이그레이션 작업 테스트 및 시작 :
   마이그레이션 세부 정보 검토 후 작업 만들기 및 시작 클릭
   이 때 마이그레이션 작업을 테스트하지 않았으므로 작업이 시작된 후 작업이 실패할 수 있다는 메시지를 받을 수 있으며
   대상 인스턴스( admin 및 bsmith ) 에서 아직 생성되지 않은 사용자와 연결된 DEFINER 항목이 있기 때문에
   지금 작업을 테스트하면 오류 메시지가 나타날 수 있음
   
   

   

   마이그레이션 작업이 생성되었지만 시작되지 않고 Cloud SQL 대상 인스턴스에서 새 사용자를 만든 후 나중 작업에서 작업을 시작

• Cloud SQL 대상 인스턴스에 필요한 사용자 생성

1. 콘솔에서 SQL 클릭 후 mysql-cloudsql-master 인스턴스를 확장하고 mysql-cloudsql 인스턴스 ID 클릭
   - Replica 인스턴스 메뉴에서 사용자 클릭
   
   

   

2. localhost 액세스 권한이 있는 사용자 생성 :
   bsmith 는 소스 인스턴스에서 localhost를 통해 연결하는 MySQL 사용자이므로 이 사용자를 localhost 액세스로만 제한
   
   

   

   
   
3. 모든 호스트에서 액세스할 수 있는 사용자 생성 :
   
   

   

• 이전에 생성된 마이그레이션 작업 실행

1. 콘솔에서 Database Migration - Migration jobs - vm-to-cloudsql을 클릭한 후 시작 버튼을 클릭해 마이그레이션 작업 실행
2. 마이그레이션 작업 상태 검토
   
   

   

   마이그레이션 작업이 성공적으로 완료

• MySQL용 Cloud SQL에서 사용자 메타데이터 확인

1. MySQL 인스턴스에 연결 :
   콘솔에서 SQL 클릭 후 mysql-cloudsql 인스턴스 ID 클릭 - 기본 인스턴스 메뉴에서 Overview 클릭
   - 이 인스턴스에 연결 패널에서 클라우드 셸 열기 버튼 클릭 후 암호를 입력해 MySQL 대화형 콘솔 활성화
   

   gcloud sql connect mysql-cloudsql --user=root --quiet
   
   supersecret!

2. MySQL용 Cloud SQL 인스턴스에서 사용자 메타데이터 검토 :
   시스템 사용자가 아닌 사용자를 보기 위해 쿼리 실행
   Cloud SQL에서 이 사용자를 생성하지 않았기 때문에 mhill은 사용자로 나열되지 않음
   

   select host, user, authentication_string from mysql.user
   where user not like '%mysql%' and user not like '%debian%'
   order by user;

   
   

   

   bsmitch와 admin 사용자만 생성

   사용자 메타데이터가 성공적으로 마이그레이션되었는지 확인하기 위해 쿼리 실행
   DEFINER 항목을 검토하여 Cloud SQL 에서 사용자 mhill 을 생성하지 않은 경우에도
   invokes_storenum_3656의 메타데이터가 성공적으로 마이그레이션되었는지 확인
   

   select definer, security_type, table_schema, table_name from information_schema.views
   where definer not like '%mysql%' and definer not like '%debian%'
   order by definer;​

   
   

   

3. MySQL 대화형 콘솔에서 데이터베이스를 선택한 후 mhill과 연결된 뷰를 쿼리하여 성공적으로 실행되는지 확인
   데이터가 마이그레이션되기 전에 이 보기에 대해 DEFINER를 INVOKER로 업데이트했기 때문에
   이 보기가 대상 인스턴스에서 성공적으로 실행 되도록 사용자 mhill 을 생성할 필요가 없음

   use sales_data;
   
   select * from invoices_storenum_3656;

   
   

   

   MySQL 대화형 콘솔 종료
   

   exit