How to Properly Backup and Restore MySQL Database for Your Non-English Blog

January 18, 2008

I’ve been maintaining the website for our church and we use the WordPress blog script to display our meeting updates and other items. Since our blog is written in Simplified Chinese (GB2312), I had several problems restoring the MySQL database used by the blog and yet still display the Chinese characters right - if you go to one of our earliest posts, which has not been fixed for the problem, you know what I am talking about.

Basically some of the Chinese characters have been replaced by “?” (the question mark) and this makes certain phrases completely unrecognizable.

What were the causes for this problem? Here they are…

(1) The blog installed in the beginning used the charset of “latin1” and collation of “latin1_swedish_ci” in the blog’s MySQL database. But since version 2.2.x, WordPress uses “utf8” in MySQL database. To comply with this, I had to save the database in the non-compressed sql format and used a unicode-supported editor to replace “latin1″ with “utf8″ throughout the file. In several cases I used an editor that does not support unicode well to do the job, thus corrupted quite a few Chinese characters.

(2) The funny thing was, the problem still occurred even when no WordPress upgrade was carried out. It happened when I had no choice but to restore the database that was either saved or backed up from the control panel. For some reason, the MySQL database was not backed up properly by the server control panel - in this case the website is hosted on a server running DirectAdmin as the control panel. I tried one of the database backup plugins out there and still did not backup and restore properly.

What I found worked was the manual database backup using phpMyAdmin and then manual restore - again using phpMyAdmin. I have yet to find a way to do this automatically using a cron job on the server.

If you know a better solution to this, please let me know using the comment form below. Thanks!

Related posts brought to you by Yet Another Related Posts Plugin.

Category Posted in Miscellaneous

One Response to “How to Properly Backup and Restore MySQL Database for Your Non-English Blog”

Belial said:

May 19th, 2008 at 11:37 am

Hello!

I need more help whith one problem.
I create a program with php, this program use a database, ok.
I need one script to create a autobackup and restore from this dabatase.

I search in google more help, but, all is not good.
The people say my: use phpMyAdmin.

I don’t use phpMyAdmin, my users are old people, and they don’t understand so much the computers…

If anywhere can help me, i greeting so much.

My email is: belialnightmares@gmail.com

Thanks and sorry for my english, i’m spanish…