BigSea'Blog

如何修正 MySQL ?料?的 encoding？

最近 gslin 的 blog 因? MySQL ?料?的 encoding ??，?生?常?象，只好?零?始。??有偶地，Pesty 最近也?理了 MySQL 使用 utf-8 的相???。

?些??之所以??生，是因?越?越多的 web application 套件使用 UTF-8 ?存?料，但大家在?定 MySQL ??都?有注意到??，仍然使用??的 latin1 作??料?的 encoding。Web application ?入 UTF-8 ?料，MySQL ??作 latin1 在?，?然 web application ?回?的?候，因??好是反向?理，?料看似?有任何?常，但??上 collation 是?的，??行移?、?份甚至?? web application ?，也都??生??。

我一年多前也有遇到?似的??，原本在公司有?用 phpBB2 架的 forum，?用 big5 ??，但??上 mysql-3.23 ?是用 latin1 存。想要??成 UTF-8，移?到 FreeBSD 5 上改用 mysql-4.1，就?生??了。我那??有解?，所以 phpBB2 移?????了一年多，但?在是多跑一? virtual machine 只?了??移不??的 phpBB2，?在浪????。?好看到 gslin ? pesty 最近也在弄????，就重新?了一遍步?如下：

用 mysqldump 自 mysql-3.23 倒出?，??必?加上 --default-character-set=latin1 的??，才能正?倒出??上是用 big5 的 phpBB2 的?料?。
用 iconv -c -f CP950 -t UTF-8 ?倒出?的 SQL ?成 UTF-8。必?要加 -c，否??死在半路。?句??，其?中?已?有?西?掉了。
? mysql 用 SET GLOBAL/SESSION character_set_XXX=utf8 ?整 system variables，? charset ? collation 通通改成 UTF-8，然後做 CREATE DATABASE phpbb2。
再用 SOURCE 指令把??用 iconv ?出?的 SQL ?倒回?。
?果在第四?步??，mysql (client) 就 core dump 了。猜?有可能是第二步用 -c 硬??，把某?指令搞?了。不? mysql (client) 是直接 core dump 而不是跑 error message 出?指出??何在，所以?下??了，只能靠 iconv 不加 -c 想?法慢慢抓??。

MySQL server set-up

MySQL Server

使用 ports system 安?。
安? MySQL Server ?，??????的 MySQL Client 一?安?上。
【安? MySQL Server】

# cd /usr/ports/databases/mysql41-server/

如果系?是 4.x 系列的版本，建?使用 Linux threads
# make WITH_CHARSET=utf8 WITH_XCHARSET=complex WITH_COLLATION=utf8_general_ci -DSKIP_DNS_CHECK -DBUILD_STATIC -DBUILD_OPTIMIZED -DWITH_LINUXTHREADS install clean

如果系?是 5.x 以後的版本，建?使用原始??的 threads，因? FreeBSD 在 5.x 以後，?於 threads 有很多的性能修正
# make WITH_CHARSET=utf8 WITH_XCHARSET=complex WITH_COLLATION=utf8_general_ci -DSKIP_DNS_CHECK -DBUILD_STATIC -DBUILD_OPTIMIZED install clean

WITH_CHARSET=utf8，???料??存格式? UTF-8，如果想要改成 Big5，?改成 WITH_CHARSET=big5。

WITH_XCHARSET=complex，增加其它?系的支援。

WITH_COLLATION=utf8_general_ci，??的????表。

SKIP_DNS_CHECK，略?主?的名??映。

BUILD_STATIC，?成 static ?，效能?快一些。

BUILD_OPTIMIZED，使用最佳??的??(-O3)。

如果安? MySQL 需要其他功能，?一?加入??。如需 SSL，? WITH_OPENSSL；不需要 DNS ?查，? SKIP_DNS_CHECK。
【?定 MySQL 的?定?】

目前 MySQL 有提供四??例?，皆位於 /usr/local/share/mysql/ 目?中，四??案的??皆? my-*。?於其中的差?，可?看其中的??行?明，然後??例本身或修改?後，置於特定目?中即可。

my-huge.cnf，??? 1G-2G。
my-large.cnf，??? 512M。
my-medium.cnf，??? 32-64M。
my-small.cnf，??? <= 64M。
my-innodb-heavy-4G.cnf，使用 INNODB，且??? 4G。
# cp /usr/local/share/mysql/my-medium.cnf /var/db/mysql/my.cnf

【?定 MySQL 的?系】

此方法???所有 MySQL 的?系皆? UTF-8。

