MySQL 的 Backup 與 Restore 不支援 UTF-8 ？？

內容

進藤光 · #1 文章由 **進藤光** » 08/30/2005 9:03 am

我把採用 UTF-8 的 phpBB（2.0.17 版）與 XOOPS 裝好之後，很快就發現這個新的問題！
關於 phpBB 2.0.17 的 UTF-8 版本，請參考這裡：
http://phpbb-tw.net/phpbb/viewtopic.php?t=37824

做法很簡單，打開 MySQL Admin (1.0 版，1.1 版在按 Backup 開始備份時，就自動結束了)：

1. 用 Backup 備份 phpBB 與 XOOPS 資料庫（使用預設值與 Normal Backup）

2. 刪除 phpBB 與 XOOPS 資料庫，不知道為啥，不刪除就不給還原...

3. 用 Restore 還原 phpBB 與 XOOPS 資料庫，哈哈所有資料通通變成亂碼！！

好吧，可能事 MySQL Admin 的問題，改用 phpMySQL 看看，使用最新的 2.6.3-pl1.....

結果下場一樣，所有 UTF-8 都會變成亂碼！
但是 Big 5 不會.....

然後我試著改用不同備份方式跟 option，情況不變亂碼依舊....
請問我做錯了什麼嗎？？

janusng · #2 文章由 **janusng** » 08/30/2005 9:28 am

你有在 phpMyAdmin 選對 encoding 嗎？
MySQL 是什麼 version？

進藤光 · #3 文章由 **進藤光** » 08/30/2005 10:05 am

編碼當然是 UTF-8.... 呃.... 應該要用 Big 5 嗎？？

MySQL 是使用最新版本 4.1.14

http://dev.mysql.com/downloads/mysql/4.1.html

我選用【Max 4.1.14】，不太了解【Standard】跟【Max】版有啥差別，我沒看到比較表。
猜想應該是【完整版】吧？

裝上這套，我的 Mac OS X Server 裡面就變成有兩套 MySQL 了.....
沒辦法，因為我找不到讓 preInstall 的 MySQL 隨 OS 起動的方式。
網路上寫的那些都是指一般安裝的 MySQL。

jamesjan · #4 文章由 **jamesjan** » 08/30/2005 10:21 am

我猜是MySQL的encoding的問題
你文中指出你phpbb用UTF-8,可是並沒有提到你MySQL的encoding
如果用source code自己compile, MySQL預設使用ISO-8859-1(Latin1),不過MySQL網頁上提供的binary我就不知道了
當然,你可以把UTF-8的資料,讓MySQL用Latin1去記錄,在使用上完全不會有問題
只是這樣dump出來,就"可能"會有問題
我記得mysqldump可以設定charset,你要不要試試哪一種charset是可以正確dump出來你的資料??

--
據我所知,很多人在FreeBSD 4.x -> FreeBSD 5.x的時候,想換到unicode的環境.然後才發現這問題,我有一個db要從FreeBSD 5.x的MySQL移到Mac OS X上.........改天再來試 :P

janusng · #5 文章由 **janusng** » 08/30/2005 10:38 am

jamesjan 兄說的對，MySQL 4.1 可以對每個 database、table、column 設定 encoding。

此外，php 本身對 mysql 也可以指定 client 編碼。請參看：
http://www.php.net/manual/en/function.m ... coding.php

加上 phpMyAdmin 有多種編碼設定，只要是任何一個設定錯誤，包括新舊設定不同，也會失敗的。

linjacky · #6 文章由 **linjacky** » 08/30/2005 10:51 am

進藤光寫:編碼當然是 UTF-8.... 呃.... 應該要用 Big 5 嗎？？

MySQL 是使用最新版本 4.1.14

http://dev.mysql.com/downloads/mysql/4.1.html

我選用【Max 4.1.14】，不太了解【Standard】跟【Max】版有啥差別，我沒看到比較表。
猜想應該是【完整版】吧？

裝上這套，我的 Mac OS X Server 裡面就變成有兩套 MySQL 了.....
沒辦法，因為我找不到讓 preInstall 的 MySQL 隨 OS 起動的方式。
網路上寫的那些都是指一般安裝的 MySQL。

Max版增加對InnoDB格式資料表的支援，才有外鍵(foreign key)與交易(transaction)鎖定等功能。
MacOS X好像Server版才有內建的MySQL，一般的Client版應該沒有內建。如果要刪除舊版安裝的MySQL，先用MySQLAdministrator把MySQL的服務關閉，再前往檔案夾：
/usr/local
把“mysql-standard-4.1.13-apple-darwin8.1.0-powerpc“或"mysql-max-4.1.13-apple-darwin8.1.0-powerpc"檔案夾移除，並同時把:
/Library/Receipts
檔案夾底下的：
mysql-max-4.1.13-apple-darwin8.1.0-powerpc.pkg 或
mysql-standard-4.1.13-apple-darwin8.1.0-powerpc.pkg
把程式收據套件移除即可。

