Linux 下将 MySQL 编码转换至 UTF-8 的实例
来源:互联网 发布:上睑松弛知乎 编辑:程序博客网 时间:2024/05/20 21:45
Converting MySQL Database Contents to UTF-8
Voici comment j'ai converti le contenu de ma base de données WordPress en UTF-8. C'est assez simple en soi, mais ?a m'a pris longtemps pour comprendre comment le faire!
A few weeks ago, I discovered (to my horror) that my site was not UTF-8, as I thought it was. Checking my pages in the validator produced this error:
The character encoding specified in the HTTP header (iso-8859-1) is different from the value in the XML declaration (utf-8). I will use the value from the HTTP header (iso-8859-1).
In all likeliness, my server adds a default header (iso-8859-1) to the pages it serves. When I switched to WordPress, I was careful to save all my import files as UTF-8, and I honestly thought that everything I had imported into the database was UTF-8. Somewhere in the process, it got switched back to iso-8859-1 (latin-1).
The solution to make sure the pages served are indeed UTF-8, as specified in the meta tags of my HTML pages, is to add the following line to .htaccess
:
AddDefaultCharset OFF
(If one wanted to force UTF-8, AddDefaultCharset UTF-8
would do it, but actually, it’s better to leave the possibility to serve pages with different encodings, isn’t it?)
Now, when I did that, of course, all the accented characters in my site went beserk — proof if it was needed that my database content was not UTF-8. Here is the story of what I went through (and it took many days to find the solution, believe me, although it takes only 2 minutes to do once everything is ready) to convert my database content from ISO-8859-1 to UTF-8. Thanks a lot to all those who helped me through this — and they are many!
First thing, dump the database. I always forget the command for dumps, so here it is:
mysqldump --opt -u root -p wordpress > wordpress.sql
As we’re going to be doing stuff, it might be wise to make a copy of the working wordpress database. I did that by creating a new database in PhpMyAdmin, and importing my freshly dumped database into it:
mysql -u root -p wordpress-backup < wordpress.sql
Then, conversion. I tried a PHP script, I tried BBEdit, and they seemed to mess up. (Though as I had other issues elsewhere, they may well have worked but I mistakenly thought the problem was coming from there.) Anyway, command-line conversion with iconv is much easier to do:
iconv -f iso-8859-15 -t utf8 wordpress.sql > wordpress-iconv.sql
Then, import into the database. I first imported it into another database, edited wp-config.php
to point to the new database, and checked that everything was ok:
mysql -u root -p wordpress-utf8 < wordpress-iconv.sql
Once I was happy that it was working, I imported my converted dump into the WordPress production database:
mysql -u root -p wordpress < wordpress-iconv.sql
On the way there, I had some trouble with MySQL. The MySQL dump more or less put the content of all my weblog posts on one line. For some reason, it didn’t cause any problems when importing the dump before conversion, to create the backup database, but it didn’t play nice after conversion.
I got this error when trying to import:
ERROR 1153 at line 378: Got a packet bigger than 'max_allowed_packet'
Line 378 contained half my weblog posts… and was obviously bigger than the 1Mb limit for max_allowed_packet
(the whole dump is around 2Mb).
I had to edit my.cnf
(/etc/mysql/my.cnf
on my system) and change the value for max_allowed_packet
in the section titled [mysqld]
. I set it to 8Mb. Then, I had to stop mysql and restart it: mysqladmin -u root -p shutdown
to stop it, and mysqld_safe &
to start it again (as root).
This is not necessarily the best way to do it, and it might not work like that on your system, but it’s what I did and the site is now back up again. Comments welcome, and hope this can be useful to others!
- Linux 下将 MySQL 编码转换至 UTF-8 的实例
- Linux下批量转换到utf-8编码的问题
- 将windows下的GBK编码格式文件转化为Linux下UTF-8编码格式文件
- GB2312编码与UTF-8编码的相互转换代码(linux windows下通用)
- linux 下URL中 UTF-8编码、GB2312编码与汉字之间的转换
- 将字符串的编码格式转换为utf-8
- Linux下GB2312编码转换UTF-8方法
- linux下GBK->UTF-8文件编码批量转换脚本
- linux下GBK->UTF-8文件编码批量转换脚本
- Linux下GBK->UTF-8文件编码批量转换命令
- linux 下 GBK-UTF-8文件编码批量转换脚本
- Linux下GBK文件编码批量转换UTF-8命令
- 将文件字符编码方式从 windows 的 gb18030 转 linux 下的 utf-8
- 将字符串转换为UTF-8编码
- linux批量转换整个目录下的文件编码为UTF-8
- Linux下Mysql编码修改为UTF-8
- linux下更改MySQL数据库编码格式为utf-8
- UTF-8编码的转换
- 计划: 把 Google 和 Yahoo 的分类目录给盗取过来.^^
- 在Web页中使用Media Player
- 连想都不要想
- 新浪网中的一角 (关于陈氏的言论)
- 《Undocumented Windows 2000 Secrets》翻译 --- 第四章(8)
- Linux 下将 MySQL 编码转换至 UTF-8 的实例
- 《Undocumented Windows 2000 Secrets》翻译 --- 第四章(9)
- MySQL 编码转换 UTF-8 方法 (WINDOWS)
- 元宵节的由来
- C# to VB.Net translator..
- 博客中国2004中文非主流网站100强
- 三年后的又一次面试
- Hello Blog!
- 盐吃的太多,容易长皱纹