mysql

C:\> mysql [-h localhost] -u root -proot [사용할 DB명] --접속

mysql> quit --종료

mysql> grant all on tuser.* to 'tuser'@'localhost' identified by 'tuser'; --계정 생성

mysql> select now(), curdate(), user(), version(); --시간, 현재 날짜, 현재 사용자, 버전 출력

+---------------------+------------+----------------+------------------+

| now()               | curdate()  | user()         | version()        |

+---------------------+------------+----------------+------------------+

| 2010-12-12 08:13:32 | 2010-12-12 | root@localhost | 5.1.51-community |

+---------------------+------------+----------------+------------------+

\g : 쿼리 작성 종료(세미콜론(;)과 동일)

\c : 쿼리 작성 중단

sql 파일 실행

C:\>mysql -u root -proot [DB명] < select.sql

또는

mysql> \. select.sql;

또는

mysql> SOURCE select.sql;

mysql> create database tdb; --DB 생성

mysql> use tdb; --사용할 DB

mysql> select database(); --현재 사용중인 DB

mysql> desc [table_name]; --test테이블 구조 보기 (mysql> show columns from [table_name] 와 동일)

mysql> show databases; --DB 목록 보기

mysql> show tables; --table 목록 보기

mysql> show index from [table_name]; --인덱스 보기

mysql> show status; --현재 상태 보기

mysql> show variables; --현재 환경 변수 보기

mysql> show create database [table_name]; --DB 생성 정보

+----------+---------------------------------------------------------------+

| Database | Create Database                                               |

+----------+---------------------------------------------------------------+

| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+---------------------------------------------------------------+

mysql> show create table [table_name]; --table 생성 정보

+-------+-----------------------------------------------------------------------------------------+

| Table | Create Table                                                                            |

+-------+-----------------------------------------------------------------------------------------+

