SQLyog 사용법

이글에서는 SQLyog Community Edition의 사용법을 알아봅니다. 설치 및 데이터베이스 연결설정은 "SQLyog - MySQL 관리툴 설치하기"를 참조 하세요. SQLyog는 MySQL, MariaDB 모두에서 동일하게 사용할 수 있습니다. 실제 이 글을 쓰면서 테스트는 MariaDB에서 하였습니다.

 

 

0. 화면 구성

 

 

 

SQLyog 의 화면구성은 상단에 메뉴 영역, 그 아래에 툴바가 있고, 그 아래에 현재 연결된 서버 이름이 보이는 탭이 있습니다. 여러개의 서버에 연결을 하여 사용중이라면 이 탭이 연결된 서버 만큼 증가할 것입니다. 그 아래에는 하나의 연결된 서버에 대한 작업을 할 수 있는 세군데 영역으로나뉘는데 좌측에는 트리 구조로 데이터베이스 정보들이 보여집니다. 우측 상단에는 쿼리를 입력해서 실행할 수 있는 쿼리 창이 있습니다. 쿼리창은 여러개를 열어서 사용할 수 있습니다.

 

우측 하단에는 기본적으로 세가지 탭으로 구성되어 있는 메세지 영역이 있습니다.

 

- Messages 탭에서는 쿼리 실행시 성공/실패 실행시간 등 메세지를 보여줍니다.

- Table Data 탭은 좌측 트리에서 테이블을 선택하면 테이블 데이터를 보여줍니다.
- Info 탭은 좌측 트리에서 객체를 선택했을때 선택된 객체의 정보를 보여줍니다.

 

이외에도 조회결과를 보여주는 Result 탭들이 작업에 따라 추가적으로 나타납니다.

이제 각각의 툴바들에 대해서 알아보겠습니다. 다음 14가지가 Community Edition에서 사용할 수 있는 기능 입니다. 나머지는 유료버전에서 사용 가능합니다.

 

㉮ 새 연결을 만듭니다.(Ctrl + m)
㉯ 새 쿼리 에디터를 엽니다.(Ctrl + t)
㉰ 커서가 위치한 곳의 쿼리 하나를 실행합니다.(F9)
㉱ 모든 쿼리를 실행합니다.(Alt + F9)
㉲ 쿼리를 실행하고, 결과를 바로 수정 가능 상태로 변경합니다.(F8)
㉳ 객체 브라우저를 새로고침합니다.(F5)
㉴ 현재 작업 DB를 선택합니다.
㉵ 사용자 관리자를 실행합니다.(Ctrl + U)
㉶ 데이터베이스를 SQL Dump로 백업 합니다.(Ctrl + Alt + e)
㉷ SQL 스크립트를 실행합니다.(Ctrl + Shift + q)
㉸ 데이터베이스를 다른 호스트 또는 데이터베이스로 복사합니다.
㉹ 테이블 데이터를 export 합니다.(Ctrl + Alt + c)
㉺ 인덱스를 관리합니다.(F7)
㉻ 외래키를 관리합니다.(F10)

 

 

1. 데이터베이스 생성 및 수정
1.1 데이터베이스 생성

데이터베이스를 생성하기 위해서는 메뉴에서 Database -> Create Database... 를 선택하거나, 좌측 트리에서 마우스 오른쪽 키 를 누르고 팝업 메뉴에서 Create Database... 를 선택합니다. 단축키는 Ctrl + d 입니다. 다음 각 항목을 입력하고 Create 버튼을 누르면 데이터베이스가 생성됩니다.

 

 

 

Database name      : 데이터베이스 이름을 입력합니다.
Database charset   : 문자셋을 선택합니다. [default]로 두면 서버 설치시 지정된 기본값이 사용됩니다.
Database collation : Collation을 선택합니다. [default]로 두면 서버 설치시 지정된 기본값이 사용됩니다.

 

1.2 데이터베이스 수정

