1. [MySQL Connection] 창 ▷ Workbench 실행 ▷ [MySQL Connections] 창 ▶ 접속될 서버와 사용자, 포트를 선택한 후 접속, MySQL에 등록된 사용자만 접속 가능 ▶ 서버 등록시 여러개 등록 가능, Connection Name : 접속하는 이름
2. [Connection] 탭 ▷ Connection Method ▶ Standard(TCP/IP), Local Socket/Pipe, Standard TCP/IP over SSH, MySQL Fabric Management Node 등 4가지 중에 선택 가능 ▶ 대부분 Standard(TCP/IP) 사용
3. [Parameters] 탭 ▷ Hostname ▶ localhost = 127.0.0.1 = 자신이 컴퓨터(MySQL이 설치된 컴퓨터) ▶ 접속할 컴퓨터가 외부에 있다면 접속할 서버 컴퓨터의 IP주소 입력 ▷ Port ▶ 접속할 MySQL 포트 번호, 특별한 경우가 아니면 3306 ▷ Username, Password, Default Schema 입력
4. [SSL] 탭 ▷ SSL (Secure Socekt Layer) ▶ 보안을 위한 암호 규약, 서버와 클라이언트 통신시 암호화 통해 비밀 유지 & 보안 강화 5. [Advanced] 탭 ▷ 프로토콜의 압축, 인증 방식 등을 설정 5. [Remote Management] 탭 ▷ 원격 관리 위해 설정하는 부분 ▷ 이 부분이 활성화 되기 위해서는 Hostname이 실제 IP 주소 ▷ 'Native Windows remote management' 선택 ▶ MySQL 서버가 설치된 OS가 Windows인 경우에만 설정 가능 ▷ 'SSH login based management'는 SSH 서버 기반으로 원격 접속
6. [System Profile] 탭 ▷ 접속할 서버의 OS 종류 및 MySQL 설정 파일의 경로 등을 설정 ▷ [Remote Management]에서 'Native Windows remote management'나 'SSH login based management'가 선택되어 있어야 활성화 ▷ [System Type]은 FreeBSD, Linux, MacOS X, OpenSolaris, Windows 등 5가지 중 선택 ▶ [Installation Type]을 선택 가능 ▷ [Configuration File] ▶ MySQL의 설정 파일이 경로와 함께 지정 ▷ [Configuration File Section] ▶ 서버의 서비스 이름 지정 ▷ [MySQL Management] ▶ MySQL 서비스를 시작하거나 중지하는 시스템 명령어
◎ MySQL Workbench의 화면 구성
▷ 3개의 패널과 쿼리 창으로 구성
▷ 내비게이터, Output, SQL Additions
◎내비게이터(Navigator)
▷ MySQL의 관리 및 운영을 위한 강력한 도구 ▷ MySQL 명령문이나 SQL문을 모르더라도 대부분의 작업 수행 가능 ▷ 내비게이터의 역할 ▶ [Schemas] 탭 → 데이터베이스(=스키마) 생성 및 삭제 → 데이터베이스 개체(테이블, 뷰, 인덱스, 저장 프로시저, 함수 등)를 생성하고 관리 → 데이터베이스의 속성 조회 ▶ [Management] 탭 → MANAGEMENT → INSTANCE → PERFORMANCE
▷ [Navigator]의 [Schemas]는 트리 형태 ▷ 각각의 항목은 '▶'를 이용해 확장 가능
◎ 내비게이터의 [Schemas] 탭 이용해 SQL문 자동 생성 ▷ 테이블 생성 이외에도 뷰와 다른 구문을 다룰 수 있습니다. ▷ 테이블 선택 → Send to SQL Editor → Create Statement
◎ 네비게이터의 [Administration] 탭 이용해 MySQL 관리
1. [MANAGEMENT 부분] ▷ [Server Status] ▶ 현재 접속된 서버의 상태 파악 가능 ▶ 현재 서버의 가동 상태, 포트, 환경 파일의 경로, 메모리 상태, CPU 사용 상태 확인 가능 ▷ [Client Connections] ▶ 연결된 클라이언트의 현재 상태가 휴면(Sleep)인지 여부 확인 ▶ 해당 연결에서 마우스 오른쪽 버튼을 클릭, [Kill Connection(s)]로 연결을 강제로 끊을 수 있습니다. ▷ [Users and Privileges]에서 MySQL 사용자 관리 ▷ [Status and System Variables] ▶ MySQL 서버에 설정된 시스템 변수들 확인 / 변경 ▷ [Data Export] 및 [Data Import/Restore] ▶ 백업 및 복원과 관련된 부분 2. [INSTANCE] 부분 ▷ [Startup/Shutdown] ▶ MySQL 서버의 현재 작동 상태 확인 ▶ MySQL 서버의 중지와 시작 설정 ▷ [Server Logs] ▶ 서버에 기록된 오류, 경고, 방화벽 등의 로그 확인 ▷ [Options File] ▶ MySQL의 핵심 설정 파일인 my.ini 파일 ▶ 파일 설정 내용을 GUI 모드로 보여줌
3. [PERFORMANCE] 부분 ▷ [Dashboard] ▶ 네트워크, MySQL 서버, InnoDB의 상태를 그래픽으로 보여줌
▷ [Performance Reports] ▶ 입출력이 오래 걸린 파일, 비용이 많이 든 쿼리문, 데이터베이스 통계 등의 항목들 조회 ▶ 결과 내보내기 가능 ▷ [Performance Schema Setup] ▶ 성능에 대한 설정 ▶ 오른쪽 위 <Show Advanced>나 <Hide Advanced> 클릭하면 세부적인 설정 확인 가능
◎ 쿼리 창(Query Editor)
▷ 쿼리 문장(SQL 구문)을 입력/실행하는 텍스트 에디터
▷ 쿼리 창 사용 방법
▶ Workbench의 상단 제일 왼쪽의 <Create a new SQL tab for executing queries> 아이콘 클릭 또는
▶ SQL 구문에 이상이 없다면 툴바의 <Execute the selected portion~~> 아이콘을 클릭하거나 Ctrl + Shift + Enter 눌러서 SQL 문장 실행
▶ 아래쪽의 결과 창을 통해 결과 확인
→ 성공된 결과 또는 오류 메시지 확인
▷ 한번 연 쿼리 창은 계속해서 SQL 입력해 사용 가능
◎ Workbench의 편리한 기능
1. 쿼리 창에서 개체 드래그해서 자동 완성 기능
2. 예약어 대문자나 소문자로 변경하기 ▷ [Edit] → [Format] 활용 ▶ UPCASE Keywords 선택 - 대문자로 변경 ▶ lowercase Keywords 선택 - 소문자로 변경
3. SQL 코드나 설명의 주석처리 방법 ▷ 주석 처리할 부분 드래그 후 [Edit] → [Format] → [Un/Comment Selection] 선택 ▷ 한줄은 '--', 여러줄은 '/* */'로 주석 처리 가능
4. 여러 개의 SQL문 실행 가능 ▷ 모든 SQL문을 실행하지 않는다면 일부만 드래그 선택해 실행
5. 결과를 다양한 방식으로 필터링하거나 파일 형태 저장 가능
6. 실행되는 SQL문 실행 계획 확인 가능 ▷ 결과 창의 오른쪽 제일 아래로 내려서 [Execution Plan]을 클릭 ▷ 결과의 그림에 마우스를 올려놓으면 상세한 내용도 확인 가능
2. 외부 MySQL 서버 관리하기
◎ 네트워크 환경 비교
▷ Windows 1대에 서버와 클라이언트가 모두 설치되된 상태
▷ Linux 설치된 MySQL Server에 Windows에 설치된 Workbench가 접속된 상태
◎ Workbench로 Linux MySQL 서버에 접속한 경우 주의해야 할 점
▷ Linux 컴퓨터 방화벽 설정
▶ MySQL의 포트인 3306번을 허용하도록 설정해야 함
▷ Linux 컴퓨터의 IP주소를 알고 있어야함
▷ Windows 컴퓨터의 Workbench에서 Linux 컴퓨터로 연결고리 만들어 놓아야 합니다.
3. 사용자 관리하기
◎ DB 사용자 관리의 필요성
▷ 현재까지 사용 방법
▶ MySQL 관리자인 root로 접속해 사용
▷ 실무에서의 문제
▶ MySQL 데이터베이스를 다양한 사용자나 응용프로그램에서 접속해 사용함
▶ 모든 사용자가 관리자로 접속을 한다면 데이터가 유출되거나 증발하는 끔찍한 일이 일어날 수 있음
◎ MySQL의 사용자 및 역할/권한 관리 실습
1. 팀장님 (Director) ▷ 데이터베이스 관리자(DBA)의 역할 부여 ▷ Workbench 실행하고 [Local instance MySQL]을 클릭해서 접속 ▶ 사용자를 생성하는 권한은 root에게만 있음 ▷ [Navigator]의 [Administration] 탭 → [Users and Privileges] 클릭
▷ [Users and Privileges] 창에서 오니쪽 아래 <Add Acount> 클릭 후 [Login] 탭의 [Login Name]에 'director' 입력 ▶ 비밀번호 입력하고 <Apply> 클릭 → director 사용자 등록 확인 ▷ [Acoount Limits] 탭으로 쿼리 한계 설정 → 0은 제한 없음 ▷ [Administrative Roles] 에서는 MySQL 자체에 대한 권한 설정 ▶ [Role]의 <DBA> 체크하여 모든 권한 설정 적용
2. 사장님(CEO) ▷ MySQL의 모든 데이터에 읽기 (Select) 권한 부여 ▷ 계정 등록방법은 '팀장님'의 경우와 같음 ▷ [Administrative Roles] 탭 클릭 ▶ 사장님은 MySQL의 읽기(Select)로 계획되어 있음 ▶ [Global Privileges] 중에서 <SELECT> 체크 ▶ 왼쪽 Role 중에 <Custom>이 자동으로 체크 3. 일반 직원(staff) ▷ ShopDB 데이터베이스의 모든 테이블에 대해 읽기(Select), 쓰기(Insert, Update, Delecte) 권한 부여 ▷ 스토어드 프로시저 등을 생성(Create Routine)하고 수정(Alter Routine)할 수 있는 권한 부여
▷ employees 데이터베이스의 테이블에 대해서는 읽기(Select) 권한 부여 ▷ 각 사용자별 권한 확인
◎ CMD창 코드
1. 팀장(director) : 관리자 DBA 권한 ▷ TUI ▷ CREATE USER 사용자명@ '접속할 IP 대역, IP' IDENTIFIED BY '패스워드'; ▷ CREATE USER director@ '%' IDENTIFIED BY 'director'; ◎권한 부여 ▷ GRANT 권한 ON 데이터베이스.테이블 TO director@'%' WITH 권한 설정; ▷ GRANT ALL ON *.* TO director@'%' WITH GRANT OPTOION;
2. 사장님(CEO) : 읽기만 가능 ▷ CREATE USER ceo@'%' IDENTIFIED BY 'ceo'; ▷ GRANT SELECT ON *.* TO ceo@'%';
3. 일반직원(Staff) : 특정 DB만 들어가고 shopdb(읽기/쓰기), employees(읽기) 설정 ▷ CREATE USER staff@'%' IDENTIFIED BY 'staff'; ▷ GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO staff@'%'; ▷ GRANT SELECT ON employees.* TO staff@'%';