| test  | CREATE TABLE `test` (`seq` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8     |

+-------+-----------------------------------------------------------------------------------------+

mysql> show table status like '[table_name]'; --table 상태 정보

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| test | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |            0 |   4194304 |           NULL | 2010-12-12 08:42:53 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

* 혹은 C:\> mysqlshow 명령어 사용

* 도움말 : mysql> help show

파일 내용 insert

: 파일 내용은 탭으로 구분되며 마지막줄은 개행문자로 종료되어야 함.

mysql> LOAD DATA LOCAL INFILE '[file_name]' INTO TABLE [table_name]; --LOAD DATA 사용

혹은

C:\> mysqlimport -u root -proot --local [table_name] [file_name] --mysqlimport 사용

NULL

mysql> select NULL<0, NULL=0, NULL!=0, NULL>0, NULL=NULL, NULL!=NULL, NULL IS NULL, NULL IS NOT NULL;

+--------+--------+---------+--------+-----------+------------+--------------+------------------+

| NULL<0 | NULL=0 | NULL!=0 | NULL>0 | NULL=NULL | NULL!=NULL | NULL IS NULL | NULL IS NOT NULL |

+--------+--------+---------+--------+-----------+------------+--------------+------------------+

|   NULL |   NULL |    NULL |   NULL |      NULL |       NULL |            1 |                0 |

+--------+--------+---------+--------+-----------+------------+--------------+------------------+

<=> 연산자 : 같으면 1, 다르면 0이며 NULL에 끼리인 경우도 1(true) 임

mysql> select NULL <=> NULL;

+---------------+

| NULL <=> NULL |

+---------------+

|             1 |

+---------------+

ORDER BY IF : IF()문을 이용한 정렬

mysql> select col1 from test order by if(col1 IS NULL, 0, 1), col1; --NULL이 맨 위로 정렬

mysql> select col1 from test order by if(col1 IS NULL, 1, 0), col1; --NULL이 맨 아래로 정렬

LIMIT : 상위 몇개 행만 출력

mysql> select * from test limit 3; --상위 3개 행만 출력

mysql> select * from test limit 1, 3; --상위 1개 행을 건느뛰고 다음 3개 행 출력

RAND()와 LIMIT 사용

mysql> select * from test order by RAND() limit 1; --랜덤하게 1개 행 출력

변수사용 @

mysql> select @var := col1 from test limit 1; --col1 값을 @var 변수에 저장

mysql> select * from test where col1 = @var; --@var 변수 사용

mysql> SET @var = 'hi'; --@var 변수값 할당

mysql> select @var; --@var 변수값 확인

프롬프트 변경

mysql> prompt \d>\_ --"DB명> " 으로 프롬프트 변경

데이터 타입

TINYINT 1byte 정수, unsigned 지정가능

SMALLINT 2byte 정수, unsigned 지정가능

MEDIUMINT 3byte 정수, unsigned 지정가능

INT 4byte 정수, unsigned 지정가능

BIGINT 8byte 정수, unsigned 지정가능

FLOAT 4byte 부동소수

DOUBLE 8byte 부동소수

DECIMAL Mbyte 부동소수(문자열 표현)

CHAR 고정길이 문자열

VARCHAR 가변길이 문자열

TINYBLOB (2^8)-1byte, 매우작은 BLOB

BLOB (2^16)-1byte,작은     BLOB

MEDIUMBLOB (2^24)-1byte,중간     BLOB

LONGBLOB (2^32)-1byte,큰       BLOB

TINYTEXT (2^8)-1byte, 매우 작은 텍스트 문자열

TEXT (2^16)-1byte,작은      텍스트 문자열

MEDIUMTEXT (2^24)-1byte,중간      텍스트 문자열

LONGTEXT (2^32)-1byte,큰        텍스트 문자열

ENUM 65535개, 열거형(열거한 멤버중 하나만 대입 가능)

SET 64개,    집합형(복수 집합형 멤버 대입 가능)

DATE 'CCYY-MM-DD' 날짜

TIME 'hh:mm:ss' 시간

DATETIME 'CCYY-MM-DD hh:mm:ss'

TIMESTAMPE 'CCYYMMDDhhmmss'

YEAR 'CCYY' 연도값

ZEROFILL : 숫자형 데이터 타입에 사용 가능

mysql> create table test(zf int(5) ZEROFILL);

mysql> insert into test values(1), (10), (10000000);

mysql> select * from test;

+----------+

| zf       |

+----------+

|    00001 |

|    00010 |

| 10000000 |

+----------+

AUTO_INCREMENT : NULL/0 insert시 자동증가. (자동으로 NOT NULL 지정 및 PK이여야 함. 테이블당 1개 사용 가능)

mysql> create table test(i int unsigned auto_increment primary key);

mysql> insert into test values(NULL);

mysql> insert into test values(0);

mysql> insert into test values(NULL);

mysql> select  * from test;

+---+

| i |

+---+

| 1 |

| 2 |

| 3 |

+---+

LAST_INSERT_ID(expr)

: 마지막 insert된 id 값을 가져온다.

mysql> create table test(i int unsigned auto_increment primary key);

mysql> insert into test values(NULL);

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

|                1 |

+------------------+

mysql> insert into test values(NULL);

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

|                2 |

+------------------+

: expr 을 사용할 수도 있다.

mysql> create table test (seq int);

mysql> insert into test values(0);

mysql> update test set seq = last_insert_id(seq + 1);

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

|                1 |

+------------------+

mysql> select * from test;

+------+

| seq  |

+------+

|    1 |

+------+

charset() : 컬럼 캐릭터 셋 확인

mysql> create table test(str varchar(10));

mysql> insert into test values ('hi');

mysql> select charset(str) from test;

+--------------+

| charset(str) |

+--------------+

| utf8         |

+--------------+

비교연산자

=, <=>, !=, <> , <, <=, >=, >

IN, BETWEEN a AND b, a NOT BETWEEN b AND c, LIKE, NOT LIKE

IS NULL, IS NOT NULL

a REGEXP b, a NOT REGEXP b : a가 b 정규표현식에 대응되는지 여부

테이블 타입

ISAM, MyISAM (default), MERGE (MRG_MyISAM), HEAP, BDB (BerkeleyDB), InnoDB

ex> create table test (..) TYPE = HEAP MIN_ROWS = 10000; --TYPE : 테이블 타입 지정, MIN_ROWS : 최적화를 위한 옵션

ex> alter table test TYPE = INNODB;

ISAM

MyISAM 전 버전으로 순차 접근 방법을 사용하여 테이블 관리

트랜잭션 미지원.

파일 : .frm(포멧정보), .ISD(데이터:최대4GB), .ISM(인덱스:최대4GB)

MyISAM (default)

ISAM 보다 큰 테이블 생성 가능.

H/W 독립적인 포맷.

ISAM보다 나아진 인덱스 제약사항을 가짐.

다른 테이블 보다 확장된 기능의 AUTO_INCREMENT 처리.

FULLTEXT 인덱스를 통한 풀 텍스트 검색 지원.

파일 : .frm(포멧정보), .MYD(데이터:4GB~8백만TB), .MYI(인덱스:4GB~8백만TB)

MERGE (MRG_MyISAM)

동일한 구조의 테이블을 하나의 테이블 처럼 사용하게 해주는 테이블 타입.

파일 : .frm(포멧정보), .MRG(테이블목록)

HEAP

메모리에 생성되어 고속 수행이 가능한 테이블. (서버 종료시 삭제됨)

create temporary table(세션종료시 삭제됨)과 달리 다른 클라이언트도 접근 가능.

파일 : .frm(포멧정보), 데이터와 인텍스는 메모리에 저장

BDB (BerkeleyDB)

트랜잭션 지원, 장애시 자동복구, 페이지 수준의 lock

파일 : .frm(포멧정보), .db(데이터와 인덱스:최대 2TB)

InnoDB

트랜잭션 지원, 장애시 자동복구, 저수준 lock, BDB 보다 우수함.

파일 : .frm(포멧정보), 데이터와 인덱스는 테이블 스페이스 파일에 저장. 최대 40억페이지(기본 페이지는 16KB이며 최대 64KB)

존재 여부 확인

ex) create table if not exists [table_name] (..);