데이터베이스를 수정하기 위해서는 좌측 트리 또는 툴바의 현재 데이터베이스에서 수정할 데이터베이스를 선택합니다. 메뉴에서 Database -> Alter Database... 를 선택, 또는 좌측 트리에서 DB에 마우스 오른쪽 키를 누르면 뜨는 팝업창에서 Alter Database... 를 선택합니다. 단축키는 F6 입니다. 데이터베이스 생성때와 같은 형태의 창이 뜨게 됩니다. 변경하려는 정보를 수정한 다음 Alter 버튼을 누르면 수정됩니다.

 

1.3 데이터베이스 삭제

- 작업할 데이터베이스를 선택합니다.

- Database 메뉴 또는 좌측 트리에서 마우스 오른쪽키를 누르고, More Database Operations -> Drop Database... 를 선택합니다. Confirm 창에서 예(Y) 를 누르면 삭제 됩니다.

- More Database Operations -> Truncate Database... 는 모든 테이블의 데이터를 삭제 합니다.
- More Database Operations -> Empty Database.. 는 데이터베이스내의 객체들을 삭제 할 수 있습니다. 모든 객체를 삭제하면 처음 데이터베이스를 생성했을 때와 같은 상태가 됩니다.

 

백업이 없으면 복구할 수 없으므로 데이터베이스, 테이블 등의 삭제는 신중하게 생각해서 작업해야 할 것입니다.

 

2. 테이블 관리
2.1 테이블 생성

테이블의 생성을 위해서 먼저 생성할 테이블이 포함될 데이터베이스를 선택합니다. 메뉴에서 Database -> Create -> Table 을 선택 또는 좌측 트리상의 데이터베이스에 마우스 오른쪽 키를 누르고, Create -> Table 을 선택합니다.

 

 

 

- Table Name, Engine, Character Set, Collation 을 입력하고, 테이블의 컬럼 정보를 입력합니다.

- Columns 탭의 +, -, 위쪽 화살표, 아래쪽 화살표 버튼으로 열을 추가, 제거, 위치 변경을 할 수 있습니다.

- 나머지 탭들을 이용하여 인덱스 생성, 외래키 생성,  데이틀에 대한 추가 정보, 실제 실행될 테이블 생성 쿼리를 확인할  수 있습니다.

- 모든 정보가 입력되었다면 "Save" 버튼을 눌러 테이블을 생성합니다.

 

 

2.2 테이블 수정

수정하기를 원하는 테이블을 좌측 트리에서 선택합니다. 메뉴에서 Table -> Alter Talbe을 선택하거나 마우스 오른쪽 키로 Alter Table 을 선택합니다. 테이블 생성시와 같은 화면에 보여집니다. 모든 수정이 완료되었다면 "Save" 버튼을 눌러 수정합니다.

 

2.3 Talbe 메뉴의 More Table Operations

  i. Rename Table : 테이블 이름을 변경합니다.
 ii. Truncate Table... : 테이블 데이터를 삭제 합니다.
iii. Drop Table From Database... : 테이블을 삭제 합니다.
 iv. Reorder Column(s) : 컬럼 순서를 변경합니다.
  v. Duplicate Table Structure/Data... : 테이블을 복사 합니다.
 vi. View Table Properties : 테이블 정보를 상세히 보여줍니다.
vii. Change Table Type To : 테이블 타입을 변경합니다.(MyISAM, InnoDB 등 을 변경합니다.)

 

3. 테이블 데이터 관리

쿼리를 사용하지 않고 GUI 환경에서 테이블 데이터를 조회/수정/삭제 할 수 있습니다. 단일 테이블에 대해서 작업을 합니다. 좌측 트리에서 테이블을 선택하고, 우측 하단의 Table Data 탭에서 조회된 데이트를 확인할 수 있습니다.

 

 

 

- 수정 : 그리드에 조회된 내용을 바로 수정할 수 있습니다. 수정후에는 상단 아이콘중 다섯번째인 디스켓 모양의 버튼을 눌러서 저장합니다.(이런 모양의 디스켓을 사용해본게 언젠지 기억도 안나는군요. SD 카드 모양이라고 하면 이해가 더 빠를수도 있겠습니다.)

- 삭제 : 행의 제일 앞에 있는 체크박스를 체크하고, 상단의 쓰레기통 모양의 버튼을 누르면 삭제됩니다.

