mysql库编码修改，库默认的编码为拉丁文，我创建的agilefant库编码也就是拉丁文的 不支持中文。
整个操作过程切记务必先备份库再操作。
mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
首先vi /etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /wwwroot/mysql
default-character-set=utf8
skip-name-resolve
default-character-set=utf8(这句）
再次：
mysql> show variables like 'collation_%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 18
Current database: agilefant
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
再次：
mysqldump -u -p --default-character-set=utf8 -d agilefant > createtables.sql
再次：
mysqldump -u -p --quick --no-create-info --extended-insert --default-character-set=latin1 agilefant > data.sql （只导出了结构）
再把 sed -i 's/latin1/utf8/g' createtables.sql
sed -i 's/latin1/utf8/g' data.sql
再导入结构再导入数据即可。
网上还看到另外种做法：
mysqldump 的时候加上–compatible=mysql40 参数，就可以指定编码了
mysqldump -uroot -proot --compatible=mysql40 --default-character-set=utf8 d5s_data > d5s.sql
整个操作过程切记务必先备份库再操作。
mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
首先vi /etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /wwwroot/mysql
default-character-set=utf8
skip-name-resolve
default-character-set=utf8(这句）
再次：
mysql> show variables like 'collation_%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 18
Current database: agilefant
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
再次：
mysqldump -u -p --default-character-set=utf8 -d agilefant > createtables.sql
再次：
mysqldump -u -p --quick --no-create-info --extended-insert --default-character-set=latin1 agilefant > data.sql （只导出了结构）
再把 sed -i 's/latin1/utf8/g' createtables.sql
sed -i 's/latin1/utf8/g' data.sql
再导入结构再导入数据即可。
网上还看到另外种做法：
mysqldump 的时候加上–compatible=mysql40 参数，就可以指定编码了
mysqldump -uroot -proot --compatible=mysql40 --default-character-set=utf8 d5s_data > d5s.sql
本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/456533