至於MySLQ語系問題，也是請用MySQLAdministrator，登入伺服器之後選"Option"圖像可以設定MySQL系統參數，選"Advance"選單，將"Def. Char Set:"內定語系欄位設定為"utf8"，然後按下"Save"儲存參數。
接著按下“Server“圖像，按下"Stop Server"停止伺服器，稍等再按下"Star Server"，新的系統參數就會有作用，你的資料庫應該可以還原了。

進藤光 · #7 文章由 **進藤光** » 08/30/2005 11:58 am

謝謝各位！！
我來貼幾張圖，可能比較清楚！

這是 phpMySQL 的畫面，老實說，看不懂要選啥才會跟 UTF-8 有關係.... 我是認為不管怎麼選都應該與資料庫編碼格式無關.....

這是 MySQL Admin 管理的 Backup 畫面，同樣的我也覺得不管怎麼選，都與 UTF-8 無關，也可能與任何編碼無關.....

我主要是希望能【定時備份】，這種事不會發生在 MS SQL Server 身上....

這是 Restore 畫面，更簡單了，就是選檔案，按【Restore Backup】而已.... 當然 Restore 前，要先幹掉同名的資料庫，不然啥事也沒....
連個警告訊息都不說一下...... Orz

最沒辦法就是直接 copy 檔案算了，但是資料夾都不給進去.... 真是討厭.....

請問【"Def. Char Set:"】這個按 ... 只能選資料庫檔案，沒有你說的 UTF-8，莫非自己手動輸入【UTF-8】？？？

Mac OS X Server 預設的資料夾就是 MySQL，沒有一堆與版本有關的東東....
我覺得當我下次更新 MySQL 時，搞不好會出問題！！又給我裝第 3 套 MySQL 而不是直接換掉前一版......

janusng · #8 文章由 **janusng** » 08/30/2005 1:50 pm

如前述般，有多個設定要弄清楚，包括原本的 mysqld 和新的 mysqld 也要無誤，才能正確匯出和匯入。請留以圖片的下的紅圈部分：

ross_tt · #9 文章由 **ross_tt** » 08/30/2005 1:57 pm

資料庫的編碼在這裡改！

linjacky · #10 文章由 **linjacky** » 08/30/2005 2:41 pm

進藤光寫:
請問【"Def. Char Set:"】這個按 ... 只能選資料庫檔案，沒有你說的 UTF-8，莫非自己手動輸入【UTF-8】？？？

"Def. Char Set:"這一項要自己輸入，把欄位前面的打叉的那個"筆"圖案點成沒有紅線，再於欄位中輸入"utf8"，我的經驗是會解決許多編碼的問題。

進藤光 · #11 文章由 **進藤光** » 08/30/2005 5:50 pm

謝謝各位圖文並茂的解說.....

當我在【"Def. Char Set:"】輸入【utf8】之後，

同一個 phpBB 網站的內容就變成亂碼了：

當然改 Safari 的文字編碼是沒用的，怎麼改都是亂碼....
換句話說，我目前 MySQL 資料庫格式並不是 UTF-8 ！

其實我有留意到這點，我用 MySQL Admin 打開【Catalogs】再打開【post_text】來看貼文格式，字元集竟然是用【cp1252 West European】，另一個 Collation 是【latin1_swedish_ci】.... 怪怪的...

可是我從 Microsoft SQL Server 把資料轉成 MySQL 的那一個 phpBB 就是使用 UTF-8..... 奇怪？？

換句話說，我得幹掉現在的 phpBB，再用 utf8 來重裝？？
嗚～～～～

笨蛋阿光，果然笨～～～

ulysses · #12 文章由 **ulysses** » 08/30/2005 5:58 pm

可能是因為備份方式是『SQL』的關係吧？可能是產生的 DB Generation SQL Script 沒有把語系設定放進去。MySQL 4.1x 建立 DB 的語法為：

代碼: 選擇全部

CREATE DATABASE xxxx_db DEFAULT CHARACTER SET utf8;

MySQL Client 連線上去後也可以動態調整使用語系：

代碼: 選擇全部

SET NAME 'utf8';

其實像這種定時備份，只要備份資料就好，不需要連同 DB Generation 一起備份。用 CSV 做資料 Only 備份的話，只要輸入輸出的 MySQL Client 連線語系相同，應該就不會有這個問題。

