从A主机备份到B主机
- mysqldump -uroot -p vw>vw.sql
- 现备份数据库文件，需要恢复到目标机B，B的数据库版本为5.5.23，A机器的mysql版本为5.0.22
1. mysql>source /root/vw.sql;
2. ……………………………………………………
3. Query OK, 6748 rows affected (0.13 sec)
4. Records: 6748 Duplicates: 0 Warnings: 0
5. Query OK, 6807 rows affected (0.12 sec)
6. Records: 6807 Duplicates: 0 Warnings: 0
7. Query OK, 6752 rows affected (0.13 sec)
8. Records: 6752 Duplicates: 0 Warnings: 0
9. Query OK, 6659 rows affected (0.13 sec)
10. Records: 6659 Duplicates: 0 Warnings: 0
11. Query OK, 6676 rows affected (0.13 sec)
12. Records: 6676 Duplicates: 0 Warnings: 0
13. Query OK, 6583 rows affected (0.12 sec)
14. Records: 6583 Duplicates: 0 Warnings: 0
15. Query OK, 6598 rows affected (0.13 sec)
16. Records: 6598 Duplicates: 0 Warnings: 0
17. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
18. mysql>在B机器上面导入的时候出现以上错误，排除数据库本身问题，后来通过navicat工具备份A的数据，再次导入机器B却成功了。
经过测试发现是字符集问题导致。解决办法如下
在A机器上面备份，使用如下命令，恢复到B机器，测试OK。
- mysqldump -uroot --default-character-set=utf8 -p vw>vw.sql
以下附mysql字符集查看以及修改命令,
一、查看 MySQL 数据库服务器和数据库字符集
1. mysql>
2. +--------------------------+----------------------------+
3. | Variable_name | Value |
4. +--------------------------+----------------------------+
5. | character_set_client | latin1 |
6. | character_set_connection | latin1 |
7. | character_set_database | latin1 |
8. | character_set_filesystem | binary |
9. | character_set_results | latin1 |
10. | character_set_server | latin1 |
11. | character_set_system | utf8 |
12. | character_sets_dir | /usr/share/mysql/charsets/ |
13. +--------------------------+----------------------------+
二、查看 MySQL 数据表（table） 的字符集。
1. mysql>
2. +---------+------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
3. | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
4. +---------+------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
5. | tid | mediumint(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
6. | tname | varchar(125) | utf8_general_ci | NO | MUL | NULL | | select,insert,update,references | |
7. | en-name | varchar(125) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
8. | type | tinyint(3) | NULL | NO | | NULL | | select,insert,update,references | |
9. +---------+------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
1. mysql>
2. +---------+------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
3. | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
4. +---------+------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
5. | tid | mediumint(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
6. | tname | varchar(125) | utf8_general_ci | NO | MUL | NULL | | select,insert,update,references | |
7. | en-name | varchar(125) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
8. | type | tinyint(3) | NULL | NO | | NULL | | select,insert,update,references | |
9. +---------+------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
1. mysql>show char set;
2. mysql>show charset;
3. +----------+-----------------------------+---------------------+--------+
4. | Charset | Description | Default collation | Maxlen |
5. +----------+-----------------------------+---------------------+--------+
6. | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
7. | dec8 | DEC West European | dec8_swedish_ci | 1 |
8. | cp850 | DOS West European | cp850_general_ci | 1 |
9. | hp8 | HP West European | hp8_english_ci | 1 |
10. | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
11. | latin1 | cp1252 West European | latin1_swedish_ci | 1 |
12. | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
13. | swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
14. | ascii | US ASCII | ascii_general_ci | 1 |
15. | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
16. | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
17. | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
18. | tis620 | TIS620 Thai | tis620_thai_ci | 1 |
19. | euckr | EUC-KR Korean | euckr_korean_ci | 2 |
20. | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
21. | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
22. | greek | ISO 8859-7 Greek | greek_general_ci | 1 |
23. | cp1250 | Windows Central European | cp1250_general_ci | 1 |
24. | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
25. | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
26. | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
27. | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
28. | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
29. | cp866 | DOS Russian | cp866_general_ci | 1 |
30. | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
31. | macce | Mac Central European | macce_general_ci | 1 |
32. | macroman | Mac West European | macroman_general_ci | 1 |
33. | cp852 | DOS Central European | cp852_general_ci | 1 |
34. | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
35. | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
36. | cp1256 | Windows Arabic | cp1256_general_ci | 1 |
37. | cp1257 | Windows Baltic | cp1257_general_ci | 1 |
38. | binary | Binary pseudo charset | binary | 1 |
39. | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
40. | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
41. | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
42. +----------+-----------------------------+---------------------+--------+
五，修改mysql默认字符设置
1. vi /etc/my.cnf
2. 在[client]下添加
3. default-character-set=utf8
4. 在[mysqld]下添加
5. default-character-set=utf8
6. 然后重启mysql
7. service mysqld restart
其他的一些设置方法：
修改数据库的字符集
- mysql>use mydb
- mysql>alter database mydb character set utf-8;
创建数据库指定数据库的字符集
- mysql>create database mydb character set utf-8;
通过配置文件修改:
修改/var/lib/mysql/mydb/db.opt
1. default-character-set=latin1
2. default-collation=latin1_swedish_ci
为
1. default-character-set=utf8
2. default-collation=utf8_general_ci
重起MySQL:
1. # /etc/rc.d/init.d/mysql restart
通过MySQL命令行修改:
1. mysql> set character_set_client=utf8;
2. mysql> set character_set_connection=utf8;
3. mysql> set character_set_database=utf8;
4. mysql> set character_set_results=utf8;
5. mysql> set character_set_server=utf8;
6. mysql> set character_set_system=utf8;
7. mysql> set collation_connection=utf8;)
8. mysql> set collation_database=utf8;
9. mysql> set collation_server=utf8;
1. mysql>
2. +---------------------------+---------------------------+
3. | Variable_name | Value |
4. +---------------------------+---------------------------+
5. | character_set_client | utf8 |
6. | character_set_connection | utf8 |
7. | character_set_database | utf8 |
8. | character_set_filesystem | binary |
9. | character_set_results | utf8 |
10. | character_set_server | utf8 |
11. | character_set_system | utf8 |
12. | character_sets_dir | /usr/share/mysql/charsets/|
13. +---------------------------+----------------------------+
14. mysql>
15. +----------------------+-----------------+
16. | Variable_name | Value |
17. +----------------------+-----------------+
18. | collation_connection | utf8_general_ci |
19. | collation_database | utf8_general_ci |
20. | collation_server | utf8_general_ci |
21. +----------------------+-----------------+MYSQL 字符集问题
MySQL的字符集支持(Character Set Support)有两个方面：
字符集(Character set)和排序方式(Collation)。
对于字符集的支持细化到四个层次:
服务器(server)，数据库(database)，数据表(table)和连接(connection)。
1.MySQL默认字符集
MySQL对于字符集的指定可以细化到一个数据库，一张表，一列，应该用什么字符集。但是，传统的程序在创建数据库和数据表时并没有使用那么复杂的配置，它们用的是默认的配置，那么，默认的配置从何而来呢？ (1)编译MySQL 时，指定了一个默认的字符集，这个字符集是 latin1；
(2)安装MySQL 时，可以在配置文件 (my.ini) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的；
(3)启动mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时 character_set_server 被设定为这个默认的字符集；
(4)当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character_set_server；
(5)当选定了一个数据库时，character_set_database 被设定为这个数据库默认的字符集；
(6)在这个数据库里创建一张表时，表默认的字符集被设定为 character_set_database，也就是这个数据库默认的字符集；
(7)当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集；
简单的总结一下，如果什么地方都不修改，那么所有的数据库的所有表的所有栏位的都用
latin1 存储，不过我们如果安装 MySQL，一般都会选择多语言支持，也就是说，安装程序会自动在配置文件中把
default_character_set 设置为 UTF-8，这保证了缺省情况下，所有的数据库的所有表的所有栏位的都用 UTF-8 存储。 参考文章：
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