- 입력 : 그리드의 제일 하단의 체크박스 부분이 * 로 되어 있는 행이 데이터를 입력할 수 있는 행입니다. 타입에 맞는 데이터를 입력하면 됩니다. text 타입의 데이터일 경우 다음과 같은 팝업 창이 뜹니다.

 

 

 

Null 허용일 경우 Set Null 을 체크하면 null 이 입력되고, 값을 입력하려면 Set Null 체크박스를 해제 해야 합니다.

상단 아이콘에 대해서 순서대로 알아보겠습니다.

 

i. 테이블 데이터를 export 할 수 있습니다. csv, json, html, xml, Excel XML, SQL 등 다양한 형태로 데이터를 파일로 저장할 수 있습니다.
ii. 그리드에 조회된 데이터를 복사를 할 수 있습니다. 많은 데이터가 아니라면 간단하게 복사해서 파일에 붙여 넣던가 할 수 있습니다.
iii. 새 행을 추가합니다. 하단의 입력 위치를 사용하는 것이랑 동일합니다.
iv. 현재행을 복사합니다.
v. 변경사항을 저장합니다.
vi. 선택된 행을 삭제 합니다.
vii. 현재 변경사항을 취소 합니다. 저장하기 버튼을 눌러 저장한것은 취소 되지 않습니다. 저장전에 수정한 사항을 취소 할 수 있습니다.
viii. 그리드 형태로 데이터를 봅니다.
ix. 폼 형태로 데이터를 보는데, 유료 기능 입니다.
x. 텍스트 형태로 데이터를 볼 수 있습니다.
xi. 깔때기 모양 아이콘으로 데이터 조회에 조건을 걸 수 있습니다.
xii. 새로고침 버튼 입니다.
xiii. Limit rows 는 한번에 조회되는 행의 수를 제한 합니다. 기본 1000개로 제한 되어 있습니다. 값을 변경하거나 체크를 해제해서 제한을 없앨 수 있습니다.

 

 

4. 데이터 백업 및 스크립트 실행
4.1 데이터베이스 덤프

 

메뉴에서 Database -> Backup/Export -> Backup Database As SQL Dump... 를 선택합니다.

 

 

 

데이터베이스 구조만, 데이터만, 구조와 데이터 모두 export 하는 옵션이 있습니다. 필요한 항목을 선택합니다. 좌측의 Objects 에서는 export 하고자하는 object 를 선택합니다.

 

오른쪽 옵션들의 설명은 너무 많으므로 일단 생략하겠습니다. 한가지만 예기하자면 Create Bulk INSERT statement(s) 항목 입니다. 이부분을 체크하면 한 테이블에 입력하는 데이터의 쿼리가 하나로 만들어 집니다. 데이터의 양이 많을 때 빠르게 입력할 수 있습니다. 하지만 MySQL 데이터베이스의 max_allowed_packet 사이즈가 작으면 packet for query is too large 에러가 발생하고 입력이 되지 않습니다.

 

MySQL 의 패킷 사이즈를 키울 권한이 있다면 늘리고 작업하는게 작업속도를 올릴 수 있는 방법이 될 것입니다. 권한이 없다면  Create Bulk INSERT Statement 의 체크를 해제하고 백업을 만들면 행당 하나의 입력 쿼리가 생성됩니다. 이 경우 데이터가 많으면 입력시간이 오래 걸릴 수 있습니다.

 

 

4.2 테이블 덤프

메뉴에서 Table -> Backup/Export -> Backup Table As SQL Dump... 또는 Export Table Data As.. 를 선택합니다. SQL 입력 쿼리로 백업하려면 Backup Table As SQL Dump.. 를 선택하고 CSV, JSON, HTML, XML, Excel XML 형태로 백업 하려면 Export Table Data As... 를 선택하여 작업합니다.

 

 

4.3 데이터베이스 원격지 복사

메뉴에서 Database -> Copy Database To Different Host/Database... 를 선택합니다. 현재 데이터베이스를 같은 서버내의 다른 데이터베이스 또는 다른 서버의 데이터베이스로 복사합니다. 다른 서버의 경우는 SQLyog 에 연결이 등록되어 있어야 합니다.

 

 

 

4.4 테이블 원격지 복사