ex) drop table if exists [table_name]

인덱스

--생성

create          index [index_name] on [table_name] ([index_columns]); --중복 허용

create unique   index [index_name] on [table_name] ([index_columns]); --중복 불허

create fulltext index [index_name] on [table_name] ([index_columns]); --MyISAM만 사용 가능

create table [table_name]

(

..

index    [index_name] ([index_columns]),

unique   [index_name] ([index_columns]),

fulltest [index_name] ([index_columns]),

primary key ([index_columns]),

..

);

--삭제

drop index [index_name] on [table_name];

alter table [table_name] drop index [index_name];

alter table [table_name] drop primary key;

FULLTEXT index

팬턴을 지정하지 않고 MATCH() AGAINST() 문으로 편리하게 검색 가능.

MyISAM 테이블 타입의 CHAR, VARCHAR, TEXT 컬럼만 사용 가능.

대소문자 구문 안함.

ex) column에 "hi"라는 단어가 포함되어있는 행 검색(대소문자 미구분)

alter table [table_name] add fulltext([column]); --index 생성

select * from [table_name] where MATCH([column]) AGAINST('hi');

  

명시적 LOCK/UNLOCK

mysql> lock table [table_name] write;

mysql> unlock tables;

TRANSACTION 사용 1 : BDB 혹은 InnoDB에서 사용 가능.

mysql> BEGIN;

mysql> insert into [table_name] values(..);

mysql> COMMIT; (혹은 ROLLBACK)

TRANSACTION 사용 2 : AUTOCOMMIT 설정

mysql> show variables like 'autocommit'; --설정 확인

mysql> set autocommit = 0; --autocommit false

mysql> insert into [table_name] values(..);

mysql> COMMIT; (혹은 ROLLBACK)

mysql> set autocommit = 1; --autocommit true

mysql 5 부터 stored procedure, trigger, view 지원

옵티마이저 (최적화)

- EXPLAIN 으로 실행 정보 확인

mysql> explain select * from grade_event where event_id = 2;

+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+

| id | select_type | table       | type  | possible_keys | key     | key_len | ref   | rows | Extra |

+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+

|  1 | SIMPLE      | grade_event | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |

+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+

- PROCEDURE ANALYSE() 로 최적 컬럼 정보 확인

mysql> select * from test procedure analyse(); --컬럼 정보 및 최적 타입 분석

+---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+------+---------------------+

| Field_name    | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std  | Optimal_fieldtype   |

+---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+------+---------------------+

| test.test.str | hi        | hi        |          2 |          2 |                0 |     0 | 2.0000                  | NULL | ENUM('hi') NOT NULL |

+---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+------+---------------------+

- 테이블 단편화 제거

1.OPTIMIZE TABLE [table_name]; 으로 단편화 제거 (MyISAM만 동작)

2.테이블 덤프 후 테이블 재생성 (모든 테이블)

ex) mysqldump --opt [db_name] [table_name] > dump.sql

mysql [db_name] < dump.sql

- 인덱스 재구성1

index 재생성 : drop index/create index

index 비활성화/활성화 :

alter table [table_name] disable keys;

alter table [table_name] enable keys;

- 인덱스 재구성2 : myisamchk, isamchk 사용

- 그 회 환경 변수 및 H/W 튜닝

디렉토리 구조

data 디렉토리 (datadir)

C:\>mysqladmin -u root -proot variables | grep datadir

C:\>mysqladmin -u root -proot --host=localhost --port=3306 variables | grep datadir

my.ini(my.cnf) 파일의 datadir

상태파일 및 로그 파일 : datadir 에 파일 존재

${HOSTNAME}.pid : mysql process id

${HOSTNAME}.log : 일반 쿼리 로그

${HOSTNAME}-slow.log : 오래 걸린 쿼리 로그

${HOSTNAME}.nnn : 데이터 갱신 쿼리 로그

${HOSTNAME}-bin.nnn : 데이터 갱신 쿼리의 바이너리 로그

${HOSTNAME}-bin.index : 현재 바이너리 갱신 로그 파일 목록

${HOSTNAME}.err : 에러 로그(시작/종료 기록 포함)

datadir 디렉토리 변경 방법

my.ini(my.cnf)의 datadir 변경 후 서버 재시작

database 별 디렉토리 위치 변경

UNIX는 심볼링 링크로 배치 가능.

