■MySQL再インストール
# cd /usr/ports/databases/mysql50-server
# make WITH_XCHARSET=all
# make install
# cp /usr/local/share/mysql/my-medium.cnf /usr/local/etc/my.cnf
# mkdir /var/log/mysql
# chown -R mysql:mysql /var/log/mysql/
#vi /usr/local/etc/my.cnf
log = /var/log/mysql/mysql.log
log-error = /var/log/mysql/mysql-err.log
# make WITH_XCHARSET=all
# make install
# cp /usr/local/share/mysql/my-medium.cnf /usr/local/etc/my.cnf
# mkdir /var/log/mysql
# chown -R mysql:mysql /var/log/mysql/
#vi /usr/local/etc/my.cnf
log = /var/log/mysql/mysql.log
log-error = /var/log/mysql/mysql-err.log
■権限を限定したデータベースを作成
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or ?g.
Your MySQL connection id is 14 to server version: 5.0.18
Type 'help;' or '?h' for help. Type '?c' to clear the buffer.
mysql> CREATE DATABASE user01;
Query OK, 1 row affected (0.01 sec)
mysql> GRANT ALL on user01.* TO user01@localhost IDENTIFIED BY 'xxxxxxxx';
Query OK, 0 rows affected (0.00 sec)
Enter password:
Welcome to the MySQL monitor. Commands end with ; or ?g.
Your MySQL connection id is 14 to server version: 5.0.18
Type 'help;' or '?h' for help. Type '?c' to clear the buffer.
mysql> CREATE DATABASE user01;
Query OK, 1 row affected (0.01 sec)
mysql> GRANT ALL on user01.* TO user01@localhost IDENTIFIED BY 'xxxxxxxx';
Query OK, 0 rows affected (0.00 sec)
※「user01」は実ユーザ。
■文字コード設定
#vi /usr/local/etc/my.cnf
[client]
default-character-set = utf8
[mysqld]
log = /var/log/mysql/mysql.log
log-error = /var/log/mysql/mysql-err.log
skip-character-set-client-handshake
default-character-set = utf8
[mysqldump]
default-character-set = utf8
[mysql]
default-character-set = utf8
[client]
default-character-set = utf8
[mysqld]
log = /var/log/mysql/mysql.log
log-error = /var/log/mysql/mysql-err.log
skip-character-set-client-handshake
default-character-set = utf8
[mysqldump]
default-character-set = utf8
[mysql]
default-character-set = utf8
※変更箇所のみ
上記の作業をしないとソフトに文字化けが発生するので確認したところ、こんな感じになっていました。
mysql> use user01
Database changed
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
7 rows in set (0.00 sec)
Database changed
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
7 rows in set (0.00 sec)
latin1...
参考
http://kuina.jugem.jp/?eid=245
http://melanges.kuma.nu/060420121309.html
http://puchiko.lowtech.ne.jp/index.php?itemid=349