메뉴에서 Table -> Copy Table(s) To Different Host/Database... 를 선택합니다. 사용방법은 데이터베이스 원격지 복사와 동일합니다.

 

4.5 스크립트 실행(데이터 import)

데이터베이스 전체 데이터 import 는 유료기능입니다. 그래서 Database -> Import -> Excute SQL Script.. 를 사용해서 데이터를 import 합니다. 백업을 SQL 스크립트로 만들면 되겠습니다.

 

 

 

 

Abort on error 에 체크를 해 두면 데이터 입력도중 에러가 발생하면 입력작업이 중지되고 에러를 확인 할 수 있습니다. 테이블 데이터의 import 는 동일한 방법을 사용할 수 있습니다. 또한 데이블 데이터에 대해서는 메뉴에서 Table -> Import -> Import CSV Data Using LOAD LOCAL 또는 Import XML Data Using LOAD LOCAL 을 사용해서 csv, xml 파일로 export 한 데이터를 import할 수 있습니다.

 

5. 쿼리하기 및 쿼리 결과 바로 수정하기

Query 창에서 필요한 쿼리를 실행할 수 있습니다. 쿼리의 양이 많지 않다면, 4.5 절의 스크립트 실행 기능이 아니라 Query창에 SQL 쿼리들을 붙여넣기하여 한번에 실행할 수도 있습니다.

 

 

5.1 조회 쿼리하기

Query 창에 쿼리를 입력하고 F9 키를 눌러 쿼리를 실행할 수 있습니다. 조회하게 되면 하단에 Result탭에 조회된 결과가 보입니다.

 

 

 

이상태는 Read Only 상태입니다. 상단의 Read Only 콤보박스의 값을 'testdb'.'tb_test' 로 변경하면 조회 결과를 수정할 수 있습니다. F8 키를 눌러서 쿼리를 실행하면 조회후 바로 수정모드가 되어 있습니다.

F8, F9 키는 쿼리창에서 현재 커서가 있는 쿼리를 실행합니다. 쿼리창 내의 모든 쿼리를 실행하기 위해서는 Ctrl + F9 키를 사용합니다.

 

 

6. 트랜잭션 사용하여 작업하기

SQLyog의 트랜잭션 관련 메뉴는 유료버전에서만 사용할 수 있습니다. SQLyog Community 버전에서 트랙잭션을 사용하려면 쿼리로 직접 트랜잭션 명령을 내려서 사용할 수 있습니다. 테이블에 대해 트랜잭션 작업을 하려면 테이블 생성시 엔진타입을 InnoDB 또는 NDB로 해야만 합니다.

 

MySQL은 기본적으로 자동 커밋 모드를 사용하여 실행됩니다. 즉, 입력/수정/삭제 작업을 수행하면 그 즉시 디스크에 영구적으로 저장되어져서 변경사항을 롤백할 수 없게 됩니다.

 

트랜잭션 관련 명령은 다음과 같습니다.

 

- START TRANSACTION 또는 BEGIN : 새 트랜잭션을 시작합니다.
- COMMIT : 현재 트랜잭션을 커밋하여 변경 내용을 영구적으로 만듭니다.
- ROLLBACK : 현재 트랜잭션을 롤백하고 변경 사항을 취소합니다.
- SET autocommit : 현재 세션에 대한 기본 자동 커밋 모드를 비활성화하거나 활성화합니다.

다음 처럼 사용할 수 있습니다.

 

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

 

START TRANSACTION 명령으로 자동 커밋 모드가 비활성화 되었다가 COMMIT 또는 ROLLBACK 후에 이전 상태로 돌아 갑니다.

또다른 방법은 아예 세션의 자동커밋 모드를 비활성화 시키고 작업을 하는 것입니다. 자동 커밋 모드를 비활성화 시키려면 다음 명령을 사용합니다.

 

SET autocommit = 0;

 

이제 부터 보내는 InnoDB, NDB 타입의 테이블에 대한 변경 명령은 바로 처리되지 않고, COMMIT 또는 ROLLBACK 할 수 있습니다.

 

지금까지 SQLyog  Community Editon 의 사용법에 대해 알아보았습니다.

 

SQLyog 사용법

SQLyog - MySQL 관리툴 설치하기