MySQL 的備份，最簡單的方式就是備份資料檔案。要復原的時候就先建立一個空資料庫，然後直接用舊資料檔案蓋掉新資料檔案就好了。在下都是用 C-Shell Script 放 CRONTAB 用 mysql 帳號權限執行備份資料檔。搭配 scp 和 ssh-keygen，還可以做定時自動異地備份。

linjacky · #13 文章由 **linjacky** » 08/30/2005 6:02 pm

進藤光寫:謝謝各位圖文並茂的解說.....

當我在【"Def. Char Set:"】輸入【utf8】之後，

同一個 phpBB 網站的內容就變成亂碼了：

當然改 Safari 的文字編碼是沒用的，怎麼改都是亂碼....
換句話說，我目前 MySQL 資料庫格式並不是 UTF-8 ！

其實我有留意到這點，我用 MySQL Admin 打開【Catalogs】再打開【post_text】來看貼文格式，字元集竟然是用【cp1252 West European】，另一個 Collation 是【latin1_swedish_ci】.... 怪怪的...

可是我從 Microsoft SQL Server 把資料轉成 MySQL 的那一個 phpBB 就是使用 UTF-8..... 奇怪？？

換句話說，我得幹掉現在的 phpBB，再用 utf8 來重裝？？
嗚～～～～

笨蛋阿光，果然笨～～～

還有兩個地方會影響輸出的結果，Apache與PHP的設定。
我不知道phpBB有沒有用到mb_string的函數，如果有，請試試看把php.ini裡的:
#mbstring.internal_encoding = EUC-JP 改為:
mbstring.internal_encoding = UTF8
然後重新啟動Apache(把Web共享關閉再開)試試看。

ulysses · #14 文章由 **ulysses** » 08/30/2005 6:05 pm

linjacky 寫:我不知道phpBB有沒有用到mb_string的函數，如果有，請試試看把php.ini裡的:
#mbstring.internal_encoding = EUC-JP 改為:
mbstring.internal_encoding = UTF8
然後重新啟動Apache(把Web共享關閉再開)試試看。

在下後來處理 PHP5 + MySQL4.1x 的狀況時，乾脆就偷懶把所有 HTML 和 PHP 通通都轉成 UTF8。省得自己在程式裡轉來轉去轉到頭昏。

進藤光 · #15 文章由 **進藤光** » 08/30/2005 6:20 pm

ulysses 寫:可能是因為備份方式是『SQL』的關係吧？可能是產生的 DB Generation SQL Script 沒有把語系設定放進去。MySQL 4.1x 建立 DB 的語法為：
代碼: 選擇全部
CREATE DATABASE xxxx_db DEFAULT CHARACTER SET utf8;
MySQL Client 連線上去後也可以動態調整使用語系：
代碼: 選擇全部
SET NAME 'utf8';
其實像這種定時備份，只要備份資料就好，不需要連同 DB Generation 一起備份。用 CSV 做資料 Only 備份的話，只要輸入輸出的 MySQL Client 連線語系相同，應該就不會有這個問題。

MySQL 的備份，最簡單的方式就是備份資料檔案。要復原的時候就先建立一個空資料庫，然後直接用舊資料檔案蓋掉新資料檔案就好了。在下都是用 C-Shell Script 放 CRONTAB 用 mysql 帳號權限執行備份資料檔。搭配 scp 和 ssh-keygen，還可以做定時自動異地備份。

我先去吃晚餐，晚點回來確認看看.....
MySQL 以前從來沒用過，要在哪下 SQL 指令，還沒看到..... 也就是說，我還有得爬..... 嗚～～～

至於你提到的【自動異地備份】我非常有興趣！！暫時免【異地備份】，我只求【異機備份】，從 Mac 備份到 Windows Server 裡。

呃～～我目前暫時只有一部 Mac server，所以只好把資料丟 Windows.... 不過這樣子應該 SSH 就行不通了吧？？

不然就要丟到 iMac -233，可是 233 老了，不能一直開機，我都讓他睡覺，所以備份前還要先網路喚醒（WOL），這只怕也有點麻煩....

你的【搭配 scp 和 ssh-keygen】一句話，我可能要爬一年功，才能有這種功力...... 嗚～～

證明【棋靈王】確實是不如【魔戒王】..... 嗚嗚嗚～～～

jamesjan · #16 文章由 **jamesjan** » 08/30/2005 9:44 pm

no.......你當初在建立db的時候,他就已經用Latin1了,所以當你現在有資料的時候,你去改成UTF-8,這是一定會亂掉的阿~~