윈도우의 경우 [db_name].sym 파일에 옮길 경로를 적은 후

--use-symbolic-links 옵션이나 my.ini 파일에 use-symbolic-links 옵션을 두어 적용 가능

테이블별 데이터 파일 위치 변경(비권장:문제 소지 많음)

pid, 로그파일등은 my.ini(my.cnf) 변경 혹은 mysqld의 시작 옵션으로 변경 가능

시작 옵션 확인 : C:\> mysqld --verbose --help

설치/실행(windows)

--mysql 실행

C:\> mysqld

--콘솔모드 실행

C:\> mysqld --console

--mysql 종료

C:\> mysqladmin -u root -proot shutdown

--윈도우 서비스 설치(부팅시 자동시작)

C:\> mysqld --install

--윈도우 서비스 설치(부팅시 수동시작)

C:\> mysqld --install-manual

--윈도우 서비스 삭제

C:\> mysqld --remove

--윈도우 서비스 시작/종료

C:\> net start MySQL

C:\> net stop MySQL

사용자 계정/권한

create user / drop user / grant / rename / revoke / set password

*. mysql은 --port 옵션을 사용하지만 서버가 한개 이상의 IP주소를 갖고 있다면 --bind-address 옵션으로 사용할 주소를 지정할 수 있다.

캐릭터셋 확인

mysql> show variables like 'character%';

복수개의 서버 운영

1. my.ini(my.cnf) 파일안에 [서비스명]으로 여러개 서버 정의

ex)

//c:\my.cnf1

[mysqld]

basedir=c:\mysql

datadir=c:\data1

//c:\my.cnf2

[mysqld2]

basedir=c:\mysql

datadir=c:\data2

--서버 시작

c:> mysqld --default-file=c:\my.cnf1

c:> mysqld --default-file=c:\my.cnf2

--서버별 서비스 설치 : mysql --install-manual [service_name]

--(서비스명이 지정되지 않은 경우 MySQL을 사용하며 my.ini파일의 [mysqld]를 읽어들임)

c:> mysql --install-manual mysqld

c:> mysql --install-manual mysqld2

리플리케이션 서버

마스터(주 서버)와 이를 복제하는 슬레이브로 구성되는 서버.

SSL(secure socket layer) 사용

ex) grant all on testdb.* to 'tuser'@'%.company.com' IDENTIFIED BY 'tuser' REQUIRE SSL;

mysql> show variables like 'have_openssl';

+---------------+----------+

| Variable_name | Value    |

+---------------+----------+

| have_openssl  | DISABLED |

+---------------+----------+

LOCK

내부잠금 - 테이블 LOCK (읽기 접근)

윈도우1 : mysql> LOCK TABLE [table_name] READ; --다른 클라이언트는 읽기만 가능

윈도우1 : mysql> FLUSH TABLE [table_name]; --캐시에 남아있는 내용을 파일에 씀

윈도우2 : C:\> myisamchk [table_name]

윈도우1 : mysql>UNLOCK TABLE;

내부잠금 - 테이블 LOCK (읽기/쓰기 접근)

윈도우1 : mysql> LOCK TABLE [table_name] WRITE; --다른 클라이언트는 어떤 접근도 못함

윈도우1 : mysql> FLUSH TABLE [table_name]; --캐시에 남아있는 내용을 파일에 씀

윈도우2 : C:\> myisamchk -recover [table_name]

윈도우1 : mysql> FLUSH TABLE [table_name];

윈도우1 : mysql>UNLOCK TABLE;

내부잠금 - 데이터베이스 LOCK

mysql> FLUSH TABLES WITH READ LOCK; --읽기 잠금

mysql> UNLOCK TABLES; --LOCK 해제

외부 잠금

mysql> SHOW VARIABLES LIKE 'skip%locking';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| skip_external_locking | ON    |

+-----------------------+-------+

skip_external_locking이 ON인 경우  : 외부잠금 불가하므로 내부잠금 사용

skip_external_locking이 OFF인 경우 : 

mysqladmin flsuh-tables를 사용하여 테이블 캐시 비운후 

myisamchk나 isamchk 사용 가능하며

작업 도중 테이블 갱신을 금지해야 한다.

(그러므로 복구 작업에선 내부 잠금을 사용해야 한다.)

백업 

mysqldump : dump 파일 생성

ex>

C:\> mysqldump testdb > testdb.dmp

C:\> mysqldump --opt testdb > testdb.dmp (--opt 옵션으로 덤프 최적화)

원격 복사

C:\> mysqladmin -h www.test.com create testdb

C:\> mysqldump --opt testdb | mysql -h www.test.com testdb

또는 ssl 사용할 경우

C:\> ssh www.test.com mysqladmin create testdb

C:\> mysqldump --opt testdb | mysql -h www.test.com testdb