如何修正 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篇文章已备日后查用
