mysql 캐릭터셋

출처 http://develop.sunshiny.co.kr/385

# 현재 캐릭터셋 검색
mysql>show variables like 'char%';

+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | latin1                            |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | latin1                            |
| character_set_system     | utf8                             |
| character_sets_dir       | /opt/mysql/mysql/share/charsets/ |
+--------------------------+----------------------------------+

mysql>status 또는 \s
--------------
mysql  Ver 14.14 Distrib 5.1.42, for pc-solaris2.10 (i386) using readline 5.1

Connection id:          1
Current database:       
Current user:           계정명@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.42 명ySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 12 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.500
--------------

# 데이타 베이스 생성 : utf8 타입
CREATE DATABASE 데이터베이스명 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

# 데이타 베이스 생성 : euckr 타입
CREATE DATABASE 데이터베이스명 DEFAULT CHARACTER SET euckr COLLATE euckr_korean_ci;

# 데이타 베이스 생성후 외부 접속하기 위해서 IP를 추가 해줍니다.
GRANT ALL PRIVILEGES ON 데이타베이스명.* TO '계정명'@'192.168.1.100' IDENTIFIED BY '비밀번호' WITH GRANT OPTION;

## 각각의 캐릭터 셋을 변경 - 이 변경 방법은 mysql 데몬이 재실행 될경우에 초기화 됨.
mysql> SET character_set_client = utf8;
mysql> SET character_set_results = utf8;
mysql> SET character_set_connection = utf8;
mysql> ALTER DATABASE DB명 DEFAULT CHARACTER SET utf8;
commit;

## /etc/my.cnf 파일에 설정 - 이 설정 방법을 추천 mysql 데몬이 재실행 되어도 설정을 유지 합니다.

mysql>vi /etc/my.cnf

################# utf8 설정 #################
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

default-character-set=utf8

[mysqld]
init_connect=SET collation_connection = utf8_general_ci
init_connect=SET NAMES utf8
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci

port            = 3306
socket          = /tmp/mysql.sock
basedir       = /opt/mysql/mysql
datadir        = /opt/mysql/mysql/data

set-variable    = table_cache=1024
set-variable    = max_connections=500
set-variable    = max_user_connections=100
set-variable    = max_connect_errors=10000

log-slow-queries
set-variable    = long_query_time=3
skip-name-resolve

# InnoDB 사용 안할시 아래 내용추가
#skip-innodb
sync_binlog = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
default-character-set=utf8

################# utf8 설정후 확인 #################

mysql> \s   
--------------
mysql  Ver 14.14 Distrib 5.1.42, for pc-solaris2.10 (i386) using readline 5.1

Connection id:          4
Current database:       
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.42-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 19 min 31 sec

Threads: 2  Questions: 33  Slow queries: 1  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.28

mysql> show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /opt/mysql/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.99 sec)

################# euckr 설정 #################

[client]
default-character-set=euckr

[mysqld]
default-character-set=euckr
init_connect=SET collation_connection=euckr_korean_ci
init_connect=SET NAMES euckr
character-set-server=euckr
collation-server=euckr_korean_ci

port            = 3306
socket          = /tmp/mysql.sock
basedir       = /opt/mysql/mysql
datadir        = /opt/mysql/mysql/data

set-variable    = table_cache=1024
set-variable    = max_connections=500
set-variable    = max_user_connections=100
set-variable    = max_connect_errors=10000

log-slow-queries
set-variable    = long_query_time=3
skip-name-resolve

# InnoDB 사용 안할시 아래 내용추가
#skip-innodb
sync_binlog = 1

[mysqldump]
default-character-set=euckr

[mysql]
default-character-set=euckr