如果系?中?非所有使用 MySQL 的?系都是 UTF-8 的?，?必??每?不同需求修正其原始?，如 phpBB ?要修 phpBB 的原始?。
############### /var/db/mysql/my.cnf ####################
[mysqld]
init_connect = 'SET NAMES utf8'
default-collation = utf8_general_ci

[mysql]
default-character-set = utf8

【修改 FULL-TEXT Searching 的字串限制】

MySQL ??的 FULL-TEXT 字串限制? 4，在 4以下的搜?皆不?行，若?此有?外的需求，?必?照如下?定。
############### /var/db/mysql/my.cnf ####################
[mysqld]
ft_min_word_len = 1

[myisamchk]
ft_min_word_len = 1


【?? MySQL Server】

首先於 /etc/rc.conf 加入?? MySQL 必?的?? (?考 /usr/local/etc/rc.d/mysql-server.sh)。
############## /etc/rc.conf ############
mysql_enable="YES"
mysql_args="--skip-networking" # 此功能令 MySQL ?允?本地端?接，是?了增加安全性。

然後?? MySQL。
# /usr/local/etc/rc.d/mysql-server.sh start


如果???程中有??，可以查看 MySQL 的???息，其存成一??，位於 /var/db/mysql/ 目?下，?名? [您主?的 Host Name].err 。
【?定 MySQL Root 密?】

?定 MySQL Server 正常??後，接著?定 root 密?。此 root 密?不同於 system root，而是指 MySQL 的 root。於 MySQL Server 安?完成後，?? root 密?是空白的。
方法一：
# mysqladmin -u root password '填入您想要的密?'

方法二：建?
# mysql -u root -p # 要求?入密??，直接按 Enter 即可?入
mysql> use mysql;
mysql> UPDATE user SET password=password('password_you_want') WHERE user='root';
mysql> flush privileges;
mysql> quit

如果?了增?安全性，其?可以?掉 root ，而改用其它使用者。
# mysql -u root -p # 要求?入密??，??入您之前?定的 root 密?，若未?定?直接按 Enter ?入
mysql> use mysql;
mysql> INSERT INTO user VALUES ('localhost','db_admin',PASSWORD('password'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> DELETE FROM user WHERE (user="root");
mysql> flush privileges;
mysql> quit

注意，若????、密???料?名?都正??，?仍?法??，且出?如下字串?：
Client does not support authentication protocol requested by server; consider upgrading MySQL client

表示也???要升? MySQL client，但我遇到的??是 MySQL client ? server 是同版的情形下，表示 PHP ?建的 MySQL Client API 的版本太低，解?方法是? php 升?(尤其是 php-mysql)，或者先用?型式的 password ?存：
# mysql -u root -p # 要求?入密??，??入您之前?定的 root 密?
mysql> use mysql;
mysql> UPDATE user SET password = old_password('password_you_want') WHERE user = 'who_you_want';
mysql> flush privileges;
mysql> quit

【?除多?的用?名?】

# mysql -u root -p # 要求?入密??，??入您之前?定的 root 密?
mysql> use mysql;
mysql> DELETE FROM user WHERE NOT (host="localhost" and user="root");
mysql> flush privileges;
mysql> quit

【?除多?的?料?】

# mysql -u root -p # 要求?入密??，??入您之前?定的 root 密?
mysql> DROP DATABASE test;

【?建一般使用者】

?了安全性，建?平常不要使用?有 root ?限的使用者?操作，而以?限?小的使用者?取代之。
# mysql -u root -p # 要求?入密??，??入您之前?定的 root 密?
mysql> use mysql;
mysql> INSERT INTO user (host, user, password) values ('localhost', 'ant', password('password')); # 密??自定
mysql> INSERT INTO db (host, db, user, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Index_priv, Alter_priv) values ('localhost', 'wordpress', 'ant', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'); # db ??定其管控的?料?
mysql> flush privileges;

FAQ

PASSWORD & OLD_PASSWORD

由於 MySQL 4.1.x 以後的改?，使得 MySQL 使用新的密????制，而原?的密?函?改? old_password()。?使得?的 PHP library 或?的 function ，?於 MySQL 的呼叫方式必?做改?。

系?安? wordpress 1.5.2 以及 mediawiki 1.5.2，但 wordpress 使用的是 old_password()，而 mediawiki 使用 password()。面???的情形，必? MySQL root 存有此??密??制。

????就?了，MySQL 4.1.x 使用新的密??制，所以?了相容 wordpress 1.5.2 及 mediawiki 1.5.2，必?多新增另一? MySQL root，且?限相同，只是密??用 old_password()。
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (host, user, password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv) values ('antbsd.twbbs.org', 'root', old_password('password_you_want'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> flush priviledges;

ps:下午被mysql编码方式搞得头很大，2篇文章已备日后查用