用mysqldump,加上 –default-character-set=latin1 的參數試試
這樣應該就可以備份成功

至於restore回去........
那就要看你的DB是Latin1,還是UTF-8了

如果是UTF-8,那就要把當初備份出來的檔案用iconv轉成UTF-8
然後在倒進去

進藤光 · #17 文章由 **進藤光** » 08/31/2005 1:33 am

janusng

先謝謝你囉～～
雖然你用的是 Windows 版，不過 phpMyAdmin 程式版本應該都一樣啊....

我就是沒有底下畫面上，紅圈部分的編碼選擇，請參考我貼的畫面。
當然用 MySQL Admin 來備份，同樣沒有編碼方式可選。

請問，有可能是哪裡出錯了嗎？？

janusng 寫:如前述般，有多個設定要弄清楚，包括原本的 mysqld 和新的 mysqld 也要無誤，才能正確匯出和匯入。請留以圖片的下的紅圈部分：

進藤光 · #18 文章由 **進藤光** » 08/31/2005 11:42 am

問題解決了！非常感謝各位室友協助！謝謝囉～～～

我在 MySQL Admin 找不到可以執行 SQL 指令的地方，新增資料庫時，也無法指定語系.....

乾脆用 phpMyAdmin 來做！

1. 先在 phpMyAdmin 新增空白資料庫，這時就可以設定使用 utf8_unicode_ci 語系！！

2. 接著再重裝 phpBB for UTF-8 版本，貼個日文試試看

3. 備份 phpBB 資料庫....

4. 把整個資料庫刪掉，再用 Restore 回復看看，哇啊～～正常了！可以看到完整的日文內容！

也就是說我的資料庫 + phpBB 通通是 UTF-8 囉～～
歡迎參觀【全 UTF-8 的 phpBB】：
http://www.bnw.com.tw/forum/index.php

不過風格【Macinscott 2】還沒有改 UTF-8，仍然使用 BIG5，目前正在修改中.....

要注意的是 XOOPS 就不能用【utf8_unicode_ci】而必須改用
【utf8_general_ci】！！

【utf8_unicode_ci】跟【utf8_general_ci】有啥差別？我不知道，用 Google 找不到【utf8_general_ci】資料....

我重裝了好幾次，也試過 XOOPS 2.2.2，都會發生裝好之後，根本不能登入，也沒有登入畫面，當然其他功能也通通不能用....

最後嘗試改用【utf8_general_ci】就 OK 了！
要裝 XOOPS 的【阿扁】與其他網友，請注意這點囉～～
架好的空白 XOOPS 也歡迎參觀：
http://www.bnw.com.tw/xoops2/modules/news/

janusng · #19 文章由 **janusng** » 08/31/2005 12:15 pm

一、光少的眼睛需要檢驗。怎樣看，小弟的快照也是 OS X 的吧？雖然不是 Safari，而是 Firefox。

二、光少真的確認安裝了 MySQL 4.1？這些 options 在 MySQL 3/4.0 是沒有的。

三、MySQL 的設定，自然是到 MySQL 網站去找。
http://dev.mysql.com/doc/mysql/en/chars ... -sets.html

四、phpMySQL 還需要安裝一個 database 到 MySQL 來協助，請自行按照 help 來執行。

ulysses · #20 文章由 **ulysses** » 08/31/2005 1:23 pm

MySQL 的 Client 自然就是 bin/mysql，那其實只是一個用 Socket 連線的 Client 程式，用裝在 Windows 上的 Client 也一樣可以連線。當然用 phpMyAdmin 也是可以啦...只是到後來在下越來越少用 phpMyAdmin...

另外 MySQL 是用自定 Repository 檔案來存放所有資料，一個 DB 兩個資料檔。資料檔名就是 DB 名稱加 Suffix 很好辨認。所以備份的時候只要備份那兩個資料檔就 OK。

所謂遠端備份云云就只是很簡單的把檔案複製到另一台機器上去。傳檔的方法很多，但是不論用 FTP 還是 SAMBA，都需要人工介入操作。用 SSH 的好處是可以使用自動認證，適合放在 CRONTAB 中定時自動執行。具體做法是：

條件：使用者在 server 端和 client 端都有同樣的帳號
在 client 端用 `ssh-keygen -t rsa' 產生 RSA key，會放在 $HOME/.ssh/id_rsa 和 $HOME/.ssh/id_rsa.pub 兩個目錄中。
把 id_rsa.pub 的內容複製到 server 端使用者的 $HOME/.ssh/authorized_keys 檔，新增一行。
SSH Copy 的指令是 scp -qr {srcfiles} {$USER@$IP:/destdir}