Mysql 5.x 에서 재설치 없이 케릭터셋 변경하기
💡 요약 정리
- Mysql 5.x 환경에서 재설치 없이 캐릭터셋을 utf8 또는 euckr로 변경할 수 있습니다.
- 설정은
/etc/my.cnf파일을 수정 후 Mysql 서비스를 재시작하여 적용됩니다. - 변경 이전과 이후 모두
mysql> status;명령어로 캐릭터셋 상태를 확인할 수 있습니다. - 서비스 운영 중인 서버의 설 정 변경이므로 백업 후 작업을 권장합니다.
설치환경
- CentOS 5.x (64bit)
- mysql 5.1.59 (uft8 → euckr)
- mysql 5.0.95 (euckr 또는 latin1 → utf8)
1. euckr로 변경하기
- mysql 설치 후 설정된 캐릭터셋을 확인합니다.
[root@cafe24 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.59-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.1.59, for unknown-linux-gnu (x86_64) using readline 5.1
Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.59-log Source distribution
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: 5 min 4 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.13
--------------
/etc/my.cnf파일을 수정합니다.[mysqld]하단에 아래 2줄을 삽입하고 Mysql을 재시작합니다.
[root@cafe24 ~]# vi /etc/my.cnf
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
default-character-set=euckr
character-set-client-handshake = FALSE
- Mysql을 재시작합니다.
[root@cafe24 ~]# /etc/rc.d/init.d/mysql restart
Shutting down MySQL..... [ OK ]
Starting MySQL. [ OK ]
[root@cafe24 ~]#
- Mysql 접속 후 변경된 캐릭터셋을 확인합니다.
[root@cafe24 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.59-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.1.59, for unknown-linux-gnu (x86_64) using readline 5.1
Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.59-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: euckr
Db characterset: euckr
Client characterset: euckr
Conn. characterset: euckr
UNIX socket: /tmp/mysql.sock
Uptime: 1 min 2 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.64
--------------
2. utf8로 변경하기
- 설치된 Mysql 캐릭터셋을 확인합니다.
[root@cafe24 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.95 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status;
--------------
mysql Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.95 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 4 sec
Threads: 1 Questions: 5 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 1.250
--------------
/etc/my.cnf파일을 수정하고 재시작합니다.[client],[mysqld]섹션에 아래 내용을 추가한 후 Mysql을 재시작합니다.
[root@cafe24 log]# vi /etc/my.cnf
[client]
default-character-set=utf8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
character-set-server=utf8
collation-server=utf8_general_ci
init_connect=set collation_connection=utf8_general_ci
init_connect=set names utf8
- Mysql을 재시작합니다.
[root@cafe24 ~]# /etc/rc.d/init.d/mysql restart
Shutting down MySQL..... [ OK ]
Starting MySQL. [ OK ]
[root@cafe24 ~]#
- 변경된 캐릭터셋을 확인합니다.
[root@cafe24 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.95 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status;
--------------
mysql Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.95 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 22 sec
Threads: 1 Questions: 6 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.273
--------------